Monthly Archives: August 2013

SQL Tuning – Part 1.2 – Intro to DBMS_XPLAN.DISPLAY_CURSOR

In the last post, we looked at the DBMS_XPLAN package and the DISPLAY function. Now we will start to look at my much preferred DISPLAY_CURSOR function.

DBMS_XPLAN.DISPLAY_CURSOR –

The DISPLAY_CURSOR function has been around since 10g. It is a table function that displays the execution plans for any cursor that is currently in the cursor cache. There are 3 parameters (as of 12c):

DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id            IN  VARCHAR2  DEFAULT  NULL,
   cursor_child_no   IN  NUMBER    DEFAULT  0, 
   format            IN  VARCHAR2  DEFAULT  'TYPICAL'
);

The SQL_ID and CURSOR_CHILD_NO are available in system views like gv$sql. If null is passed for sql_id, it will use the last cursor executed by the session. If null is passed for the cursor_child_no, the execution plan of all cursors matching the supplied sql_id parameter are displayed. The format parameter controls the level of details displayed. If null is passed for the format, the value of TYPICAL is used. We will get more into what is available to be displayed by the format options later. The typical call to DISPLAY_CURSOR looks like:

select *
from table(dbms_xplan.display_cursor('b810y0qrnpgw5',0,'ADVANCED ALLSTATS'));

To me, the major benefit of DISPLAY_CURSOR is that it allows you to display additional statistics, including actual counts, as well as I/O, memory, and timing stats. Other benefits is that it can be run for any cursor in the cursor cache, and that it will show you the execution plan that was actually used (as opposed to the guess you get with EXPLAIN PLAN). The downside is that the cursor must be in the cursor cache, so the SQL must have already been executed. Note, however, that currently running queries are available to the DISPLAY_CURSOR function.

Additional Statistics

As mentioned previously, DISPLAY_CURSOR allows you to view additional statistics, that can be very helpful in diagnosing issues. Some of these statistics require that parameters get set prior to the SQL running to tell Oracle that you want to collect the statistics during execution. This is done by:

To gather I/O and “Actuals” Statistics:


  • Set parameter statistics_level = ALL
    • This can be done at the session level (alter session set statistics_level = ALL)


  • OR you can use the hint gather_plan_statistics (select /*+ gather_plan_statistics */ …) during execution

To gather Memory Management Statistics:


  • Set parameter pga_aggregate_target to non-zero value
    • This is typically already done, as this is the default as of 10g


Note: There is a slight overhead in gathering the I/O statistics, so the statistics_level = ALL setting isn’t something you would typically want on at a system level on a production database.

Output

There are several options for format, which can be found in the documentation: http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_xplan.htm#i998364. There is also an undocumented option ADVANCED that includes the OUTLINE data section, making for a total of 7 sections of output. To produce the output below I used format = ‘ADVANCED ALLSTATS’, so I could show everything available:

– Information about SQL Statement:

Includes the SQL_ID, Child Number, and SQL that was executed. This is pretty much just for reference when looking back at the output.

SQL_ID  b810y0qrnpgw5, child number 0
-------------------------------------
select /*+ cm2_test1 gather_plan_statistics */ at1.owner, 
at1.table_name, at1.num_rows, ac1.constraint_name, at2.owner, 
at2.table_name, at2.num_rows from a_tables at1     inner join 
a_constraints ac1         on ac1.owner = at1.owner         and 
ac1.table_name = at1.table_name     inner join a_constraints ac2        
 on ac2.owner = ac1.r_owner         and ac2.constraint_name = 
ac1.r_constraint_name     inner join a_tables at2         on at2.owner 
= ac2.owner         and at2.table_name = ac2.table_name where     
ac1.constraint_type = 'R'

Note: You can see from the statement that I put in the comment gather_plan_statistics to instruct Oracle to collect I/O statistics and “actuals” for this query. The cm2_test1 part is just a comment to help me find the query in the cursor cache.

– Execution Plan:

This shows the path that was taken to complete the SQL statement. The columns shown in this section depend on the value passed into the FORMAT parameter. We will look more closely at this output shortly.

Plan hash value: 3592798741
 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |      1 |        |       |    62 (100)|          |   1059 |00:00:44.23 |      32M|       |       |          |
