实战:oracle巡检脚本v1(十)
ze 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
SELECT OPNAME,TARGET,B.NUM_ROWS,B.TABLESPACE_NAME,COUNT(TARGET) FROM
V\$SESSION_LONGOPS A,
ALL_ALL_TABLES B
WHERE A.TARGET=B.OWNER||'.'||B.TABLE_NAME
HAVING COUNT(TARGET)>10
GROUP BY OPNAME,TARGET,
B.NUM_ROWS,B.TABLESPACE_NAME;
spool off
exit;
!01
cechon "5.15 full table scan is: " red
echo
cat 515.txt
echo
rm -rf 515.txt
#5.16 Which operating segments using a large number of temporary
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 516.txt
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V\$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;
spool off
exit;
!01
cechon "5.16 Which operating segments using a large number of temporary is: " red
echo
cat 516.txt
echo
rm -rf 516.txt
#5.17 High degree of fragmentation of the table
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 517.txt
SELECT segment_name table_name,COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY segment_name HAVING COUNT(*)=(SELECT MAX(COUNT(*))
FROM dba_segments GROUP BY segment_name);
spool off
exit;
!01
cechon "5.17 High degree of fragmentation of the table is: " red
echo
cat 517.txt
echo
rm -rf 517.txt
#5.21 Table space I / O ratio of
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 521.txt
SELECT DF.TABLESPACE_NAME NAME,DF.FILE_NAME "FILE",F.PHYRDS PYR,
F.PHYBLKRD PBR,F.PHYWRTS PYW, F.PHYBLKWRT PBW
FROM V\$FILESTAT F, DBA_DATA_FILES DF
WHERE F.FILE# = DF.FILE_ID
ORDER BY DF.TABLESPACE_NAME;
spool off
exit;
!01
cechon "5.21 Table space I / O ratio of is: " red
echo
cat 521.txt
echo
rm -rf 521.txt
#5.22 file system I/O ratio
sqlplus -S "${