ITPub博客

首页 > 数据库 > Oracle > 在Oracle中通过优化器计划稳定性来优化应用程序

在Oracle中通过优化器计划稳定性来优化应用程序

原创 Oracle 作者:yaanzy 时间:2005-03-08 10:48:49 0 删除 编辑
dba在实际工作中经常会遇到这种情况:应用程序开发者写得SQL效率极其低下,dba想通过
给sql语句添加提示(hint)的方式来提高效率,但是又不允许直接对应用程序进行修改。[@more@]

对此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 Optimizer=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 Optimizer=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 Optimizer=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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/3898/viewspace-791926/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    108
  • 访问量
    761479