sql监控的sh

export ORACLE_BASE=/oracleITPUB个人空间cmO$C`h4B6w H
export AIXTHREAD_SCOPE=S

Zj3Uo ?!j0b0export TMP=/tmpITPUB个人空间 s"}'mZ5Wz:B
export TEMP=/tmpITPUB个人空间8mvz$`$_C5W
export TMPDIR=/tmpITPUB个人空间oW6cM[&J
umask 022
lf!yz!_k(p0export CRS_HOME=$ORACLE_BASE/crs
Gi;SQ:b c"J8h0export ORA_CRS_HOME=$ORACLE_BASE/crsITPUB个人空间,Wv8lD ?~,pK$zjEZ
export ORACLE_HOME=$ORACLE_BASE/db10gITPUB个人空间 u?_9^~#qJd
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$CRS_HOME/lib:$ORACLE_HOME/lib32:$CRS_HOME/lib32ITPUB个人空间&Z`PRCU
export PATH=$ORACLE_HOME/bin:/oracle/OPatch:$CRS_HOME/bin:$PATHITPUB个人空间J"{M6O8J:P6W g
export ORACLE_SID=zgdb1ITPUB个人空间@Z XM^r o~
export NLS_LANG=american_america.ZHS16GBK

PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:$ORACLE_HOME/bin:/sbin:.

export PATH

if [ -s "$MAIL" ] # This is at Shell startup. In normalITPUB个人空间u8v_ H;qvWR
then echo "$MAILMSG" # operation, the Shell checks
#mp(o&k|0fi # periodically.ITPUB个人空间 m1O1m$Kr8i;P t
prex=`date "+%Y%m%d"`ITPUB个人空间0B2EAe'rG
sqlplus "/ as sysdba">>/oracle/zxc/zgdb1_sql_$prex.out<<!
7CafWW;_ z0set pagesize 50000 linesize 300 ITPUB个人空间#^2TKU"r$J
set echo off

column executions heading "Execs" format 99999999ITPUB个人空间
d#sIIwN
column rows_processed heading "Rows Procd" format 99999999
@1`.@W+s0column loads heading "Loads" format 999999.99ITPUB个人空间}5Wke$Y
column buffer_gets heading "Buffer Gets"
]+]:V!FT,R_/S0column disk_reads heading "Disk Reads"ITPUB个人空间+E)B-x ]i)W#J[1~
column elapsed_time heading "Elasped Time"ITPUB个人空间x{Y:Rf_q*s
column cpu_time heading "CPU Time"
#n|Jcz6^Fj0column sql_text heading "SQL Text" format a60 wrap
1Ew Zd,U*i0column avg_cost heading "Avg Cost" format 99999999ITPUB个人空间u1{3} iJ5[?yy
column gets_per_exec heading "Gets Per Exec" format 99999999ITPUB个人空间;ev9QV [R@
column reads_per_exec heading "Read Per Exec" format 99999999ITPUB个人空间F:G:Ri[*l0PY
column rows_per_exec heading "Rows Per Exec" format 99999999

break on report
X?M4tG x0compute sum of rows_processed on report
e@kN8^\"l8Hh0compute sum of executions on reportITPUB个人空间
t,DX3O'U2hVA%L
compute avg of avg_cost on report
;l%D{K7mu1m9y n`4u0compute avg of gets_per_exec on report
)R5a,@h0z.x{0compute avg of reads_per_exec on report
?l'We+L/T7`0compute avg of row_per_exec on reportITPUB个人空间/NH0Xq-igGe'm
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

PROMPT ITPUB个人空间o3sUk3Y4v3x:H-K
PROMPT Top 10 most expensive SQL (Elapsed Time)...ITPUB个人空间/pG4j|o5b
PROMPT
5K)Uyy ^J j0select rownum as rank, a.*
[0Zonv#cA0from (ITPUB个人空间X"T]#NXPo y"qE
select elapsed_Time,
h2]~&N
f
k.c4`0 executions,ITPUB个人空间+\7Qt|&K
x&}Nf MN)\
buffer_gets,
k6S"E1yO:q2n"y0 disk_reads,ITPUB个人空间#Du8A3m$d'}
cpu_timeITPUB个人空间D)emZ1V(E
hash_value,
q(T"} L~0 sql_textITPUB个人空间p0x hj4jYAw
from v$sqlarea
v.C6vl$?&KY0 where elapsed_time > 20000ITPUB个人空间vF"VW\RNx0y
order by elapsed_time desc) a
d
[A C+Ude1]0where rownum < 11
Qk&?t~}$N0/

PROMPT
tR\2k(g&V|M?0PROMPT Top 10 most expensive SQL (CPU Time)...ITPUB个人空间/o,U}7gL-B
PROMPT

select rownum as rank, a.*ITPUB个人空间p'LF?O`
from (
q,jK$o)JT;T0 select elapsed_Time,
k5t0x0s'n8gY P,OCV0 executions,
.K'W%W??!U}0 buffer_gets,ITPUB个人空间7Q_J~+n$jnuJG&n
disk_reads,
-G*[,u^)wf0 cpu_timeITPUB个人空间~ }OqO/EU&C iKD D
hash_value,ITPUB个人空间@:Ec&x)i[
sql_textITPUB个人空间'X4ss
Cz XX_
from v$sqlareaITPUB个人空间6N-o.r*ve2s])F%^
where cpu_time > 20000
O wD7I+H0 order by cpu_time desc) a
|U-ud:HOAVov0where rownum < 11
V4K}:u(a_D0/

PROMPT ITPUB个人空间oG3@$\qJK?2w4a
PROMPT Top 10 most expensive SQL (Buffer Gets by Executions)...
v6s(W DW?0PROMPT

select rownum as rank, a.*
8c%TwC3] Cg0from (ITPUB个人空间5Bg4enxBN$M
select buffer_gets, ITPUB个人空间V-d}V&jLdp
executions,ITPUB个人空间*W0J9z+`ab
buffer_gets/ decode(executions,0,1, executions) gets_per_exec,
:o
I5uA5^q:L|0 hash_value,
R(B;`5L/T!|W0 sql_textITPUB个人空间dP%[_(`X#d:b
from v$sqlareaITPUB个人空间t*cB,E["|,N^|0Qi
where buffer_gets > 50000ITPUB个人空间nP0Q;`9Rm#xMIK.l
order by buffer_gets desc) a
ZE
d!k{'aFBQ0where rownum < 11
cCYL$H6D2y0/

a.k3tAr0PROMPT Top 10 most expensive SQL (Physical Reads by Executions)...ITPUB个人空间 ]_7DO.nh:oz
PROMPT

select rownum as rank, a.*ITPUB个人空间!Ia#PF5rGoH8m
from (
#l7nUI%]0 select disk_reads, ITPUB个人空间8c&{7k6U:Eg
executions,
!p}*Q~/Q z2V2p&e$e1[0 disk_reads / decode(executions,0,1, executions) reads_per_exec,
W[6bN;K%M0 hash_value,ITPUB个人空间k-dnmD$h
sql_text
q0e-g3o%b b[d[ A0 from v$sqlarea
~z8Xu6?$Cx0 where disk_reads > 10000
A6pp%{T7v
`0 order by disk_reads desc) aITPUB个人空间xR+iu+jp&g9`Z"y
where rownum < 11ITPUB个人空间"SE&`m L~
/

PROMPT Top 10 most expensive SQL (Rows Processed by Executions)...ITPUB个人空间'OSQ baN
PROMPT

select rownum as rank, a.*ITPUB个人空间HqY"Yv9Wb
from (ITPUB个人空间6d%l7d`q
select rows_processed, ITPUB个人空间,u7r#KT;O;X/o4{
executions,
4{Sb6\&X+b ~D5r0 rows_processed / decode(executions,0,1, executions) rows_per_exec,
CY
f"U]7s0 hash_value,ITPUB个人空间:u0j C6C1a~
sql_textITPUB个人空间fu'h'qmC
from v$sqlareaITPUB个人空间$`\pu*\ m}5UH
where rows_processed > 10000
kOmn\-q7m&{0 order by rows_processed desc) a
IG&iUB0where rownum < 11ITPUB个人空间 WB~.O
z+c+NY
/

PROMPT
Le8p
H|0PROMPT Top 10 most expensive SQL (Buffer Gets vs Rows Processed)...ITPUB个人空间 rqv;PV'Fn
PROMPT

select rownum as rank, a.*
|6o'xQv}U-V7y0from (ITPUB个人空间qmZ;y4}2o0o M
select buffer_gets, lpad(rows_processed ||
0e1W&yy"M0 decode(users_opening + users_executing, 0, ' ','*'),20) "rows_processed",ITPUB个人空间"Gi/knsG{6J#@i|
executions, loads,
Qh_/O$x-n0 (decode(rows_processed,0,1,1)) *
0m{,}VT"M0 buffer_gets/ decode(rows_processed,0,1,ITPUB个人空间9^U(v+},G%L
@
rows_processed) avg_cost,
MvPC
GcC&Y0 sql_textITPUB个人空间2|K.tt _-f\
from v$sqlareaITPUB个人空间 S1EX7w_ u;Lj+x(o
where decode(rows_processed,0,1,1) * buffer_gets/ decode(rows_processed,0,1,rows_processed) > 10000
i%p3Go:u*PW0 order by 5 desc) aITPUB个人空间m2Kq"w+StPq
where rownum < 11ITPUB个人空间kxHw1i
/

ITPUB个人空间"V$x3m t_2\#{/aae
select rownum as rank, a.*ITPUB个人空间`p3?`F}
from (ITPUB个人空间4z#az6QU^#q
select upper(substr(sql_text, 1, 65)) sqltext, count(*)ITPUB个人空间(I1D+Xd(H6B.x lx
from v$sqlarea
(D wrx8a"m*L0c0 group by upper(substr(sql_text, 1, 65))ITPUB个人空间,bSy8l[&]
having count(*) > 1ITPUB个人空间8pk#y,n!{%NU
order by count(*) desc) aITPUB个人空间 f\7Yp+Bl i;d?
where rownum < 11ITPUB个人空间bVN6]s9i5EZW#k"g
/ITPUB个人空间([r pN*R:g
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;ITPUB个人空间&Y+e*q
o&yxM
!