advice for query cpu time difference on two servers

From: Singh, Alok <asingh_at_pplweb.com>
Date: Thu, 10 Jun 2004 16:29:33 -0400
Message-ID: <32080FFBEEE7104BBDBF34FB8E53EF1404E1975D@leh-mbx-2.ppl.com>

Hi!

I need some advice.

HP-UX 11.00/Oracle v9205. SGA is larger on the server where it takes more time.

Can someone pl. explain whether it is normal to see stats like 35 more disk buffers reads and 200K more consistent reads with diff. SGA size for the same q.?

SELECT DISTINCT LEVEL, QUAL_CODE AS S_QUAL_CODE, QUAL_QTYP_CODE AS   S_QUAL_QTYP_CODE, EFFECTIVE_DATE
FROM
QUALIFICATION_EQUIVALENCES
START WITH (QUAL_CODE, QUAL_QTYP_CODE)
IN (SELECT
  WQ.QUAL_CODE, WQ.QUAL_QTYP_CODE
  FROM WORK_QUALIFICATIONS WQ ,
   CURRENT_ASSIGNMENTS CA
WHERE CA.PER_PER_DB_ID = 100005667
  AND WQ.WASS_DB_ID = CA.WA_DB_ID
  AND (WQ.QUAL_CODE, WQ.QUAL_QTYP_CODE)
  IN ( SELECT DISTINCT
   QUAL_CODE,
   QUAL_QTYP_CODE
FROM QUALIFICATION_EQUIVALENCES
WHERE TRUNC(NVL(EFFECTIVE_DATE, '01-JAN-1900')) <= TRUNC(SYSDATE) START WITH QUAL_CODE_THE_SOURCE_OF = 'CH001R'    AND QUAL_QTYP_CODE_THE_SOURCE_OF = 'TRN'

 CONNECT BY PRIOR QUAL_CODE = QUAL_CODE_THE_SOURCE_OF AND
            PRIOR QUAL_QTYP_CODE = QUAL_QTYP_CODE_THE_SOURCE_OF))
CONNECT BY PRIOR  QUAL_CODE_THE_SOURCE_OF = QUAL_CODE
       AND PRIOR QUAL_QTYP_CODE_THE_SOURCE_OF =  QUAL_QTYP_CODE

ORDER BY LEVEL DESC call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.11 0.11 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 271 13.61 13.36 0 225167 0 539
------- ------ -------- ---------- ---------- ---------- ----------

total 273 13.72 13.48 0 225167 0 539

 CONNECT BY PRIOR QUAL_CODE = QUAL_CODE_THE_SOURCE_OF AND
            PRIOR QUAL_QTYP_CODE = QUAL_QTYP_CODE_THE_SOURCE_OF))
CONNECT BY PRIOR  QUAL_CODE_THE_SOURCE_OF = QUAL_CODE
       AND PRIOR QUAL_QTYP_CODE_THE_SOURCE_OF =  QUAL_QTYP_CODE

ORDER BY LEVEL DESC call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.22 0.20 5 59 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 268 28.54 28.25 35 433615 0 534
------- ------ -------- ---------- ---------- ---------- ----------

total 270 28.76 28.45 40 433674 0 534

Execution plan is exactly same on the two servers. #

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 36 (SUPERDATA)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (UNIQUE)
      0    CONNECT BY (WITH FILTERING)
      0     NESTED LOOPS
      0      NESTED LOOPS
      0       NESTED LOOPS
      0        MERGE JOIN (CARTESIAN)
      0         VIEW OF 'VW_NSO_1'
      0          FILTER
      0           CONNECT BY (WITH FILTERING)
      0            NESTED LOOPS
      0             INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                        'QEQU_QUAL_SOURCE_REV_I' (NON-UNIQUE)
      0             TABLE ACCESS   GOAL: ANALYZED (BY USER ROWID)
                         OF 'QUALIFICATION_EQUIVALENCES'
      0            NESTED LOOPS
      0             BUFFER (SORT)
      0              CONNECT BY PUMP
      0             TABLE ACCESS   GOAL: ANALYZED (BY INDEX
                        ROWID) OF 'QUALIFICATION_EQUIVALENCES'
      0              INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                         'QEQU_QUAL_SOURCE_REV_I' (NON-UNIQUE)
      0         BUFFER (SORT)
      0          INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'CAS_PK'
                     (UNIQUE)
      0        INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'QWQU_UC'
                   (UNIQUE)
      0       INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'QEQU_PK' (UNIQUE)

      0      TABLE ACCESS   GOAL: ANALYZED (BY USER ROWID) OF
                 'QUALIFICATION_EQUIVALENCES'
      0     NESTED LOOPS
      0      BUFFER (SORT)
      0       CONNECT BY PUMP
      0      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                 'QUALIFICATION_EQUIVALENCES'
      0       INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'QEQU_PK' (UNIQUE)

      0     NESTED LOOPS
      0      NESTED LOOPS
      0       INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'QWQU_PK' (UNIQUE)

      0       VIEW OF 'VW_NSO_2'
      0        FILTER
      0         CONNECT BY (WITH FILTERING)
      0          NESTED LOOPS
      0           INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                      'QEQU_QUAL_SOURCE_REV_I' (NON-UNIQUE)
      0           TABLE ACCESS   GOAL: ANALYZED (BY USER ROWID) OF
                      'QUALIFICATION_EQUIVALENCES'
      0          NESTED LOOPS
      0           BUFFER (SORT)
      0            CONNECT BY PUMP
      0           TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID)
                      OF 'QUALIFICATION_EQUIVALENCES'
      0            INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                       'QEQU_QUAL_SOURCE_REV_I' (NON-UNIQUE)
      0      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'CAS_PK' (UNIQUE)

Thanks,

Alok

The information contained in this message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please notify us immediately, and delete the original message.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Thu Jun 10 2004 - 15:28:16 CDT