This is and old but effective script, it's what you asked for and even
more....
REM
REM $Author: oracle $
REM $Locker: $
REM $Date: 2000/06/19 17:35:26 $
REM $Revision: 1.1 $
REM $RCSfile: tool_shared_pool_statements.sql,v $
REM $Source: /home/oracle/DBA/tool/RCS/tool_shared_pool_statements.sql,v
$
REM $State: Exp $
REM
set verify off
set pagesize 35
set linesize 132
set pause off
set pause 'Hit enter to continue'
set feedback off
set showmode off
set echo off
PROMPT
PROMPT Sort BY Selections
PROMPT ------------------------
PROMPT 1 = Sorts Performed
PROMPT 2 = Executions
PROMPT 3 = Disk Blocks Read
PROMPT 4 = Disk Blocks Read / Executions
PROMPT 5 = Buffer Blocks Gotton
PROMPT 6 = Buffer Blocks Gotton / Executions
PROMPT 7 = Rows Processed
PROMPT 8 = Rows / Executions
ACCEPT USER_INPUT1 NUMBER PROMPT 'Please enter one now:>'
col sort_by_number new_value sort_by_number_value noprint
col sort_by_text new_value sort_by_text_value noprint
select decode(&USER_INPUT1, 1,1, 2,2, 3,3, 4,4, 5,5, 6,6, 7,7, 8,8, 8)
sort_by_number,
decode(&USER_INPUT1, 1,'Sorts Performed',
2,'Executions',
3,'Disk Blocks Read',
4,'Disks / Executions',
5,'Buffer Blocks Gotton',
6,'Buffers / Executions',
7,'Rows Processed',
8,'Rows / Executions',
'Rows / Executions') sort_by_text
from dual;
REM
----------------------------------------------------------------------------
PROMPT
PROMPT Sort ORDER Selections
PROMPT ---------------------
PROMPT 1 = Descending
PROMPT 2 = Ascending
ACCEPT USER_INPUT2 NUMBER PROMPT 'Please enter one now:>'
col order_by_text new_value order_by_text_value noprint
select decode(&USER_INPUT2, 1,'Desc', 2,'Asc', 'Desc') order_by_text
from dual;
REM
----------------------------------------------------------------------------
PROMPT spooling output to /tmp/tool_shared_pool_statements.lst
PROMPT
PROMPT Working, Please wait.....
set term off
spool /tmp/tool_shared_pool_statements.lst
col SQL_TEXT format a132 heading "SQL Statment"
col SORTS format 9999 heading "Sorts"
col EXECUTIONS format 99999 heading "Executions"
col USERS_EXECUTING format 999 heading "Currently|Executing"
col DISK_READS format 9999999 heading "Disk|Blocks|Read"
col BUFFER_GETS format 9999999 heading "Buffer|Blocks|Gotten"
col ROWS_PROCESSED format 999999999 heading "Rows|Processed"
col COMMAND_TYPE format 999 heading "Command|Number"
col OPTIMIZER_MODE format a6 heading "Parse|Mode"
REM ADDRESS RAW(4)
REM HASH_VALUE NUMBER
col AA format 99999999 heading "Disks /|Executions"
col BB format 99999999 heading "Buffers /|Executions"
col CC format 99999999 heading "Rows /|Executions"
ttitle left 'SORT BY: ' '&sort_by_text_value' -
center 'V$SQL' -
right 'PAGE:' format 999 sql.pno skip 1 -
left 'SORT ORDER: ' &order_by_text_value -
center 'Shared Pool Statements' skip 2
select SORTS, EXECUTIONS,
DISK_READS, DISK_READS / decode(EXECUTIONS, NULL,1, 0,1, EXECUTIONS)
AA,
BUFFER_GETS, BUFFER_GETS / decode(EXECUTIONS, NULL,1, 0,1,
EXECUTIONS) BB,
ROWS_PROCESSED, ROWS_PROCESSED / decode(EXECUTIONS, NULL,1, 0,1,
EXECUTIONS) CC,
OPTIMIZER_MODE, USERS_EXECUTING,
SQL_TEXT
from v$sql
order by &sort_by_number_value &order_by_text_value;
spool off
!more /tmp/tool_shared_pool_statements.lst
exit
REM ================================ END OF FILE
===============================
"David Jones"
<djones1688@ho To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
tmail.com> cc:
Sent by: Subject: SQL statement extract from
v$sqltext
[EMAIL PROTECTED]
om
03/15/02 02:45
PM
Please respond
to ORACLE-L
Does anyone have a handy PL/SQL script which can extract complete SQL
statement from v$sqltext sorting by v$sqlarea's buffer_gets ?
Thanks for the help
dj
_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: David Jones
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
