Mohamed Houri’s Oracle Notes

June 13, 2013

SQLTXPLAIN: Execution plan and operation order : Exec Ord column

Filed under: Oracle — hourim @ 10:01 am

I’ve recently decided to start exploring the Oracle SQLTXPLAIN tool developed by Carlos Sierra from Oracle support. Up to know I am still exploring the different possibilities that tool offers for diagnosing query response time performance problem. I thought that the best strategy to start with SQLTXPLAIN is to take a query that I have trouble shouted using a traditional method and apply SQLTXTRACT for it to see if I can point out from this SQLTXTRACT output the issue I know it is at the origin of the query performance problem.

While I was exploring the execution plan part of the sqltxtract report applied to my query

ID Exec Ord Operation Go To More Cost2 Estim Card
0 8 SELECT STATEMENT 255 100
1 7  NESTED LOOPS [+] 257
2 5 . NESTED LOOPS [+] 254 100
3 3 .. SORT UNIQUE [+] 103 100
4 2 … TABLE ACCESS BY INDEX ROWID T2 [+] [+] 103 100
5 1 …. INDEX RANGE SCAN T2_I1 [+] [+] 3 100
6 4 .. INDEX RANGE SCAN T1_N1 [+] [+] 2 1
7 6 . TABLE ACCESS BY INDEX ROWID T1 [+] [+] 3 1

I was suddenly attracted by the Exec Ord column. That’s a very nice feature showing the order of the operation as they have been executed by the SQL engine. However, the traditional strategy for reading plans following a parent-child relationship and indentation is not always correct.  This is why I decided to see if the Exec Ord column is correctly reported in the particular cases where the traditional plan reading is wrong. For that I used the example given by Jonathan Lewis in his “constant subquery” case and that you can easily reproduce:

------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | A-Rows |
------------------------------------------------------------------
|*  1 |  FILTER               |       |      1 |        |      0 |
|*  2 |   HASH JOIN RIGHT SEMI|       |      0 |      1 |      0 |
|   3 |    TABLE ACCESS FULL  | F1    |      0 |     20 |      0 |
|   4 |    TABLE ACCESS FULL  | T1    |      0 |  10000 |      0 |
|*  5 |   INDEX UNIQUE SCAN   | F2_PK |      1 |      1 |      0 |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - access("F1"."ID"="T1"."ID" AND "F1"."SMALL_VC"="T1"."SMALL_VC")
5 - access("F2"."ID"=21)

The traditional plan reading will say that the first operation started here is operation 3, followed by operation 4 all combined with operation 2; and finally the operation 5 is triggered by its parent operation 1. But, as explained in Jonathan Lewis post, things have not been executed as this traditional plan reading suggests. Operation 2, 3 and 4 has not been started at all (Starts = 0).

My curiosity dictated me to run sqltxtract for the above query and get the corresponding execution plan together with the Exec Ord column:

ID Exec Ord Operation Go To More Cost2 Estim Card Work Area
0 6 SELECT STATEMENT 55
1 5  FILTER [+] 55
2 3 . HASH JOIN RIGHT SEMI [+] 55 1 [+]
3 1 .. TABLE ACCESS FULL F1 [+] [+] 3 20
4 2 .. TABLE ACCESS FULL T1 [+] [+] 51 10000
5 4 . INDEX UNIQUE SCAN F2_PK [+] [+] 0 1

As you can see from the above execution plan, sqltxtract module is, unfortunately, also reporting a wrong operation order (Exec Ord column) as far as it is showing that the first operations executed here are operation 3 and 4 while actually these two operations have not been executed at all. It would be very nice if the Exec Ord operation could take into account the special cases where the traditional “first child first” rule is not applicable. Among those cases, I know two :  the  “constant subquery” and index scan with a filter predicate applying a subquery

About these ads

3 Comments »

  1. Mohamed,
    You are right SQLT uses a simple algorithm to report in execution order, which in most cases is correct, but not in special cases like the ones you mentioned. It would make a nice enhancement to SQLT, unfortunately there are always more important/urgent issues to incorporate. Maybe one day…
    Cheers — Carlos

    Comment by Carlos Sierra — June 13, 2013 @ 10:08 am | Reply

  2. Thanks for the post and there are two things we can learn here….
    (a) the existence of SQLTXPLAIN and
    (b) there are special cases where the traditional execution plan operation rule is not correct

    Comment by Nassyam Basha — June 13, 2013 @ 10:18 am | Reply

  3. […] issue?’’. Up to now this is my sole strategy of using this tool like what I have published here […]

    Pingback by SQLTXPLAIN under Oracle 12c | Mohamed Houri’s Oracle Notes — November 18, 2013 @ 8:15 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Database 11g

Oracle Database

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Follow

Get every new post delivered to your Inbox.

Join 99 other followers

%d bloggers like this: