OraFAQ Forum: Performance Tuning » Exec plan explanation

Home » RDBMS Server » Performance Tuning » Exec plan explanation (11.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Exec plan explanation [message #590593]

Fri, 19 July 2013 10:23

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

I have a simple plan, that I can't understand:

orcl112>
orcl112> alter session set statistics_level=all;

Session altered.

orcl112> select emp.empno,emp.deptno,(select dept.dname from dept where dept.deptno=emp.deptno) from emp;

     EMPNO     DEPTNO (SELECTDEPT.DN
---------- ---------- --------------
      7369         20 RESEARCH
      7499         30 SALES
      7521         30 SALES
      7566         20 RESEARCH
      7654         30 SALES
      7698         30 SALES
      7782         10 ACCOUNTING
      7788         20 RESEARCH
      7839         10 ACCOUNTING
      7844         30 SALES
      7876         20 RESEARCH
      7900         30 SALES
      7902         20 RESEARCH
      7934         10 ACCOUNTING

14 rows selected.

orcl112> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  025cuqwr53ru3, child number 1
-------------------------------------
select emp.empno,emp.deptno,(select dept.dname from dept where
dept.deptno=emp.deptno) from emp

Plan hash value: 2981343222

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |     14 |00:00:00.01 |       7 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |      3 |00:00:00.01 |       5 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      3 |      1 |      3 |00:00:00.01 |       2 |
|   3 |  TABLE ACCESS FULL          | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPT"."DEPTNO"=:B1)


21 rows selected.

orcl112>

Can someone verbalize the sequence of events for me? The concept is clear enough: there are three distinct deptno values in emp, therefore three scans of pk_dept. My normal reading of a plan would be to say that the first operation is Id 2, but at that point there is no value to plug into :B1. I could understand it if the driving table were emp. But it isn't.

Thank you for any insight.

Report message to a moderator

Re: Exec plan explanation [message #590597 is a reply to message #590593]

Fri, 19 July 2013 10:34

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The first operation is n°3 and for each row returns by this operation the SELECT statement execute the operation n°2 and then n°1.

This is a a very bad way to write the statement, the correct way is to join emp and dept.

select emp.empno,emp.deptno,dept.dname
from emp, dept
where dept.deptno=emp.deptno
/

Regards
Michel

Report message to a moderator

Re: Exec plan explanation [message #590600 is a reply to message #590597]

Fri, 19 July 2013 10:43

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

Thankyou for replying. I'm sure that is the way it is running, but I don't understand the display. I would have expected the plan to look like this:

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |     14 |00:00:00.01 |       7 |
|   3 |  TABLE ACCESS FULL          | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |      3 |00:00:00.01 |       5 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      3 |      1 |      3 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------

(I do know that this not the best SQL structure, I'm dealing with a query that projects numerous scalar sub-queries).

Report message to a moderator

Re: Exec plan explanation [message #590601 is a reply to message #590600]

Fri, 19 July 2013 11:00

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No it is not, remember the chart has to be read from bottom to top.
So you have to access the whole EMP table and for each row of this later one, the SELECT statement has to execute the query in its clause.
Note this is the SELECT statement that executes the query in it, there is no join as for the query I gave:

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN                  |         |      1 |     15 |     14 |00:00:00.01 |      11 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |      4 |00:00:00.01 |       4 |       |       |          |
|   3 |    INDEX FULL SCAN           | DEPT_PK |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
|*  4 |   SORT JOIN                  |         |      4 |     15 |     14 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL         | EMP     |      1 |     15 |     14 |00:00:00.01 |       7 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO")

Regards
Michel

[Updated on: Fri, 19 July 2013 11:01]

Report message to a moderator

Re: Exec plan explanation [message #590603 is a reply to message #590601]

Fri, 19 July 2013 11:12

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

I do appreciate your time for such a dumb question. But surely in the plan you have just given it is dept that is accessed first, ID 3 then 2 to get departments in deptno order. You get the same plan if you hint it, so surely dept is first:

orcl112> set autot trace exp
orcl112> select /*+ leading(dept) */* from emp natural join dept;

Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   812 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   812 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

orcl112>

Report message to a moderator

Re: Exec plan explanation [message #590604 is a reply to message #590603]

Fri, 19 July 2013 12:35

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes, you are surely right.
But in case of a MERGE JOIN it does not really matter.
You have 3 steps:
1/ Access one table and sort it on the join key
2/ Access the other table and sort it on the join key
3/ Merge the rows of the 2 sorted results on equal key.
If I had to implement this I think I took as first table the one with the lowest number of distinct key values.

Here's a funny result when you change the leading table:

SQL> select /*+ leading(dept) */* from emp natural join dept;

Execution Plan
----------------------------------------------------------
Plan hash value: 2125045483

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    15 |   825 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    15 |   825 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_PK |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    15 |   525 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    15 |   525 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

SQL> select /*+ leading(emp) */* from emp natural join dept;

Execution Plan
----------------------------------------------------------
Plan hash value: 1123238657

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    15 |   825 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    15 |   825 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    15 |   525 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

The operation is now a HASH JOIN with dept accessed in full scan...
And if you add an index on emp(deptno):

SQL> select /*+ leading(dept) */* from emp natural join dept;

Execution Plan
----------------------------------------------------------
Plan hash value: 694149207

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |    15 |   825 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP        |     4 |   140 |     1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |            |    15 |   825 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL        | DEPT       |     4 |    80 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | EMP_DEPTNO |     5 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

SQL> select /*+ leading(emp) */* from emp natural join dept;

Execution Plan
----------------------------------------------------------
Plan hash value: 43749909

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |    15 |   825 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |            |    15 |   825 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP        |    15 |   525 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | EMP_DEPTNO |    15 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |            |     4 |    80 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | DEPT       |     4 |    80 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

Regards
Michel

Report message to a moderator

Re: Exec plan explanation [message #590612 is a reply to message #590604]

Sat, 20 July 2013 03:48

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

OK, I think I have it straight: when a query projects a scalar subquery, the dbms_xplan format doesn't follow the usual rule. That is all. Generally speaking, if two operations are at the same level of indentation, the higher one runs first. But that isn't a rule, it is only the usual case, and it doesn't apply in this case. Just a bit of confusion in the layout. I've been reading plans for years, I'm surprised I hadn't noticed this oddity before.

Trivial, really.

Report message to a moderator

Re: Exec plan explanation [message #590656 is a reply to message #590604]

Sun, 21 July 2013 05:51

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Michel -

The example looked trivial at first look, but I have quick question if you can help me understand.

1. I always follow Rule of Thumb for any joins as to keep the smaller table as leading table to join with the other table. Are there oddities with this rule? (Like you mentioned about MERGE JOIN)
2. In your example with "/*+ leading(emp) */", why is the operation a HASH JOIN? I have read about hash joins a lot of times and the concept is to hash the smaller of the two result sets. So why is EMP hashed with DEPT. Is it due to the hint that we have forced the optimizer or am I reading the plan incorrectly.
3. Adding an index on emp(deptno) forced it to use nested loops. It is quite obvious because the rule is to do an index range scan to find a block. Is my understanding correct here?

[Updated on: Sun, 21 July 2013 06:04]

Report message to a moderator

Re: Exec plan explanation [message #590657 is a reply to message #590656]

Sun, 21 July 2013 06:21

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Activate the 10053 trace and you will have your answers.

Regards
Michel

Report message to a moderator

Re: Exec plan explanation [message #590659 is a reply to message #590657]

Sun, 21 July 2013 07:08

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

That was an useful reply Michel, thanks. I have never come across situation to use the 10053 Event to trace CBO. So I will do that and learn.

I was just going through http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:63445044804318
and T.Kyte says this trace event is undocumented, unsupported, and works only in certain circumstances. Just wondering what it exactly means.

Report message to a moderator

Re: Exec plan explanation [message #590660 is a reply to message #590659]

Sun, 21 July 2013 07:55

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And Jonathan Lewis wrote a whole book on the subject.
Your refer to a 2006 topic, changes appear with the time.
It is fully documented on MOS (and J. Lewis' book).
It is THE way to know why the optimizer chooses one plan or another one.

Regards
Michel

Report message to a moderator

Previous Topic:	Different execution plan in Different DB
Next Topic:	SQL Execution Plan Export/Import within 10.2.0.4 version databases

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 23 14:48:04 CST 2025