|
第9章性能診斷與SQL優(yōu)化 在Oracle Explain plan for select count(*) from dual; @?/rdbms/admin/utlxplp 該腳本中調(diào)用了dbms_xplan： Select * from table(dbms_xplan.display()); 在Oracle AUTOTRACE功能的內(nèi)部操作 當(dāng)使用AUTOTRACE功能時(shí)，在數(shù)據(jù)庫內(nèi)部，Oracle實(shí)際上是啟動(dòng)了2個(gè)session連接，一個(gè)session用于執(zhí)行查詢等操作，另外一個(gè)session用于記錄執(zhí)行計(jì)劃和輸出最終結(jié)果等操作。注意，這兩個(gè)session都是由一個(gè)進(jìn)程衍生創(chuàng)建的。這就是通常所說的，一個(gè)進(jìn)程在數(shù)據(jù)庫中可能對(duì)應(yīng)多個(gè)session。 通過在全局啟用10046事件，可以得到AUTOTRACE的內(nèi)部操作： Alter system set event=’10046 trace name context forever,level 從V$SYSSTAT視圖中，可以查詢得到關(guān)于全表掃描的系統(tǒng)統(tǒng)計(jì)信息： Select name,value from v$sysstat Where name in (‘table scans(short tables)’,’table scans(long tables)’); 其中table scans(short tables)指對(duì)于小表的全表掃描的。Table scans(long tables)指對(duì)于大表的全表掃描繁的次數(shù)。 Oracle通過一個(gè)內(nèi)部參數(shù)_small_table_threshold來定義大表和小表的界限。缺省情況下該參數(shù)等于2%的Buffer數(shù)量。 之所以要區(qū)分大小表是因?yàn)槿頀呙杩赡芤?/span>Buffer Cache的抖動(dòng)，缺省情況下，大表的全表掃描會(huì)被置于LRU的末端，以期盡快老化，減少Buffer的占用。 有效地降低SQL的邏輯讀是SQL優(yōu)化的基本原則之一。 使用SQL_TRACE /10046事件進(jìn)行數(shù)據(jù)庫診斷 當(dāng)在數(shù)據(jù)庫中啟用SQL_TRACE或者設(shè)置10046事件之后，Oracle將會(huì)啟動(dòng)內(nèi)核跟蹤程序，持續(xù)記錄會(huì)話的相關(guān)信息，并寫入到相應(yīng)trace文件中。跟蹤記錄的內(nèi)容包括SQL的解析過程、SQL的執(zhí)行計(jì)劃、綁定變量的使用及會(huì)話中發(fā)生的等待事件等。 SQL_TRACE及10046事件的基礎(chǔ)介紹 在使用SQL_TRACE之前，需要注意幾點(diǎn)： 初始化參數(shù)TIMED_STATISTICS最好設(shè)為TRUE，否則一些重要信息不會(huì)被收集； 設(shè)置MAX_DUMP_FILE_SIZE 在session級(jí)可以設(shè)置： Alter session set max_dump_file_size=unlimited; 在全局啟用SQL_TRACE 在當(dāng)前session級(jí)設(shè)置 跟蹤其他用戶進(jìn)程： 在很多時(shí)候需要跟蹤其他用戶的進(jìn)程，而不是當(dāng)前用戶，這可以通過Oracle提供的系統(tǒng)包DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION來完成。 通過查詢v$session可以獲得sid、serial#等信息： Select sid,serial#,username from v$session where username is not null; 設(shè)置跟蹤： Exec dbms_system.set_sql_trace_in_session(9,437,true); 如果要對(duì)其他用戶的參數(shù)進(jìn)行設(shè)置，需要用到dbms_system包中的另一個(gè)過程： Begin Sys.dbms_system.set_bool_param_in_session(18,1605,’timed_statistics’,true); Sys.dbms_system.set_bool_param_in_session(18,1605,’max_dump_file_size’,2147483647); Sys.dbms_system.set_sql_trace_in_session(18,1605, true); End; / 10046事件說明 10046事件是Oracle提供的內(nèi)部事件，是對(duì)sql_trace的增強(qiáng)。10046事件可以設(shè)置為以下4個(gè)級(jí)別： Level 1：?jiǎn)⒂脴?biāo)準(zhǔn)的SQL_TRACE功能，等價(jià)于SQL_TRACE Level 4：等價(jià)于Level 1+綁定值（bind values） Level 8：等價(jià)于Level 1+等待事件跟蹤 Level 12：等價(jià)于Level 1+ Level 4+ Level 12 類似SQL_TRACE，10046事件可以在全局設(shè)置，也可以在session級(jí)設(shè)置。 在全局設(shè)置： 在參數(shù)文件中增加：event=’10046 trace name context forever, level 此設(shè)置對(duì)所有用戶的所有進(jìn)程生效、包括后臺(tái)進(jìn)程。 對(duì)當(dāng)前session設(shè)置： Alter session set events ‘10046 trace name context forever’; Alter session set events ‘10046 trace name context forever, level Alter session set events ‘10046 trace name context off’; 對(duì)其他用戶session設(shè)置： 通過DBMS_SYSTEM.SET_ENV系統(tǒng)包來實(shí)現(xiàn)： Exec dbms_system.set_ev(9,437,10046,8,’eygle’); --執(zhí)行跟蹤 Exec dbms_system.set_ev(9,437,10046,0,’eygle’); --結(jié)束跟蹤 診斷案例一：隱式轉(zhuǎn)換與索引失效 在Oracle開發(fā)中，應(yīng)該盡量避免使用隱式的數(shù)據(jù)類型轉(zhuǎn)換，因?yàn)殡[式數(shù)據(jù)類型轉(zhuǎn)換可能會(huì)帶來索引失效的問題，給系統(tǒng)埋下隱患。使用函數(shù)導(dǎo)致索引失效的問題與此類似。 診斷案例二：跟蹤后臺(tái)錯(cuò)誤 很多時(shí)候，在進(jìn)行數(shù)據(jù)庫操作時(shí)，如drop user、drop table等，經(jīng)常會(huì)遇到這樣的錯(cuò)誤： ORA-00604：error occurred at recursive SQL level 1. 關(guān)于recursive SQL錯(cuò)誤的說明： 當(dāng)發(fā)出一條簡(jiǎn)單的SQL命令以后，Oracle數(shù)據(jù)庫要在后臺(tái)解析這條命令，并轉(zhuǎn)換為Oracle數(shù)據(jù)庫的一系列后臺(tái)操作，這些后臺(tái)操作統(tǒng)稱為遞歸SQL。 比如create table這樣一條簡(jiǎn)單的DDL命令，Oracle數(shù)據(jù)庫在后臺(tái)，實(shí)際上要把這個(gè)命令轉(zhuǎn)換為對(duì)obj$、tab$、col$等低層表的插入操作；對(duì)于drop table操作，則是在這些系統(tǒng)表中進(jìn)行反向刪除操作。 10046與等待事件 10046事件時(shí)SQL_TRACE功能的增強(qiáng)，可以通過10046跟蹤獲得更多的信息，包括非常有用的等待事件等。對(duì)于不同進(jìn)程的等待可以通過v$session_wait來查詢，對(duì)于數(shù)據(jù)庫全局等待可以通過v$system_event來獲得。 等待事件Db file scattered read意味著使用全表掃描來訪問數(shù)據(jù) 初始化參數(shù)db_file_multiblock_read_count代表Oracle在執(zhí)行全表掃描時(shí)每次IO操作可以讀取的數(shù)據(jù)塊的數(shù)量。 如果db_file_multiblock_read_count參數(shù)設(shè)置為16，而extent大小為8個(gè)block，Oracle的一次IO操作不能跨越extent。所以全表掃描每次只能讀取8個(gè)block。 可以通過下面的方法進(jìn)行調(diào)整： Create tablespace mytbs datafile ‘…..’ size Alter table t move tablespace mytbs; Select file_id,block_id,blocks from dba_extents where segment_name=’T’; Show parameter db_file_multiblock_read_count 通常較大的db_file_multiblock_read_count設(shè)置可以加快全表掃描的執(zhí)行，但是根據(jù)經(jīng)驗(yàn)大于32的設(shè)置通常不會(huì)帶來更大的性能提高。 10046與執(zhí)行計(jì)劃的選擇 需要注意的是，增大db_file_multiblock_read_count參數(shù)的設(shè)置，會(huì)使全表掃描的成本降低，在CBO優(yōu)化器下可能會(huì)使Oracle更傾向于使用全表掃描而不是索引訪問。 使用物化視圖進(jìn)行翻頁性能調(diào)整 物化視圖通過預(yù)計(jì)算或匯總構(gòu)建自己的獨(dú)立存儲(chǔ)，從而可以極大地提高相關(guān)處理的性能，通過查詢重寫功能，Oracle可以自動(dòng)對(duì)SQL進(jìn)行改寫以最大程度地發(fā)揮物化視圖的作用。 物化視圖是典型的通過存儲(chǔ)空間換取性能的方式，通過物化視圖，Oracle可以： 有效地減少邏輯讀取； 減少寫操作——通過消除排序及聚集實(shí)現(xiàn)； 減少CPU的消耗——無需實(shí)時(shí)進(jìn)行復(fù)雜運(yùn)算； 顯著提高相應(yīng)速度。 結(jié)合業(yè)務(wù)邏輯，考慮創(chuàng)建物化視圖，通過物化視圖的中間存儲(chǔ)消除不必要的全表掃描，同時(shí)必須充分考慮用戶的業(yè)務(wù)需求是否允許足夠的刷新間隔。 降序索引本質(zhì)上是基于函數(shù)的索引，只有在CBO下才能被用到： Create index inx_desc on HW_USER(col_nm1 desc,col_nm2 desc,col_nm3 desc); 實(shí)驗(yàn)驗(yàn)證： SQL> show user USER為"SCOTT" SQL> select * from v$version where rownum<2; BANNER ---------------------------------------------------------------- Oracle9i SQL> create table t as select * from user_objects; 表已創(chuàng)建。 SQL> create index idx_objname_desc on t(object_name desc); 索引已創(chuàng)建。 SQL> select index_name,table_name,index_type 2 from user_indexes where table_name='T'; INDEX_NAME TABLE_NAME ------------------------------ ------------------------------ INDEX_TYPE --------------------------- IDX_OBJNAME_DESC T FUNCTION-BASED SQL> select column_name,column_position,descend from user_ind_columns 2 where table_name='T'; COLUMN_NAME -------------------------------------------------------------------------------- COLUMN_POSITION DESC --------------- ---- SYS_NC00013$ 1 DESC 降序索引以及FBI的定義可以從DBA_IND_EXPRESSIONS或USER_IND_EXPRESSIONS視圖中獲得。 SQL> select * from user_ind_expressions where table_name='T'; INDEX_NAME TABLE_NAME ------------------------------ ------------------------------ COLUMN_EXPRESSION -------------------------------------------------------------------------------- COLUMN_POSITION --------------- IDX_OBJNAME_DESC T "OBJECT_NAME" 1 |
|
|
