Re: Index advice requested.

Ed Stevens wrote:
>
> OK, I've been running "top sql" queries, OEM Index Analysis, and explain plans
> until my eyballs are about to crystalize.
>
> We have one particularly ugly query that is beating us to death. It repeatedly
> shows up when I run queries on the v$sqlarea to identify the sql with the most
> disk reads, and is far and away the costliest query out there. I have also run
> OEM Index Analysis Wizard at several different times of the day, over a several
> day period. None of its reports and recommendations identified the query we're
> looking at. I tried some of the recommneded indexes that looked like they might
> have an impact on the query, but the best I could get was one index making a
> small incremental improvement in the plan by changing one of 4 full table scans
> to an index scan with a cost reduction from 7171 to 7115.
>
> So here's the deal . . . below is the query, followed by a plan with the index
> structure as it exists today, followed definitions of the existing indexes. I'm
> open to any and all recommendations.
>
> explain plan for
> Select Distinct TBL1_REQ_NBR,
>
> <SNIP A BUNCH OF COLUMNS>
>
> FROM TABLE_01,
> SYN_REMOTE_TABLE A,
> SYN_REMOTE_TABLE B,
> TABLE_04,
> TABLE_02,
> SYN_REMOTE_TABLE C,
> TABLE_03
> WHERE ((TBL1_REQ_STA_CDE IN('APPROVED', 'COMPLETE')
> AND (TBL2_PO_STA_CDE NOT IN('COMPLETE', 'TOOLING')
> OR TBL2_PO_STA_CDE IS NULL))
> OR (TBL1_EMER_IND = 'Y'
> AND TBL1_REQ_STA_CDE <> 'COMPLETE'))
> AND TBL1_REQ_EMP_NBR = A.OLNUSR_USER_CDE
> AND TBL1_BYR_EMP_NBR = B.OLNUSR_USER_CDE (+)
> AND TBL1_PAEMPNBR = C.OLNUSR_USER_CDE (+)
> AND TBL1_RFQ_NBR = TBL3_RFQ_NBR (+)
> AND TBL1_RFQ_NBR = TBL4_RFQ_NBR (+)
> AND TBL1_PO_NBR = TBL2_PO_NBR (+)
> AND TBL1_PO_AMDT_NBR = TBL2_PO_AMDT_NBR (+)
> AND TBL1_BYR_EMP_NBR = 'XXXXXXXXXXX'
> ORDER BY TBL1_byr_emp_nbr,
> TBL1_reqd_dte
>
> OPERATION OPTIONS OBJECT NAME
> ORDER OPT
> ------------------------- ---------------
> ----------------------------------------------- ------------ ------
> SELECT STATEMENT COST = 7171
> 0-0-7171 CHOOSE
> SORT UNIQUE
> 1-0-1
> HASH JOIN OUTER
> 2-1-1
> HASH JOIN OUTER
> 3-2-1
> HASH JOIN OUTER
> 4-3-1
> HASH JOIN OUTER
> 5-4-1
> FILTER
> 6-5-1
> HASH JOIN OUTER
> 7-6-1
> HASH JOIN
> 8-7-1
> TABLE ACCESS FULL TABLE_01
> 9-8-1 ANALYZ
> REMOTE
> 10-8-2
> TABLE ACCESS FULL TABLE_02
> 11-7-2 ANALYZ
> TABLE ACCESS FULL TABLE_03
> 12-5-2 ANALYZ
> REMOTE
> 13-4-2
> REMOTE
> 14-3-2
> TABLE ACCESS FULL TABLE_04
> 15-2-2 ANALYZ
>
> TBL NDX POS COL
>
> -------------------- -------------------- -------- --------------------
>
> TABLE_01 TBL1_IDX1 1 TBL1_REQ_NBR
>
> 2 TBL1_SPLT_SEQNBR
>
> TBL1_IDX2 1 TBL1_PO_NBR
>
> 2 TBL1_PO_AMDT_NBR
>
> 3 TBL1_REQ_NBR
>
> 4 TBL1_SPLT_SEQNBR
>
> 5 TBL1_RFQ_NBR
>
> TBL NDX POS COL
>
> -------------------- -------------------- -------- --------------------
>
> TABLE_02 TBL2_IDX1 1 TBL2_PO_NBR
>
> 2 TBL2_PO_AMDT_NBR
>
> TBL2_IDX2 1 TBL2_PRIM_REQ_NBR
>
> 2 TBL2_PRIM_SEQNBR
>
> TBL2_IDX3 1 TBL2_PO_NBR
>
> 2 TBL2_PO_AMDT_NBR
>
> 3 TBL2_PDF_IND
>
> 4 TBL2_ADJ_TOT_CSTAMT
>
> TBL2_IDX4 1 TBL2_PO_STA_CDE
>
> 2 TBL2_PO_NBR
>
> TBL2_IDX5 1 TBL2_PO_AMDT_NBR
>
> 2 TBL2_PRIM_REQ_NBR
>
> 3 TBL2_PRIM_SEQNBR
>
> 4 TBL2_PO_NBR
>
> TBL NDX POS COL
>
> -------------------- -------------------- -------- --------------------
>
> TABLE_03 TBL3_IDX1 1 TBL3_RFQ_NBR
>
> TBL NDX POS COL
>
> -------------------- -------------------- -------- --------------------
>
> TABLE_04 TBL4_IDX1 1 TBL4_RFQ_NBR
>
> 2 TBL4_SEQ_NBR
>
> 3 TBL4_QTE_SEQ_NBR
>
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)
Are the stats up to date? Have you used tkprof to analyze the query? IMHO, tkprof will be your best tool in tuning this.

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.