在Oracle中通过优化器计划稳定性来优化应用程序
===========================================================
在Oracle中通过优化器计划稳定性来优化应用程序
作者: yaanzy(http://yaanzy.itpub.net)
发表于: 2005.03.08 10:48
分类: Oracle技术
出处: http://yaanzy.itpub.net/post/1263/20803
---------------------------------------------------------------
dba在实际工作中经常会遇到这种情况：应用程序开发者写得SQL效率极其低下，dba想通过
给sql语句添加提示(hint)的方式来提高效率，但是又不允许直接对应用程序进行修改。

对此Oracle从8i开始就提供了优化器计划稳定性(Optimizer Plan Stability)来完成这
一功能，可以对当前应用程序中的SQL创建最优的执行计划存储概要。

下面就通过一个例子来描述这一功能的具体实现：(Oracle 9.2.0.6)

SQL> create table aaa as select * from dba_objects where object_id is not null;

Table created

SQL> alter table aaa modify object_id not null;

Table altered

SQL> create table bbb as select * from dba_objects where object_id is not null and rownum <=3000;

Table created

SQL> alter table bbb modify object_id not null;

Table altered

SQL> set timing on
SQL> set autotrace on exp
SQL> select count(*) from aaa where aaa.object_id not in (select object_id from bbb);

COUNT(*)
----------
28087

Elapsed: 00:00:17.33

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'AAA'
   4    2       TABLE ACCESS (FULL) OF 'BBB'

SQL> select count(*) from aaa where aaa.object_id not in (select /*+ hash_aj */object_id from bbb);

COUNT(*)
----------
28087

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=57 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (ANTI) (Cost=57 Card=22066 Bytes=573716)
   3    2       TABLE ACCESS (FULL) OF 'AAA' (Cost=44 Card=25066 Bytes
          =325858)

4 2 TABLE ACCESS (FULL) OF 'BBB' (Cost=6 Card=3000 Bytes=3
9000)

可以看出上面两句SQL在有和没有提示下，执行效率相差是很大的，下面我们就来设置如何使得运行第一句
时，实际执行的是第二句的执行计划。

SQL> create outline slow on select count(*) from aaa where aaa.object_id not in (select object_id from bbb);

Outline created

SQL> create outline fast on select count(*) from aaa where aaa.object_id not in (select /*+ hash_aj */object_id from bbb);

Outline created

我们通过修改基础概要表来交换这两个返回相同结果的语句的存储概要，改动后，慢的SQL就可以执行快的执行
计划(如果它使用了概要)。

SQL> update outln.ol$hints set ol_name=decode(ol_name,'FAST','SLOW','SLOW','FAST')
2 where ol_name in ('FAST','SLOW');

23 rows updated

SQL> commit;

Commit complete

SQL> drop outline fast;

Outline dropped

设置在会话级别使用存储概要，缺省类别是default

SQL> alter session set use_stored_outlines=true;

Session altered

SQL> select count(*) from aaa where aaa.object_id not in (select object_id from bbb);

COUNT(*)
----------
28087

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=59 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (ANTI) (Cost=59 Card=1 Bytes=26)
   3    2       TABLE ACCESS (FULL) OF 'AAA' (Cost=44 Card=36021 Bytes
          =468273)

4 2 TABLE ACCESS (FULL) OF 'BBB' (Cost=6 Card=4084 Bytes=5
3092)

现在我们所要想实现的目标实现了，存储概要给SQL语句添加上了提示后执行。

BTW: 查询存储概要的视图 user_outlines user_outline_hints
基表 outln.ol$ outln.ol$hints

在实际工作中首先需要通过sql_trace等确定有问题的sql语句，然后写出优化后语句的存储概要，
最后在ON LOGON数据库触发器中设置只有特定的应用程序连接到数据库后才使用存储概要。

create or replace trigger db_logon
after logon on database
begin
if (user='demo_app') then
execute immediate 'alter session set create_stored_outlines=true';
end if;
end;
yaanzy 发表于:2005.03.08 10:48 ::分类