Re: Explain plan cardinality way off / bad plan problem

Katrea wrote:
> I have been trying to figure out why the optimizer is making the
> choices it is, and am stumped by some of its assumptions. The first
> step in the explain plan is a full table scan with a cardinality of 1
> row. There is no criteria on that table that I can see that can be
> filtered at the first step, so it basically grabs everything in the
> table as row source. There are 5302 rows in the table, but the
> optimizer seems to think there is only 1. There are many other queries
> in the system I need to optimize that seem to have the same problem,
> but those have millions of rows in them.
>
> I reran statistics for all the tables in the query and all the indexes
> and columns, deleted them, tried DBMS_STATS vs ANALYZE... I was
> thinking maybe it was possibly using a histogram to figure out this
> cardinality that was inaccurate? Toad says there are histograms for all
> the columns, but when I looked at the histograms in user_histograms
> they had If I delete the column statistics for the
> process_control_strategy table, the query doesnt do the scan for this
> table first and is much faster, and the cardinality values look like
> more reasonable guesses. If anyone could shed some light on this, I
> would appreciate it. My wall is suffering from the many dents made by
> my head. Thanks much.
>
> Query:
> (this has a view it queries from that I added to the from clause)
>
> SELECT docname, operation, route, event, entity, product, param FROM
> (
> SELECT
> e.PARAM,
> e.ATTR,
> e.TYPE,
> pp.OPERATION,
> pp.ROUTE,
> pp.EVENT,
> entity.ENTITY,
> prod.PRODUCT,
> pcs.VERSION_SK,
> pcs.DOC_SK,
> doc.DOCNAME
> FROM
> ASPECT.PROC_POINT pp,
> ASPECT.ENTRY e,
> ASPECT.PROC_STEP step,
> ASPECT.CONTEXT_PRODUCT prod,
> ASPECT.CONTEXT_ENTITY entity,
> ASPECT.PROCESS_CONTROL_STRATEGY pcs,
> FASTBAL.DOCUMENT doc
> WHERE
> pp.STEP_SK = e.STEP_SK (+)
> AND pp.PATH_SK = prod.PATH_SK (+)
> AND pp.STEP_SK = entity.STEP_SK (+)
> AND pp.STEP_SK = step.STEP_SK
> AND step.VERSION_SK = pcs.VERSION_SK
> AND pcs.DOC_SK = doc.DOC_SK
> AND step.ORDER_NO = (
> SELECT MAX(LAST_STEP.ORDER_NO)
> FROM PROC_STEP last_step
> WHERE last_step.VERSION_SK = pcs.VERSION_SK
> )
> AND (e.TYPE IN (0, 1, 14) OR e.TYPE IS NULL)
> AND doc.IS_ACTIVE = 'T' ) myview
> WHERE ( (OPERATION = '5231' OR OPERATION IS NULL)
> AND ROUTE IS NULL
> AND (EVENT = 'MYEVENT3' OR EVENT IS NULL)
> AND (PRODUCT = 'MyProduct' OR PRODUCT IS NULL )
> )
> AND (entity = 'entity3' OR entity IS NULL)
> AND ( param IN ('EI-STPTIME3A') )
> AND version_sk =
> ( SELECT max(version_sk)
> FROM fastbal.version child
> WHERE
> child.VERSIONSTATE = 'APPR'
> and child.DOC_SK=myview.DOC_SK)
>
>
> Explain Plan
> Explain Plan from the optimizer, notice at step 1 it thinks 1 row. (I
> tried to make this as pretty as I could)
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 4 28
>
> FILTER
> FILTER
> NESTED LOOPS OUTER
> FILTER
> NESTED LOOPS OUTER
> NESTED LOOPS 1 83 24
> NESTED LOOPS 1 74 22
> NESTED LOOPS 1 54 19
> NESTED LOOPS 1 41 17
> TABLE ACCESS FULL ASPECT.PROCESS_CONTROL_STRATEGY 1 10 16
>
> TABLE ACCESS BY INDEX ROWID FASTBAL.DOCUMENT 1 31 1
> INDEX UNIQUE SCAN FASTBAL.PK_DOCUMENT 1
> TABLE ACCESS BY INDEX ROWID ASPECT.PROC_STEP 1 13 2
> INDEX RANGE SCAN ASPECT.IDX_PROCSTEP_VERSION_ORDER 1 1
> SORT AGGREGATE 1 8
> FILTER
> FIRST ROW 3 24 2
> INDEX RANGE SCAN (MIN/MAX) ASPECT.IDX_PROCSTEP_VERSION_ORDER 2 K 2
> TABLE ACCESS BY INDEX ROWID ASPECT.PROC_POINT 1 20 3
>
> INDEX RANGE SCAN ASPECT.PK_PROC_POINT 1 2
>
> TABLE ACCESS BY INDEX ROWID ASPECT.ENTRY 1 9 2
>
> INDEX RANGE SCAN ASPECT.IDX_ENTRY_STEP_SK_1 3 1
>
> TABLE ACCESS BY INDEX ROWID ASPECT.CONTEXT_PRODUCT 1 13 2
>
> INDEX RANGE SCAN ASPECT.FK_CTXT_PROD_PATH_SK 1 1
>
> TABLE ACCESS BY INDEX ROWID ASPECT.CONTEXT_ENTITY 4 48 2
>
> INDEX RANGE SCAN ASPECT.IDX_ENTITY_STEP_SK 4 1
>
> SORT AGGREGATE 1 15
> TABLE ACCESS BY INDEX ROWID FASTBAL.VERSION 2 30 2
>
> INDEX RANGE SCAN FASTBAL.IDX_VERSION_2 8 1
>
>
>
> TKPROF Plan
>
> Select Statement
> 25 FILTER cr : 27,272.00 Act Rows : 1 Time : 0.1900
> 21 FILTER cr : 27,269.00 Act Rows : 1 Time : 0.1900
> 20 NESTED LOOPS OUTER cr : 27,269.00 Act Rows : 1 Time : 0.1900
> 17 FILTER cr : 27,266.00 Act Rows : 1 Time : 0.1900
> 16 NESTED LOOPS OUTER cr : 27,266.00 Act Rows : 1 Time : 0.1900
> 13 NESTED LOOPS cr : 27,263.00 Act Rows : 1 Time : 0.1900
> 10 NESTED LOOPS cr : 27,259.00 Act Rows : 1 Time : 0.1900
> 7 NESTED LOOPS cr : 20,794.00 Act Rows : 3,221 Time : 0.1500
> 4 NESTED LOOPS cr : 10,674.00 Act Rows : 3,670 Time : 0.0500
> 1 TABLE ACCESS FULL ASPECT.PROCESS_CONTROL_STRATEGY cr : 68.00 Act Rows
> : 5,302 Time : 0.0100
> 3 TABLE ACCESS BY INDEX ROWID FASTBAL.DOCUMENT cr : 10,606.00 Act Rows
> : 3,670 Time : 0.0400
> 2 INDEX UNIQUE SCAN FASTBAL.PK_DOCUMENT cr : 5,304.00 Act Rows : 5,302
> Time : 0.0200
> 6 TABLE ACCESS BY INDEX ROWID ASPECT.PROC_STEP cr : 6,448.00 Act Rows :
> 3,221 Time : 0.0300
> 5 INDEX RANGE SCAN ASPECT.IDX_PROCSTEP_VERSION_ORDER cr : 3,229.00 Act
> Rows : 3,221 Time : 0.0200
> 9 TABLE ACCESS BY INDEX ROWID ASPECT.PROC_POINT cr : 6,465.00 Act Rows
> : 1 Time : 0.0300
> 8 INDEX RANGE SCAN ASPECT.PK_PROC_POINT cr : 3,235.00 Act Rows : 4,328
> Time : 0.0200
> 12 TABLE ACCESS BY INDEX ROWID ASPECT.ENTRY cr : 4.00 Act Rows : 1 Time
> : 0.0000
> 11 INDEX RANGE SCAN ASPECT.IDX_ENTRY_STEP_SK_1 cr : 3.00 Act Rows : 1
> Time : 0.0000
> 15 TABLE ACCESS BY INDEX ROWID ASPECT.CONTEXT_PRODUCT cr : 3.00 Act
> Rows : 1 Time : 0.0000
> 14 INDEX RANGE SCAN ASPECT.FK_CTXT_PROD_PATH_SK cr : 2.00 Act Rows : 1
> Time : 0.0000
> 19 TABLE ACCESS BY INDEX ROWID ASPECT.CONTEXT_ENTITY cr : 3.00 Act Rows
> : 1 Time : 0.0000
> 18 INDEX RANGE SCAN ASPECT.IDX_ENTITY_STEP_SK cr : 2.00 Act Rows : 1
> Time : 0.0000
> 24 SORT AGGREGATE cr : 3.00 Act Rows : 1 Time : 0.0000
> 23 TABLE ACCESS BY INDEX ROWID FASTBAL.VERSION cr : 3.00 Act Rows : 1
> Time : 0.0000
> 22 INDEX RANGE SCAN FASTBAL.IDX_VERSION_2 cr : 2.00 Act Rows : 1 Time :
> 0.0000

What are the values in v$parameter for optimizer_index_cost_adj and optimizer_index_caching?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)