|   1 |  NESTED LOOPS                   |               |      1 |        |       |            |          |   1059 |00:00:44.23 |      32M|       |       |          |
|   2 |   NESTED LOOPS                  |               |      1 |      1 |   154 |    62   (0)| 00:00:01 |     99M|00:02:15.27 |      21M|       |       |          |
|   3 |    MERGE JOIN CARTESIAN         |               |      1 |      1 |   105 |    60   (0)| 00:00:01 |     25M|00:00:13.03 |    3480 |       |       |          |
|   4 |     NESTED LOOPS                |               |      1 |        |       |            |          |   9951 |00:00:00.05 |    3385 |       |       |          |
|   5 |      NESTED LOOPS               |               |      1 |      1 |    75 |    31   (0)| 00:00:01 |   9951 |00:00:00.03 |    2285 |       |       |          |
|   6 |       TABLE ACCESS FULL         | A_TABLES      |      1 |      1 |    30 |    29   (0)| 00:00:01 |   2573 |00:00:00.01 |      96 |       |       |          |
|*  7 |       INDEX RANGE SCAN          | A_CONS_NUK2   |   2573 |      1 |       |     1   (0)| 00:00:01 |   9951 |00:00:00.01 |    2189 |       |       |          |
|   8 |      TABLE ACCESS BY INDEX ROWID| A_CONSTRAINTS |   9951 |      1 |    45 |     2   (0)| 00:00:01 |   9951 |00:00:00.01 |    1100 |       |       |          |
|   9 |     BUFFER SORT                 |               |   9951 |      1 |    30 |    58   (0)| 00:00:01 |     25M|00:00:07.41 |      95 |   178K|   178K|     1/0/0|
|  10 |      TABLE ACCESS FULL          | A_TABLES      |      1 |      1 |    30 |    29   (0)| 00:00:01 |   2573 |00:00:00.01 |      95 |       |       |          |
|* 11 |    INDEX RANGE SCAN             | A_CONS_NUK2   |     25M|      1 |       |     1   (0)| 00:00:01 |     99M|00:01:30.82 |      21M|       |       |          |
|* 12 |   TABLE ACCESS BY INDEX ROWID   | A_CONSTRAINTS |     99M|      1 |    49 |     2   (0)| 00:00:01 |   1059 |00:00:52.50 |      10M|       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------

– Query Blocks

This section is useful when you are wanting to hint specific query blocks from one location (for example when adding hints through OBIEE, where you can only add hints to the very outer level, but may want to hint a block nested several layers deep). The number in the beginning of each line corresponds to the ID in the execution plan table, letting you know which alias matches with which block/object.

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$EE94F965
   6 - SEL$EE94F965 / AT2@SEL$3
   7 - SEL$EE94F965 / AC2@SEL$2
   8 - SEL$EE94F965 / AC2@SEL$2
  10 - SEL$EE94F965 / AT1@SEL$1
  11 - SEL$EE94F965 / AC1@SEL$1
  12 - SEL$EE94F965 / AC1@SEL$1

Note: In this example, there was no subqueries, so all objects have the same Query Block identifier

– Outline

This section shows all the hints that would be needed to ensure the query executes in exactly the same fashion again. This is typically used with stored outlines and SQL plan baselines.

Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$EE94F965")
      MERGE(@"SEL$9E43CB6E")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$9E43CB6E")
      MERGE(@"SEL$58A6D7F6")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$58A6D7F6")
      MERGE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$EE94F965" "AT2"@"SEL$3")
      INDEX(@"SEL$EE94F965" "AC2"@"SEL$2" ("A_CONSTRAINTS"."OWNER" "A_CONSTRAINTS"."TABLE_NAME"))
      FULL(@"SEL$EE94F965" "AT1"@"SEL$1")
      INDEX(@"SEL$EE94F965" "AC1"@"SEL$1" ("A_CONSTRAINTS"."OWNER" "A_CONSTRAINTS"."TABLE_NAME"))
      LEADING(@"SEL$EE94F965" "AT2"@"SEL$3" "AC2"@"SEL$2" "AT1"@"SEL$1" "AC1"@"SEL$1")
      USE_NL(@"SEL$EE94F965" "AC2"@"SEL$2")
      NLJ_BATCHING(@"SEL$EE94F965" "AC2"@"SEL$2")
      USE_MERGE_CARTESIAN(@"SEL$EE94F965" "AT1"@"SEL$1")
      USE_NL(@"SEL$EE94F965" "AC1"@"SEL$1")
      NLJ_BATCHING(@"SEL$EE94F965" "AC1"@"SEL$1")
      END_OUTLINE_DATA
  */

