Author Archives: cmartin2

I was recently watching this SQL “Tune-Off” webinar that Jonathan Lewis and Kyle Hailey put together a few years back. As part of this webinar, Jonathan briefly mentions a script he has for looking at the number of keys that exist per leaf block of an index. The idea being that if the majority of your index leaf blocks are mostly empty, then it is a good candidate for being rebuilt (or coalesced or shrunk.. for info on which to choose, check out this post by Richard Foote). Jonathan being his normal generous self, shared this script in the presentation. This is a huge reason why I love the Oracle community. Jonathan could have just as easily kept his script to himself as a “competitive advantage”, but he is more interested in helping others learn. There are a huge number of people in the Oracle community who spend a large amount of time with the majority of the benefit for people they will never meet. For this generosity, I will never be able to thank them enough!

I have taken this script and made it a little easier for me to use. I can pass this an Owner and Table and it will loop through all the Indexes and print the results for each via DBMS_OUTPUT (which can be easily changed to a different method). There is a 3rd parameter as well, where you can optionally pass just a single index if you only care about one.

The script looks like:

DECLARE
    PROCEDURE check_index_health
    (
        owner_in IN VARCHAR2,
        table_in IN VARCHAR2,
        index_in IN VARCHAR2 := null
    )
    IS
        TYPE rec_t IS RECORD(keys_per_leaf PLS_INTEGER, blocks PLS_INTEGER);
        TYPE tbl_t IS TABLE OF rec_t;

        v_predicate VARCHAR2(3000);
        v_cnt PLS_INTEGER := 0;

        FUNCTION indx_health (
            owner_in IN VARCHAR2,
            tbl_in IN VARCHAR2,
            predicate_in IN VARCHAR2,
            object_id_in IN VARCHAR2
        )
        RETURN TBL_T
        IS
            /* Helper function that gets details for index specified by object_id_in */
            tbl TBL_T; 
        BEGIN
            execute immediate '
                select
                    keys_per_leaf, count(*) blocks
                from (
                    select
                        sys_op_lbid('||object_id_in||',''L'',t.rowid) block_id,
                        count(*) keys_per_leaf
                    from
                        '||owner_in||'.'||tbl_in||' t
                    where
                        '||predicate_in||'
                    group by
                        sys_op_lbid('||object_id_in||',''L'',t.rowid)
                )
                group by keys_per_leaf
                order by keys_per_leaf'
            BULK COLLECT INTO
                tbl;

            RETURN tbl;
        END indx_health;
    
        PROCEDURE p (
            text_in IN VARCHAR2
        )
        IS
            /* 
                Helper procedure to output single line of text.  Proceduralized in
                case want to switch to logging to file, etc. 
            */
        BEGIN
            dbms_output.put_line(text_in);
        END p;

        PROCEDURE output (
            index_name_in IN VARCHAR2,
            tbl_in IN TBL_T
        )
        IS
            /*
                Helper procedure to output details for a single index.
                Proceduralized in case want to switch to insert to table, etc.
             */
        BEGIN
            p('Index: '||index_name_in);
            IF tbl_in.COUNT = 0
            THEN
                p('No rows in Index');
            ELSE
                p('KEYS PER LEAF          BLOCKS');
                p('-------------      ---------');
                FOR i IN tbl_in.FIRST .. tbl_in.LAST
                LOOP
                    p(rpad(tbl_in(i).keys_per_leaf,23)||tbl_in(i).blocks);
                END LOOP;
            END IF;
            p(chr(10)||chr(10));
        END output;
    BEGIN
        /* 
            Loop over each index in table, building predicate from columns in index
            and then gathering details about health of index
        */ 
        FOR rec in (
            select
                do.object_id,
                ic.index_owner,
                ic.index_name,
                ic.column_name,
                count(*) over (partition by do.object_id) cnt,
                row_number() over (partition by do.object_id order by 1) rn
            from
                dba_ind_columns ic
                inner join dba_objects do
                    on do.owner = ic.index_owner
                    and do.object_name = ic.index_name
            where
                ic.table_owner = upper(owner_in)
                and ic.table_name = upper(table_in)
                and (ic.index_name = upper(index_in) or index_in is null) 
            order by
                object_id
        )
        LOOP

            v_predicate := v_predicate||' or '||rec.column_name||' is not null'; -- add each column in index to predicate        

            IF rec.cnt = rec.rn -- Last line for index
            THEN
                v_predicate := substr(v_predicate,4); -- take off leading ' or '
                output(rec.index_name, indx_health(owner_in,table_in,v_predicate,rec.object_id)); -- get results for current index
                v_predicate := null; -- reset predicate              
                v_cnt := v_cnt + 1; -- increment index counter
            END IF;

        END LOOP;

        p('Matching indexes found: '||v_cnt);

    END check_index_health;
