SPM怎麼感覺不起作用

jacksan

最近一直在研究SPM，本來是很簡單的一個試驗，可是結果總不是期望的，不知為何。

試驗環境：
Redhat 2.6.18-308.el5 64 bit
11.2.0.3.0 64 bit installed （standard edition one）
11.2.0.3.0 32 bit client installed（standard edition one）

步驟如下：
（1）
create table test_spm(seq_no number(10,0), flg number(10,0));
BEGIN
FOR i IN 1..10000 LOOP
   insert into test_spm values(i,0);
   commit;
END LOOP;
END;
/

（2）
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
     ownname      => 'TEST'
    ,tabname      => 'TEST_SPM'
  );
END;
/

（3）
alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;
show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

NAME                                 TYPE      VALUE
------------------------------------ --------- --------
optimizer_capture_sql_plan_baselines boolean   TRUE

show parameter OPTIMIZER_USE_SQL_PLAN_BASELINES

NAME                                 TYPE      VALUE
------------------------------------ --------- --------
optimizer_use_sql_plan_baselines     boolean   TRUE

（4）
set autotrace trace
select * from test_spm where seq_no = 1; <-- 第一次執行

Execution Plan
----------------------------------------------------------
Plan hash value: 1145642998

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     6 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SPM |     1 |     6 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SEQ_NO"=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        591  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

set autotrace off
select sql_text,sql_handle from dba_sql_plan_baselines; <--no baselines captured

no rows selected

（5）
set autotrace trace
select * from test_spm where seq_no = 1;   <-- 第二次執行

Execution Plan
----------------------------------------------------------
Plan hash value: 1145642998

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     6 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SPM |     1 |     6 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SEQ_NO"=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        591  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

set autotrace off
select sql_text,sql_handle from dba_sql_plan_baselines;   <-- still no baselines captured

no rows selected

問題就在這里，爲什麽執行了兩次同一個SQL，dba_sql_plan_baselines里卻沒有任何數據呢？？

Maclean Liu(刘相兵

alter session set optimizer_capture_sql_plan_baselines=true;


select count(object_id) from dba_objects where object_id between 10 and 1000;
select count(object_id) from dba_objects where object_id between 10 and 1000;

alter session set optimizer_capture_sql_plan_baselines=false;


  select * from dba_sql_plan_baselines;

jacksan

SQL> alter session set optimizer_capture_sql_plan_baselines=true;

Session altered.

SQL> select count(object_id) from dba_objects where object_id between 10 and 1000;

COUNT(OBJECT_ID)
----------------
             990

SQL> select count(object_id) from dba_objects where object_id between 10 and 1000;

COUNT(OBJECT_ID)
----------------
             990

SQL> alter session set optimizer_capture_sql_plan_baselines=false;

Session altered.

SQL> select * from dba_sql_plan_baselines;

no rows selected

這樣做有什麽發現嗎？

Maclean Liu(刘相兵

感觉确实SPM不生效


查下非默认参数


select name,value from v$system_parameter where ISDEFAULT!='TRUE'  order by 1;


并做一下10046 trace


alter session set events '10046 trace name context forever,level 8';

alter session set optimizer_capture_sql_plan_baselines=true;


select count(object_id) from dba_objects where object_id between 10 and 1000;
select count(object_id) from dba_objects where object_id between 10 and 1000;

alter session set optimizer_capture_sql_plan_baselines=false;

jacksan

select name,value from v$system_parameter where ISDEFAULT!='TRUE'  order by 1;

NAME                           VALUE
------------------------------ ----------------------------------------------------------------------------------------------------
audit_file_dest                /u01/app/oracle/admin/sid/adump
audit_trail                    DB
compatible                     11.2.0.0.0
control_files                  /u01/app/oracle/oradata/sid/control01.ctl, /u01/app/oracle/fast_recovery_area/sid/control02.ctl
db_block_size                  8192
db_domain
db_name                        sid
db_recovery_file_dest          /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size     4322230272
diagnostic_dest                /u01/app/oracle
dispatchers                    (PROTOCOL=TCP) (SERVICE=sidXDB)

NAME                           VALUE
------------------------------ ----------------------------------------------------------------------------------------------------
job_queue_processes            1000
memory_max_target              734003200
memory_target                  734003200
nls_language                   ENGLISH
nls_territory                  AMERICA
open_cursors                   300
pga_aggregate_target           0
processes                      150
remote_login_passwordfile      EXCLUSIVE
sga_target                     0
undo_tablespace                UNDOTBS1

10046 trace file 內容請看附件。

Maclean Liu(刘相兵

没有 冲突的参数， 在10046 trace里也没出现记录Sql plan的递归SQL，比较奇怪的问题、


不过回过头来看一下你的是 standard edition 啊， 不是EE Enterprise Edition 啊，标准版貌似没SPM可以用哦， 查一下吧

select * from v$option where parameter='SQL Plan Management';

jacksan

我這裡的是SE1，怪不得SPM沒法用。

Feature Availability for Oracle Database Editions
http://docs.oracle.com/cd/E11882_01/license.112/e10594/editions.htm#CJACGHEB