| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL statement extract from v$sqltext
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$
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 / ExecutionsPROMPT 7 = Rows Processed
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 noprintselect 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
spool off
!more /tmp/tool_shared_pool_statements.lst
exit
REM ================================ END OF FILE
===============================
"David Jones"
<djones1688_at_ho To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
tmail.com> cc:
Sent by: Subject: SQL statement extract from v$sqltext
root_at_fatcity.c
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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Jones INET: djones1688_at_hotmail.com 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: ListGuru_at_fatcity.com (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: Brian_P_MacLean_at_eFunds.Com 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: ListGuru_at_fatcity.com (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).Received on Fri Mar 15 2002 - 16:23:29 CST
 |
 |