1 2 Previous Next 29 Replies Latest reply: Jul 24, 2013 10:41 AM by Shidu RSS

    HOW TO: Post a SQL statement tuning request - template posting

    Randolf Geist
      This post is not a question, but similar to Rob van Wijk's "When your query takes too long ..." post should help to improve the quality of the requests for SQL statement tuning here on OTN.

      On the OTN forum very often tuning requests about single SQL statements are posted, but the information provided is rather limited, and therefore it's not that simple to provide a meaningful advice. Instead of writing the same requests for additional information over and over again I thought I put together a post that describes how a "useful" post for such a request should look like and what information it should cover.

      I've also prepared very detailed step-by-step instructions how to obtain that information on my blog, which can be used to easily gather the required information. It also covers again the details how to post the information properly here, in particular how to use the \
       tag to preserve formatting and get a fixed font output:
      
      http://oracle-randolf.blogspot.com/2009/02/basic-sql-statement-performance.html
      
      So again: This post here describes how a "useful" post should look like and what information it ideally covers. The blog post explains in detail how to obtain that information.
      
      In the future, rather than requesting the same additional information and explaining how to obtain it, I'll simply refer to this HOW TO post and the corresponding blog post which describes in detail how to get that information.
      
      *Very important:*
      
      Use the \
      tag to enclose any output that should have its formatting preserved as shown below.

      So if you want to use fixed font formatting that preserves the spaces etc., do the following:

      \
         This preserves formatting
         . . .
      \
      And it will look like this:
         This preserves formatting
         . . .
      Your post should cover the following information:

      1. The SQL and a short description of its purpose
      2. The version of your database with 4-digits (e.g. 10.2.0.4)
      3. Optimizer related parameters
      4. The TIMING and AUTOTRACE output
      5. The EXPLAIN PLAN output
      6. The TKPROF output snippet that corresponds to your statement
      7. If you're on 10g or later, the DBMS_XPLAN.DISPLAY_CURSOR output

      The above mentioned blog post describes in detail how to obtain that information.

      Your post should have a meaningful subject, e.g. "SQL statement tuning request", and the message body should look similar to the following:

      *-- Start of template body --*

      The following SQL statement has been identified to perform poorly. It currently takes up to 10 seconds to execute, but it's supposed to take a second at most.

      This is the statement:
      select
               *
      from
               t_demo
      where
               type = 'VIEW'
      order by
               id;
      It should return data from a table in a specific order.

      The version of the database is 11.1.0.7.

      These are the parameters relevant to the optimizer:
      SQL> 
      SQL> show parameter optimizer
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      optimizer_capture_sql_plan_baselines boolean     FALSE
      optimizer_dynamic_sampling           integer     2
      optimizer_features_enable            string      11.1.0.7
      optimizer_index_caching              integer     0
      optimizer_index_cost_adj             integer     100
      optimizer_mode                       string      ALL_ROWS
      optimizer_secure_view_merging        boolean     TRUE
      optimizer_use_invisible_indexes      boolean     FALSE
      optimizer_use_pending_statistics     boolean     FALSE
      optimizer_use_sql_plan_baselines     boolean     TRUE
      SQL> 
      SQL> show parameter db_file_multi
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      db_file_multiblock_read_count        integer     8
      SQL> 
      SQL> show parameter db_block_size
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      db_block_size                        integer     8192
      SQL> 
      SQL> show parameter cursor_sharing
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      cursor_sharing                       string      EXACT
      SQL>
      SQL> column sname format a20
      SQL> column pname format a20
      SQL> column pval2 format a20
      SQL> 
      SQL> select
        2             sname
        3           , pname
        4           , pval1
        5           , pval2
        6  from
        7           sys.aux_stats$;
      
      SNAME                PNAME                     PVAL1 PVAL2
      -------------------- -------------------- ---------- --------------------
      SYSSTATS_INFO        STATUS                          COMPLETED
      SYSSTATS_INFO        DSTART                          01-30-2009 16:25
      SYSSTATS_INFO        DSTOP                           01-30-2009 16:25
      SYSSTATS_INFO        FLAGS                         0
      SYSSTATS_MAIN        CPUSPEEDNW              494,397
      SYSSTATS_MAIN        IOSEEKTIM                    10
      SYSSTATS_MAIN        IOTFRSPEED                 4096
      SYSSTATS_MAIN        SREADTIM
      SYSSTATS_MAIN        MREADTIM
      SYSSTATS_MAIN        CPUSPEED
      SYSSTATS_MAIN        MBRC
      SYSSTATS_MAIN        MAXTHR
      SYSSTATS_MAIN        SLAVETHR
      
      13 rows selected.
      Here is the output of EXPLAIN PLAN:
      SQL> explain plan for
        2  -- put your statement here
        3  select
        4             *
        5  from
        6             t_demo
        7  where
        8             type = 'VIEW'
        9  order by
       10             id;
      
      Explained.
      
      Elapsed: 00:00:00.01
      SQL> 
      SQL> select * from table(dbms_xplan.display);
      
      PLAN_TABLE_OUTPUT
      ----------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 1390505571
      
      ----------------------------------------------------------------------------------------
      | Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT            |          |     1 |    60 |     0   (0)| 00:00:01 |
      |   1 |  TABLE ACCESS BY INDEX ROWID| T_DEMO   |     1 |    60 |     0   (0)| 00:00:01 |
      |*  2 |   INDEX RANGE SCAN          | IDX_DEMO |     1 |       |     0   (0)| 00:00:01 |
      ----------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("TYPE"='VIEW')
      
      14 rows selected.
      Here is the output of SQL*Plus AUTOTRACE including the TIMING information:
      SQL> rem Set the ARRAYSIZE according to your application
      SQL> set autotrace traceonly arraysize 100
      
      SQL> select
        2             *
        3  from
        4             t_demo
        5  where
        6             type = 'VIEW'
        7  order by
        8             id;
      
      149938 rows selected.
      
      Elapsed: 00:00:02.21
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 1390505571
      
      ----------------------------------------------------------------------------------------
      | Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT            |          |     1 |    60 |     0   (0)| 00:00:01 |
      |   1 |  TABLE ACCESS BY INDEX ROWID| T_DEMO   |     1 |    60 |     0   (0)| 00:00:01 |
      |*  2 |   INDEX RANGE SCAN          | IDX_DEMO |     1 |       |     0   (0)| 00:00:01 |
      ----------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("TYPE"='VIEW')
      
      
      Statistics
      ----------------------------------------------------------
                0  recursive calls
                0  db block gets
           149101  consistent gets
              800  physical reads
              196  redo size
          1077830  bytes sent via SQL*Net to client
            16905  bytes received via SQL*Net from client
             1501  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
           149938  rows processed
      
      SQL> 
      SQL> disconnect
      Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      The TKPROF output for this statement looks like the following:
      TKPROF: Release 11.1.0.7.0 - Production on Mo Feb 23 10:23:08 2009
      
      Copyright (c) 1982, 2007, Oracle.  All rights reserved.
      
      Trace file: orcl11_ora_3376_mytrace1.trc
      Sort options: default
      
      ********************************************************************************
      count    = number of times OCI procedure was executed
      cpu      = cpu time in seconds executing 
      elapsed  = elapsed time in seconds executing
      disk     = number of physical reads of buffers from disk
      query    = number of buffers gotten for consistent read
      current  = number of buffers gotten in current mode (usually for update)
      rows     = number of rows processed by the fetch or execute call
      ********************************************************************************
      
      select
               *
      from
               t_demo
      where
               type = 'VIEW'
      order by
               id
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        1      0.00       0.00          0          0          0           0
      Execute      1      0.00       0.00          0          0          0           0
      Fetch     1501      0.53       1.36        800     149101          0      149938
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total     1503      0.53       1.36        800     149101          0      149938
      
      Misses in library cache during parse: 0
      Optimizer mode: ALL_ROWS
      Parsing user id: 88  
      
      Rows     Row Source Operation
      -------  ---------------------------------------------------
       149938  TABLE ACCESS BY INDEX ROWID T_DEMO (cr=149101 pr=800 pw=0 time=60042 us cost=0 size=60 card=1)
       149938   INDEX RANGE SCAN IDX_DEMO (cr=1881 pr=1 pw=0 time=0 us cost=0 size=0 card=1)(object id 74895)
      
      
      Elapsed times include waiting on following events:
        Event waited on                             Times   Max. Wait  Total Waited
        ----------------------------------------   Waited  ----------  ------------
        SQL*Net message to client                    1501        0.00          0.00
        db file sequential read                       800        0.05          0.80
        SQL*Net message from client                  1501        0.00          0.69
      ********************************************************************************
      The DBMS_XPLAN.DISPLAY_CURSOR output:
      SQL> -- put your statement here
      SQL> -- use the GATHER_PLAN_STATISTICS hint
      SQL> -- if you're not using STATISTICS_LEVEL = ALL
      SQL> select /*+ gather_plan_statistics */
        2  *
        3  from
        4  t_demo
        5  where
        6  type = 'VIEW'
        7  order by
        8  id;
      
      149938 rows selected.
      
      Elapsed: 00:00:02.21
      
      SQL> 
      SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
      
      PLAN_TABLE_OUTPUT
      ----------------------------------------------------------------------------------------------------------------------------------
      SQL_ID  d4k5acu783vu8, child number 0
      -------------------------------------
      select   /*+ gather_plan_statistics */          * from          t_demo
      where          type = 'VIEW' order by          id
      
      Plan hash value: 1390505571
      
      -----------------------------------------------------------------------------------------------------------
      | Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
      -----------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT            |          |      1 |        |    149K|00:00:00.02 |     149K|   1183 |
      |   1 |  TABLE ACCESS BY INDEX ROWID| T_DEMO   |      1 |      1 |    149K|00:00:00.02 |     149K|   1183 |
      |*  2 |   INDEX RANGE SCAN          | IDX_DEMO |      1 |      1 |    149K|00:00:00.02 |    1880 |    383 |
      -----------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("TYPE"='VIEW')
      
      
      20 rows selected.
      I'm looking forward for suggestions how to improve the performance of this statement.

      *-- End of template body --*

      I'm sure that if you follow these instructions and obtain the information described, post them using a proper formatting (don't forget about the \
       tag) you'll receive meaningful advice very soon.
      
      So, just to make sure you didn't miss this point:
      Use proper formatting!
      If you think I missed something important in this sample post let me know so that I can improve it.
      
      Regards,
      Randolf
      
      Oracle related stuff blog:
      http://oracle-randolf.blogspot.com/
      
      SQLTools++ for Oracle (Open source Oracle GUI for Windows):
      http://www.sqltools-plusplus.org:7676/
      http://sourceforge.net/projects/sqlt-pp/                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
        • 1. Re: HOW TO: Post a SQL statement tuning request - template posting
          21205
          ...you missed the proper formatting of the Autotrace section ;-)
          • 2. Re: HOW TO: Post a SQL statement tuning request - template posting
            Satyaki_De
            Excellent.

            That must have solved our repeated request to op regarding the proper post of any thread.

            Regards.

            Satyaki De.
            • 3. Re: HOW TO: Post a SQL statement tuning request - template posting
              BluShadow
              Satyaki_De wrote:
              Excellent.

              That must have solved our repeated request to op regarding the proper post of any thread.
              It won't but it'll give us another thread we can just link to in order to tell them to give enough information, rather than us type it all over and over again.

              One to add to my favourites methinks. ;)
              • 4. Re: HOW TO: Post a SQL statement tuning request - template posting
                Randolf Geist
                Alex Nuijten wrote:
                ...you missed the proper formatting of the Autotrace section ;-)
                Alex,

                can't reproduce, does it still look unformatted? Or are you simply kidding? :-)

                Randolf

                PS: Just noticed that it actually sometimes doesn't show the proper formatting although the code tags are there. Changing to the \
                 tag helped in this case, but it seems to be odd.
                
                Edited by: Randolf Geist on Feb 23, 2009 11:28 AM
                
                Odd behaviour of forum software                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                • 5. Re: HOW TO: Post a SQL statement tuning request - template posting
                  21205
                  no, wasn't kidding... but it looks fine now.

                  Great initiative!
                  • 6. Re: HOW TO: Post a SQL statement tuning request - template posting
                    Sven W.
                    Great post!

                    Should be made sticky to the first page.

                    I just miss the SET AUTOTRACE TRACEONLY command in the sql*plus output. But maybe I overlooked it.
                    • 7. Re: HOW TO: Post a SQL statement tuning request - template posting
                      Randolf Geist
                      Sven W. wrote:
                      Great post!

                      Should be made sticky to the first page.

                      I just miss the SET AUTOTRACE TRACEONLY command in the sql*plus output. But maybe I overlooked it.
                      Sven,

                      in order to keep this post as short as possible I've decided to write a very detailed accompanying blog post that instructs step-by-step how to obtain that information. There you'll find the AUTOTRACE option as part of the provided script.

                      The script provided in the blog post does a lot of things in as few steps as possible, it e.g. enables AUTOTRACE + extended tracing in a single step, but since I didn't want to make this post too lengthy and convoluted it's not yet mentioned here. But the suggestion is sound, I'll add it at least to the provided output in case someone is just reading this post.

                      This is meant to be merely a "template" post how an "ideal" statement tuning request post should look like. The blog post explains how to actually gather that information in detail.

                      Regards,
                      Randolf

                      Oracle related stuff blog:
                      http://oracle-randolf.blogspot.com/

                      SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                      http://www.sqltools-plusplus.org:7676/
                      http://sourceforge.net/projects/sqlt-pp/
                      • 8. Re: HOW TO: Post a SQL statement tuning request - template posting
                        Karthick_Arp
                        I have already started linking this thread to performance related questions :)
                        • 9. Re: HOW TO: Post a SQL statement tuning request - template posting
                          Randolf Geist
                          Karthick_Arp wrote:
                          I have already started linking this thread to performance related questions :)
                          Well, I guess that was the intention, or what do you mean by this comment?

                          Randolf
                          • 10. Re: HOW TO: Post a SQL statement tuning request - template posting
                            Karthick_Arp
                            The comment meant - Great job friend!!
                            • 11. Re: HOW TO: Post a SQL statement tuning request - template posting
                              Randolf Geist
                              Karthick_Arp wrote:
                              The comment meant - Great job friend!!
                              Karthick,

                              thanks for that kind comment, but to be honest I don't see (yet) any significant improvement in those posts requesting statement tuning help.

                              Even when provided with that link it seems that the users struggle to follow the instructions or it gets simply ignored most of the time.

                              May be people are simply too lazy to read through it. May be my description of the task is still too convoluted and complicated, I don't know.

                              It might be good to get some feedback from users that attempted to follow the thread and the accompanying blog post but weren't able to cope with it, so that it could be improved where necessary.

                              Regards,
                              Randolf

                              Oracle related stuff blog:
                              http://oracle-randolf.blogspot.com/

                              SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                              http://www.sqltools-plusplus.org:7676/
                              http://sourceforge.net/projects/sqlt-pp/
                              • 12. Re: HOW TO: Post a SQL statement tuning request - template posting
                                6363
                                May be people are simply too lazy to read through it.
                                There is that, at least most tuning questions aren't homework or exam questions, but many still seem to want a one line answer, such as +Did you check you don't have the database hidden parameter _fast = FALSE+ or Try adding the tune_query hint. A while back one poster who obviously didn't want to be bothered doing any research or understanding what the problem might be, actually asked for clarification on why the tune_query hint I suggested didn't seem to be having any effect.

                                Also these forums are not suitable for posting formatted technical information, as highlighted by the problems you faced getting the formatting of this post right. How can newcomers or those just looking for a quick answer be expected to get their formatting right, when it is impossible for experts to do so.

                                I had to remove the hint syntax from my references to the fake tune_query hint, as the forums removed some, but not all, of the asterisks and bolded the rest of the post. If the forum software only supported plain text, fixed font, it would be an improvement.
                                • 13. Re: HOW TO: Post a SQL statement tuning request - template posting
                                  685910
                                  How do you run the explain? I see an Explain tab in SQL Developer, but after I run the query there is nothing in there.
                                  I tried put the Explain plan for....prior to query. That just ended in end error stating Plan_table not found.
                                  Again, I new to SQL Developer and Oracle.
                                  Thanks
                                  • 14. Re: HOW TO: Post a SQL statement tuning request - template posting
                                    Randolf Geist
                                    strangermike wrote:
                                    How do you run the explain? I see an Explain tab in SQL Developer, but after I run the query there is nothing in there.
                                    I tried put the Explain plan for....prior to query. That just ended in end error stating Plan_table not found.
                                    Again, I new to SQL Developer and Oracle.
                                    You might want to open a new thread for your question (May be in the SQL Developer forum).

                                    You need to generate a PLAN_TABLE first if you're not on Oracle 10g already.

                                    On the Oracle server side in the directory $ORACLE_HOME/rdbms/admin there is a file called "utlxplan.sql" which generates a plan table.

                                    Regards,
                                    Randolf

                                    Oracle related stuff blog:
                                    http://oracle-randolf.blogspot.com/

                                    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                                    http://www.sqltools-plusplus.org:7676/
                                    http://sourceforge.net/projects/sqlt-pp/
                                    1 2 Previous Next