Re: Query tuning help

From: <michael_bialik_at_my-deja.com>
Date: Fri, 19 Nov 1999 15:01:33 GMT
Message-ID: <813ooa$o2c$1@nnrp1.deja.com>

Hi.

 I think your message was truncated.
 A number of questions :

1. Why are you using FULL hint? If you have an index with first column ACCOUNT for PS_DISTRIB_LINE table , the query will read about 1000 accounts only ( '751000' to '751999' ). Instead of it it must scan now the whole table. ( Unless it's just an example and you really need to access all rows of PS_DISTRIB_LINE table ).
2. If PS_DISTRIB_LINE is a large table, then CACHE hint just degrades paerformance.
3. Try to exchange tables order in FROM clause of sub-query: Instead of : 10 FROM PS_VCHR_ACCTG_LINE B, PS_JRNL_HEADER C use : 10 FROM PS_JRNL_HEADER C, PS_VCHR_ACCTG_LINE B From your Explain it seens that optimizer accesses PS_JRNL_HEADER first. I think that using VOUCHER_ID will improve the query. ( Assuming PS_VCHR2 index has VOUCHER_ID field at first place, otherwise define a new index ).
4. I suspect that PS_JRNL2 index of PS_JRNL_HEADER table has one field only - JRNL_HDR_STATUS. If it's true then
5. Check that you have another index for that table containing BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE. If not - define one. Let's call it I_PS_JRNL .
6. Ensure that the I_PS_JRNL index is used either by using hint : SELECT /*+ index ( B I_PS_JRNL) */ 'X' or by disabling the usage of PS_JRNL2 index using function : C.JRNL_HDR_STATUS || '' = 'P'

  HTH. Michael.

In article <4WkY3.7$pI1.13_at_tattler>,
  "Scott Klemens" <sklemens_at_gsu.edu> wrote:
> I am having a horrible time tuning this query. It really bothers me
that I
> can't figure this out.
>
> Can anyone tell me what I should look for first? This is the explain
plan.
>
> SORT GROUP BY
> FILTER
> TABLE ACCESS FULL PS_DISTRIB_LINE
> NESTED LOOPS
> INDEX RANGE SCAN PS_JRNL2
> TABLE ACCESS BY ROWID PS_VCHR_ACCTG_LINE
> INDEX RANGE SCAN PS_VCHR2
>
> Based on the where clause can you tell what you think should be
indexed for
> this query?
>
> The output from tkprof shows an obvious problem so....
>
> SELECT /*+ FULL(PS_DISTRIB_LINE) CACHE(PS_DISTRIB_LINE) */
> 2 A.ACCOUNT, A.FUND_CODE, A.DEPTID,
> 3 A.PROGRAM_CODE, A.APPROPRIATION_NBR, A.BUDGET_YEAR,
> 4 A.PROJECT_ID, SUM( A.MONETARY_AMOUNT)
> 5 FROM PS_DISTRIB_LINE A
> 6 WHERE A.ACCOUNT BETWEEN '751000' AND '751999'
> 7 AND A.FUND_CODE IN ('10','12','13','14','20','50','51')
> 8 AND A.BCM_LINE_STATUS = 'V'
> 9 AND NOT EXISTS (SELECT 'X'
> 10 FROM PS_VCHR_ACCTG_LINE B, PS_JRNL_HEADER C
> 11 WHERE A.VOUCHER_ID = B.VOUCHER_ID AND C.JRNL_HDR_STATUS = 'P'
> 12 AND C.POSTED_DATE IS NOT NULL
> 13 AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
> 14 AND B.JOURNAL_ID = C.JOURNAL_ID
> 15 AND B.JOURNAL_DATE = C.JOURNAL_DATE)
> 16* GROUP BY A.ACCOUNT, A.FUND_CODE, A.DEPTID, A.PROGRAM_CODE,
> A.APPROPR
> IATION_NBR, A.BUDGET_YEAR, A.PROJECT_ID
>
> SELECT /*+ FULL(PS_DISTRIB_LINE) CACHE(PS_DISTRIB_LINE) */
> 2 A.ACCOUNT, A.FUND_CODE, A.DEPTID,
> 3 A.PROGRAM_CODE, A.APPROPRIATION_NBR, A.BUDGET_YEAR,
> 4 A.PROJECT_ID, SUM( A.MONETARY_AMOUNT)
> 5 FROM PS_DISTRIB_LINE A
> 6 WHERE A.ACCOUNT BETWEEN '751000' AND '751999'
> 7 AND A.FUND_CODE IN ('10','12','13','14','20','50','51')
> 8 AND A.BCM_LINE_STATUS = 'V'
> 9 AND NOT EXISTS (SELECT 'X'
> 10 FROM PS_VCHR_ACCTG_LINE B, PS_JRNL_HEADER C
> 11 WHERE A.VOUCHER_ID = B.VOUCHER_ID AND C.JRNL_HDR_STATUS = 'P'
> 12 AND C.POSTED_DATE IS NOT NULL
> 13 AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
> 14 AND B.JOURNAL_ID = C.JOURNAL_ID
> 15 AND B.JOURNAL_DATE = C.JOURNAL_DATE)
> 16* GROUP BY A.ACCOUNT, A.FUND_CODE, A.DEPTID, A.PROGRAM_CODE,
> A.APPROPR
> IATION_NBR, A.BUDGET_YEAR, A.PROJECT_ID
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 19 1999 - 09:01:33 CST