最近做項目發現很多SQL沒有優化: 現在總結幾種優化方式.
?首先先了解一個SQL語句的執行過程分3步: 語法分析(parase)與編譯,執行,取數據.
1： 在語法分析與編譯時:oracle 使用哈希函數為SQL語句在庫緩存中分配一個SQL區,
首先檢查語句是否存在,若在,則查詢數據庫字典、檢查必須的權限。
若無，需要語法分析與編譯。所以SQL語句存在與內存中，將減少分析，編譯時間。
SQL語句的分析與編譯占整個語句運行過程的60%的時間，SQL優化的目標就是減少分析與編譯的時間，共享代碼。
查詢SQL語句分析與編譯的時間：
select * from v$sysstat
where name in ('parse time cpu','parse time elapsed','parse count (hard)')
一個SQL語句的響應時間(elapsed time )應該是服務時間+等待時間.
服務時間= CPU執行時間.
等待時間 可以從v$system_event
select total_waits, total_timeouts, time_waited, average_wait ,event
from v$system_event
where event='latch free'
所以解析一個SQL語句的平均等待時間是"等待時間/parse count" 這個值接近0
通過數據字典v$sqlare,可以查詢到頻繁被分析與編譯的SQL語句.應該減少SQL語句的分析與編譯的次數.
2: 將常用的實體駐留內存.
為了減少分析與編譯時間,可以將常用的的實體如: 存儲過程,包等,盡可能駐留在內存區域.
?1)預留內存空間. sql> show parameter shared_pool_reserved_size
?????? 2)將頻繁使用的實體駐留在內存中. 在使用DBMS_SHARED_POOL程序包前,必須首先運行系統提供的程序包: dbmspool.sql 和prvtpool.plb
?在加載這兩個程序包后,自動生成所需的包.
????? 加載: 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包含以下存儲過程.
????? dbms_shared_pool.keep 用于將實體保存內存. dbms_shared_pool.keep(object in varchar2,[type in char default p]);
???????????????????????????? object 表示參數名, type 表示被駐留內存的實體類型;P 表示存儲過程,C表示光標,R表示觸發器,默認P
???????
????? dbms_shared_pool.unkeep 用于取消被設置進入內存的實體. dbms_shared_pool.unkeep(object in varchar2,[type in char default p]);
???????????????????????????? object 表示參數名, type 表示被駐留內存的實體類型;P 表示存儲過程,C表示光標,R表示觸發器,默認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: 創建索引.
?? 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: 創建聚簇(cluster): 是一組存儲在一起的有共同列或經常一起使用的表,被聚簇的兩個表只有一個數據段.聚簇表在存儲時,在物理層將子表合并到父表中,這樣就少了表的連接時間.
5: 創建哈希索引.
?
6: SQL優化器: 基于成本的優化器CBO(cose_based)和基于規則RBO(rule_based)
?? sql> show parameter OPTIMIZER_MODE
?? 可以修改參數文件: initSID.ora,增加: optimizer_Mode={CHOOSE| RULE| FIRST_ROWS|ALL_ROWS}
? all_rows , first_rows(n)基于成本; rule 基于規則,choose基于規則、成本。
? /*+ ordered*/
? /*+ rule */
? /*+ first_rows(50) */
? /*+ordered star*/
? 寫發：?
? alter system flush shared_pool;
select /*+ rule */ aa from visit