Index Health Check

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:

    PROCEDURE check_index_health
        owner_in IN VARCHAR2,
        table_in IN VARCHAR2,
        index_in IN VARCHAR2 := null
        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
            /* Helper function that gets details for index specified by object_id_in */
            tbl TBL_T; 
            execute immediate '
                    keys_per_leaf, count(*) blocks
                from (
                        sys_op_lbid('||object_id_in||',''L'',t.rowid) block_id,
                        count(*) keys_per_leaf
                        '||owner_in||'.'||tbl_in||' t
                    group by
                group by keys_per_leaf
                order by keys_per_leaf'

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

        PROCEDURE output (
            index_name_in IN VARCHAR2,
            tbl_in IN TBL_T
                Helper procedure to output details for a single index.
                Proceduralized in case want to switch to insert to table, etc.
            p('Index: '||index_name_in);
            IF tbl_in.COUNT = 0
                p('No rows in Index');
                p('KEYS PER LEAF          BLOCKS');
                p('-------------      ---------');
                FOR i IN tbl_in.FIRST .. tbl_in.LAST
                END LOOP;
            END IF;
        END output;
            Loop over each index in table, building predicate from columns in index
            and then gathering details about health of index
        FOR rec in (
                count(*) over (partition by do.object_id) cnt,
                row_number() over (partition by do.object_id order by 1) rn
                dba_ind_columns ic
                inner join dba_objects do
                    on do.owner = ic.index_owner
                    and do.object_name = ic.index_name
                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

            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
                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;
        owner_in => 'CMARTIN2',
        table_in => 'TBL1'

And gives output like:

Index: TBL1_IDX1
-------------          -----------
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
-------------          -----------
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!


Hotsos Symposium Day 3-4

Day 3 – This was the final day of the normal sessions, and it finished strong. I went to sessions by Alex Fatkulin, Bryn Llewellyn, Kellyn Pot’Vin, Maria Colgan, and Kerry Osborne. I was impressed with all of the speakers, which was no surprise given the people presenting. I definitely enjoyed Bryn’s talk. I really like his no-nonsense style and his sense of humor. My favorite session of the day though was Maria’s.. I hadn’t seen her present before, so had no idea how good she was. I got quite a bit of information just from her side comments and was entertained throughout.

Day 4 – This was the training day with Tanel. He showed off his process for looking into performance issues, including many of his scripts and how to use them. Like everything Tanel does, it was an excellent presentation. Now I just need to spend some time with them so I don’t forget what I have learned.

Overall, I was very impressed with my first Hotsos Symposium. The organizers and volunteers were very organized and always helpful. I had a great time and met a lot of brilliant people.. this will definitely not be my last Symposium!


Hotsos Symposium Days 0 – 2

Day 2

Just finished Day 2 of Hotsos Symposium presentations. It has been a great event so far, like always with great content. My presentation was today as well. The room was packed (due to the topic, not the speaker 😉 … SQL tuning always pulls a crowd), so thanks to everyone who came, and hopefully people were able to walk away from it with a couple useful things. I guess we will find out when the evaluations come out :) If you did attend, please fill out the evaluations and include what I can do to improve this particular presentation or just my presentation style in general. A huge thanks to Rhonda and the rest of the folks helping to run the event for doing an excellent job. There have been some slight hiccups that were out of their control, and they handled them wonderfully.

Today Luca Canali and Kellyn Pot’Vin definitely gave me some homework to do downloading their scripts and playing around. Kellyn’s was on ASH / AWR and included some very intriguing scripts against ASH data. Luca’s included great examples of how a heatmap can be used to show histograms over time, and he has some scripts for reporting latency events this way which I will definitely be looking over. He also mentioned Kevin Closson’s SLOB tool, which I need to give some time to as well.. The other talks I went to were also very good, but about 12c, so I will need to revisit them later this year once I have more exposure on 12c. Unfortunately I missed Rusty Schmidt’s presentation on analyzing Oracle Workflow, but he got the bad luck of getting assigned at the same time as Tanel Poder, and I have a have a rule that if Tanel speaks, I listen :)

Day 1
Unfortunately, I arrived late (which I will cover in a bit), so missed out on Maria Colgan’s keynote and Kerry Osborne’s 1st presentation, which I am very bummed about. I did pick up some great things about compression from Tim Gorman and am very intrigued with the new 12c In-Database Archiving feature that Julian Dontcheff mentioned. Jon Hurley did a great job explaining about the Shared Pool and how it can be monitored to prevent disaster. He also plugged the upcoming Great Lakes Oracle Conference in Cleveland in May, which is always an excellent event and I can’t wait to attend again.

Day 0
I arrived late because I had quite the adventure with my flights. Both Dallas and the Midwest both got snow on Sunday, which made airlines start cancelling flights like it was giving them free money. Which is mostly because cancelling flights is like giving them free money… They don’t use gas for the flights when they are cancelled and it isn’t like they add new flights or change flights to use bigger planes to try to accommodate people and get them to their destination as soon as possible. No.. they just shove people onto existing flights no matter how much later that happens to be. Pretty crazy there isn’t more of a fuss about this, but I guess we are just used to airlines treating us however they want. So my original flight from Columbus was cancelled. Which the kind folks at American Airlines didn’t even bother to tell me about. I got a google alert that my flight was cancelled. There is no easy way to reschedule my flight, so I tried to call their reservations area. 2+ hour wait time. Not helpful. So off to the airport to do this in person. 2.5 hours after the google alert came in, as I was walking up to talk to ticket agent at airport I finally got an automated phone call from AA saying my flight was cancelled. Thanks for the prompt notice. At the airport, they tried to re-book me onto a Monday evening flight. I didn’t want to miss any of the conference, and since I was presenting I was supposed to be in on Sunday to test out the laptop with their equipment and make sure everything was working, so I wanted to get in as soon as I could. Sunday still if at all possible. After the ticket agent checked everything they could, the best they could do was fly out at 6am on Monday into New York and get to Dallas at around 1pm Monday. I wanted to do better. They told me Cincinnati was still flying out Sunday night and had space on their 7:55pm flight that would get me in at 9:30pm. Great.. book it. So now we drive 3 hours through the snowy roads to Cinci. After we sit in the airport for about 4 hours, we get some good news. Around 5:30 a flight leaves Cinci for Dallas. So the flights are still on. Until around 6:15pm. That is when they announced that our flight had been cancelled. This time not for weather. I guess someone on the crew decided not to show up. Yay! And now since it was so late in the evening, they couldn’t even book us on a flight for Monday from Columbus or Cinci due to everyone else already rescheduling. Ugggh.. Their best option? They have a flight leaving from Indy at 8:50am, getting in at 10:10am. Since it wasn’t weather related, they will pay for our hotel in Indy, but we need to go to the Indy airport that night and talk to the agents there to get it arranged. Back on the road. 3+ more hours through the snowy roads. Arrive at the Indy airport after 10pm to find nobody there from AA. Of course. Call the number.. 2+ hour wait. So we get our own hotel and then catch the flight in the morning. So 24 hours after we left the house and 3 states (Cinci airport is in Kentucky) later, we finally get into the air. Luckily nothing else went wrong and I got to the conference around 11:30am, but it does mean I will get to have some “pleasant” conversations with the AA representatives when I get back. :)


