This discussion is archived
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 Oracle ACE Director
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    ...you missed the proper formatting of the Autotrace section ;-)
  • 2. Re: HOW TO: Post a SQL statement tuning request - template posting
    Satyaki_De Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    The comment meant - Great job friend!!
  • 11. Re: HOW TO: Post a SQL statement tuning request - template posting
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 (Not for general SQL/PLSQL questions) 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