Re: Reading Nested Loop Xplans

From: Paul Drake <bdbafh_at_gmail.com>
Date: Thu, 13 Sep 2012 11:39:50 -0400
Message-ID: <CAPptggV=rVKE4RyDBaWd4vAmhvaBDkXBkWTXGxuuCuFEfdoaUg_at_mail.gmail.com>

Fuad,
Why are you focusing on the access paths in the execution plan when it appears that the main issue is the difference between estimated and actual rows?
Kudos for supplying an execution plan with gather_plan_statistics that in the post, btw.
If you can get the correct statistics, histograms, etc. so that the CBO gets the estimated cardinailities right, you probably will have a different, better execution plan.

What might the filter factors be for m.DELIVERY_STATUS = 's' and e.type_id in(1,2,3)?
Are there histograms computed on those columns?

Paul

On Thu, Sep 13, 2012 at 10:26 AM, Fuad Habash <fmhabash_at_gmail.com> wrote:

> 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")))
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
http://www.completestreets.org/faq.html
http://safety.fhwa.dot.gov/ped_bike/docs/pamanual.pdf


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 13 2012 - 10:39:50 CDT