SQL Statement Tuning Question

From: Buck Turgidson <jc_va_at_hotmail.com>
Date: 8 Aug 2001 10:31:22 -0700
Message-ID: <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)

Received on Wed Aug 08 2001 - 12:31:22 CDT