Explain plan cardinality way off / bad plan problem

From: Katrea <accolasia_at_comcast.net>
Date: 21 Jul 2005 16:14:09 -0700
Message-ID: <1121987649.792235.176470@g49g2000cwa.googlegroups.com>

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 Received on Thu Jul 21 2005 - 18:14:09 CDT