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

> 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.

Yes, but this is not the intention of AWR. However it is much easier to check the dependency with a query on v$db_object_cache, v$object_dependency and v$sqlarea as you are already on 11gR2. Something like this (hopefully with no typo):

SELECT SUBSTR(owner,1,30) object_owner,
  SUBSTR(name,1,30) object_name,
  SUBSTR(type,1,30) object_type
FROM v$db_object_cache
WHERE type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY' ) AND hash_value IN
  (SELECT to_hash
  FROM v$object_dependency
  WHERE from_hash IN
    (SELECT hash_value FROM v$sqlarea WHERE sql_id = '<SQL_ID>'     )
  )

Best Regards
Stefan Koehler

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

> fmhabash_at_gmail.com hat am 20. April 2016 um 21:11 geschrieben:
>
> 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

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