From: "Robert Fazio" <rfazio@home.com.nospam>
Newsgroups: comp.databases.oracle.server
References: <f98999c8.0108080931.8a9301a@posting.google.com>
Subject: Re: SQL Statement Tuning Question
Lines: 59
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <oeic7.84633$EP6.20381528@news1.rdc2.pa.home.com>
Date: Wed, 08 Aug 2001 21:33:08 GMT
NNTP-Posting-Host: 24.8.218.197
X-Complaints-To: abuse@home.net
X-Trace: news1.rdc2.pa.home.com 997306388 24.8.218.197 (Wed, 08 Aug 2001 14:33:08 PDT)
NNTP-Posting-Date: Wed, 08 Aug 2001 14:33:08 PDT
Organization: Excite@Home - The Leader in Broadband http://home.com/faster


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@yahoo.com

"Buck Turgidson" <jc_va@hotmail.com> wrote in message
news:f98999c8.0108080931.8a9301a@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)