1 2 Previous Next 24 Replies Latest reply: Aug 30, 2012 11:56 PM by Ashu_Neo RSS

    Performance Tuning Scope of study for a PLSQL Developer

    Ashu_Neo
      Could anybody please help me out of finding specific topics(Points to cover in SQL/PLSQL part), that can be covered by a PLSQL developer instead of covering all chapters? As Per performance tuning is full chapter and mainly a part of DBA study and I need to choose specific areas and it's not possible to digest all oracle stuffs in one go!
        • 1. Re: Performance Tuning Scope of study for a PLSQL Developer
          Nikolay Savvinov
          Hi,

          1) performance tuning is primarily responsibility of application developers, not DBAs
          2) start with indexes, reading plans, optimizer math (cardinality & selectivity), trace files
          3) recommended reading:
          Optimizing Oracle Performance by Milsap & Holt (classics)
          Troubleshooting Oracle Performance by Antognini (covers 11g)
          Cost-Based Fundamentals by Lewis (an insight into optimizer internals, a must-read)

          Best regards,
          Nikolay
          • 2. Re: Performance Tuning Scope of study for a PLSQL Developer
            Karthick_Arp
            Nikolay Savvinov wrote:
            Hi,

            1) performance tuning is primarily responsibility of application developers, not DBAs
            Thats what BAD DBAs say ;)
            • 3. Re: Performance Tuning Scope of study for a PLSQL Developer
              sybrand_b
              No. They don't.
              It is a fact most developers do not tune anything at all, where they should, because they don't know how to do it.
              After development the 'application' is thrown over the fence, and it is suddenly the problem of DBA, who can not do anything.

              ----------
              Sybrand Bakker
              Senior Oracle DBA
              • 4. Re: Performance Tuning Scope of study for a PLSQL Developer
                Nikolay Savvinov
                Hi,
                Karthick_Arp wrote:
                Nikolay Savvinov wrote:
                Hi,

                1) performance tuning is primarily responsibility of application developers, not DBAs
                Thats what BAD DBAs say ;)
                That's also what some Oracle ACE Directors (e.g. Randolf Geist) say.

                Best regards,
                Nikolay
                • 5. Re: Performance Tuning Scope of study for a PLSQL Developer
                  Nikolay Savvinov
                  Hi,
                  sybrand_b wrote:
                  No. They don't.
                  It is a fact most developers do not tune anything at all, where they should, because they don't know how to do it.
                  well one of common reasons for that is that DBAs don't give them tools to tune anything.
                  E.g. I heard a senior DBA in a major company claim that all a developer needs to tune his code is explain plan -- that's quite a common
                  attitude in the DBA community.

                  Best regards,
                  Nikolay
                  • 6. Re: Performance Tuning Scope of study for a PLSQL Developer
                    Etbin
                    IMO Database Tuning is a perfect example of a hot potato neither side is willing to hold for too long.
                    And is getting more and more convoluted.
                    It's not Oracle assigning responsibilities to one side rather than to the other (SQL Plan Management, DBMS_ADVISOR, ...) inappropriately, it's both sides attitude I-know-the-one-who-will-not-do-it accepting we are on the same boat only when it seems convenient.

                    Regards

                    Etbin
                    • 7. Re: Performance Tuning Scope of study for a PLSQL Developer
                      Karthick_Arp
                      sybrand_b wrote:
                      No. They don't.
                      It is a fact most developers do not tune anything at all, where they should, because they don't know how to do it.
                      After development the 'application' is thrown over the fence, and it is suddenly the problem of DBA, who can not do anything.

                      ----------
                      Sybrand Bakker
                      Senior Oracle DBA
                      When a house is on fire i have seen DBAs (Not all of them) try to find who did it rather than puting the fire down. All of the time its pointed towards the Developer.

                      A fine DBA who know End-to-End about the database will be reasonable. But the sad thing is to find a DBA at that caliber is very hard these days.

                      DBA is not there for Just Backing up and Recovering the DB. They have more responsibility. It has always been pleasing environment where a DBA shows active participation in the development activities. A DBA who shows his involvement makes sure a Developer is provided with adequate resource to perform his work. But think how many of them are like that?
                      • 8. Re: Performance Tuning Scope of study for a PLSQL Developer
                        sybrand_b
                        The problem usually arises with 3rd party developed 'applications' where developers don't have an inhouse DBA and refuse to cooperate with customer DBA's.
                        I have been in that situation way too many times. Usually I didn't manage to have them change anything, because they always stated it is the database.

                        -----------
                        Sybrand Bakker
                        Senior Oracle DBA
                        • 9. Re: Performance Tuning Scope of study for a PLSQL Developer
                          sybrand_b
                          Usually DBA doesn't have any responsibility during development. partly because the 'application' is bought, partly because the developing firm doesn't think it is required.

                          ----------
                          Sybrand Bakker
                          Senior Oracle DBA
                          • 10. Re: Performance Tuning Scope of study for a PLSQL Developer
                            Carlovski
                            I don't quite get this them vs us mentality of Developers vs DBAs. Surely you are supposed to be working for the same company? Apart from Sybrand's comment that it is difficult when dealing with third party developers of course.
                            The job of application tuning, like the job of doing anything to improve an application isn't restricted to one person or one role, but the whole team surely? The product manager/lead developer/technical lead - whoever is taking responsibility for the quality of the product should be taking a lead and making sure that the most suitable people are looking at the problem.

                            And back to the OP's question - you don't want to pigeonhole yourself into just knowing PL/SQL. You need to at least understand the DBA side, know a bit about the operating system, the hardware. Are there many people these days who are just a 'PL/SQL' developer anyway? Would make my life easier if I was - I pick up bits of database programming for light relief these days!
                            • 11. Re: Performance Tuning Scope of study for a PLSQL Developer
                              Ashu_Neo
                              See, Its not like that. It depends upon the environment you got in your IT experience. Data Modeling/SQL/PLSQL/ Result Set as Reports even not an easy task. And DBA is even comes in different track with difficult exercises.

                              By the way "See everybody finds other's work easy" .. :-)

                              Being a good plsql developer, I just wants to know, where are the important area where we can do performance tuning before writing a SQL or programed SQLs. So that I can have a list of missing topics that i can learn from my side. And I want to be in business side not in DBA role in my career path. :-)

                              Let me know in points.( IF any).
                              • 12. Re: Performance Tuning Scope of study for a PLSQL Developer
                                Ashu_Neo
                                By mistake I made it answered. But still it's unconvincing with lots of random discussion!
                                • 13. Re: Performance Tuning Scope of study for a PLSQL Developer
                                  Carlovski
                                  No, these things aren't necessarily easy (Although on the scale of things, not that hard either!).
                                  The issue is, you can either learn a whole set of 'rules' about what is good and what is bad, or you can try and understand why. Learning a bunch of rules may be quicker, but it is very easy to learn something that isn't true, or is out of date(There are lots of such 'rules' in oracle that some people still follow - separate indexes and tables to improve performance, select count(1) is quicker than count(*) etc) . Learn why and you can recognise when to question a rule of thumb, or to identify why the usual rules are not relevant in your situation.
                                  But to learn 'why' you need to have a more broad understanding of your field.

                                  But if you want rules to follow - which are never likely to go out of date

                                  - If you can do it in SQL, do it in SQL
                                  - Do as few logical reads as you can
                                  • 14. Re: Performance Tuning Scope of study for a PLSQL Developer
                                    Ashu_Neo
                                    :-)... See I got your points. But I know a check list by what I can have a basic knowledge of performance tuning.

                                    Like -: (What I know) 1) Better way of writting sql with proper join condition ( like indexed column). 2) Use if EXISTS in sub-query 3) Order of Joining tables(table having lesser records should come first in join statement) 4) Use of hints for SQL Patching 5) Avoid using inner blocks 6) Use collection type instead of Cursors 7) Use to write dynamic sql with execute immediate for making generic your business logic 8) Use of explain plan and check for cost based optimization to reduce unnecessary joins.. ect.

                                    So I just ask you to add some more points or topics ( note down in points please). Like, if somebody asks me, "Do you know performance tuning? ", then I can able to explain him/her in addition of more knowledge. .. :-)

                                    That's all I really want to say.

                                    Thanks..

                                    Edited by: Ashu_Neo on Aug 30, 2012 4:02 PM
                                    1 2 Previous Next