explain plan shows fts on same table multiple times with different costs

From: <wodenic_at_googlemail.com>
Date: Wed, 22 Apr 2009 13:51:07 -0700 (PDT)
Message-ID: <5923d1a9-d25f-4636-9cac-a979497a23a8_at_q9g2000yqc.googlegroups.com>



Hi,

Sorry if this is a simple question.
It may be relevant to all versions, but this one comes from 10.2.0.4

The statement reads from the BKMAP_CONTRACT_LINE 3 times in this statement all using full table scans, but the reported cost for each fts differs

Is this usual?
Is the difference in cost caused by dbms_xplan being aware of the likelihood that many of the rows will already been in the buffer cache (presumably from the largest fts - Id 13)

I expected to see the same cost for each fts on the BKMAP_CONTRACT_LINE - hence the reason for my question

thanks
Craig


| Id  | Operation                 | Name                      | Rows
| Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                           |
550K|   222M|       |   150K  (6)| 01:22:39 |

|* 1 | HASH JOIN RIGHT OUTER | |
550K| 222M| 36M| 150K (6)| 01:22:39 |
|   2 |   INDEX FAST FULL SCAN    | BKMAP_CUSTOMER_SITE_N02   |
1601K|    18M|       |    75  (14)| 00:00:03 |

|* 3 | HASH JOIN OUTER | |
550K| 216M| 180M| 148K (6)| 01:21:13 |
|* 4 | HASH JOIN | |
550K| 173M| 157M| 117K (6)| 01:04:32 |
|* 5 | HASH JOIN RIGHT OUTER | |
550K| 151M| 17M| 82844 (8)| 00:45:25 | | 6 | TABLE ACCESS FULL | BKMAP_MATERIAL_ITEM | 781K| 9162K| | 231 (19)| 00:00:08 |
|* 7 | HASH JOIN OUTER | |
550K| 144M| 130M| 80959 (8)| 00:44:23 |
|* 8 | HASH JOIN | |
550K| 124M| 150M| 66175 (7)| 00:36:17 |
|* 9 | HASH JOIN | |
820K| 140M| 17M| 50570 (8)| 00:27:44 |
|* 10 | HASH JOIN | |
310K| 13M| | 5205 (5)| 00:02:52 |
|* 11 | TABLE ACCESS FULL| BKMAP_ORG_TO_GEO_XREF | 3
| 27 | | 3 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL| BKMAP_CONTRACT_HEADER |
5957K| 210M| | 5174 (5)| 00:02:51 |
|* 13 | TABLE ACCESS FULL | BKMAP_CONTRACT_LINE |
15M| 2012M| | 23742 (14)| 00:13:01 | | 14 | TABLE ACCESS FULL | BKMAP_CONTRACT_REFERENCES | 15M| 866M| | 3799 (13)| 00:02:05 | | 15 | TABLE ACCESS FULL | BKMAP_ITEM_INSTANCES | 13M| 512M| | 6873 (20)| 00:03:47 | | 16 | TABLE ACCESS FULL | BKMAP_CONTRACT_LINE | 23M| 974M| | 20994 (3)| 00:11:31 |
|* 17 | TABLE ACCESS FULL | BKMAP_CONTRACT_LINE |
8009K| 618M| | 21715 (6)| 00:11:55 | ---------------------------------------------------------------------------------------------------------------
Received on Wed Apr 22 2009 - 15:51:07 CDT

Original text of this message