|
Home > Archive > Other Oracle database topics > November 2005 > Execution statistics on SQLs, SPs, SFs and Package Calls
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Execution statistics on SQLs, SPs, SFs and Package Calls
|
|
| slee328@gmail.com 2005-11-22, 1:23 pm |
| 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
| |
| DA Morgan 2005-11-22, 8:23 pm |
| slee328@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!!
>
> BTW, I am on 10.1.0.4. RAC
>
> Regards,
> Stephen Lee
Already asked ... already answered. What was it about the previous
answer you didn't like?
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
| |
| slee328@gmail.com 2005-11-22, 8:23 pm |
| Daniel:
I do not quite get the answers that you posted in the server message
board. Therefore I wanted to post a similar message on this board to
see whether anyone else has any suggestions for me. You asked me of my
RDBMS version, which I answered "10.1.0.4.0". You asked whether I have
Diagnostic Pack purchased which I answered "yes".
I think diagnostic Pack functions are merged into AWR and ADDM report
in 10g but I still did not get how your answers (or questions) answered
my question? Can you please explain a little bit more? Thanks!
Regards,
Stephen Lee
|
|
|
|
|