Re: SQL Statement Tuning Question

Find out how this helps. If it does then I can show you how to incorporate it into the peoplesoft environment.

First remove the hint.

alter session set optimizer_index_cost_adj=1; -- you can try any number from 1-10,000, 100 is the default.

I have found this to really help with certain queries.

P.S. See below.

--
Robert Fazio
Senior Technical Analyst
dbabob_at_yahoo.com

"Buck Turgidson" <jc_va_at_hotmail.com> wrote in message
news:f98999c8.0108080931.8a9301a_at_posting.google.com...


> I am trying to tune a PeopleSoft SQL statement.  I have added the

> Index hint which went a long way to improving performance(before it

> was FTS).  It still takes about 4 seconds.  Maybe this is as good as

> it gets, but I'd like to squeeze a little more out of it, since this

> drives the on-line system.  I don't really want to change the

> statement, but hope to acccomplish any improvements with hints.

>

> Any suggestions would be appreciated.  This is 8.1.6.1.

>

>

> SELECT A.BARNAME, MIN(A.BARLABEL) BARLABEL, MIN(A.ITEMNUM) ITEMNUM

> FROM PSMENUITEM A

> WHERE A.MENUNAME = 'MANAGE_PAYROLL_PROCESS_U.S.'

> AND A.MENUNAME <> 'TREE_MANAGER'

> AND A.BARNAME <> 'STRUCTURE'

> AND EXISTS (SELECT   /*+ INDEX (X PSBPSAUTHITEM) */ 'X'

>             FROM PSAUTHITEM X

>             WHERE X.CLASSID IN (SELECT OPRCLASS FROM PSOPRCLS

>                                 WHERE OPRID='ABCXYZ') OR ('N' = 'Y')

> AND X.MENUNAME=A.MENUNAME AND X.BARNAME = A.BARNAME AND

> X.AUTHORIZEDACTIONS > 0)

> GROUP BY A.BARNAME ORDER BY 3;

>

> SELECT STATEMENT Optimizer=CHOOSE (Cost=1988 Card=25 Bytes=2000)

>   SORT (ORDER BY)

>     SORT (GROUP BY NOSORT)

>       MERGE JOIN (CARTESIAN)

>         TABLE ACCESS (BY INDEX ROWID) OF PSMENUITEM

>           INDEX (RANGE SCAN) OF PS_PSMENUITEM (UNIQUE)

>         SORT (JOIN)

>           TABLE ACCESS (BY INDEX ROWID) OF PSAUTHITEM

>             INDEX (FULL SCAN) OF PSBPSAUTHITEM (NON-UNIQUE)


See above the full scan.  The index order is bad for this query.  My quess
is that whatever the fields are in that index, classid is not first.

Create index  psauthitem_i1 on psauthitem(classid) ...;



>               INDEX (UNIQUE SCAN) OF PS_PSOPRCLS (UNIQUE)