BEGIN
    check_index_health(
        owner_in => 'CMARTIN2',
        table_in => 'TBL1'
    );
END;

And gives output like:

Index: TBL1_IDX1
KEYS PER LEAF          BLOCKS
-------------          -----------
96                     1
170                    10
187                    1
222                    248
223                    11
224                    9
225                    12
226                    17
227                    12
228                    115
229                    40
230                    7
231                    4
232                    4
233                    2
237                    4
238                    2
239                    5
240                    9
241                    19
242                    143
243                    1258
244                    285
245                    150
246                    66
247                    5
248                    9
249                    3
250                    9



Index: TBL1_IDX2
KEYS PER LEAF          BLOCKS
-------------          -----------
167                    1
220                    1
221                    1106
222                    1056
262                    1
295                    1
296                    368

Which is telling me in the index TBL1_IDX2, there is 1 leaf block pointing to 167 rows in the table, 1 leaf block pointing to 220 rows, 1106 leaf blocks pointing to 221 rows each, etc. I have already put this to use at work and found a few indexes which had thousands of leaf blocks with less than 10 keys in them. I suspected the indexes may need to be rebuilt by the nature of what happens to the table, but this script was able to confirm it.

If you see any issues or areas for improvements, let me know!

In the last post we took a look at how to get execution plans using DBMS_XPLAN.DISPLAY_CURSOR. In the post before that we looked at how to get the estimated execution plan using DBMS_XPLAN.DISPLAY. I mentioned that DISPLAY_CURSOR allowed you to get additional statistics, so let’s take a look in more detail.

We’ll start with some output produced by DISPLAY:

Plan hash value: 3063390629
 
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 14653 |   113M|   119   (3)| 00:00:02 |
|*  1 |  HASH JOIN           |      | 14653 |   113M|   119   (3)| 00:00:02 |
|   2 |   TABLE ACCESS FULL  | T3   |   100 |   198K|     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN          |      | 14653 |    85M|   116   (3)| 00:00:02 |
|*  4 |    HASH JOIN         |      |     8 | 32344 |     7  (15)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| T4   |     6 | 12090 |     3   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   |    50 |    99K|     3   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL | T1   | 92800 |   180M|   108   (1)| 00:00:02 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T3"."T3_ID"="T1"."T3_ID")
   3 - access("T2"."T2_ID"="T1"."T2_ID")
   4 - access("T4"."T4_ID"="T2"."T4_ID")
   5 - filter("T4"."RAND_STRING" LIKE 'f%')

Here are what the columns mean:

• Id – ID corresponding to the current line of the execution plan. This is how other sections of the output reference where in the execution plan they apply to. For example, in the Predicate Information section, the numbers at the start of each line tell you to which line of the execution plan that particular predicate applies to. Also notice in the ID column there are some asterisks in front of some IDs. This indicates that these rows are referenced in the Predicate Information section.
• Operation – What the database is actually doing on each line of the execution plan.
• Name – Name of the object being acted on by the current operation. Note this column does not apply to all operations.
• Rows – Number of rows Oracle believes will be returned by the current operation.
• Bytes – Amount of data Oracle believes will be returned by the current operation.
• Cost (%CPU) – Cost Oracle assigned to current operation, with percentage of CPU cost in parenthesis. Cost is cumulative, so the cost at the parent line contains the cost of itself as well as its children. More on cost will come later.
• Time – Amount of time Oracle believes current operation will take.

Now let’s look at an execution plan from DBMS_STATS.DISPLAY_CURSOR using IOSTATS as the format option:

Plan hash value: 3063390629
 
---------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |  14000 |00:00:00.06 |     399 |
|*  1 |  HASH JOIN           |      |      1 |  14653 |  14000 |00:00:00.06 |     399 |
|   2 |   TABLE ACCESS FULL  | T3   |      1 |    100 |    100 |00:00:00.01 |       3 |
|*  3 |   HASH JOIN          |      |      1 |  14653 |  14000 |00:00:00.04 |     396 |
|*  4 |    HASH JOIN         |      |      1 |      8 |      7 |00:00:00.01 |       6 |
|*  5 |     TABLE ACCESS FULL| T4   |      1 |      6 |      6 |00:00:00.01 |       3 |
|   6 |     TABLE ACCESS FULL| T2   |      1 |     50 |     50 |00:00:00.01 |       3 |
|   7 |    TABLE ACCESS FULL | T1   |      1 |  92800 |    100K|00:00:00.02 |     390 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T3"."T3_ID"="T1"."T3_ID")
   3 - access("T2"."T2_ID"="T1"."T2_ID")
   4 - access("T4"."T4_ID"="T2"."T4_ID")
   5 - filter("T4"."RAND_STRING" LIKE 'f%')