Note: You can see here how the Query Block / Object Aliases from the previous section can be referenced in hints

– Predicates

The predicate information shows the filters that get applied when accessing the data. This section is helpful to catch transformations that may have been done to the query by Oracle, including implicit datatype conversion. We will discuss this in more detail later as well. Again, the number in the beginning of each line corresponds to the ID in the execution plan table, letting you know at exactly which steps each predicate is applied.

Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - access("AT2"."OWNER"="AC2"."OWNER" AND "AT2"."TABLE_NAME"="AC2"."TABLE_NAME")
  11 - access("AC1"."OWNER"="AT1"."OWNER" AND "AC1"."TABLE_NAME"="AT1"."TABLE_NAME")
  12 - filter(("AC1"."R_OWNER" IS NOT NULL AND "AC1"."R_CONSTRAINT_NAME" IS NOT NULL AND "AC1"."CONSTRAINT_TYPE"='R' AND "AC2"."OWNER"="AC1"."R_OWNER" AND 
              "AC2"."CONSTRAINT_NAME"="AC1"."R_CONSTRAINT_NAME"))

There are 2 types of predicates:
– Access = Only matching rows are retrieved
– Filter = All rows are retrieved, only matching rows are kept

This is an important distinction when understanding buffer counts

– Column Projection

This section shows each field and its corresponding data type that is returned from each step in the execution plan. Once again, the number in the beginning of each line corresponds to the ID in the execution plan table

Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "AT2"."OWNER"[VARCHAR2,30], "AT2"."TABLE_NAME"[VARCHAR2,30], "AT2"."NUM_ROWS"[NUMBER,22], "AT1"."OWNER"[VARCHAR2,30], "AT1"."TABLE_NAME"[VARCHAR2,30], 
       "AT1"."NUM_ROWS"[NUMBER,22], "AC1"."CONSTRAINT_NAME"[VARCHAR2,30]
   2 - "AT2"."OWNER"[VARCHAR2,30], "AT2"."TABLE_NAME"[VARCHAR2,30], "AT2"."NUM_ROWS"[NUMBER,22], "AC2"."OWNER"[VARCHAR2,30], 
       "AC2"."CONSTRAINT_NAME"[VARCHAR2,30], "AT1"."OWNER"[VARCHAR2,30], "AT1"."TABLE_NAME"[VARCHAR2,30], "AT1"."NUM_ROWS"[NUMBER,22], "AC1".ROWID[ROWID,10]
   3 - "AT2"."OWNER"[VARCHAR2,30], "AT2"."TABLE_NAME"[VARCHAR2,30], "AT2"."NUM_ROWS"[NUMBER,22], "AC2"."OWNER"[VARCHAR2,30], 
       "AC2"."CONSTRAINT_NAME"[VARCHAR2,30], "AT1"."OWNER"[VARCHAR2,30], "AT1"."TABLE_NAME"[VARCHAR2,30], "AT1"."NUM_ROWS"[NUMBER,22]
   4 - "AT2"."OWNER"[VARCHAR2,30], "AT2"."TABLE_NAME"[VARCHAR2,30], "AT2"."NUM_ROWS"[NUMBER,22], "AC2"."OWNER"[VARCHAR2,30], 
       "AC2"."CONSTRAINT_NAME"[VARCHAR2,30]
   5 - "AT2"."OWNER"[VARCHAR2,30], "AT2"."TABLE_NAME"[VARCHAR2,30], "AT2"."NUM_ROWS"[NUMBER,22], "AC2".ROWID[ROWID,10], "AC2"."OWNER"[VARCHAR2,30]
   6 - "AT2"."OWNER"[VARCHAR2,30], "AT2"."TABLE_NAME"[VARCHAR2,30], "AT2"."NUM_ROWS"[NUMBER,22]
   7 - "AC2".ROWID[ROWID,10], "AC2"."OWNER"[VARCHAR2,30]
   8 - "AC2"."CONSTRAINT_NAME"[VARCHAR2,30]
   9 - (#keys=0) "AT1"."OWNER"[VARCHAR2,30], "AT1"."TABLE_NAME"[VARCHAR2,30], "AT1"."NUM_ROWS"[NUMBER,22]
  10 - "AT1"."OWNER"[VARCHAR2,30], "AT1"."TABLE_NAME"[VARCHAR2,30], "AT1"."NUM_ROWS"[NUMBER,22]
  11 - "AC1".ROWID[ROWID,10]
  12 - "AC1"."CONSTRAINT_NAME"[VARCHAR2,30]

– Notes

Oracle displays any relevant information about what it may have done. Examples are dynamic sampling, star transformations, SQL Plan Management, and even comments that you are trying to display information in DISPLAY_CURSOR that wasn’t collected due to the proper parameters not being set.

Note
-----
   - cardinality feedback used for this statement

Note: The demo didn’t actually produce any notes. I got the above note from a different query, just to include an example of what the section might look like.

The FORMAT parameter that I normally use is IOSTATS, which just displays the columns and sections that I use most often:

SQL_ID  b810y0qrnpgw5, child number 0
-------------------------------------
select /*+ cm2_test1 gather_plan_statistics */ at1.owner, 
at1.table_name, at1.num_rows, ac1.constraint_name, at2.owner, 
at2.table_name, at2.num_rows from a_tables at1     inner join 
a_constraints ac1         on ac1.owner = at1.owner         and 
ac1.table_name = at1.table_name     inner join a_constraints ac2        
 on ac2.owner = ac1.r_owner         and ac2.constraint_name = 
ac1.r_constraint_name     inner join a_tables at2         on at2.owner 
= ac2.owner         and at2.table_name = ac2.table_name where     
ac1.constraint_type = 'R'
 
Plan hash value: 3592798741
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |      1 |        |   1059 |00:00:44.23 |      32M|
|   1 |  NESTED LOOPS                   |               |      1 |        |   1059 |00:00:44.23 |      32M|
|   2 |   NESTED LOOPS                  |               |      1 |      1 |     99M|00:02:15.27 |      21M|
|   3 |    MERGE JOIN CARTESIAN         |               |      1 |      1 |     25M|00:00:13.03 |    3480 |
|   4 |     NESTED LOOPS                |               |      1 |        |   9951 |00:00:00.05 |    3385 |
|   5 |      NESTED LOOPS               |               |      1 |      1 |   9951 |00:00:00.03 |    2285 |
|   6 |       TABLE ACCESS FULL         | A_TABLES      |      1 |      1 |   2573 |00:00:00.01 |      96 |
|*  7 |       INDEX RANGE SCAN          | A_CONS_NUK2   |   2573 |      1 |   9951 |00:00:00.01 |    2189 |
|   8 |      TABLE ACCESS BY INDEX ROWID| A_CONSTRAINTS |   9951 |      1 |   9951 |00:00:00.01 |    1100 |
|   9 |     BUFFER SORT                 |               |   9951 |      1 |     25M|00:00:07.41 |      95 |
|  10 |      TABLE ACCESS FULL          | A_TABLES      |      1 |      1 |   2573 |00:00:00.01 |      95 |
|* 11 |    INDEX RANGE SCAN             | A_CONS_NUK2   |     25M|      1 |     99M|00:01:30.82 |      21M|
|* 12 |   TABLE ACCESS BY INDEX ROWID   | A_CONSTRAINTS |     99M|      1 |   1059 |00:00:52.50 |      10M|
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - access("AT2"."OWNER"="AC2"."OWNER" AND "AT2"."TABLE_NAME"="AC2"."TABLE_NAME")
  11 - access("AC1"."OWNER"="AT1"."OWNER" AND "AC1"."TABLE_NAME"="AT1"."TABLE_NAME")
  12 - filter(("AC1"."R_OWNER" IS NOT NULL AND "AC1"."R_CONSTRAINT_NAME" IS NOT NULL AND 
              "AC1"."CONSTRAINT_TYPE"='R' AND "AC2"."OWNER"="AC1"."R_OWNER" AND 
              "AC2"."CONSTRAINT_NAME"="AC1"."R_CONSTRAINT_NAME"))
 

In the next post, we will take a look at the execution plan in detail.

SQL Tuning – Part 1.1 – What’s the Plan, Stan?

If you are following along from Part 0, at this point we have found a query that we need to tune, as we have found that we need to get a 43 second query down to 3 seconds to meet the requirements given. So where do we start? The first thing I like to do is to take a look at the current execution plan of the query.

So, how do you find what a query does? One option is to run Explain Plan. The easiest way to do this is with the Explain Plan command:

explain plan for
{SQL Statement};

Running this command gathers information and puts it into the plan_table. In Oracle 10g or above, by default a plan_table is created as a global temporary table owned by SYS along with a plan_table public synonym so it can be used by all users. Once explain plan has been executed, there are a few different ways to access the information:


  • Query PLAN_TABLE directly

  • DBMS_XPLAN package

Also, most GUI tools (TOAD, SQL Developer, etc) have some sort of button that does both run the explain plan command and display the results.

My personal preference is the DBMS_XPLAN package. I don’t have to worry about getting used to one tool, then not have that tool available. More importantly, DBMS_XPLAN also contains some helpful information that is not always included with the other tools.

DBMS_XPLAN is a package that has up to 7 functions (as of 12c). In this article, I will just focus on 2 of them.. DISPLAY and DISPLAY_CURSOR.

DBMS_XPLAN.DISPLAY –

The DISPLAY function has been around since at least 9i. It is a table function that displays the contents of the specified plan table in a formatted fashion. Technically, there are 4 parameters (as of 12c):

DBMS_XPLAN.DISPLAY(
   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',
   statement_id  IN  VARCHAR2  DEFAULT  NULL, 
   format        IN  VARCHAR2  DEFAULT  'TYPICAL',
   filter_preds  IN  VARCHAR2  DEFAULT  NULL
);

Using all of the defaults, this function will return the plan from the global temporary plan_table for the last statement explained. This means in normal usage, you will call it with no parameters immediately after you run the explain plan command:

explain plan for
select at1.owner, at1.table_name, at1.num_rows, ac1.constraint_name, at2.owner, at2.table_name, at2.num_rows
from a_tables at1
    inner join a_constraints ac1
        on ac1.owner = at1.owner
        and ac1.table_name = at1.table_name
    inner join a_constraints ac2
        on ac2.owner = ac1.r_owner
        and ac2.constraint_name = ac1.r_constraint_name
    inner join a_tables at2
        on at2.owner = ac2.owner
        and at2.table_name = ac2.table_name
where
    ac1.constraint_type = 'R';


select *
from table(dbms_xplan.display);

The output looks something like:

Plan hash value: 3592798741
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |     1 |   154 |    62   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                   |               |       |       |            |          |
|   2 |   NESTED LOOPS                  |               |     1 |   154 |    62   (0)| 00:00:01 |
|   3 |    MERGE JOIN CARTESIAN         |               |     1 |   105 |    60   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |               |       |       |            |          |
|   5 |      NESTED LOOPS               |               |     1 |    75 |    31   (0)| 00:00:01 |
|   6 |       TABLE ACCESS FULL         | A_TABLES      |     1 |    30 |    29   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN          | A_CONS_NUK2   |     1 |       |     1   (0)| 00:00:01 |
|   8 |      TABLE ACCESS BY INDEX ROWID| A_CONSTRAINTS |     1 |    45 |     2   (0)| 00:00:01 |
|   9 |     BUFFER SORT                 |               |     1 |    30 |    58   (0)| 00:00:01 |
|  10 |      TABLE ACCESS FULL          | A_TABLES      |     1 |    30 |    29   (0)| 00:00:01 |
|* 11 |    INDEX RANGE SCAN             | A_CONS_NUK2   |     1 |       |     1   (0)| 00:00:01 |
|* 12 |   TABLE ACCESS BY INDEX ROWID   | A_CONSTRAINTS |     1 |    49 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - access("AT2"."OWNER"="AC2"."OWNER" AND "AT2"."TABLE_NAME"="AC2"."TABLE_NAME")
  11 - access("AC1"."OWNER"="AT1"."OWNER" AND "AC1"."TABLE_NAME"="AT1"."TABLE_NAME")
  12 - filter("AC1"."R_OWNER" IS NOT NULL AND "AC1"."R_CONSTRAINT_NAME" IS NOT NULL AND 
              "AC1"."CONSTRAINT_TYPE"='R' AND "AC2"."OWNER"="AC1"."R_OWNER" AND 
              "AC2"."CONSTRAINT_NAME"="AC1"."R_CONSTRAINT_NAME")

For more information on DBMS_XPLAN.DISPLAY, see the documentation

The major advantage of using the DBMS_XPLAN.DISPLAY function is that the actual SQL doesn’t have to be executed, just explained. However, there are a few drawbacks. It is important to understand that the plan that is returned may not actually be the plan that gets used when the SQL is executed. This is especially true when bind variables are involved. Also, because the SQL has not been executed, Oracle is limited in what information it can provide. The combination of these two things (especially the 2nd) is why I use DBMS_XPLAN.DISPLAY_CURSOR 99% of the time, which I will get to next time…