Oracle-L: RE: SQL_Trace versus Statspack

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Wed, 19 Nov 2003 14:04:59 -0800
Message-ID: <F001.005D7318.20031119140459@fatcity.com>

Vivek did mention single DB connect process, so this should increase the possibility of statspack matching the trace file

Did you have parallel query occurring - as then the single trace wouldn't have caught everything. Are you SURE no other users were active at the same time - eg look at logons cumulative in v$sysstat

Were the trace and snapshot for the EXACT same time interval?

HTH,
Bruce Reardon

NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed.

-----Original Message-----
Sent: Thursday, 20 November 2003 12:15 AM To: Multiple recipients of list ORACLE-L

Vivek,

as others have pointed out before, Statspack is instance wide snapshot. Trace file is session specific.

Raj

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
Sent: Wednesday, November 19, 2003 5:40 AM To: Multiple recipients of list ORACLE-L

We are doing Interest processing on a SET of 10,000 Bank A/cs using a Single Database connect process.

SQL Query :-

select <field names>,rowid into :b0,:b1,... from TBA_ENTITY_INTEREST_TBL
where (entity_id=:b105 and entity_type=:b106) for update of <same (above) field names> nowait;

Above SQL Query Shows DIFFERING values (taken concurrently) for:- "Execute" from SQL_TRACE = 2584
"Executions" from Statspack report = 10,000

Qs. What is the reason for this?
Qs. Have we possibly missed some SQL trace files?

SQL_TRACE :-
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0
Execute 2584 0.55 0.55 0 10475 2783 0
Fetch 2584 0.50 0.51 0 0 0 2584
------- ------ -------- ---------- ---------- ---------- ----------

total 5169 1.05 1.06 0 10475 2783 2584

Statspack :-

                                                     CPU      Elapsd

Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash
Value
--------------- ------------ -------------- ------ -------- ---------

51,271 10,000 5.1 23.0 4.31 3.95 1862033429
Module: icbx4008_at_bomitd7003 (TNS V1-V3)
select TO_CHAR(accrued_upto_date_cr,'DD-MM-YYYY HH24:MI:SS') ,TO

_CHAR(accrued_upto_date_dr,'DD-MM-YYYY HH24:MI:SS') ,TO_CHAR(las
t_accrual_run_date_cr,'DD-MM-YYYY HH24:MI:SS') ,TO_CHAR(last_acc
rual_run_date_dr,'DD-MM-YYYY HH24:MI:SS') ,TO_CHAR(booked_upto_d

ate_cr,'DD-MM-YYYY HH24:MI:SS') ,TO_CHAR(booked_upto_date_dr,'DD

NOTE - Actual SQL Query partly visible above too in statspack Output

Will provide any data needed

Thanks
--

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

Author: VIVEK_SHARMA
INET: VIVEK_SHARMA_at_infosys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.

**************************************************************************************5

--

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

Author: Jamadagni, Rajendra
INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

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

Author: Reardon, Bruce (CALBBAY)
INET: Bruce.Reardon_at_comalco.riotinto.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------