1 2 Previous Next 16 Replies Latest reply: May 11, 2012 3:17 AM by Dom Brooks RSS

    Sql tuning

    819404
      Hi Guys,

      I am supporting a product in our organization where i have to tune the sql statements which are not performing well.

      Can anybody tell the methodologies how to tune the sql and also how to suggest the developer to write the sql queries.


      Or anybody know any good sql tuning books or sites to start with.



      Thanks in advance



      Prafull
        • 1. Re: Sql tuning
          Venkadesh Raja
          Hi Prafulla,

          Here are some very simple yet powerful SQL tips to remember

          Avoid using the following:
               Boolean operators >, <, >=, <=, is null, is not null

               Not in, !=

               Like '%pattern', not exists

               Calculations on unindexed columns or (use union instead)

               Having (use a WHERE clause instead)


          Do use the following:
               Enable aliases to prefix all columns

               Place indexed columns higher in the WHERE clause

               Use SQL Joins instead of using sub-queries

               Make the table with the least number of rows the driving table by making it first in the FROM clause

          Other important points for SQL Tuning
          Establish a tuning environment that reflects your production database

               Establish performance expectations before you begin

               Always Design and develop with performance in mind

               Create Indexes to support selective WHERE clauses and join conditions

               Use concatenated indexes where appropriate

               Consider indexing more than you think you should, to avoid table lookups

               Pick the best join method

               Nested loops joins are best for indexed joins of subsets

               Hash joins are usually the best choice for "big" joins

               Pick the best join order

               Pick the best "driving" table

               Eliminate rows as early as possible in the join order

               Use bind variables. Bind variables are key to application scalability

               Use Oracle hints where appropriate

               Compare performance between alternative syntax for your SQL statement

               Consider utilizing PL/SQL to overcome difficult SQL tuning issues

               Consider using third party tools to make the job of SQL tuning easier
          Thanks

          Venkadesh
          • 2. Re: Sql tuning
            Centinul
            Prafulla wrote:
            Hi Guys,

            I am supporting a product in our organization where i have to tune the sql statements which are not performing well.

            Can anybody tell the methodologies how to tune the sql and also how to suggest the developer to write the sql queries.
            Understanding Oracle thoroughly is a key thing in my opinion. I'd read the Oracle 11.2 Concepts Guide at http://tahiti.oracle.com

            Some other good resources are:

            {message:id=9360003}
            AskTom
            The Oracle Scratchpad
            Charle's Hooper Oracle Notes
            Cary Millsap
            • 3. Re: Sql tuning
              Billy~Verreynne
              I disagree with many points you posted. Some of those are a pretty naive approach to performance/SQL tuning.
              • 4. Re: Sql tuning
                SomeoneElse
                Venkadesh wrote:
                Hi Prafulla,

                Here are some very simple yet powerful SQL tips to remember

                Avoid using the following:
                     Boolean operators >, <, >=, <=, is null, is not null

                     Not in, !=

                     Like '%pattern', not exists

                     Calculations on unindexed columns or (use union instead)

                     Having (use a WHERE clause instead)


                Do use the following:
                     Enable aliases to prefix all columns

                     Place indexed columns higher in the WHERE clause

                     Use SQL Joins instead of using sub-queries

                     Make the table with the least number of rows the driving table by making it first in the FROM clause

                Other important points for SQL Tuning
                Establish a tuning environment that reflects your production database

                     Establish performance expectations before you begin

                     Always Design and develop with performance in mind

                     Create Indexes to support selective WHERE clauses and join conditions

                     Use concatenated indexes where appropriate

                     Consider indexing more than you think you should, to avoid table lookups

                     Pick the best join method

                     Nested loops joins are best for indexed joins of subsets

                     Hash joins are usually the best choice for "big" joins

                     Pick the best join order

                     Pick the best "driving" table

                     Eliminate rows as early as possible in the join order

                     Use bind variables. Bind variables are key to application scalability

                     Use Oracle hints where appropriate

                     Compare performance between alternative syntax for your SQL statement

                     Consider utilizing PL/SQL to overcome difficult SQL tuning issues

                     Consider using third party tools to make the job of SQL tuning easier
                Thanks

                Venkadesh
                Sorry to be blunt but this is bullshit.
                • 5. Re: Sql tuning
                  Paulie
                  >


                  Hi Prafulla,
                  Or anybody know any good sql tuning books or sites to start with.
                  As a first performance tuning book, I cannot recommend too highly Christopher Lawson's
                  The Art and Science of Oracle Performance Tuning.

                  It's actually written in a very readable style and covers the basics well.

                  You really should read Tom Kyte's books and work through them.

                  You won't be an expert overnight and you have lots of reading/study ahead of you.

                  Best of luck. HTH,


                  Paul...

                  Prafull
                  • 6. Re: Sql tuning
                    Venkadesh Raja
                    HI SomeoneElse and Billy,


                    I taken this from some website..

                    Sorry i forget that link otherwise i'll post that link also still searching..if i get it..surely i'll post it.


                    Got it : here the link http://it.toolbox.com/blogs/peoplesoft-pro/sql-tuning-tips-11775

                    Edited by: Venkadesh on May 10, 2012 8:41 AM
                    • 7. Re: Sql tuning
                      SomeoneElse
                      Sorry i forget that link otherwise i'll post that link also still searching..if i get it..surely i'll post it.
                      No hurry :-)
                      • 8. Re: Sql tuning
                        Himanshu Binjola
                        Hi SomeoneElse,

                        Sorry - But why do you think the SQL Tips are bullshit, I find them rather interesting.

                        The tips may not be applicable for every SQL but they sound pretty fine for general normal SQL

                        Note** - These are tips not techniques

                        Rgds,
                        Himanshu
                        • 9. Re: Sql tuning
                          Venkadesh Raja
                          Hi SomeoneElse


                          Can you please explain me the original steps :) .. i also want to learn this


                          Thanks

                          Venkadesh
                          • 10. Re: Sql tuning
                            696547
                            Whatever tuning experience I have, these smart tuning tips never helped.

                            Every tuning requirement is too specific to apply general tips, its far better to go through Oracle docs and understand how Oracle Optimizer works.

                            Consider these things while tuning:
                            1. DB environment parameters/Optimizer settings
                            2. Actual Explain Plan
                            3. Your data-set
                            4. Selectivity & Cardinality of each table involved
                            ...............etc

                            Still, please refer Oracle docs and develop your own methodology to performance tuning.

                            Regards,
                            Ankit Rathi
                            http://theoraclelog.blogspot.in/
                            • 11. Re: Sql tuning
                              Billy~Verreynne
                              Himanshu Binjola wrote:
                              Hi SomeoneElse,

                              Sorry - But why do you think the SQL Tips are bullshit, I find them rather interesting.
                              Well, if smelly is interesting, then I find them interesting too. ;-)

                              IMO they are not even worthwhile tips. There is no set of rules/tips that guarantees performance. Every single one of those "tips" have exceptions. Heck, some of them are in fact exceptions to the rule. And some are just plain nonsense.

                              I do not think it worthwhile discussing why the list is essentially bs. And if anyone want to dispute that statement, then please explain why the definitive basis for Oracle performance tuning, the Oracle® Database Performance Tuning Guide does not have anything remotely resembling such a list of (quoting from the blog source) "+simple yet powerful SQL tips+".

                              Want to discuss performance? Raise a specific performance issue - then we can have a meaningful discussion.
                              • 12. Re: Sql tuning
                                Billy~Verreynne
                                Ankit Rathi wrote:

                                Every tuning requirement is too specific to apply general tips, its far better to go through Oracle docs and understand how Oracle Optimizer works.
                                Well said.
                                • 13. Re: Sql tuning
                                  parikp
                                  you will need to do couple of things in order to find the root of the cause. the following steps will help you find out which statement is causing the issue and based on that you can make your decision or change your sql statement

                                  here is what i would do
                                  1. go to your db_home\rdbms\admin and find UTLXPLAN.sql. run this script in your database session, this will create a table( dont worry it wont affect your production environment)
                                  2. from sql plus or your database tool, run this command
                                  EXPLAIN PLAN set statement_id = 'test_sql' FOR
                                  ---your sql query here
                                  3. you will see explained when the execution is completed.
                                  4. now run this script or run individual command if you are using sql plus.
                                  set markup html preformat on
                                  set pagesize 200;
                                  Set line 150;
                                  select plan_table_output from table(dbms_xplan.display('plan_table','test_sql',''));
                                  5. this will show you the explain plan used by oracle to run your sql statement.
                                  analyze all the joins, filters, aggregates and see which one is taking most of the cpu time.

                                  change left outer join to join if you can
                                  also, try to find out the indexes on the tables used in your query.
                                  2 things-- if you have indexes on the tables then use them in the same column order in the where clause as they appear in the table.
                                  --if you dont have index then create the index on the columns that appears in your where clause.

                                  i would be able to help you analyze your sql statement if you can send me the output you have received in step 5 above.

                                  let me know how it works
                                  --parik                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                                  • 14. Re: Sql tuning
                                    SomeoneElse
                                    OK, here goes...of these "powerful tips" only these do I agree with completely:

                                    *Enable aliases to prefix all columns (for readability, has nothing to do with tuning)
                                    *Establish performance expectations before you begin
                                    *Always Design and develop with performance in mind
                                    *Use bind variables. Bind variables are key to application scalability
                                    *Establish a tuning environment that reflects your production database (this would be nice, but rarely have I seen it)

                                    These imply you are forcing the execution of a SQL statement and not letting the CBO do its work (or you are still using the RBO):

                                    *Place indexed columns higher in the WHERE clause
                                    *Make the table with the least number of rows the driving table by making it first in the FROM clause
                                    *Eliminate rows as early as possible in the join order
                                    *Pick the best join method
                                    *Nested loops joins are best for indexed joins of subsets
                                    *Hash joins are usually the best choice for "big" joins
                                    *Pick the best join order
                                    *Pick the best "driving" table
                                    *Use Oracle hints where appropriate (this one is a big red flag to me)

                                    These are vague or highly questionable and can be disproven by examples:

                                    *Create Indexes to support selective WHERE clauses and join conditions (possibly, maybe but not all indexes are good)
                                    *Use concatenated indexes where appropriate (uhhh...ok)
                                    *Use SQL Joins instead of using sub-queries (maybe, maybe not)
                                    *Consider indexing more than you think you should, to avoid table lookups (could be a big mistake, indexes are overhead)
                                    *Consider utilizing PL/SQL to overcome difficult SQL tuning issues (??? I'd like to see an example)
                                    *Consider using third party tools to make the job of SQL tuning easier (like what?  Oracle's own tools are quite powerful)

                                    And these are laughable:

                                    *Avoid using the following:
                                    *Boolean operators >, <, >=, <=, is null, is not null
                                    *Not in, !=
                                    *Like '%pattern', not exists
                                    *Having (use a WHERE clause instead)

                                    For crying out loud, these operators are a core part of the sql language. Saying "avoid them" is like saying "to prevent hay fever, avoid breathing".
                                    1 2 Previous Next