Looking at these columns, Id, Operation, and Name are all the same as what they were above in the DISPLAY output. The other columns are:

• Starts – Number of times current operation was executed.
• E-Rows – Estimated Rows. This is the same as Rows in the DISPLAY output above.
• A-Rows – Actual Rows. This is the actual number of rows retrieved by the current operation.
• A-Time – Actual Time. This it the actual time spent on the current operation.
• Buffers – Also known as consistent gets, this is the number of logical reads performed for the current operation.

Using the Output – Antipatterns

Cost

What is cost? Cost is a calculated number Oracle uses to have a relative way to compare different operations. This is the basis of the Cost-Based Optimizer. By default, for each query Oracle will generate several execution plan options and choose to use the one with the lowest overall cost. Cost is supposed to be an indicator of how much time the execution will take. For all the information you could ever want to know about Cost, refer to Jonathan Lewis’s book Cost-Based Oracle Fundamentals. However, until you truly understand how Cost is calculated and you know how to determine where Oracle is going wrong in its calculations, my advice is to completely ignore Cost altogether. The main reason is that you can’t directly control cost. Too many times I have seen people just aimlessly play around with SQL until they get a lower cost and call that “tuning”. This is not tuning, it is blindly throwing darts hoping to get lucky.

TABLE ACCESS FULL

Another pet peeve of mine is just looking at the execution plans for TABLE ACCESS FULL operations and trying to eliminate those. This is an especially touchy subject with me because it was how I was first taught how to “tune”. Once I learned more, it became painfully clear this was a terrible way to go about it. However, I know a lot of people are still being trained the way I was, and this is what they are doing. It makes it worse that some tools like TOAD’s explain plan output shows TABLE ACCESS FULL in bright red, further propagating the myth that this is something to remove. I have enough to say about this that I will make another full post on it. For now, suffice it to say that if you are thinking that TABLE ACCESS FULL is always bad, and index access is always good, you are looking at tuning wrong.

Using the Output – Better Ways

Hopefully from the columns described above, you can start to see why I much prefer the output of DISPLAY_CURSOR. The extra information available is extremely valuable. I would like to highlight a few of the key areas:

Cardinality Feedback

A major reason for performance issues is a significant difference between the Estimated Rows and Actual Rows columns. If Oracle is wrong by a factor of 10 about how many rows will be retrieved from a given operation, there is a good chance it will access that data in an inefficient way or in an inefficient order. Wolfgang Breitling put together a great whitepaper on Tuning by Cardinality Feedback, which is all about using this difference in Estimated to Actual rows to identify where in your query to focus your tuning efforts.

Buffers

The goal of tuning is eliminating waste. Any time you can achieve the same goal using less buffers, it is a win for performance. This is the whole point of indexes.. instead of reading every block in a table, you visit a few index blocks which then point to only the table blocks that you need to get your answer. Less blocks visited = less work = less time to complete. More information on eliminating buffers to achieve better execution can be found in this excellent white paper by Cary Millsap.

Understanding the Execution Tree

We also need to understand what the indentation in the Operation column of the execution plan output is telling us, which will be the topic of my next post.

I always enjoy learning new things.. especially when it is something that comes as a complete surprise. A developer was updating some code from using literals to instead use bind variables. So far, fantastic! However, during testing, the code no longer worked as expected. Before, when using literals, it returned rows. Now that it used bind variables, no rows were returned. I checked the code and everything looked right. So I was quite puzzled. After a bit of digging, it turns out that one of the columns in the WHERE clause was of type CHAR(10). However, the data that was in it was not always 10 characters, so some of the rows would contain trailing spaces. So now I understood why the query was not returning rows now. However, I was still confused how this could have been working before. I came up with a simple test to see if I could reproduce this issue:

Make a table with 2 columns, one VARCHAR2(10), one CHAR(10):

SQL> create table x_cm2
(c_varchar varchar2(10), c_char char(10))
Table created.

Add some test data. This is a query I like to use when I need a row for each month of the year:

