Exec plan explanation [message #590593] |
Fri, 19 July 2013 10:23 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 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.
|
|
|
|
Re: Exec plan explanation [message #590600 is a reply to message #590597] |
Fri, 19 July 2013 10:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 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).
|
|
|
Re: Exec plan explanation [message #590601 is a reply to message #590600] |
Fri, 19 July 2013 11:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 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>
|
|
|
|
Re: Exec plan explanation [message #590612 is a reply to message #590604] |
Sat, 20 July 2013 03:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 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.
|
|
|
Re: Exec plan explanation [message #590656 is a reply to message #590604] |
Sun, 21 July 2013 05:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #590660 is a reply to message #590659] |
Sun, 21 July 2013 07:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
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
|
|
|