performance tuning_1-查看性能問題

1、Oracle 性能因素性能因素Design20%Database17.5%Application60%System2.5%Unix環(huán)境下，執(zhí)行命令查看cpu的消耗： sar u 采樣頻率 執(zhí)行次數(shù)VmstatUnix環(huán)境下，執(zhí)行命令查看隊列的情況：vmstat -s 5 5Sar也可以看到i/o的性能Unix環(huán)境下，執(zhí)行命令查看磁盤的繁忙率： sar d如果busy60%，那就有I/O問題了NAME PARAMETER1 PARAMETER2 PARAMETER3 - - - -PL/SQL lock timer duration alter system set mts_dispatcher

2、 waited buffer busy waits file# block# id library cache pin handle addr pin address 0*mode+name log buffer space log file switch(checkpoint incomplete) transaction undo seg# wrap# count .286 rows selected.SQL SELECT name, parameter1, parameter2, parameter3 2 FROM v$event_name;兩類等待事件：空閑等待：oracle正在等待某

3、種動作的發(fā)生client message(客戶機消息)、NULL event（NULL事件）、pipe get（管道取操作）、SQL*NET message from client（來自客戶端的消息）、rdbms ipc message(數(shù)據(jù)庫icp消息)等非空閑等待事件：數(shù)據(jù)庫發(fā)生了競爭buffer busy waits(數(shù)據(jù)高速緩存忙等待)、db file scattered read(數(shù)據(jù)文件離散讀取)、db file sequential read(數(shù)據(jù)文件順序讀)、enqueue(隊列)、free buffer waits(空閑緩沖區(qū)等待)、latch free(拴空閑)、log f

4、ile parallel write(日志文件并行寫入)、log file sync(日志文件同步)等等待事件等待事件EVENTTOTAL_TOTAL_TIME_AVERAGE_WAITSTIMEOUTSWAITEDWAIT-latch free5551pmon timer932535254430272.993562process startup382.66666667buffer busy waits12055.34 rows selected.SQL SELECT event, total_waits, total_timeouts, 2 time_waited, average_wait

5、 3 FROM v$system_event; SID EVENT TOTAL_WAITS AVERAGE_WAIT - - - - 10 buffer busy waits 12 5 10 db file sequential read 129 0 10 file open 1 0 10 SQL*Net message to client 77 0 10 SQL*Net more data to client 2 0 10 SQL*Net message from client 76 0 SQL select sid, event, total_waits,average_wait 2 fr

6、om v$session_event where sid=10;SID SEQ#EVENTWAITSTATETIME- - -11284pmon timer0WAITING21697rdbms ipc message0WAITING3183rdbms ipc message0WAITING44688rdbms ipc message0WAITING5114smon timer0WAITING614SQL*Net message from client-1WAITEDSHORTTIMESQL SELECT sid, seq#, event, wait_time, state 2FROM v$se

7、ssion;通過等待事件判斷數(shù)據(jù)庫的問題通過等待事件判斷數(shù)據(jù)庫的問題Select * from V$SYSTEM_EVENTWhere Event in(bufferbusywaits, dbfilesequentialread, dbfilescatteredread, enqueue, freebufferwaits, latchfree, logfileparallelwrite, logfilesync);EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT-Latch free 236563 230494 41893 .1

8、77090247Enqueue 424 40 343 .808962264Free buffer waits 4232 3561 282016 .66375236Buffer busy waits 894377 2502 181907 .203389622Log file parallel write 3938548 0 804870 .204357037Log file sync 1890409 890 544425 .287993233Db file sequential read 62769635 0 3118192464 .96767658Db file scattered read

9、17928634 0 3843986 .214404845通過等待事件判斷數(shù)據(jù)庫的問題通過等待事件判斷數(shù)據(jù)庫的問題通過等待事件判斷數(shù)據(jù)庫的問題通過等待事件判斷數(shù)據(jù)庫的問題通過等待事件判斷數(shù)據(jù)庫的問題通過等待事件判斷數(shù)據(jù)庫的問題buffer busy waits(數(shù)據(jù)高速緩存忙等待)db file parallel write(數(shù)據(jù)文件并行寫)db file scattered read(數(shù)據(jù)文件離散讀取)db file sequential read(數(shù)據(jù)文件順序讀)db file single write(數(shù)據(jù)文件單次寫)direct path read(直接路徑讀取)direct path

10、 write(直接路徑寫出)enqueue(隊列)free buffer inspected(空閑數(shù)據(jù)緩沖區(qū)探測)free buffer waits(空閑緩沖區(qū)等待)latch free(鎖存器空閑)library cache load lock(庫高速緩存裝載鎖)常見的等待事件常見的等待事件library cache lock(庫高速緩存鎖)library cache pin(庫高速緩存執(zhí)行鎖)log buffer space(日志緩沖區(qū)空間分配)log file parallel write(日志文件并行寫入)log file single write(日志文件單次寫)log file s

11、witch(archiving needed)log file switch(checkpoint incomplete)log file sync(日志文件同步)timer in sksawat(歸檔過慢)Transaction(事務(wù)阻塞)undo segment extension(回滾段動態(tài)擴展)常見的等待事件常見的等待事件buffer busy waitsIndicates wait for buffers in the database buffer cache.This indicates that a session is reading this buffer into the

12、 cache and/or modifying it. Can also be a symptom of lack of enough free lists on tables that support many concurrent INSERT operations.等待事件說明等待事件說明db file scattered readIndicates waits associated with a full table scan. May indicate I/O contention or an excessive amount of I/O. db file sequential r

13、eadIndicates (among other things) waits associated with an index scan. May indicate I/O contention or an excessive amount of I/O.db file single writeIndicates waits associated with header writes during a checkpoint. Typical in an environment with an inordinate number of data files.等待事件說明等待事件說明direct

14、 path readIndicates waits associated with direct I/O enabled. Usually indicates I/O contention on devices. direct path writeSame as above. Relevant to writes.enqueueIndicates waits associated with internal queuing mechanism for locking various resources and components of Oracle. Please refer to Appe

15、ndix B of the Oracle11g Reference Manual for the complete list of enqueues in Oracle.等待事件說明等待事件說明library cache lockIndicates waits associated with concurrency of multiple processes accessing the library cache. Can indicate an improperly sized shared pool area, as this lock needs to be acquired to lo

16、cate objects in the library cache.等待事件說明等待事件說明library cache pinThis wait event is also associated with library cache concurrency and can occur when a given object needs to be modified or examined in the library cache. log file parallel writeIndicates waits associated with the writing of redo records

17、 from the redo log buffer to disk.Usually indicates slow device(s) or contention where the online redo logs are located.等待事件說明等待事件說明log file switch(archiving needed)Waits indicate ARCH is not keeping up with GWR.Could be because of online redo logs being too small, slow devices,or high contention on devices (usually caused by the logfiles placed on the devices where datafiles are). May also want to investigate the possibility of multiple ARCH processes or I/O slaves,as the case may be.log file switch(checkpoint incomplete)Indicates waits associated with improperly sized onlin