SQL Tuning – Part 1.3 – DISPLAY vs. DISPLAY_CURSOR

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


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.


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.


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.


Looking Forward – Goals for 2014

For another year, I didn’t do very well on my goals. Again, I didn’t get through the books I wanted to or get my certification. (This is starting to sound too familiar) But again, I would also consider my year quite a success. I have been presenting much more frequently, and got accepted to present in 2014 at Hotsos, which I am very excited about. I have also been able to work on some fun challenges at work. I was also a little busier than normal, as I got married this past year. Life outside of work couldn’t be much better. But I do really want to get back to the books… I have some on my shelf that I have really wanted to get into, but haven’t made the time. So my goals for 2014 are much more simplified:

  • Read! – My Oracle reading (as far as books go) has slipped in the past couple years. I want to pick back up and get at least 3 Oracle books read.
  • Blog! – This also didn’t take off last year as I had hoped. I never got it into my routine, but have a better plan for this year, so again my goal is to post at least once per week.

Good luck to everyone else in achieving their 2014 goals!


“Fun” with CHAR fields

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

---------- ----------
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'

---------- ----------
Dec        Dec       
1 row selected.

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

---------- ----------
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.


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.


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):

   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.


There are several options for format, which can be found in the documentation: 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
      FULL(@"SEL$EE94F965" "AT2"@"SEL$3")
      FULL(@"SEL$EE94F965" "AT1"@"SEL$1")
      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")

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")

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], 
   2 - "AT2"."OWNER"[VARCHAR2,30], "AT2"."TABLE_NAME"[VARCHAR2,30], "AT2"."NUM_ROWS"[NUMBER,22], "AC2"."OWNER"[VARCHAR2,30], 
   3 - "AT2"."OWNER"[VARCHAR2,30], "AT2"."TABLE_NAME"[VARCHAR2,30], "AT2"."NUM_ROWS"[NUMBER,22], "AC2"."OWNER"[VARCHAR2,30], 
   4 - "AT2"."OWNER"[VARCHAR2,30], "AT2"."TABLE_NAME"[VARCHAR2,30], "AT2"."NUM_ROWS"[NUMBER,22], "AC2"."OWNER"[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]
   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]

– 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.

   - 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")
              "AC1"."CONSTRAINT_TYPE"='R' AND "AC2"."OWNER"="AC1"."R_OWNER" AND 

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.


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):

   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
    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")
              "AC1"."CONSTRAINT_TYPE"='R' AND "AC2"."OWNER"="AC1"."R_OWNER" AND 

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…


SQL Tuning – Part 0 – Getting started

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

SQL Tuning – Intro

I have recently been doing presentations about SQL tuning, but haven’t really written anything on the topic here. Therefore, I have decided to put together a series of posts over the next few weeks on the process I use for tuning SQL. I plan to cover:

– Viewing and interpreting the current execution plan for a query
– Using a visual approach to find an optimal execution plan
– Various methods that can be used to go from the current execution plan to the optimal one
– Review of some tools that can help with these tasks