扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条
作者:QQ新人类 来源:yesky 2007年10月23日
关键字: ORACLE
在本页阅读全文(共2页)
set lines 80; set pages 999; column mydate heading 'Yr. Mo Dy Hr.' format a16 column c1 heading "execs" format 9,999,999 column c2 heading "Cache Misses|While Executing" format 9,999,999 column c3 heading "Library Cache|Miss Ratio" format 999.99999 break on mydate skip 2; select to_char(snap_time,'yyyy-mm-dd HH24') mydate, sum(new.pins-old.pins) c1, sum(new.reloads-old.reloads) c2, sum(new.reloads-old.reloads)/ sum(new.pins-old.pins) library_cache_miss_ratio from stats$librarycache old, stats$librarycache new, stats$snapshot sn where new.snap_id = sn.snap_id and old.snap_id = new.snap_id-1 and old.namespace = new.namespace group by to_char(snap_time,'yyyy-mm-dd HH24') ; Cache Misses Yr. Mo Dy Hr. execs While Executing LIBRARY_CACHE_MISS_RATIO ---------------- ---------- --------------- ------------------------ 2001-12-11 10 10,338 3 .00029 2001-12-12 10 182,477 134 .00073 2001-12-14 10 190,707 202 .00106 2001-12-16 10 2,803 11 .00392 |
RAM Area Too-small Condition Too-Large Condition Shared pool Library cache misses No misses Data buffer cache Hit ratio < 90% Hit ratio > 95% PGA aggregate high multi-pass executions 100% optimal executions |
work_area.sql select name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage from ( select name, value cnt, (sum(value) over ()) total from v$sysstat where name like 'workarea exec%' ); |
PROFILE CNT PERCENTAGE ----------------------------------- ---------- ---------- workarea executions - optimal 5395 95 workarea executions - onepass 284 5 workarea executions - multipass 0 0 |
check_pga.sql column name format a30 column value format 999,999,999 select name, value from v$pgastat ; The output of this query might look like the following: NAME VALUE ------------------------------------------------------ ---------- aggregate PGA auto target 736,052,224 global memory bound 21,200 total expected memory 141,144 total PGA inuse 22,234,736 total PGA allocated 55,327,872 maximum PGA allocated 23,970,624 total PGA used for auto workareas 262,144 maximum PGA used for auto workareas 7,333,032 total PGA used for manual workareas 0 maximum PGA used for manual workareas 0 estimated PGA memory for optimal 141,395 maximum PGA memory for optimal 500,123,520 estimated PGA memory for one-pass 534,144 maximum PGA memory for one-pass |
dss_config.ksh #!/bin/ksh # First, we must set the environment . . . . ORACLE_SID=$1 export ORACLE_SID ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'` #ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'` export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH $ORACLE_HOME/bin/sqlplus -s /nologin<connect system/manager as sysdba; ALTER SYSTEM set db_cache_size=1500m; ALTER SYSTEM set shared_pool_size=500m; ALTER SYSTEM set pga_aggregate_target=400m; exit ! |
婵犵鈧啿鈧綊鎮樻径鎰畺闁靛ň鏅滄慨婊堟偨椤栨稓鎽冮柟鐑╂櫊瀹曟岸宕堕埡鍌滄殸闂佽鍨伴崢鏍姳閿涘嫭鍠嗘い銈呭姬婵☆偅婢樺Λ妤呮偂濞嗘挸瀚夐柍褜鍓熷顒侊紣娓氣偓閻涙捇鏌涘┑鍛樂缂佹鐭傞獮搴ㄥ焵椤掑嫬瀚夋い鏍ㄧ懁缁诲棝鏌熼褍鐏茬紒杈ㄧ箞閺屽洭鏁愰崟顓犳澖闁荤姳闄嶉崹钘壩i崟顖涘殜闁硅泛顫曢埀顒€锕︾槐鏃堝箣閻愬弬妤呮煛閸偄鐏﹂柛瀣墬缁傛帞鎹勯搹瑙勵啈闂佸搫瀚烽崹閬嶅磻瀹ュ鍎嶉柛鏇ㄥ墯娴犳ê霉閿濆棗鈻曢柍褜鍓氶弻銊ф閻愬鈻曢悗锝傛櫇椤忛亶鏌曢崱顓熷