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

From: joel garry <joel-garry_at_home.com>
Date: Wed, 22 Apr 2009 16:15:23 -0700 (PDT)
Message-ID: <dfb3549b-e0e6-4826-9204-6dbc3bc1ad59_at_s1g2000prd.googlegroups.com>

On Apr 22, 1:51 pm, wode..._at_googlemail.com wrote:
> 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 |
> ---------------------------------------------------------------------------­------------------------------------

It's getting different numbers of rows and total size of data for each scan. You'd probably want it to just do the full table scan one time? What does the code look like?

jg

--
_at_home.com is bogus.
http://perens.com/works/articles/MorganHill/

Received on Wed Apr 22 2009 - 18:15:23 CDT