Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 关于baseline优化的疑问--1楼有txt文档，内容在贴上不方 ...

saup007

133 积分	0 好友	17 主题

发消息

关于baseline优化的疑问--1楼有txt文档，内容在贴上不方便看

1^#

发表于 2012-6-11 12:04:25 | 查看: 9328| 回复: 7

之前发帖子http://t.askmaclean.com/thread-1159-1-1.html
知道scheduler job ：BSLN_MAINTAIN_STATS_JOB是一个维护baseline的job，所以就学了一下11g的特性baseline

内容有点多，全贴出来太乱了，结贴的时候，再贴出来吧，内容全在txt里面。也方便看一下。最后把后缀改为.sql，用Uedit32等文本编辑工具，有高亮部分，容易看一些。

版本：

13:23:18 saup@PRI11G> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE
11.2.0.2.0
Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

$ uname -a
Linux dbaone 2.6.18-238.el5 #1 SMP Sun Dec 19 14:22:44 EST 2010 x86_64 x86_64 x86_64 GNU/Linux

--这么做是因为测试机器，装10g的时候注释做的
$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 4.6 (Tikanga)
#Red Hat Enterprise Linux Server release 5.6 (Tikanga)

关于数据倾斜data skew，绑定变量窥视

SQL> create test2 as select object_id as id,object_name from dba_objects;

SQL> create index test2_idx on test2(id)

11:02:04 saup@PRI11G> select count(1) from test2;

  COUNT(1)
----------
   73173

11:02:25 saup@PRI11G> select max(id) from test2;

MAX(ID)
----------
   83061

saup@PRI11G> update test2 set id=1 where rownum<=70000;

saup@PRI11G> commit;

11:02:19 saup@PRI11G> select count(1) from test2 where id=1;

  COUNT(1)
----------
   70000

saup@PRI11G> exec dbms_stats.gather_table_stats('saup','test2',method_opt => 'for columns id size 254',cascade => true);

--开启自动获取
saup@PRI11G>alter system set OPTIMIZER_CAPTURE_PLAN_BASELINES=true;

saup@PRI11G>alter system flush shared_pool;

[ 本帖最后由 saup007 于 2012-6-11 18:05 编辑 ]

base_saup007_test.txt

20.77 KB, 下载次数: 883

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2012-6-11 12:08:09

请格式化你的输出至少说明你做测试的版本

回复只看该作者道具举报

saup007

3^#

发表于 2012-6-11 13:09:31

回复 2# 的帖子

好的，我编辑好的贴进来就乱了。

吃饭回来，现在编辑。。

版本11.2.0.2

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2012-6-11 22:21:53

完整的问题贴出

