Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index advice requested.

Re: Index advice requested.

From: Peter <p.simpkin_at_its.uq.edu.au>
Date: Tue, 20 Nov 2001 09:08:33 +1000
Message-ID: <9tc3gg$oja$1@bunyip.cc.uq.edu.au>


Have you tried any hints? Sometimes a simple hint can drastically improve performance.

"Ed Stevens" <Ed_Stevens_at_nospam.noway.nohow> wrote in message news:3bf951f1.20904248_at_ausnews.austin.ibm.com...
> 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.)
Received on Mon Nov 19 2001 - 17:08:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US