【正文】
d_object t1,v$session t2,v$sqltext t3 and =latch從V$LATCH,V$LATCH_CHILDREN,V$LATCH_MISSES查看有關LATCH的統(tǒng)計信息例:SELECT latch, name, gets, misses, sleeps FROM v$latchWHERE sleeps0ORDER BY sleeps。在PEAK TIME,查看當前的LATCH競爭例:select count(*), name latchname from v$session_wait, v$latchnamewhere event=39。 and state=39。 and p2=latchgroup by name order by 1 desc。最常見的LATCH競爭是LIBRARY CACHE、SHARED POOL、CACHE BUFFER CHAINS和CACHE BUFFERS LRU CHAINS。顯示系統(tǒng)范圍內的latch統(tǒng)計信息*/columnformattruncateLATCHpidHOLDER,v$latchv$latchholderv$latchname=and=by給出一個latchnamea6439。selectfromv$latchnamev$latch=amp。=。顯示latch統(tǒng)計信息*/namea3239。NAME39。pid39。PID39。select,fromv$latchv$latchholderv$latchnamewhere(+)39。latch_name%39。orderenqueue等待Select eq_type lock,total_req gets,total_wait waits,cum_wait_time from v$enqueue_stat where total_wait0。ACTIVE39。select event,p1 File ,p2 Block ,p3 Reason Code from v$session_waitorder by event。buffer busy waits39。P1 and amp。column event for a35。select sid,event,p1,p1text from v$session_wait order by event;RAC全局等待事件select * from v$event_name where NAME like 39。 and WAIT_CLASS=39。數(shù)據(jù)文件IOselect name, pbr, pbw, pyr,from v$filestat f, v$dbfile fswhere = order by 2,3,4 desc。長事務set linesize 200column name for a16column username for a10select ,from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction ewhere =and =and =and =and =order by ,。yyyymmdd hh24:mi:ss39。%RMAN%39。yyyymmdd hh24:mi:ss39。where (sofar/totalwork)*100 100。如果session過多,察看select * from v$session,察看是什么程序察看用戶session使用內存大小SELECT username,sum(trunc((value/1024))) MEM_USEDFROM v$session sess, v$sesstat stat, v$statname name WHERE = AND = AND in (39。 ,39。)group by username,order by mem_used歸檔的生成頻率set linesize 120column begin_time for a26column end_time for a26select ,to_char(,39。) begin_time,to_char(,39。) end_time,round(( )*24*60,2) minutesfrom v$log_history a,v$log_history bwhere = +1。physical reads39。db block gets39。consistent gets39。重做日志命中率SELECT name, gets, misses, immediate_gets, immediate_misses,100 Decode(gets,0,0,misses/(gets+misses))*100 ratio1,100 Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses))*100 ratio2FROM v$latch WHERE name IN (39。, 39。)。TOP SQLselect sql_text,buffer_gets,executions,buffer_gets/executions from v$sqlarea where buffer_gets1000000 order by 4 desc;查看 oracle sga使用情況,及分析select round(sga,0) sga,round(cache_size,0) cache_size,round(coun/cache_size*100,0)||39。 cache_used,round(shared_size,0) shared_size,round(freemb/shared_size*100,0)||39。 free_shared,round(java_size,0) java,round(large_pool_size,0) largefrom (select bytes/1024/1024 sga from v$sgainfo where name=39。),(select bytes/1024/1024 shared_size from v$sgainfo where name=39。),(select bytes/1024/1024 cache_size from v$sgainfo where name=39。),(select bytes/1024/1024 java_size from v$sgainfo where name=39。),(select bytes/1024/1024 large_pool_size from v$sgainfo where name=39。),(select bytes/1024/1024 freemb from v$sgastat s where =39。 and =39。),(select sum(count(*))*8/1024 coun from v$bh where status39。 group by status )。沒有free不一定說明需要增加,還要結合當前cache_size的大小,緩沖區(qū)命中率,等待事件(如:free buffer waits39。在線日志情況SELECT ,ROUND(()*24*60,2) MINATESFROM V$LOG_HISTORY A,V$LOG_HISTORY BWHERE = +1 AND SYSDATE 20 AND ROUND(()*24*60,2)30ORDER BY DESC。yyyymmdd hh24:mi:ss39。20130304 00:00:0039。YYYYMMDD HH24:MI:SS39。20130305 08:00:0039。YYYYMMDD HH24:MI:SS39。收集統(tǒng)計信息察看統(tǒng)計信息SELECT * FROM DBA_TAB_STATISTICS where owner=39。SELECT * FROM DBA_IND_STATISTICS where owner=39。自動? 方法一: exec (39。)。39。? 方法二: alter system set _optimizer_autostats_job=false scope=spfile。 修改記錄最多的對象(產生歸檔過快)select snap_id,obj,db_block_changes_total from dba_hist_seg_stat order bydb_block_changes_total。統(tǒng)計Oracle讀塊的IO能力比如一個表有1萬個