Execution statistics on SQLs, SPs, SFs and Package Calls

Hi:

I would like to know how I can collect all stored procedures (SPs), functions (SFs), packages and SQL calls to the DB between two timestamps. I tried to write SQL against, DBA_HIST_SNAPSHOT, DBA_HIST_SYSSTAT, DBA_HIST_SQLSTAT, DBA_HIST_SQL_PLAN and DBA_HIST_SQLTEXT dba views. However, all I get are SQL statements executed by the DB between two timestamps. These "SQL statements" includes all the SQL cursors within the SPs, SFs, Packages and SQLs executed by
the DB but I cannot distinguish which SQLs are coming from which SPs, SFs and
Packages easily from the information of these dba views.

This is the format of data that I would like to get between two timestamps:
Schema, Object Type, SPs/SFs/Packages/SQL, # of executions

For Example:

SCOTT, STORED PROCEDURE, SP_SEARCH_CUST, 20
SCOTT, STORED FUNCTION, SF_SEARCH_CUST, 4
DAVID, STORED PACKAGE, PKG_DEPOSIT, 300

Any help is much appreciate!!

BTW, I am on 10.1.0.4. RAC

Regards,
Stephen Lee Received on Tue Nov 22 2005 - 12:58:50 CST