最近做項(xiàng)目發(fā)現(xiàn)很多SQL沒(méi)有優(yōu)化: 現(xiàn)在總結(jié)幾種優(yōu)化方式.
?首先先了解一個(gè)SQL語(yǔ)句的執(zhí)行過(guò)程分3步: 語(yǔ)法分析(parase)與編譯,執(zhí)行,取數(shù)據(jù).
1： 在語(yǔ)法分析與編譯時(shí):oracle 使用哈希函數(shù)為SQL語(yǔ)句在庫(kù)緩存中分配一個(gè)SQL區(qū),
首先檢查語(yǔ)句是否存在,若在,則查詢(xún)數(shù)據(jù)庫(kù)字典、檢查必須的權(quán)限。
若無(wú)，需要語(yǔ)法分析與編譯。所以SQL語(yǔ)句存在與內(nèi)存中，將減少分析，編譯時(shí)間。
SQL語(yǔ)句的分析與編譯占整個(gè)語(yǔ)句運(yùn)行過(guò)程的60%的時(shí)間，SQL優(yōu)化的目標(biāo)就是減少分析與編譯的時(shí)間，共享代碼。
查詢(xún)SQL語(yǔ)句分析與編譯的時(shí)間：
select * from v$sysstat
where name in ('parse time cpu','parse time elapsed','parse count (hard)')
一個(gè)SQL語(yǔ)句的響應(yīng)時(shí)間(elapsed time )應(yīng)該是服務(wù)時(shí)間+等待時(shí)間.
服務(wù)時(shí)間= CPU執(zhí)行時(shí)間.
等待時(shí)間 可以從v$system_event
select total_waits, total_timeouts, time_waited, average_wait ,event
from v$system_event
where event='latch free'
所以解析一個(gè)SQL語(yǔ)句的平均等待時(shí)間是"等待時(shí)間/parse count" 這個(gè)值接近0
通過(guò)數(shù)據(jù)字典v$sqlare,可以查詢(xún)到頻繁被分析與編譯的SQL語(yǔ)句.應(yīng)該減少SQL語(yǔ)句的分析與編譯的次數(shù).
2: 將常用的實(shí)體駐留內(nèi)存.
為了減少分析與編譯時(shí)間,可以將常用的的實(shí)體如: 存儲(chǔ)過(guò)程,包等,盡可能駐留在內(nèi)存區(qū)域.
?1)預(yù)留內(nèi)存空間. sql> show parameter shared_pool_reserved_size
?????? 2)將頻繁使用的實(shí)體駐留在內(nèi)存中. 在使用DBMS_SHARED_POOL程序包前,必須首先運(yùn)行系統(tǒng)提供的程序包: dbmspool.sql 和prvtpool.plb
?在加載這兩個(gè)程序包后,自動(dòng)生成所需的包.
????? 加載: sql> @/u01/app/oracle/product/8.17/rdbms/admin/dbmspool.sql
?????????? sql> @/u01/app/oracle/product/8.17/rdbms/admin/prvtpool.sql
????? 包DBMS_SHARED_POOL包含以下存儲(chǔ)過(guò)程.
????? dbms_shared_pool.keep 用于將實(shí)體保存內(nèi)存. dbms_shared_pool.keep(object in varchar2,[type in char default p]);
???????????????????????????? object 表示參數(shù)名, type 表示被駐留內(nèi)存的實(shí)體類(lèi)型;P 表示存儲(chǔ)過(guò)程,C表示光標(biāo),R表示觸發(fā)器,默認(rèn)P
???????
????? dbms_shared_pool.unkeep 用于取消被設(shè)置進(jìn)入內(nèi)存的實(shí)體. dbms_shared_pool.unkeep(object in varchar2,[type in char default p]);
???????????????????????????? object 表示參數(shù)名, type 表示被駐留內(nèi)存的實(shí)體類(lèi)型;P 表示存儲(chǔ)過(guò)程,C表示光標(biāo),R表示觸發(fā)器,默認(rèn)P
?????
????? dbms_shared_pool.size(minsize in number)
????? select name ,type ,source_size+code_size+parsed_size+error_size "total bytes"
????? from dba_object_size
????? where owner='SCOTT'
3: 創(chuàng)建索引.
?? select index_name,table_owner, table_name, tablespace_name from all_indexes
??
?? select user_indexes.TABLE_NAME, user_indexes.INDEX_NAME,uniqueness, column_name
?? from user_ind_columns ,user_indexes
?? where user_ind_columns.INDEX_NAME=user_indexes.INDEX_NAME
?? and user_ind_columns.TABLE_NAME=user_indexes.TABLE_NAME
?? order by user_indexes.TABLE_TYPE,user_indexes.TABLE_NAME,user_indexes.INDEX_NAME,user_ind_columns.COLUMN_POSITION
4: 創(chuàng)建聚簇(cluster): 是一組存儲(chǔ)在一起的有共同列或經(jīng)常一起使用的表,被聚簇的兩個(gè)表只有一個(gè)數(shù)據(jù)段.聚簇表在存儲(chǔ)時(shí),在物理層將子表合并到父表中,這樣就少了表的連接時(shí)間.
5: 創(chuàng)建哈希索引.
?
6: SQL優(yōu)化器: 基于成本的優(yōu)化器CBO(cose_based)和基于規(guī)則RBO(rule_based)
?? sql> show parameter OPTIMIZER_MODE
?? 可以修改參數(shù)文件: initSID.ora,增加: optimizer_Mode={CHOOSE| RULE| FIRST_ROWS|ALL_ROWS}
? all_rows , first_rows(n)基于成本; rule 基于規(guī)則,choose基于規(guī)則、成本。
? /*+ ordered*/
? /*+ rule */
? /*+ first_rows(50) */
? /*+ordered star*/
? 寫(xiě)發(fā)：?
? alter system flush shared_pool;
select /*+ rule */ aa from visit