之前发帖子http://t.askmaclean.com/thread-1159-1-1.html
知道scheduler job ：BSLN_MAINTAIN_STATS_JOB是一个维护baseline的job，所以就学了一下11g的特性baseline
我整理格式的时候，又做了一次。结果如下：
版本：
13:23:18 saup@PRI11G> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
$ uname -a
Linux dbaone 2.6.18-238.el5 #1 SMP Sun Dec 19 14:22:44 EST 2010 x86_64 x86_64 x86_64 GNU/Linux
--这么做是因为测试机器，装10g的时候注释做的
$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 4.6 (Tikanga)
#Red Hat Enterprise Linux Server release 5.6 (Tikanga)
关于数据倾斜data skew，绑定变量窥视
SQL> create test2 as select object_id as id,object_name from dba_objects;
SQL> create index test2_idx on test2(id)
11:02:04 saup@PRI11G> select count(1) from test2;
COUNT(1)
----------
73173
11:02:25 saup@PRI11G> select max(id) from test2;
MAX(ID)
----------
83061
saup@PRI11G> update test2 set id=1 where rownum<=70000;
saup@PRI11G> commit;
11:02:19 saup@PRI11G> select count(1) from test2 where id=1;
COUNT(1)
----------
70000
saup@PRI11G> exec dbms_stats.gather_table_stats('saup','test2',method_opt => 'for columns id size 254',cascade => true);
--开启自动获取
saup@PRI11G>alter system set OPTIMIZER_CAPTURE_PLAN_BASELINES=true;
saup@PRI11G>alter system flush shared_pool;
1、这个只有一条记录，肯定走索引
saup@PRI11G> var v_id number;
saup@PRI11G> exec :v_id := 83061 ;
--需要执行2次，在v$sql，查询EXECUTIONS为1次
saup@PRI11G> select * from test2 where id=:v_id;
SQL> SELECT sql_handle, plan_name,sql_text, enabled, accepted,FIXED,AUTOPURGE,ORIGIN FROM dba_sql_plan_baselines where sql_text like 'select * from test2%';
SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACCEPTED FIXED AUTOPURGE ORIGIN
--------------------- ------------------------------ ----------------------------------- ------- -------- ----- --------- --------------
SQL_69c431b33cb76beb SQL_PLAN_6mj1jqcybfuzbe2a98ff7 select * from test2 where id=:v_id YES YES NO YES AUTO-CAPTURE
SQL> select * from table(dbms_xplan.display_sql_plan_baseline ('SQL_69c431b33cb76beb','SQL_PLAN_6mj1jqcybfuzbe2a98ff7'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_69c431b33cb76beb
SQL text: select * from test2 where id=:v_id
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6mj1jqcybfuzbe2a98ff7 Plan id: 3802763255
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 1905508226
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 621 | 2 (0)| 0
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 23 | 621 | 2 (0)| 0
|* 2 | INDEX RANGE SCAN | TEST2_IDX | 23 | | 1 (0)| 0
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=TO_NUMBER(:V_ID))
25 rows selected
saup@PRI11G>exec :v_id := 1;
saup@PRI11G>set autotrace traceonly stat;
--此sql也需要查询2次，否则在baseline不会有记录
saup@PRI11G>select * from test2 where id=:v_id;
--上面的一条，为新自动获取的，是走全表扫描的，但accepted为no，baseline不能使用
SQL> SELECT sql_handle, plan_name,sql_text, enabled, accepted,FIXED,AUTOPURGE,ORIGIN FROM dba_sql_plan_baselines where sql_text like 'select * from test2%';
SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACCEPTED FIXED AUTOPURGE ORIGIN
------------------------------ ------------------------------ ----------------------------------- ------- -------- ----- --------- --------------
SQL_69c431b33cb76beb SQL_PLAN_6mj1jqcybfuzb99963deb select * from test2 where id=:v_id YES NO NO YES AUTO-CAPTURE
SQL_69c431b33cb76beb SQL_PLAN_6mj1jqcybfuzbe2a98ff7 select * from test2 where id=:v_id YES YES NO YES AUTO-CAPTURE
产生新的plan_name SQL_PLAN_6mj1jqcybfuzb99963deb ，这个的确是走全表扫描的
SQL> select * from table(dbms_xplan.display_sql_plan_baseline ('SQL_69c431b33cb76beb','SQL_PLAN_6mj1jqcybfuzb99963deb'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_69c431b33cb76beb
SQL text: select * from test2 where id=:v_id
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6mj1jqcybfuzb99963deb Plan id: 2576760299
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 300966803
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 621 | 101 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST2 | 23 | 621 | 101 (1)| 00:00:02 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=TO_NUMBER(:V_ID))
24 rows selected
查看SQL，如果绑定变量窥视的话，同sql_id，应该还有一个不同的plan_has_valeue的，但是没有
--第一次走索引时，每次执行的逻辑读为4
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------- ------------ --------------- ---------- ---------- -- -- -
55d5cn125hxb0 0 1905508226 1 4 Y N Y
EXECUTIONS表示执行2次，为id=1，所以每次执行4942
在id=1执行第二次的时候，结果也如下一样，EXECUTIONS、BG_PER_EX都没有变
SQL> select sql_id,CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,BUFFER_GETS/EXECUTIONS BG_PER_EX,IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S from v$sql where sql_text like 'select * from test2%';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------- ------------ --------------- ---------- ---------- -- -- -
55d5cn125hxb0 0 1905508226 2 4942 Y N Y
但我再执行三次id=1的SQL，就变成这样了，BS=no 没有绑定变量的窥视
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------- ------------ --------------- ---------- ---------- -- -- -
55d5cn125hxb0 0 1905508226 1 9884 N N Y
看结果没有使用绑定变量窥视，IS_BIND_AWARE=N，表明Oracle还没有使用extended cursor sharing。
IS_SHAREABLE=Y，表明这个cursor可以被再次使用，即能够共享
--接下来尝试修改baseline属性，并演化，通过在网上查询，是可以直接修改accepted的，但现在不行，报错如下：
ERROR at line 1:
ORA-38136: invalid attribute name ACCEPTED specified
ORA-06512: at "SYS.DBMS_SPM", line 2477
ORA-06512: at line 4
我现在修改fixed为yes，但演化accepted为yes
13:33:45 sys@PRI11G> DECLARE
ret PLS_INTEGER;
BEGIN
ret := dbms_spm.alter_sql_plan_baseline(sql_handle => '&sql_handle',
plan_name => '&plan_name',
attribute_name => '&attribute',
attribute_value => '&value');
dbms_output.put_line(ret || ' SQL plan baseline(s) dropped');
END;
/
Enter value for sql_handle: SQL_69c431b33cb76beb
old 4: ret := dbms_spm.alter_sql_plan_baseline(sql_handle => '&sql_handle',
new 4: ret := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_69c431b33cb76beb',
Enter value for plan_name: SQL_PLAN_6mj1jqcybfuzb99963deb
old 5: plan_name => '&plan_name',
new 5: plan_name => 'SQL_PLAN_6mj1jqcybfuzb99963deb',
Enter value for attribute: fixed
old 6: attribute_name => '&attribute',
new 6: attribute_name => 'fixed',
Enter value for value: yes
old 7: attribute_value => '&value');
new 7: attribute_value => 'yes');
1 SQL plan baseline(s) dropped
PL/SQL procedure successfully completed.
--可看走full table scan的plan_name=SQL_PLAN_6mj1jqcybfuzb99963deb的fixed为yes，此时还不能走全表扫描，需要演化accepted为yes
SQL> SELECT sql_handle, plan_name,sql_text, enabled, accepted,FIXED,AUTOPURGE,ORIGIN FROM dba_sql_plan_baselines where sql_text like 'select * from test2%';
SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACCEPTED FIXED AUTOPURGE ORIGIN
--------------------- ------------------------------ ----------------------------------- ------- -------- ----- --------- --------------
SQL_69c431b33cb76beb SQL_PLAN_6mj1jqcybfuzb99963deb select * from test2 where id=:v_id YES NO YES YES AUTO-CAPTURE
SQL_69c431b33cb76beb SQL_PLAN_6mj1jqcybfuzbe2a98ff7 select * from test2 where id=:v_id YES YES NO YES AUTO-CAPTURE
执行2次后，EXECUTIONS，只增长1次
SQL> select sql_id,CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,BUFFER_GETS/EXECUTIONS BG_PER_EX,IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S from v$sql where sql_text like 'select * from test2%';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------- ------------ --------------- ---------- ---------- -- -- -
55d5cn125hxb0 0 1905508226 2 9882 N N Y
--演化：
13:41:31 saup@PRI11G> select dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SQL_69c431b33cb76beb',plan_name=>'SQL_PLAN_6mj1jqcybfuzb99963deb',time_limit=>10,verify=>'yes',commit=>'yes') from dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_69C431B33CB76BEB',PLAN_NAME=>
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_69c431b33cb76beb
PLAN_NAME = SQL_PLAN_6mj1jqcybfuzb99963deb
TIME_LIMIT = 10
VERIFY = yes
COMMIT = yes
Plan: SQL_PLAN_6mj1jqcybfuzb99963deb
------------------------------------
Plan was verified: Time used .22 seconds.
Plan passed performance criterion: 1.63 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 70000 70000
Elapsed Time(ms): 14.15 3.231 4.38
CPU Time(ms): 13.997 3.221 4.35
Buffer Gets: 573 351 1.63
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1
SQL> SELECT sql_handle, plan_name,sql_text, enabled, accepted,FIXED,AUTOPURGE,ORIGIN FROM dba_sql_plan_baselines where sql_text like 'select * from test2%';
SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACCEPTED FIXED AUTOPURGE ORIGIN
--------------------- ------------------------------ ----------------------------------- ------- -------- ----- --------- --------------
SQL_69c431b33cb76beb SQL_PLAN_6mj1jqcybfuzb99963deb select * from test2 where id=:v_id YES YES YES YES AUTO-CAPTURE
SQL_69c431b33cb76beb SQL_PLAN_6mj1jqcybfuzbe2a98ff7 select * from test2 where id=:v_id YES YES NO YES AUTO-CAPTURE
--可以看到逻辑读立场少了很多，此是v$sql里查询不到关于test2的SQL，再执行一次可以看到，结果在下面
13:39:50 saup@PRI11G> select * from test2 where id=:v_id;
70000 rows selected.
Statistics
----------------------------------------------------------
21 recursive calls
15 db block gets
5017 consistent gets
0 physical reads
3088 redo size
2359278 bytes sent via SQL*Net to client
51845 bytes received via SQL*Net from client
4668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
70000 rows processed
--第二次执行，Note 显示是使用oracle baseline了
13:44:03 saup@PRI11G> set autotrace traceonly
13:45:04 saup@PRI11G> select * from test2 where id=:v_id;
70000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 300966803
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 621 | 101 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST2 | 23 | 621 | 101 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=TO_NUMBER(:V_ID))
Note
-----
- SQL plan baseline "SQL_PLAN_6mj1jqcybfuzb99963deb" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4999 consistent gets
0 physical reads
0 redo size
2359278 bytes sent via SQL*Net to client
51845 bytes received via SQL*Net from client
4668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
70000 rows processed
SQL> select sql_id,CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,BUFFER_GETS/EXECUTIONS BG_PER_EX,IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S from v$sql where sql_text like 'select * from test2%';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------- ------------ --------------- ---------- ---------- -- -- -
55d5cn125hxb0 0 300966803 1 4999 Y N Y
--修改属性fixed为no，这样二个baseline的状态一致，accepted都为yes，ENABLED为yes，fixed为no
--此时id=1，依然走full table scan，这时oracle会算cost
13:48:49 saup@PRI11G> DECLARE
ret PLS_INTEGER;
attribute_name => '&attribute',
attribute_value => '&value');
dbms_output.put_line(ret || ' SQL plan baseline(s) dropped');
END;
BEGIN
ret := dbms_spm.alter_sql_plan_baseline(sql_handle => '&sql_handle',
plan_name => '&plan_name',
attribute_name => '&attribute',
attribute_value => '&value');
dbms_output.put_line(ret || ' SQL plan baseline(s) dropped');
END;
13:54:53 10 /
Enter value for sql_handle: SQL_69c431b33cb76beb
old 4: ret := dbms_spm.alter_sql_plan_baseline(sql_handle => '&sql_handle',
new 4: ret := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_69c431b33cb76beb',
Enter value for plan_name: SQL_PLAN_6mj1jqcybfuzb99963deb
old 5: plan_name => '&plan_name',
new 5: plan_name => 'SQL_PLAN_6mj1jqcybfuzb99963deb',
Enter value for attribute: fixed
old 6: attribute_name => '&attribute',
new 6: attribute_name => 'fixed',
Enter value for value: no
old 7: attribute_value => '&value');
new 7: attribute_value => 'no');
1 SQL plan baseline(s) dropped
PL/SQL procedure successfully completed.
SQL> SELECT sql_handle, plan_name,sql_text, enabled, accepted,FIXED,AUTOPURGE,ORIGIN FROM dba_sql_plan_baselines where sql_text like 'select * from test2%';
SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACCEPTED FIXED AUTOPURGE ORIGIN
---------------------- ------------------------------ ------------------------------------ ------- -------- ----- --------- --------------
SQL_69c431b33cb76beb SQL_PLAN_6mj1jqcybfuzb99963deb select * from test2 where id=:v_id YES YES NO YES AUTO-CAPTURE
SQL_69c431b33cb76beb SQL_PLAN_6mj1jqcybfuzbe2a98ff7 select * from test2 where id=:v_id YES YES NO YES AUTO-CAPTURE
--再执行一次，此处的执行计划是不准确的，因为autotrace不能正确显示各个child cursor的实际执行计划
13:56:06 saup@PRI11G> select * from test2 where id=:v_id;
70000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1905508226
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 621 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 23 | 621 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST2_IDX | 23 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=TO_NUMBER(:V_ID))
Note
-----
- SQL plan baseline "SQL_PLAN_6mj1jqcybfuzbe2a98ff7" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4999 consistent gets
0 physical reads
0 redo size
2359278 bytes sent via SQL*Net to client
51845 bytes received via SQL*Net from client
4668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
70000 rows processed
--这是修改fixed为no，执行一次的结果
SQL> select sql_id,CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,BUFFER_GETS/EXECUTIONS BG_PER_EX,IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S from v$sql where sql_text like 'select * from test2%';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------- ------------ --------------- ---------- ---------- -- -- -
55d5cn125hxb0 0 300966803 1 4999 Y N Y
--这是修改fixed为no，执行二次的结果
--可知道走index时的plan_hash_value为1905508226，而plan_hash_value=300966803，正是走的全表扫描
SQL> select sql_id,CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,BUFFER_GETS/EXECUTIONS BG_PER_EX,IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S from v$sql where sql_text like 'select * from test2%';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------- ------------ --------------- ---------- ---------- -- -- -
55d5cn125hxb0 0 300966803 2 4999 Y N Y
2个问题其实是1个问题，很纠结，不知道怎么描述，因为11g绑定变量ACS，应该走全表扫描，更好的执行计划啊。
1、开启自动获取：为什么呢？怎么能够走全表扫描。只能通过手动修改fixed属性，再演化，可以使accepted为yes，才能使用吗？
如果这样baseline没办法开启自动获取啊
2、如果手动添加baseline：走hints的索引等等，以后数据发生倾斜，还会生成accepted为no的baseline，如果新生成的执行计划更好，那只能手动来操作吗？
这个链接为11g，哪个版本不清楚，自动获取baseline为true，也可以实现。
http://www.jiagulun.com/thread-106-1-1.html
如果设置baseline自动获取为false，，则这个绑定变量窥视是是成功的，参考：
http://space.itpub.net/15415488/viewspace-621535
alter system set optimizer_capture_sql_plan_baselines=false;
--写的有点多，非常感谢！

