Re: how to read this execution plan
Date: Sun, 28 Mar 2010 22:28:09 -0500
Message-ID: <i2w516d05a1003282028y81465196pb61d19d424898137_at_mail.gmail.com>
My read of that plan, sequentally translated into English:
- Perform a fast full scan using D_EEII_ADDRESS_LOOKUP_UI, building a hash table.
- Perform a fast full scan of MSNS_HTTP_REJECTED, probing the hash table built in step 1 to generate an intermediate result set.
- Perform a fast full scan of D_SERVICE_IP_ADDR, building a hash table.
- Probe the hash table generated in step 3 with the intermediate result from step 2.
- Lather, rinse, and repeat going up the stack.
One very useful tool I've found for interpreting explain plan is the "fat" OEM client that shipped as part of the client installation for 10g and previous releases; one can look at each step of a plan for an executing query (such as all the HASH JOIN RIGHT OUTERS) and get a detailed explanation of what it does, what it means, and which table is the driving table.
On Sat, Mar 27, 2010 at 4:57 PM, amonte <ax.mount_at_gmail.com> wrote:
> Hello guys
>
> I have following execution plan
>
> ---------------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows
> | Bytes |TempSpc| Cost (%CPU)| Time |
> ---------------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1
> | 1742 | | 5145K (1)| 04:07:42 |
> | 1 | SORT AGGREGATE | | 1
> | 1742 | | | |
> |* 2 | HASH JOIN RIGHT OUTER | |
> 14M| 23G| | 5145K (1)| 04:07:42 |
> | 3 | VIEW | | 50603
> | 1087K| | 83 (3)| 00:00:01 |
> | 4 | INDEX FAST FULL SCAN | D_TERMINAL_ID_WID_UI | 50603
> | 444K| | 83 (3)| 00:00:01 |
> |* 5 | HASH JOIN RIGHT OUTER | |
> 14M| 23G| | 5145K (1)| 04:07:41 |
> | 6 | VIEW | | 23
> | 4646 | | 1 (0)| 00:00:01 |
> | 7 | BITMAP CONVERSION TO ROWIDS | | 23
> | 299 | | 1 (0)| 00:00:01 |
> | 8 | BITMAP INDEX FAST FULL SCAN| D_PXTR_ADDRESS_IDX |
> | | | | |
> |* 9 | HASH JOIN RIGHT OUTER | |
> 14M| 20G| 641M| 5145K (1)| 04:07:40 |
> | 10 | VIEW | |
> 2650K| 611M| | 7120 (2)| 00:00:21 |
> | 11 | TABLE ACCESS FULL | D_PRTR_USER_AGENT |
> 2650K| 611M| | 7120 (2)| 00:00:21 |
> |* 12 | HASH JOIN RIGHT OUTER | |
> 14M| 17G| 2193M| 3557K (1)| 02:51:13 |
> | 13 | VIEW | |
> 9056K| 2090M| | 16289 (3)| 00:00:48 |
> | 14 | TABLE ACCESS FULL | D_PRTR_HOST |
> 9056K| 2090M| | 16289 (3)| 00:00:48 |
> |* 15 | HASH JOIN RIGHT OUTER | |
> 14M| 13G| 189M| 2112K (1)| 01:41:41 |
> | 16 | VIEW | |
> 928K| 178M| | 1557 (2)| 00:00:05 |
> | 17 | INDEX FAST FULL SCAN | D_SERVICE_IP_ADDR_PK |
> 928K| 12M| | 1557 (2)| 00:00:05 |
> |* 18 | HASH JOIN RIGHT OUTER | |
> 14M| 11G| 3144K| 1094K (1)| 00:52:41 |
> | 19 | VIEW | | 15031
> | 2965K| | 36 (3)| 00:00:01 |
> | 20 | INDEX FAST FULL SCAN | D_EEII_ADDRESS_LOOKUP_UI | 15031
> | 220K| | 36 (3)| 00:00:01 |
> | 21 | TABLE ACCESS FULL | MSNS_HTTP_REJECTED |
> 14M| 8640M| | 333K (2)| 00:16:04 |
> ---------------------------------------------------------------------------------------------------------------------
>
>
> I think the execution starts from step 20,, then 21 and 18 and so on but
> while this was running I checked v$session_longops and observed this:
>
> D_PRTR_USER_AGENT was accessed before D_PRTR_HOST and MSNS_HTTP_REJECTED,
> D_PRTR_HOST was accessed before MSNS_HTTP_REJECTED
>
> So I have a doubt how to read this plan? My assumption of starting step 20
> then 18 and then 21 is wrong?
>
>
> Alex
>
>
--
Adam Musch
ahmusch_at_gmail.com
†Ûiÿü0ÁúÞzX¬¶Ê+ƒü n– {ú+iÉ^
Received on Sun Mar 28 2010 - 22:28:09 CDT