prompt -- SQL_TEXT The first thousand characters of the SQL text for the current cursor -- SHARABLE_MEM Amount of shared memory, in bytes, used by this child cursor -- PERSISTENT_MEM Fixed amount of memory, in bytes, used for the lifetime of this child cursor -- RUNTIME_MEM Fixed amount of memory required during the execution of this child cursor -- SORTS The number of sorts that was done for this child cursor -- LOADED_VERSIONS 1 if context heap is loaded, 0 otherwise -- OPEN_VERSIONS 1 if the child cursor is locked, 0 otherwise -- USERS_OPENING The number of users executing the statement -- EXECUTIONS The number of executions that took place on this object since it was brought into the library cache -- USERS_EXECUTING The number of users executing the statement -- LOADS The number of times the object was loaded or reloaded -- FIRST_LOAD_TIME The time stamp of the parent creation time -- INVALIDATIONS The number of times this child cursor has been invalidated -- PARSE_CALLS The number of parse calls for this child cursor -- DISK_READS The number of disk reads for this child cursor -- BUFFER_GETS The number of buffer gets for this child cursor -- ROWS_PROCESSED The total number of rows the parsed SQL statement returns -- COMMAND_TYPE The Oracle command type definition -- OPTIMIZER_MODE Mode under which the SQL statement is executed -- OPTIMIZER_COST The cost of this query given by the optimizer -- PARSING_USER_ID The user ID of the user who originally built this child cursor -- PARSING_SCHEMA_ID The schema ID that was used to originally build this child cursor -- KEPT_VERSIONS Indicates whether this child cursor has been marked to be kept pinned in cache using the DBMS_SHARED_POOL package -- ADDRESS The address of the handle to the parent for this cursor -- TYPE_CHK_HEAP The descriptor of the type check heap for this child cursor -- HASH_VALUE The hash value of the parent statement in the library cache -- PLAN_HASH_VALUE A numerical representation of the SQL plan for this cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line). -- CHILD_NUMBER The number of this child cursor -- MODULE Contains the name of the module that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_MODULE -- MODULE_HASH The hash value of the module that is named in the MODULE column -- ACTION Contains the name of the action that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_ACTION -- ACTION_HASH The hash value of the action that is named in the ACTION column -- SERIALIZABLE_ABORTS The number of times the transaction fails to serialize, producing ORA-08177 errors, per cursor -- OUTLINE_CATEGORY If an outline was applied during construction of the cursor, this column displays the category of that outline. Otherwise the column is left blank. -- ELAPSED_TIME Elapsed time (in microseconds) used by this cursor for parsing/executing/fetching -- OUTLINE_SID Outline session identifier -- CHILD_ADDRESS Address of the child cursor -- SQLTYPE Denotes the version of the SQL language used for this statement -- REMOTE (Y/N) Identifies whether the cursor is remote mapped -- OBJECT_STATUS Status of the cursor (VALID/INVALID) -- LITERAL_HASH_VALUE The hash value of the literals which are replaced with system generated bind variables and are to be matched, when CURSOR_SHARING is used. This is not the hash value for the SQL statement. If CURSOR_SHARING is not used, the value is 0. -- LAST_LOAD_TIME spool topsql.rpt prompt Top sql statements prompt prompt Select order by option : prompt prompt sorts prompt prompt fetches prompt prompt executions prompt prompt users_executing prompt prompt loads prompt prompt parse_calls prompt prompt disk_reads prompt prompt buffer_gets prompt prompt rows_processed prompt prompt serializable_aborts prompt prompt cpu_time prompt prompt elapsed_time/executions prompt prompt ( elapsed_time/decode( executions, 0, 1, executions ) ) * ( buffer_gets + disk_reads ) prompt prompt accept x_order_by prompt "Order by : " prompt accept x_gt_executions prompt "Executions greater than : " prompt accept x_num_rows prompt "Number of rows to return : " prompt accept x_showall prompt "Show all values [Y/N] : " declare cursor c_sqlarea( x_current in varchar2 ) is select * from ( select a.*, decode( a.command_type, 2, 'INSERT', 3, 'SELECT', 6, 'UPDATE', 7, 'DELETE', 17, 'GRANT', 26, 'LOCK TABLE', 42, 'ALTER SESSION', 44, 'COMMIT', 45, 'ROLLBACK', 46, 'SAVEPOINT', 47, 'PL/SQL', 48, 'SET TRANSACTION', 85, 'TRUNCATE', 90, 'SET CONSTRAINTS', 95, 'ALTER PACKAGE COMPILE', 98, 'ALTER PACKAGE COMPILE BODY', 'Unknown' ) "COMTYPE" from v$sql a where command_type != 47 and executions > to_number( nvl( '&&x_gt_executions', '0' ) ) -- ignore SYS statements and exists ( select 1 from v$sql_plan b where b.address = a.address and b.hash_value = a.hash_value and b.object_owner <> 'SYS' ) and elapsed_time > 0 and executions > 0 and ( x_current != 'Y' or exists ( select 1 from v$open_cursor c where c.hash_value = a.hash_value ) ) order by &&x_order_by DESC ) where rownum <= nvl( to_number( '&&x_num_rows' ), 10 ) order by &&x_order_by DESC; cursor c_sqltext( x_hash_value in v$sqltext.hash_value%type ) is select * from v$sqltext a where a.hash_value = x_hash_value order by piece; cursor c_session( x_address in v$sqltext.address%type, x_hash_val in v$sqltext.hash_value%type ) is select vs.*, vproc.spid from v$session vs, v$process vproc where vs.sid in ( select voc.sid from v$open_cursor voc, v$sqltext vst where voc.address = x_address and voc.hash_value = x_hash_val and vst.address = voc.address and vst.hash_value = voc.hash_value ) and vproc.addr = vs.paddr; ln_sessions number; ls_divider varchar2( 200 ) := rpad( '-', 75, '-' ); procedure p_display( x_open in varchar2 ) is begin for i in c_sqlarea( x_open ) loop dbms_output.put_line( ls_divider ); dbms_output.put_line( 'SQL_ID : ' || i.sql_id ); dbms_output.put_line( 'Sorts : ' || i.sorts ); dbms_output.put_line( 'Fetches : ' || i.fetches ); dbms_output.put_line( 'Executions : ' || i.executions ); dbms_output.put_line( 'Parse calls : ' || i.parse_calls ); dbms_output.put_line( 'Disk reads : ' || i.disk_reads ); dbms_output.put_line( 'Buffer gets : ' || i.buffer_gets ); dbms_output.put_line( 'Rows processed : ' || i.rows_processed ); dbms_output.put_line( 'Elapsed time : ' || to_char( i.elapsed_time/1000000, '999,999.99' ) || ' (seconds)' ); if i.executions = 0 then dbms_output.put_line( 'Time per exec : Not executed' ); else dbms_output.put_line( 'Time per exec : ' || to_char( i.elapsed_time/( 1000000 * i.executions ), '999,999.99' ) ); end if; if i.rows_processed = 0 then dbms_output.put_line( 'Time per row : No rows processed' ); else dbms_output.put_line( 'Time per row : ' || to_char( i.elapsed_time/( 1000000 * i.rows_processed ), '999,999.99' ) ); end if; dbms_output.put_line( 'Ser.ble aborts : ' || i.serializable_aborts ); dbms_output.put_line( 'CPU time : ' || i.cpu_time || ' (microseconds)' ); dbms_output.put_line( 'Command type : ' || i."COMTYPE" ); dbms_output.put_line( '' ); if upper( nvl( '&&x_showall', 'n' ) ) = 'Y' then dbms_output.put_line( 'Invalidations : ' || i.invalidations ); dbms_output.put_line( 'Loads : ' || i.loads ); dbms_output.put_line( 'Module : ' || i.module ); dbms_output.put_line( 'Action : ' || i.action ); -- dbms_output.put_line( 'Action Hash : ' || i.action_hash ); -- dbms_output.put_line( 'Address : ' || i.address ); dbms_output.put_line( 'Child Latch : ' || i.child_latch ); -- dbms_output.put_line( 'Hash value : ' || i.hash_value ); dbms_output.put_line( 'Kept versions : ' || i.kept_versions ); dbms_output.put_line( 'Loaded versions : ' || i.loaded_versions ); -- dbms_output.put_line( 'Module Hash : ' || i.module_hash ); dbms_output.put_line( 'Open versions : ' || i.open_versions ); dbms_output.put_line( 'Parsing schema id : ' || i.parsing_schema_id ); dbms_output.put_line( 'Parsing user id : ' || i.parsing_user_id ); dbms_output.put_line( 'Persistent mem : ' || i.persistent_mem ); dbms_output.put_line( 'Runtime mem : ' || i.runtime_mem ); dbms_output.put_line( 'Sharable mem : ' || i.sharable_mem ); dbms_output.put_line( 'Users executing : ' || i.users_executing ); dbms_output.put_line( 'Users opening : ' || i.users_opening ); dbms_output.put_line( 'Version count : ' || i.loaded_versions ); dbms_output.put_line( 'First load time : ' || i.first_load_time ); dbms_output.put_line( 'Last load time : ' || i.last_load_time ); dbms_output.put_line( 'Optimizer mode : ' || i.optimizer_mode ); dbms_output.put_line( 'Optimizer cost : ' || i.optimizer_cost ); dbms_output.put_line( 'Remote : ' || i.remote ); dbms_output.put_line( 'Object status : ' || i.object_status ); dbms_output.put_line( 'Is obsolete : ' || i.is_obsolete ); end if; ln_sessions := 0; dbms_output.put_line( '' ); dbms_output.put_line( 'Users executing:' ); for j in c_session( i.address, i.hash_value ) loop dbms_output.put_line( j.username || ' (' || j.sid || ',' || j.serial# || ') OS PID: ' || j.spid ); ln_sessions := ln_sessions + 1; end loop; if ln_sessions = 0 then dbms_output.put_line( 'None found.' ); end if; dbms_output.put_line( '' ); dbms_output.put_line( 'SQL:' ); for j in c_sqltext( i.hash_value ) loop dbms_output.put_line( j.sql_text ); end loop; end loop; dbms_output.put_line( ls_divider ); end; begin dbms_output.put_line( '' ); dbms_output.put_line( 'All SQL statements' ); dbms_output.put_line( '' ); p_display( 'N' ); dbms_output.put_line( ls_divider ); dbms_output.put_line( ls_divider ); dbms_output.put_line( '' ); dbms_output.put_line( 'Currently open cursors' ); dbms_output.put_line( '' ); p_display( 'Y' ); end; / spool off