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@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@xxxxxxxxx
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: @OracleSK
fmhabash@xxxxxxxxx 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
