如何优化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