Reading Nested Loop Xplans
- From: Fuad Habash <fmhabash@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 13 Sep 2012 10:26:09 -0400
Trying to figure out why there are 2 'nested loops' and a 'hash unique'
steps in this xplan. This is how am reading it.
1) access table_02 to get rowset matching time stamp (step 5)
2) For each row returned from step 1, get a matching row from table_01
where m.message_id = e.message_id using index PK_MSG_PERSON (step 6).
3) above 2 steps form nested loop 1 (step 3)
From this point, I'm not clear on what happens in step 2 & 1.
Any insights will be appreciated.
SELECT /*+ gather_plan_statistics */ distinct
m.MESSAGE_ID,m.SENDER_CODE,m.RECEIVER_CODE
,m.doc_type_name,m.receiver_doc_type_name,
m.sender_id,m.receiver_id,m.sender_channel_id,
m.receiver_channel_id,m.control_number,e.TYPE_ID,
nvl(e.INSTANCE_MESSAGE,'null'), m.RECEIVED_TIMESTAMP
FROM
table_01 m, table_02 e
WHERE
m.message_id = e.message_id
and
e.INSTANCE_TIMESTAMP >= dt_tm_01
AND
e.INSTANCE_TIMESTAMP <= dt_tm_02
AND
(m.DELIVERY_STATUS = 's'
or
e.type_id in(1,2,3))
----------------------------------------
Plan hash value: 469965103
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts
| E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem
| Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1
| | 37076 |00:01:17.84 | 264K| 24989 | 8130 | |
| | |
| 1 | HASH UNIQUE | | 1
| 1 | 37076 |00:01:17.84 | 264K| 24989 | 8130 | 67M|
7218K| 11M (1)| 69632 |
| 2 | NESTED LOOPS (2) | |
1 | | 50495 |00:01:01.65 | 264K| 16859 | 0 | |
| | |
| 3 | NESTED LOOPS (1) | |
1 | 1 | 53162 |00:00:25.24 | 214K| 4005 | 0 | |
| | |
|* 4 | TABLE ACCESS BY INDEX ROWID| table_02 | 1 | 1 |
53162 |00:00:01.95 | 57665 | 102 | 0 | | |
| |
|* 5 | INDEX RANGE SCAN | EXP_INST_INST_TMSTMP_IDX | 1
| 1 | 59198 |00:00:00.07 | 348 | 1 | 0 | |
| | |
|* 6 | INDEX UNIQUE SCAN | PK_MSG_PERSON | 53162 |
1 | 53162 |00:00:17.99 | 157K| 3903 | 0 | | |
| |
|* 7 | TABLE ACCESS BY INDEX ROWID | table_01 | 53162 |
1 | 50495 |00:00:57.13 | 49705 | 12854 | 0 | | |
| |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("E"."MESSAGE_ID" IS NOT NULL)
5 - access("E"."INSTANCE_TIMESTAMP">=dt_tm_01 AND
"E"."INSTANCE_TIMESTAMP"<=dt_tm_02)
6 - access("M"."MESSAGE_ID"="E"."MESSAGE_ID")
7 - filter(("M"."DELIVERY_STATUS"='s' OR
INTERNAL_FUNCTION("E"."TYPE_ID")))
--
//www.freelists.org/webpage/oracle-l
Other related posts:
