Execution Statistics for SPs, SFs, Packages and SQLs by schema objects

From: <slee328_at_gmail.com>
Date: 21 Nov 2005 17:55:58 -0800
Message-ID: <1132624558.410359.199680@g49g2000cwa.googlegroups.com>

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

Regards,
Stephen Lee Received on Mon Nov 21 2005 - 19:55:58 CST