RE: SQL_Trace versus Statspack
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 ,rowid into :b0,:b1,... from TBA_ENTITY_INTEREST_TBL where (entity_id=:b105 and entity_type=:b106) for update of 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 cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 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 GetsExecutions Gets per Exec %Total Time (s) Time (s) Hash Value --- -- -- - -- 51,271 10,0005.1 23.0 4.31 3.95 1862033429 Module: [EMAIL PROTECTED] (TNS V1-V3) select TO_CHAR(accrued_upto_date_cr,'DD-MM- HH24:MI:SS') ,TO _CHAR(accrued_upto_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(las t_accrual_run_date_cr,'DD-MM- HH24:MI:SS') ,TO_CHAR(last_acc rual_run_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(booked_upto_d ate_cr,'DD-MM- 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). ** 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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 HE
RE: SQL_Trace versus Statspack
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 ,rowid into :b0,:b1,... from TBA_ENTITY_INTEREST_TBL where (entity_id=:b105 and entity_type=:b106) for update of 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 cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 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 GetsExecutions Gets per Exec %Total Time (s) Time (s) Hash Value --- -- -- - -- 51,271 10,0005.1 23.0 4.31 3.95 1862033429 Module: [EMAIL PROTECTED] (TNS V1-V3) select TO_CHAR(accrued_upto_date_cr,'DD-MM- HH24:MI:SS') ,TO _CHAR(accrued_upto_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(las t_accrual_run_date_cr,'DD-MM- HH24:MI:SS') ,TO_CHAR(last_acc rual_run_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(booked_upto_d ate_cr,'DD-MM- 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). ** 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
SQL_Trace versus Statspack
We are doing Interest processing on a SET of 10,000 Bank A/cs using a Single Database connect process. SQL Query :- select ,rowid into :b0,:b1,... from TBA_ENTITY_INTEREST_TBL where (entity_id=:b105 and entity_type=:b106) for update of 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 cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 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 GetsExecutions Gets per Exec %Total Time (s) Time (s) Hash Value --- -- -- - -- 51,271 10,0005.1 23.0 4.31 3.95 1862033429 Module: [EMAIL PROTECTED] (TNS V1-V3) select TO_CHAR(accrued_upto_date_cr,'DD-MM- HH24:MI:SS') ,TO _CHAR(accrued_upto_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(las t_accrual_run_date_cr,'DD-MM- HH24:MI:SS') ,TO_CHAR(last_acc rual_run_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(booked_upto_d ate_cr,'DD-MM- 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
