消除11.2上的db file parallel read
客戶在11.2.0.3環境中進行壓力測試,發現出現大量的db file parallel read等待事件。
這個等待是11g以後才出現的,而在11g以前,一般這個等待事件發生在資料檔案的恢復過程中。而11g新增了prefetch的特性,也可能導致這個等待事件的產生。
當執行壓力測試時,後臺的等待事件如下:
SQL> select event, count(*) from v$session where username = user group by event order by 2;
EVENT
COUNT(*)
---------------------------------------------------------------- ----------
SQL*Net message from client
1
SQL*Net message to client
1
db file sequential read 24
db file scattered read
33
db file parallel read
42
可以看到使用者程式經歷比較嚴重的IO等待,而此時的db file parallel read,並不會帶來效能提升。
可以透過新增隱含引數的方法來遮蔽prefetch功能,從而避免db file parallel read等待事件的產生:
_db_block_prefetch_limit=0
_db_block_prefetch_quota=0
_db_file_noncontig_mblock_read_count=0
控制檔案新增這三個隱含引數後,重啟資料庫,再次執行壓力測試,發現db file parallel read等待事件已經消失:
SQL> select event, count(*) from v$session where username = user group by event order by 2;
EVENT
COUNT(*)
---------------------------------------------------------------- ----------
SQL*Net message to client
1
db file scattered read
30
db file sequential read 70
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4227/viewspace-719997/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 0322理解db file parallel read等待事件2Parallel事件
- 0316理解db file parallel read等待事件Parallel事件
- db file parallel writeParallel
- 解決db file sequential read與db file scattered read
- 等待事件db file sequential read、db file scattered read和direct read的區別事件
- db file scattered read與事件db file sequential read相類似(轉)事件
- Oracle db file parallel write 和 log file parallel write 等待事件 說明OracleParallel事件
- Oracle:db file scattered readOracle
- 事件:db file scattered read事件
- I/O上的等待事件 —— control file sequential read/control file parallel write事件Parallel
- db file scattered read等待事件事件
- db file sequential read等待事件事件
- 【等待事件】db file sequential read事件
- 【等待事件】db file scattered read事件
- db file sequential read 詳解
- [20210315]理解db file parallel read等待事件3.txtParallel事件
- [20210315]理解db file parallel read等待事件4.txtParallel事件
- db file sequential read事件的發生事件
- db file sequential read及優化優化
- data file int write和db file sequential read個人想法
- 詳解 db file sequential read 等待事件事件
- High Waits on 'Db File Sequential Read'AI
- 非空閒的等待事件-db file scattered read事件
- DB_FILE_MULTIBLOCK_READ_COUNT的設定BloC
- Waiting Too Frequently for 'db file sequential read'AI
- oracle wait event之db file sequential readOracleAI
- 【TUNE_ORACLE】等待事件之IO等待“db file parallel write”Oracle事件Parallel
- tatspack之十二-db file scattered read-DB檔案分散讀取
- oracle之 db file sequential read等待事件優化思想Oracle事件優化
- 非空閒等待事件之:db file scattered read(轉)事件
- 何時會發生db file sequential read等待事件?事件
- db file sequential read wait event等待事件之二AI事件
- 【TUNE_ORACLE】等待事件之IO等待“db file sequential read”Oracle事件
- 【TUNE_ORACLE】等待事件之IO等待“db file scattered read”Oracle事件
- Oracle中db_file_multiblock_read_count引數探究OracleBloC
- 關於db_file_multiblock_read_count引數的設定BloC
- oracle 10g的db_file_multiblock_read_count引數Oracle 10gBloC
- log file sync 和 log file parallel writeParallel