Oracle Resource Intensive SQL

accept x_ignore_sys prompt "Ignore SYS? [Y/N]: "
accept x_owner prompt "Owner: "
accept x_num_rows prompt "Number of top records to return: "

prompt Choose sort order:
prompt action
prompt buffer_gets
prompt cpu_time
prompt disk_reads
prompt elapsed_time
prompt executions
prompt fetches
prompt invalidations
prompt kept_versions
prompt loaded_versions
prompt loads
prompt module
prompt open_versions
prompt parse_calls
prompt parsing_schema_id
prompt parsing_user_id
prompt persistent_mem
prompt rows_processed
prompt runtime_mem
prompt serializable_aborts
prompt sharable_mem
prompt sorts
prompt users_executing
prompt users_opening
prompt version_count

accept x_order prompt "Enter sort order of inner select: "

declare

cursor c_top_records is
select *
from ( select *
from v$sqlarea a
where exists ( select 1
from v$sql_plan
where address = a.address
and hash_value = a.hash_value
and ( object_owner <> 'SYS' or upper( nvl( '&x_ignore_sys', 'N' ) ) <> 'Y' )
and nvl( upper( '&x_owner' ), object_owner ) = object_owner
)
order by &x_order desc, cpu_time desc
)
where rownum < ( to_number( nvl( '&x_num_rows', '10' ) ) + 1 )
order by &x_order desc, cpu_time desc;

cursor c_vsql_plan ( x_address in varchar2, x_hash in number ) is
select *
from v$sql_plan
where address = x_address
and hash_value = x_hash
order by address, hash_value, depth;

cursor c_sql ( x_address in varchar2, x_hash in number ) is
select *
from v$sqltext
where address = x_address
and hash_value = x_hash
order by piece;

cursor c_sqlarea ( x_address in varchar2, x_hash in number ) is
select *
from v$sqlarea
where address = x_address
and hash_value = x_hash;
lt_sqlarea v$sqlarea%rowtype;

ls_sql_text varchar2( 10000 );

begin

dbms_output.put_line( '' );

for i in c_top_records loop
dbms_output.put_line( rpad( '-', 150, '-' ) );
open c_sqlarea( i.address, i.hash_value );
fetch c_sqlarea into lt_sqlarea;
close c_sqlarea;

case lower( '&x_order' )
when lower( 'action' ) then
dbms_output.put_line( 'Action :' || lt_sqlarea.action );
when lower( 'buffer_gets' ) then
dbms_output.put_line( 'Buffer gets :' || lt_sqlarea.buffer_gets );
when lower( 'cpu_time' ) then
dbms_output.put_line( 'CPU time:' || lt_sqlarea.cpu_time );
when lower( 'disk_reads' ) then
dbms_output.put_line( 'Disk reads :' || lt_sqlarea.disk_reads );
when lower( 'elapsed_time' ) then
dbms_output.put_line( 'Elapsed time :' || lt_sqlarea.elapsed_time );
when lower( 'executions' ) then
dbms_output.put_line( 'Executions :' || lt_sqlarea.executions );
when lower( 'fetches' ) then
dbms_output.put_line( 'Fetches :' || lt_sqlarea.fetches );
when lower( 'invalidations' ) then
dbms_output.put_line( 'Invalidations :' || lt_sqlarea.invalidations );
when lower( 'kept_versions' ) then
dbms_output.put_line( 'Kept versions:' || lt_sqlarea.kept_versions );
when lower( 'loaded_versions' ) then
dbms_output.put_line( 'Loaded versions:' || lt_sqlarea.loaded_versions );
when lower( 'loads' ) then
dbms_output.put_line( 'Loads :' || lt_sqlarea.loads );
when lower( 'module' ) then
dbms_output.put_line( 'Module :' || lt_sqlarea.module );
when lower( 'open_versions' ) then
dbms_output.put_line( 'Open versions :' || lt_sqlarea.open_versions );
when lower( 'parse_calls' ) then
dbms_output.put_line( 'Parse calls:' || lt_sqlarea.parse_calls );
when lower( 'parsing_schema_id' ) then
dbms_output.put_line( 'Parsing Schema Id:' || lt_sqlarea.parsing_schema_id );
when lower( 'parsing_user_id' ) then
dbms_output.put_line( 'Parsing User Id:' || lt_sqlarea.parsing_user_id );
when lower( 'persistent_mem' ) then
dbms_output.put_line( 'Persistent mem :' || lt_sqlarea.persistent_mem );
when lower( 'rows_processed' ) then
dbms_output.put_line( 'Rows Processed :' || lt_sqlarea.rows_processed );
when lower( 'runtime_mem' ) then
dbms_output.put_line( 'Runtime mem:' || lt_sqlarea.runtime_mem );
when lower( 'serializable_aborts' ) then
dbms_output.put_line( 'Serialisable aborts :' || lt_sqlarea.serializable_aborts );
when lower( 'sharable_mem' ) then
dbms_output.put_line( 'Sharable mem:' || lt_sqlarea.sharable_mem );
when lower( 'sorts' ) then
dbms_output.put_line( 'Sorts :' || lt_sqlarea.sorts );
when lower( 'users_executing' ) then
dbms_output.put_line( 'Users executing:' || lt_sqlarea.users_executing );
when lower( 'users_opening' ) then
dbms_output.put_line( 'Users opening :' || lt_sqlarea.users_opening );
when lower( 'version_count' ) then
dbms_output.put_line( 'Version count :' || lt_sqlarea.version_count );
else
dbms_output.put_line( 'Sort order not recognised.' );
end case;

dbms_output.put_line( '' );

ls_sql_text := '';
for j in c_sql( i.address, i.hash_value ) loop
-- if procedure bigt is not available or not required, comment the line
-- out and uncomment the following line:
-- dbms_output.put_line( j.sql_text );
ls_sql_text := ls_sql_text || j.sql_text;
end loop;
bigt( ls_sql_text );

dbms_output.put_line( rpad( 'Operation', 30 ) || ' ' ||
rpad( 'Options', 25 ) || ' ' ||
rpad( 'Owner', 25 ) || ' ' ||
rpad( 'Obj Name', 30 ) || ' ' ||
' Bytes' || ' ' ||
' Cost' || ' ' ||
'Part Start' || ' ' ||
'Part Stop ' || ' ' ||
'Card.' );
dbms_output.put_line( rpad( '---------', 30 ) || ' ' ||
rpad( '-------', 25 ) || ' ' ||
rpad( '-----', 25 ) || ' ' ||
rpad( '-----', 30 ) || ' ' ||
' -----' || ' ' ||
' ----' || ' ' ||
'----------' || ' ' ||
'--------- ' || ' ' ||
'-----' );

for k in c_vsql_plan( i.address, i.hash_value ) loop
dbms_output.put_line( rpad( lpad( ' ', 2 * ( k.depth-1 ), '. ' ) || k.operation, 30 ) || ' ' ||
rpad( nvl( k.options, ' ' ), 25 ) || ' ' ||
rpad( nvl( k.object_owner, ' ' ), 25 ) || ' ' ||
rpad( nvl( k.object_name, ' ' ), 30 ) || ' ' ||
nvl( to_char( k.bytes, '99,999,999' ), ' ' ) || ' ' ||
nvl( to_char( k.cost, '99,999,999' ), ' ' ) || ' ' ||
rpad( nvl( k.partition_start, ' ' ), 10 ) || ' ' ||
rpad( nvl( k.partition_stop, ' ' ), 10 ) || ' ' ||
nvl( to_char( k.cardinality, '9999' ), ' ' ) );
end loop;

end loop;

end;
/