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,
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 |550K| 222M| 36M| 150K (6)| 01:22:39 |
|* 1 | HASH JOIN RIGHT OUTER | |
| 2 | INDEX FAST FULL SCAN | BKMAP_CUSTOMER_SITE_N02 | 1601K| 18M| | 75 (14)| 00:00:03 |Received on Wed Apr 22 2009 - 15:51:07 CDT
|* 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 | ---------------------------------------------------------------------------------------------------------------