如何最佳化PL/SQL

(Walter Fan Fetched from Oracle 9i performance Tuning Tips & Techniques Richard J.Niemiec)
1. 利用DBMS_APPLICATION_INFO進行即時監控
DECLARE
 lv_count_num PLS_INTEGER:=0;
 lv_start_time_num PLS_INTEGER;
BEGIN

2.在資料庫表中記錄計時資訊

3.減少PL/SQL程式的單元迭代和迭代時間

4.使用 ROWID進行迭代處理

5.將資料類型,IF語句的排列和PLS_INTEGER標準化
--確保比較運算中的資料類型相同
--根據條件出現的頻率來排序IF條件
--使用PLS_INTEGER PL/SQL資料類型進行整數運算

6.減少對SYSDATE的調用

7.減少對MOD函數的調用

8.在共用池中預載對象

9.標識需要載入的PL/SQL對象

10.使用和修改DBMS_SHARED_POOL.SIZES

11. 從DBA_OBJECT_SIZE中擷取詳細的資訊

12. 發現無效的對象
col "Owner" format a12
col "Object" format a20
col "OType" format a12
col "Change Date" format a20
SELECT SUBSTR(owner,1,12) "Owner",
 SUBSTR(object_name,1,20) "Object" ,
 object_type "OType",
 TO_CHAR(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') "Change Date"
 FROM dba_objects
 WHERE status<>'VALID'
 ORDER BY 1,2

 重新編譯所有的預存程序,函數和程式包
 begin
 dbms_utility.compile_schema('GW');
 end;

13. 發現已禁用的觸發器

14. 將PL/SQL作為快速參考表查詢

15.尋找和調整所使用對象的SQL

16.在處理DATE資料類型時使用時間資訊

17.調整和測試PL/SQL

18.瞭解PL/SQL對象定位的含義

19.使用復原段開啟大型遊標

20. 使用暫存資料表來提高效能

21.整合使用者跟蹤機制以定位執行位置

22.限制動態SQL的使用

23.簡單樣本

查詢索引
select table_name, index_name,column_name,column_position
from dba_ind_columns
where table_name='XXX'
and table_owner='XXX'
order by index_name,column_position

在v$sqlarea中找出最佔資源的查詢
SELECT b.username username,a.disk_reads READS,
a.executions EXEC,
a.disk_reads/DECODE(a.executions,0,1,a.executions) rds_exec_ratio,
a.sql_text statement
FROM v$sqlarea a,dba_users b
WHERE a.parsing_user_id=b.user_id
AND a.disk_reads>100000
ORDER BY a.disk_reads DESC

在v$sql中找出最佔資源的查詢
SELECT * FROM (
 SELECT sql_text,rank() OVER(ORDER BY buffer_gets DESC) AS rank_bufgets,
 TO_CHAR(100*ratio_to_report(buffer_gets) OVER(), '999.99') pct_bufgets
 FROM v$sql)
 WHERE rank_bufgets<11