SQL> insert into x_cm2(c_varchar, c_char)
select str, str
from (
    select to_char(add_months(trunc(sysdate, 'YY'), rownum-1), 'Mon') str 
    from dual
    connect by level <= 12
)
12 rows created.

Now let's run some tests. First we will test with the bind variables:

SQL> var mon varchar2(10)
SQL> exec :mon := 'Dec'
PL/SQL procedure successfully completed.
SQL> select *
from x_cm2
where c_varchar = :mon

C_VARCHAR  C_CHAR    
---------- ----------
Dec        Dec       
1 row selected.

SQL> select *
from x_cm2
where c_char = :mon
no rows selected.

So this is behaving like the new code... exactly how I would expect. The query returns rows when filtering against the VARCHAR column, but not the CHAR column. This is because the CHAR column would have 7 trailing spaces at the end. So far, so good.

Now lets try with a literal:

SQL> select *
from x_cm2
where c_varchar = 'Dec'

C_VARCHAR  C_CHAR    
---------- ----------
Dec        Dec       
1 row selected.

SQL> select *
from x_cm2
where c_char = 'Dec'

C_VARCHAR  C_CHAR    
---------- ----------
Dec        Dec       
1 row selected.

So with a literal, both queries return a row. What is going on? As is usually the case, the documentation had the answer (emphasis mine):

With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR, NCHAR, text literals, or values returned by the USER function.

So it turns out this is expected behavior... just not the behavior I would have expected.

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.

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…

The first step in any tuning project should be obvious, but is often overlooked. You need to clearly understand the problem as well as the desired result. More specifically, the problem and desired result need to be defined from the perspective of an end user and defined by wall time. Anything else will lead to what is commonly known as CTD.. compulsive tuning disorder.

It takes 45 seconds from the time I click this button to when I see the report. I need it to return in 5 seconds or less.

That is a perfect problem and requirements statement.

Query x runs for 45 seconds. Please make it faster.

That leads to disaster. If I get the query to return in 42 seconds, is that a success? Probably not. If I get the query to return in 4 seconds, do I spend the time looking into different options that might get it to return in 3.5 seconds? And once I get there, do I keep going until all options are exhausted? (hint: they never are!). This is the definition of CTD!

So after you have a good problem and requirements statement, you are ready for step 2.. figuring out where the time is going. So back to our example, from the time that the user clicks a button on a webpage, it takes 45 seconds. So what does that button do? For our example, we will say clicking the button:

• Client browser makes a post to an application server
• Application server processes the request and submits a query to the database
• Database processes query and returns results to application server
• Application server processes the results and returns the data to the client
• Client browser processes and displays the data

Note that this is a very simplistic representation and is missing what could be key steps (network hops between each layer, application server might go back to database several times to fetch more data, etc). Also, in reality each of these steps will typically have several steps of their own. However, this should be good for our simple example.

If you are extremely lucky, your entire application stack will be very well instrumented, and you can easily look at the timings for each of these steps. I have never been that lucky in a real-world example, so usually I am stuck trying to piece together what I do have to make a good enough representation. The fortunate thing is that Oracle is extremely well instrumented, so that makes for a great place to start.

For the purposes of this exercise, we will say that we have no instrumentation on anything except for Oracle. We trace our query and see that it takes 43 seconds to execute. That leaves 2 seconds in the other steps of the process. I now know that to meet the requirements (5 second response time), I need to get the query to return in 3 seconds (the 2 seconds is assumed to not change). If I get the query down to 4 seconds and would have to do something drastic to reduce it further, it would make more sense to start investigating where exactly in the stack the 2 non-Oracle seconds are going and see if there is a better solution there.

However, if Oracle were reporting that the query was completing in anything less than 40 seconds, I would know I need to immediately start getting other teams involved to look into where the perfomance issue actually lies. Otherwise, no matter how much I tune the query, I will never be able to meet the 5 second requirement.

The moral of the story is when you are looking at a performance issue, you need to understand where your time is actually going. This also applies when looking only at the query.. of the 43 seconds, where is Oracle spending its time? Is it wasted I/O operations? Is it contention for a resource that another processes is using? You don’t really know where to start with the query until, again, you know exactly where your time is going. I could make a series of posts on this subject alone, but would never be able to do justice to the work that Cary Millsap has already done. SO I will just urge you to read his work on the topic. A few must read’s are:

• Optimizing Oracle Performance – Cary’s classic book with Jeff Holt – Available on Amazon
• Thinking Clearly About Performance and Mastering Performance with Extended SQL Trace – 2 of my favorite papers that Cary has written, which can be downloaded on Method R’s website