#4.6 temp tablespace usage

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 46.txt
select 'the '||name||' temp tablespaces '||tablespace_name||' idle '||round(100-(s.tot_used_blocks/s.total_blocks)*100,3)||'% at '||to_char(sysdate,'yyyymmddhh24miss')
from
(select d.tablespace_name tablespace_name,
nvl(sum(used_blocks),0) tot_used_blocks,
sum(blocks) total_blocks
from v\$sort_segment v ,dba_temp_files d
where d.tablespace_name=v.tablespace_name(+)
group by d.tablespace_name) s, v\$database;
spool off
exit;
!01

cechon "4.6 check Non-system tables in system tablespace is: " red
echo
cat 46.txt
echo
rm -rf 46.txt

cechon "***********************************************************************" yellow
echo
cechon "5.oracle database performance:" green
echo
echo 5.1-5.14 about sql
echo 5.15-5.20 about table
echo 5.21-5.30 about IO
echo 5.31-5.40 about REDO
echo 5.41-5.60 about HIT RATE
cechon "***********************************************************************" yellow
echo

##########5.1-5.14 about sql##########
#5.1 database Wait event

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

select sid,event,WAIT_TIME from v\$session_wait
where event not like 'SQL%' and event not like 'rdbms%';

spool off
exit;
!01

cechon "5.1 database Wait event is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""sid","event","wait_time" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 51.txt
echo
rm -rf 51.txt

#5.2 system Wait event

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

select EVENT,TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, AVERAGE_WAIT from (
select * from v\$system_event
where event not like '%rdbms%'
and event not like '%message%'
and event not like 'SQL*Net%'
order by total_waits desc
)
where rownum <=5 ;

spool off
exit;
!01

cechon "5.2 system Wait event is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:" "EVENT","TOTAL_WAITS", "TOTAL_TIMEOUTS", "TIME_WAITED", "AVERAGE_WAIT" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 52.txt
echo
rm -rf 52.txt

#5.3 inefficient sql statements

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