Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Execution Statistics for SPs, SFs, Packages and SQLs by schema objects

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

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 21 Nov 2005 18:11:52 -0800
Message-ID: <1132625522.783645@yasure>


slee328_at_gmail.com wrote:
> 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

And your Oracle installation has a version number? Have you licensed diagnostic pack?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Mon Nov 21 2005 - 20:11:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US