ORACLE sql调整（三）

使用SPM

SQL PLAN MANAGEMENT是ORACLE11g中引入的新功能。SQL PROFILE实际上一些hint和统计信息，来帮助优化器得到更好的执行计划；stored outline是将稳定的执行计划存储起来，供以后使用；而SPM则是将生成的执行计划放在一个库中进行管理，使用了spm，优化器就不会突然选择效率很 低的查询。

capture SQL plan baseline：
捕捉sql plan baseline在默认情况下是关闭的，所以需要打开:

alter session
set optimizer_capture_sql_plan_baselines = true;

运行以下语句：
select * /* ARUP */ from sales
where quantity_sold > 1 order by cust_id;
-- Change the optimizer mode
alter session set optimizer_mode = first_rows;
-- Second execution. Opt Mode changed
select * /* ARUP */ from sales
where quantity_sold > 1 order by cust_id;
-- Gather stats now
begin
  dbms_stats.gather_table_stats (
      ownname            => 'SH',
      tabname             => 'SALES',
      cascade              => TRUE,
      no_invalidate      => FALSE,
      method_opt        => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
      granularity          => 'GLOBAL AND PARTITION',
      estimate_percent => 10,
      degree                => 4
  );
end;
/
-- Third execution. After stats
select * /* ARUP */ from sales
where quantity_sold > 1 order by cust_id;

/* ARUP */并不是sql hint主要是方便我们比较容易的表示这条语句。

然后可以在EM里点击 Server tab -> SQL Plan Control->SQL Plan Baseline,enable之。

Using Baselines：

alter session set
optimizer_use_sql_plan_baselines = true;

SQL> explain plan for select * /* ARUP */ from sales
2 where quantity_sold > 1 order by cust_id;
Explained.

SQL> select * from table(dbms_xplan.display(null, null, 'basic'));
PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 143117509
--------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | PARTITION RANGE ALL | |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 4 | BITMAP CONVERSION TO ROWIDS | |
| 5 | BITMAP INDEX FULL SCAN | SALES_TIME_BIX |
--------------------------------------------------------------

-- disable baselines

SQL> alter session set optimizer_use_sql_plan_baselines = false;
Session altered.

SQL> explain plan for select * /* ARUP */ from sales
2 where quantity_sold > 1 order by cust_id;
Explained.

SQL> select * from table(dbms_xplan.display(null, null, 'basic'));

PLAN_TABLE_OUTPUT
----------------------------
Plan hash value: 3803407550

--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | PARTITION RANGE ALL | |
| 3 | TABLE ACCESS FULL | SALES |
--------------------------------------

其实很多操作完全可以在EM里完成，具体可参照：
http://www.oracle.com/technology/obe/11gr1_db/manage/spm/spm.htm