From: jc_va@hotmail.com (Buck Turgidson)
Newsgroups: comp.databases.oracle.server
Subject: SQL Statement Tuning Question
Date: 8 Aug 2001 10:31:22 -0700
Organization: http://groups.google.com/
Lines: 33
Message-ID: <f98999c8.0108080931.8a9301a@posting.google.com>
NNTP-Posting-Host: 65.193.99.4
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 997291882 31408 127.0.0.1 (8 Aug 2001 17:31:22 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 8 Aug 2001 17:31:22 GMT


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)