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: 45
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: <qkic7.84639$EP6.20386832@news1.rdc2.pa.home.com>
Date: Wed, 08 Aug 2001 21:39:34 GMT
NNTP-Posting-Host: 24.8.218.197
X-Complaints-To: abuse@home.net
X-Trace: news1.rdc2.pa.home.com 997306774 24.8.218.197 (Wed, 08 Aug 2001 14:39:34 PDT)
NNTP-Posting-Date: Wed, 08 Aug 2001 14:39:34 PDT
Organization: Excite@Home - The Leader in Broadband http://home.com/faster


Look into the fast security views from peoplesoft also.  I think thats where
this is coming from.

--
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)
>               INDEX (UNIQUE SCAN) OF PS_PSOPRCLS (UNIQUE)