how to read this execution plan
From: amonte <ax.mount_at_gmail.com>
Date: Sat, 27 Mar 2010 22:57:31 +0100
Message-ID: <85c1fb131003271457v58259afegdc0b38620cd6fabc_at_mail.gmail.com>
Hello guys
| Id | Operation | Name | Rows
| Bytes |TempSpc| Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1
| 1742 | | 5145K (1)| 04:07:42 |
| 1 | SORT AGGREGATE | | 1
| 1742 | | | |
Date: Sat, 27 Mar 2010 22:57:31 +0100
Message-ID: <85c1fb131003271457v58259afegdc0b38620cd6fabc_at_mail.gmail.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Mar 27 2010 - 16:57:31 CDT