advice for query cpu time difference on two servers
- From: "Singh, Alok" <asingh@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Jun 2004 16:29:33 -0400
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
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.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.
Other related posts:
- » advice for query cpu time difference on two servers
