Chinese

之前发帖子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;