RE: PLSQL Call Stats in AWR Reports: Am I double-counting?
Date: Wed, 20 Apr 2016 15:11:23 -0400
Message-ID: <5717d45b.464e320a.74e5a.ffffda9b_at_mx.google.com>
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-lReceived on Wed Apr 20 2016 - 21:11:23 CEST