RE: PLSQL Call Stats in AWR Reports: Am I double-counting?

From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Wed, 20 Apr 2016 13:53:49 -0700
Message-ID: <BLU179-W257BEC4005C994D1B0646EEB6D0_at_phx.gbl>

I might add that you could always your own custom report against the AWR. The AWR is a repository (of aggregated as well as unaggregated data). The AWR report is an example of what is possible with the data in the AWR, not the end all and be-all report. You could go as far as unwrapping the AWR packages to use as a starting point. Iggy

Date: Wed, 20 Apr 2016 21:42:30 +0100
Subject: Re: PLSQL Call Stats in AWR Reports: Am I double-counting? From: dombrooks_at_hotmail.com
To: fmhabash_at_gmail.com; contact_at_soocs.de; oracle-l_at_freelists.org

True... it's parsing module.
SQL is intended to be shareable, as a general rule with some exceptions, regardless of calling program. If you need that then you should look at ash report / ash data but that is sampled not aggregated.

On 20 April 2016, at 20:12, fmhabash_at_gmail.com wrote:

True, but it does not conclusively tell if the SQL_ID is part of the call or not. One needs to lookup the program_id to verify.

 
On another note ….
 
Looking at the same top SQL by cpu, if I have 2 hosts, each hit the DB using identical SQL_ID, say, 1M each. Let’s assume both qualified to be included in this section, I should see 2M executions. However, the module column shows something
 like ‘perl_at_app_host_1.domain.com’. This confused the readers as they concluded that the hostname showing in module column was the busiest of all others. They started investigating app configuration. It was  time wasted.
 
In a scenario like this, hostnames in the ‘module’ columns just distractions. They serve no purpose, if not a bad one.

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

Thanks From: Stefan Koehler
Sent: Wednesday, April 20, 2016 2:32 PM To: Oracle-L Group; fmhabash_at_gmail.com

Subject: Re: PLSQL Call Stats in AWR Reports: Am I double-counting?

 
Hello,
the Top SQL by CPU section is recursive. It means that your PL/SQL package call includes the CPU time of the SELECT statement. 

 
By the way Oracle itself writes a note about it at every section header: "Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code".

Best Regards
Stefan Koehler
 
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: _at_OracleSK

 
> fmhabash_at_gmail.com hat am 19. April 2016 um 19:33 geschrieben:
> 
>  On 11204 RDBMS, I’m examining top sql by cpu section and I see 2 entries, one for a PLSQL package call and the a second for a select statement.
> When I checked v$SQL, I see the select statement is having a program_id that belongs to the same package. The package call shows 20% of %total and
> the ‘select’ has 10 %total.
> 
>  If the source of these 2 calls is shutdown, am I saving 20 or 30% of CPU time?
> 
>  ----------------------------------------
>  Thanks
 
 

 		 	   		  

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 20 2016 - 22:53:49 CEST