复制代码

回复只看该作者道具举报

Maclean Liu(刘相兵

5^#

发表于 2012-6-11 22:36:21

saup007 同学你的钻研精神值得鼓励！

但是请注意　做实验之前　请熟读文档

Interacting with Adaptive Cursor Sharing　
SQL Plan Management (SPM):
– If OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to
TRUE, only the first generated plan is used.
– As a workaround, set this parameter to FALSE, and run
your application until all plans are loaded in the cursor
cache.
– Manually load the cursor cache into the corresponding
plan baseline.

When using the SPM automatic plan capture, the first plan captured for a SQL statement
with bind variables is marked as the corresponding SQL plan baseline. If another plan is
found for that same SQL statement (which maybe the case with Adaptive Cursor Sharing), it
is added to the SQL statements plan history and marked for verification. It will not be used.
So even though Adaptive Cursor Sharing has come up with a new plan based on a new set of
bind values, SPM does not let it be used until the plan has been verified. Thus reverting back
to 10g behavior, only the plan generated based on the first set of bind values will be used by
all subsequent executions of the statement. One possible workaround is to run the system for
some time with automatic plan capture set to FALSE, and after the cursor cache has been
populated with all the plans a SQL statement with bind will have, load the entire plan
directly from the cursor cache into the corresponding SQL plan baseline. By doing this, all
the plans for a single SQL statement are marked as SQL baseline plans by default

简单来说

当　OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE 且 _optimizer_adaptive_cursor_sharing 默认打开时

对于绑定变量的 SQL语句仅仅其第一个执行计划被被加入 SQL plan baseline ；之后由 Adaptive Cursor Sharing产生的执行计划会被加入到 SQL statements plan history 并标记为待验证 verification，而不被使用。但是不通过OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES ，而通过load sql plan from cursor cache 则可以将一个SQL的所有执行计划都加入到SQL baseline plans中。

Oracle 官方推荐在Adaptive Cursor Sharing启用的情况下，不要设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TURE ，而使用 dbms_spm.load_plans_from_cursor_cache 来创建SQL baseline plan

回复只看该作者道具举报