PL/SQL性能优化(一)

Oracle SQL性能优化

（1）       选择最有效率的表名顺序(只在基于 规则的优化器中有效)：

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果, 交叉表是指那个被其他表所引用的表.

--在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表
--假设a表中有数据120W，b表中有数据20W
SELECT count(a.a1) FROM a,b WHERE a.a1 = b.b1

--有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表
SELECT count(a.a1) FROM a,b,c WHERE a.a1 = c.c1 and b.b1 = c.c1

 

（2）       WHERE子句中的连接顺序：
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

SELECT count(a.a1) FROM a,b WHERE a.a1 = b.b1 AND a.a1 > 100000

 

（3）       SELECT子句中避免使用 ‘ * ‘：
ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

--效率不高，SELECT count(*) FROM a;
SELECT count(a.a1) FROM a;

 

（4）       减少访问数据库的次数： 能TM的一次检索出来的话就别分多次
ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等；

--当执行每条SQL语句时，ORACLE在内部执行了许多工作：解析SQL语句，估算
--索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数，
--就能实际上减少ORACLE的工作量。

--例如：
--以下有三种方法可以检索出雇员号等于0342或0291的职员在一行
--方法1：(最低效)
select emp_name, salary, grade from emp where emp_no = 342;
select emp_name, salary, grade from emp where emp_no = 291;
--方法2：(次低效)
declare
cursor c1(e_no number) is
select emp_name, salary, grade from emp where emp_no = e_no;
begin
open c1(342);
fetch c1
into .., .., ..;
open c1(291);
fetch c1
into .., .., ..;
close c1;
end;


--方法3：(高效)
select  a.emp_name, a.salary, a.grade, b.emp_name, b.salary, b.grade
from  emp a, emp b
where  a.emp_no = 342
and  b.emp_no = 291;

 

（5）       在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200 不是说这个数越多越好 啊，很占逻辑 I/O，具体问题具体分析，如果想深入研究的话，请参考 Arraysize的设置以及对查询性能的影响 。

--Arraysize是sql*plus中可以设置的一个参数，这个参数设置的意思表
--示，sql*plus一次可以从数据库服务器端获取的记录行数。
show arraysize
arraysize 15

--可以看到，在SQL*plus中，默认设置是15。
--有效值是1-5000。我们把以下语句运行看看效果

set arraysize 15
select * from colocated a15 where x between 20000 and 30000;

set arraysize 100
select * from colocated a100 where x between 20000 and 30000;

tkprof报告显示：

（a15）
Rows     Row Source Operation
-------  ---------------------------------------------------
  10001  TABLE ACCESS BY INDEX ROWID COLOCATED (cr=1452 pr=0 pw=0 time=100109 us)
  10001   INDEX RANGE SCAN COLOCATED_PK (cr=689 pr=0 pw=0 time=40047 us)(object id 53215)

（a100）
Rows     Row Source Operation
-------  ---------------------------------------------------
  10001  TABLE ACCESS BY INDEX ROWID COLOCATED (cr=344 pr=0 pw=0 time=90081 us)
  10001   INDEX RANGE SCAN COLOCATED_PK (cr=124 pr=0 pw=0 time=30043 us)(object id 53215)
--Arraysize为15时，对索引执行了689个逻辑I/O，
--对表执行了763（1452-689）个逻辑I/O，arraysize为100时，
--对索引执行了124个逻辑I/O，
--对表执行了220个逻辑I/O。
--这说明因为表有序，
--所以在一个块中有较多需要的记录，增加arraysize可以获得良好效果。

 

（6）       使用DECODE函数来减少处理时间：
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

--从网上找到了下面的例子，要计算两个SUM的话一般都会这样去写
　　 SELECT COUNT(*)，SUM(SAL) FROM　EMP
　　 WHERE DEPT_NO = 0020  AND ENAME LIKE　‘SMITH%’;

　　 SELECT COUNT(*)，SUM(SAL) FROM　EMP
　　 WHERE DEPT_NO = 0030  AND ENAME LIKE　‘SMITH%’;
　　
--咱们用DECODE函数来试试
　　SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
　　FROM EMP WHERE ENAME LIKE ‘SMITH%’;

--说白了还是跟上面我说的第4个优化原理是一样的

 

（7）       整合简单,无关联的数据库访问：
如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)

--下面有三条比较有代表性的语句
SELECT a.a1 FROM a WHERE a.id = 1;
SELECT b.b1 FROM b WHERE b.editdate = sysdate;
SELECT c.c1 FROM c WHERE c.id='c';

--我们一起来把他们混搭一起
SELECT a.a1,  b.b1, c.c1 FROM a,b,c,DUAL X
WHERE NVL(‘X’,X.DUMMY) = NVL(‘X’,a.ROWID(+))
AND NVL(‘X’,X.DUMMY) = NVL(‘X’,b.ROWID(+))
AND NVL(‘X’,X.DUMMY) = NVL(‘X’,c.ROWID(+))
AND a.id (+) = 1
AND b.editdate (+) = sysdate
AND c.id(+) = ‘c’; 

--看起来挺乱的，所以最好是能够将分开的写法作为注释，便于日后进行修改升级

 

（8）       删除重复记录：
最高效的删除重复记录方法 ( 因为使用了ROWID)例子：

DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID)
FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO);

 

（9）       用TRUNCATE替代DELETE：
当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.但是truncate的应用还有很多地方需要我们仔细研究，请参考[ORACLE]浅谈truncate的使用

--表中插入1000万条数据
insert into test_table select level from dual connect by level<=10000000;
10000000 rows created.

commit;
Commit complete.
Elapsed: 00:00:00.02

truncate table test_table;
Table truncated.
Elapsed: 00:00:29.52

set autotrace traceonly statistics;
select * from test_table;
no rows selected
Elapsed: 00:00:00.00

Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
          6  consistent gets
          0  physical reads
         96  redo size
        318  bytes sent via SQL*Net to client
        453  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
--truncate后再查询只做了7个逻辑读，也就是读了7个内存块。

 

（10） 尽量多使用COMMIT：
只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. ORACLE为管理上述3种资源中的内部花费