spool off
exit;
!01

cechon "5.3 inefficient sql statements is: " red
echo
cat 53.txt
echo
rm -rf 53.txt

#5.4 Long-running SQL

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 54.txt

SELECT sql_text "SQL", executions "Number of runs",
buffer_gets / decode(executions, 0, 1, executions) / 4000 "Response time"
FROM v\$sql
WHERE buffer_gets / decode(executions, 0,1, executions) / 4000 > 10
AND executions > 0;

spool off
exit;
!01

cechon "5.4 Long-running SQL is: " red
echo
cat 54.txt
echo
rm -rf 54.txt

#5.5 top 10 Poor performance sql

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 55.txt

SELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
SQL_TEXT FROM V\$SQLAREA ORDER BY DISK_READS DESC)
WHERE ROWNUM<10 ;

spool off
exit;
!01

cechon "5.5 top 10 Poor performance sql is: " red
echo
cat 55.txt
echo
rm -rf 55.txt

#5.6 Long run SQL

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 56.txt

select username,sid,opname,
round(sofar*100 / totalwork,0) || '%' as progress,
time_remaining,sql_text
from v\$session_longops , v\$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value
;

spool off
exit;
!01

cechon "5.6 Long run SQL is: " red
echo
cat 56.txt
echo
rm -rf 56.txt

#5.7 Most disk reads SQL

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 57.txt

select
st.sql_text
from
v\$sql s,
v\$sqlarea st
where
s.address=st.address
and s.hash_value=st.hash_value
and s.disk_reads > 300
order by s.disk_reads asc
;

spool off
exit;
!01

cechon "5.7 Most disk reads SQL is: " red
echo
cat 57.txt
echo
rm -rf 57.txt

#5.8 The most serious SQL disk sorting

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 58.txt

select
sess.username,
sql.sql_text,
sort1.blocks
from v\$session sess,
v\$sqlarea sql,
v\$sort_usage sort1
where
sess.serial# = sort1.session_num
and sort1.sqladdr = sql.address
and sort1.sqlhash = sql.hash_value
and sort1.blocks > 200
order by sort1.blocks asc
;

spool off
exit;
!01

cechon "5.8 The most serious SQL disk sorting is: " red
echo
cat 58.txt
echo
rm -rf 58.txt

#5.9 Top 10 most expensive SQL(Elapsed Time)

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 59.txt

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

spool off
exit;
!01

cechon "5.9 Top 10 most expensive SQL(Elapsed Time) is: " red
echo
cat 59.txt
echo
rm -rf 59.txt

#5.10 Top 10 most expensive SQL (CPU Time)