Re: Data in STATSPACK report doesn’t match (V$SQL: BUFFER_GETS, CPU

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Data in STATSPACK report doesn’t match (V$SQL: BUFFER_GETS, CPU_TIME; V$SYSSTAT).

From: Danisment Gazi Unal (ubTools) <dunal_at_ubTools.com>
Date: Fri, 18 Jun 2004 10:59:58 +0300
Message-ID: <04ea01c4550a$41bd5ea0$fe19100a@danismentgu>

Hi,

I think the rule to determine if a statement is recursive or not should be determined by if a statement is called from other statement. If's it's called from other statement, it's definitely recursive even if it's PL/SQL or SQL. Top parent statements always run in user mode even if it's PL/SQL or SQL. For example, outer PL/SQL block runs in user mode. But, the SQLs and PL/SQLs called from outer PL/SQL runs in recursive mode.

The most importing here is that many statistics of recursive statements are included in their parent statements. So, If you still use V$ for your SQLs, be careful. They are cumulative. If you want to return exclusive statistics of your statement, you should use event10046 trace files with proper parser.

best regards...

http://www.ubTools.com
Web Based Oracle Products and Services

Original Message ----- From: J.Velikanovs_at_alise.lv To: oracle-l_at_freelists.org Sent: Friday, June 18, 2004 10:26 AM Subject: Re: Data in STATSPACK report doesn’t match (V$SQL: BUFFER_GETS, CPU_TIME; V$SYSSTAT).

Thank you Zhu for pointing me,

This explains issue.
Taking in account that 60% of CPU usage is Recursive then the figures I mentioned can be true.

One comment on that: "resources reported for PL/SQL includes the resources used by all SQL statements called within the PL/SQL code." In my case I haven’t PL/SQL blocks in report, just SQL run from JDBC driver. Comment: not only PL/SQL can generate recursive statements, in regular SQL this can happen quite frequently.

Jurijs

       zhu chao <zhuchao_at_gmail.com> 
        Sent by: oracle-l-bounce_at_freelists.org 
        18.06.2004 05:43 
        Please respond to oracle-l 
               
                To:        oracle-l_at_freelists.org 
                cc:         
                Subject:        Re: Data in STATSPACK report doesn’t match (V$SQL: BUFFER_GETS, CPU_TIME; V$SYSSTAT).

It can be possible.
Pay attention to the sentence in the statspack report:

-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL statements are also reported, it is possible and valid for the summed total % to exceed 100

Original Message ----- From: j.velikanovs_at_alise.lv <j.velikanovs_at_alise.lv> Date: Thu, 17 Jun 2004 23:47:22 +0300 Subject: Data in STATSPACK report doesn't match (V$SQL: BUFFER_GETS, CPU_TIME; V$SYSSTAT). To: oracle-l_at_freelists.org

  --
  Regards
  Zhu Chao
  www.cnoug.org

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

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 Fri Jun 18 2004 - 02:56:18 CDT