select rownum as rank, a.*
from (
select elapsed_Time,
executions,
buffer_gets,
disk_reads,
cpu_time
hash_value,
sql_text
from v\$sqlarea
where cpu_time > 20000
order by cpu_time desc) a
where rownum < 11;

spool off
exit;
!01

cechon "5.10 Top 10 most expensive SQL (CPU Time) is: " red
echo
cat 510.txt
echo
rm -rf 510.txt

#5.11 Top 10 most expensive SQL (Buffer Gets by Executions)

sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 511.txt

select rownum as rank, a.*
from (
select buffer_gets,
executions,
buffer_gets/ decode(executions,0,1, executions) gets_per_exec,
hash_value,
sql_text
from v\$sqlarea
where buffer_gets > 50000
order by buffer_gets desc) a
where rownum < 11;

spool off
exit;
!01

cechon "5.11 Top 10 most expensive SQL (Buffer Gets by Executions) is: " red
echo
cat 511.txt
echo
rm -rf 51.txt

#5.12 Top 10 most expensive SQL (Physical Reads by Executions)

sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 512.txt

select rownum as rank, a.*
from (
select disk_reads,
executions,
disk_reads / decode(executions,0,1, executions) reads_per_exec,
hash_value,
sql_text
from v\$sqlarea
where disk_reads > 10000
order by disk_reads desc) a
where rownum < 11;

spool off
exit;
!01

cechon "5.12 Top 10 most expensive SQL (Physical Reads by Executions) is: " red
echo
cat 512.txt
echo
rm -rf 512.txt

#5.13 Top 10 most expensive SQL (Rows Processed by Executions)

sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 513.txt

select rownum as rank, a.*
from (
select rows_processed,
executions,
rows_processed / decode(executions,0,1, executions) rows_per_exec,
hash_value,
sql_text
from v\$sqlarea
where rows_processed > 10000
order by rows_processed desc) a
where rownum < 11;

spool off
exit;
!01

cechon "5.13 Top 10 most expensive SQL (Rows Processed by Executions) is: " red
echo
cat 513.txt
echo
rm -rf 513.txt

#5.14 Top 10 most expensive SQL (Buffer Gets vs Rows Processed)

sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 514.txt

select rownum as rank, a.*
from (
select buffer_gets, lpad(rows_processed ||
decode(users_opening + users_executing, 0, ' ','*'),20) "rows_processed",
executions, loads,
(decode(rows_processed,0,1,1)) *
buffer_gets/ decode(rows_processed,0,1,
rows_processed) avg_cost,
sql_text
from v\$sqlarea
where decode(rows_processed,0,1,1) * buffer_gets/ decode(rows_processed,0,1,rows_processed) > 10000
order by 5 desc) a
where rownum < 11;

spool off
exit;
!01

cechon "5.14 Top 10 most expensive SQL (Rows Processed by Executions) is: " red
echo
cat 514.txt
echo
rm -rf 514.txt

#5.15 full table scan

sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 515.txt