| Sort Join understanding [message #611840] |
Wed, 09 April 2014 05:57  |
 |
rekha.singhal@tcs.com
Messages: 6
Registered: March 2012
Location: India
|
Junior Member |
|
|
Please look at folloiwng query and its execution plan.
select /*+ ordered USE_MERGE(o c) */
sum(c.c_acctbal)+ sum(o.o_totalprice)
from orders o, customer c
where c.c_custkey = o.o_custkey;
EXECUTION PLAN:-----------------------
------------------------------------------------------------------------------------------------------------------------------------- -------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------- -------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:15:29.75 | 3573K| 5013K| 568K| | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:15:29.75 | 3573K| 5013K| 568K| | | | |
| 2 | MERGE JOIN | | 1 | 192M| 192M|00:15:00.89 | 3573K| 5013K| 568K| | | | |
| 3 | SORT JOIN | | 1 | 192M| 192M|00:10:22.83 | 3132K| 3607K| 475K| 4175M| 20M| 100M (1)| 3712K|
| 4 | TABLE ACCESS FULL| ORDERS | 1 | 192M| 192M|00:04:08.01 | 3132K| 3132K| 0 | | | | |
|* 5 | SORT JOIN | | 192M| 19M| 192M|00:03:20.64 | 440K| 1406K| 93446 | 411M| 6704K| 100M (1)| |
| 6 | TABLE ACCESS FULL| CUSTOMER | 1 | 19M| 19M|00:00:34.02 | 440K| 440K| 0 | | | | |
------------------------------------------------------------------------------------------------------------------------------------- -------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C"."C_CUSTKEY"="O"."O_CUSTKEY")
filter("C"."C_CUSTKEY"="O"."O_CUSTKEY")
I have two questions:-
1. How does 'Sort Join" works? If you look at line number 5 in the plan- it is started 192 M times. I understand that sort merge join, sorts both the tables and merges them. Why it is started 192M times (number of rows in order table). What oracle 11g is doing in each start.
2. Again line number 5 - Number of temp Reads are around 1000K becuase of tempspace, however writes are only 93K- why so much extra reads for. As expected, in line number 3 for Orders Sort- there are same number of temp reads and temp writes.
Kindly explain.
Warm Regards,
Rekha
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
