Oracle DDL 并行(10G)

1、CREATE INDEX 操作

SQL> conn chf/xifenfei
Connected.
SQL> EXPLAIN PLAN FOR create index ind_t2 on t1(object_name) online nologging PARALLEL(degree 4);
Explained.
SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2130784087
--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT   |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE| IND_T2   |  Q1,01 | PCWP |            |
|   4 |     SORT CREATE INDEX    |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANGE      | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------
SQL> conn chf/xifenfei
Connected.
SQL> ALTER SESSION ENABLE PARALLEL DDL;
Session altered.
SQL> EXPLAIN PLAN FOR create index ind_t2 on t1(object_name) online nologging PARALLEL(degree 4);
Explained.
SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2130784087
--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT   |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE| IND_T2   |  Q1,01 | PCWP |            |
|   4 |     SORT CREATE INDEX    |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANGE      | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------

2、REBUILD INDEX 操作

SQL> conn chf/xifenfei
Connected.
SQL> EXPLAIN PLAN FOR alter  index ind_t2 rebuild online nologging PARALLEL(degree 4);
Explained.
SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2130784087
--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT    |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE| IND_T2   |  Q1,01 | PCWP |            |
|   4 |     SORT CREATE INDEX    |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANGE      | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------
SQL> conn chf/xifenfei
Connected.
SQL> ALTER SESSION ENABLE PARALLEL DDL;
Session altered.
SQL> EXPLAIN PLAN FOR alter  index ind_t2 rebuild online nologging PARALLEL(degree 4);
Explained.
SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2130784087
--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT    |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE| IND_T2   |  Q1,01 | PCWP |            |
|   4 |     SORT CREATE INDEX    |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANGE      | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------

3、CREATE TABLE 操作

SQL> conn chf/xifenfei
Connected.
SQL> EXPLAIN PLAN FOR create table t_6 parallel (degree 4)
  2    as select /*+ parallel (t1,4) */ * from t1 where rownum<5000000;
Explained.
SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2102891290
------------------------------------------------------------------------------
| Id  | Operation                    | Name     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT       |          |        |      |            |
|   1 |  PX COORDINATOR              |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ20001 |  Q2,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT            | T_6      |  Q2,01 | PCWP |            |
|   4 |     BUFFER SORT              |          |  Q2,01 | PCWC |            |
|   5 |      PX RECEIVE              |          |  Q2,01 | PCWP |            |
|   6 |       PX SEND ROUND-ROBIN    | :TQ20000 |        | S->P | RND-ROBIN  |
|   7 |        COUNT STOPKEY         |          |        |      |            |
|   8 |         PX COORDINATOR       |          |        |      |            |
|   9 |          PX SEND QC (RANDOM) | :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|  10 |           COUNT STOPKEY      |          |  Q1,00 | PCWC |            |
|  11 |            PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|  12 |             TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------
SQL> conn chf/xifenfei
Connected.
SQL> ALTER SESSION ENABLE PARALLEL DDL;
Session altered.
SQL> EXPLAIN PLAN FOR create table t_6 parallel (degree 4)
  2    as select /*+ parallel (t1,4) */ * from t1 where rownum<5000000;
Explained.
SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2102891290
------------------------------------------------------------------------------
| Id  | Operation                    | Name     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT       |          |        |      |            |
|   1 |  PX COORDINATOR              |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ20001 |  Q2,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT            | T_6      |  Q2,01 | PCWP |            |
|   4 |     BUFFER SORT              |          |  Q2,01 | PCWC |            |
|   5 |      PX RECEIVE              |          |  Q2,01 | PCWP |            |
|   6 |       PX SEND ROUND-ROBIN    | :TQ20000 |        | S->P | RND-ROBIN  |
|   7 |        COUNT STOPKEY         |          |        |      |            |
|   8 |         PX COORDINATOR       |          |        |      |            |
|   9 |          PX SEND QC (RANDOM) | :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|  10 |           COUNT STOPKEY      |          |  Q1,00 | PCWC |            |
|  11 |            PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|  12 |             TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------

4、说明
1）本次测试的数据库版本为10.2.0.4，Linux环境，其他版本可能有差异
2）关于INDEX的并行操作，并行度可能不会和指定相同（测试为2倍）
3）操作过程中，是否指定ddl 并发，效果相同。建议指定未佳：
ALTER SESSION ENABLE PARALLEL DDL;
ALTER SESSION DISABLE PARALLEL DDL;
4）修改表结构操作，指定并发无效（待寻找方法）

Oracle DML并行

1、UPDATE 操作

SQL> conn chf/xifenfei
Connected.
SQL> EXPLAIN PLAN FOR update /*+ parallel (t1,4) */ t1 set object_name='chengfei';
Explained.
SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 121765358
-----------------------------------------------------------------------
| Id  | Operation             | Name     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |        |      |            |
|   1 |  UPDATE               | T1       |        |      |            |
|   2 |   PX COORDINATOR      |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------
SQL> conn chf/xifenfei
Connected.
SQL> ALTER SESSION ENABLE  PARALLEL DML;
Session altered.
SQL> EXPLAIN PLAN FOR update /*+ parallel (t1,4) */ t1 set object_name='chengfei';
Explained.
SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3308547044
--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | UPDATE STATEMENT         |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    INDEX MAINTENANCE     | T1       |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE       | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   6 |       UPDATE             | T1       |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------

通过执行计划可以看出，只有执行了ALTER SESSION ENABLE PARALLEL DML后，UPDATE操作才真正的实现了并行操作，如果不执行该语句，只是执行了并发查询，并没有实现并发更新操作
2、DELETE 操作

SQL> conn chf/xifenfei
Connected.
SQL> EXPLAIN PLAN FOR delete /*+ parallel (t1,3) */ from t1;
Explained.
SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3718066193
-----------------------------------------------------------------------
| Id  | Operation             | Name     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------
|   0 | DELETE STATEMENT      |          |        |      |            |
|   1 |  DELETE               | T1       |        |      |            |
|   2 |   PX COORDINATOR      |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------
SQL> conn chf/xifenfei
Connected.
SQL> ALTER SESSION ENABLE  PARALLEL DML;
Session altered.
SQL> EXPLAIN PLAN FOR delete /*+ parallel (t1,3) */ from t1;
Explained.
SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2132458150
--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | DELETE STATEMENT         |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    INDEX MAINTENANCE     | T1       |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE       | :TQ10000 |  Q1,00 | P->P | RANGE      |
|   6 |       DELETE             | T1       |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------

试验证明，也需要执行ALTER SESSION ENABLE PARALLEL DML，才能够实现真正的删除并发操作
3、INSERT 操作

SQL> conn chf/xifenfei
Connected.
SQL> EXPLAIN PLAN FOR INSERT  /*+ parallel (t_1,4) */ INTO t_1 SELECT /*+ parallel (t1,4) */* FROM t1;
Explained.
SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2494645258
----------------------------------------------------------------------
| Id  | Operation            | Name     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------
|   0 | INSERT STATEMENT     |          |        |      |            |
|   1 |  PX COORDINATOR      |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
----------------------------------------------------------------------
SQL> conn chf/xifenfei
Connected.
SQL> ALTER SESSION ENABLE  PARALLEL DML;
Session altered.
SQL> EXPLAIN PLAN FOR INSERT  /*+ parallel (t_1,4) */ INTO t_1 SELECT /*+ parallel (t1,4) */* FROM t1;
Explained.
SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 783041698
-------------------------------------------------------------------------
| Id  | Operation               | Name     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT        |          |        |      |            |
|   1 |  PX COORDINATOR         |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT       | T_1      |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   5 |      PX SEND ROUND-ROBIN| :TQ10000 |  Q1,00 | P->P | RND-ROBIN  |
|   6 |       PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------

通过这个执行计划可以发现，数据是被使用APPEND方式插入到表中，如果需要常规方式插入，需要加上noappend提示，同样设置了session 并行dml才能够实现真正意义上的插入并发操作
4、总结
通过上面的试验可以得出，如果要DML实现真正意义上的并发，在开始执行需要并发语句前，需要执行开启session并发
ALTER SESSION ENABLE PARALLEL DML;
在执行完语句后，需要执行关闭session并发
ALTER SESSION DISABLE PARALLEL DML;

人在社会混，一定要有些心计

01．犯了错误就该诚实地认错——狡辩、诿过只会害了自己。
02．朋友之间要保持距离——这样的友谊才能长久。
03．钱追人，人追健康——有了健康，还怕挣不到钱么？
04．别轻易转行——转行的风险很高，最好不要轻率为之。
05．适度地抬高身价——在就业市场中，人也是一种商品。
06．把敬业变成习惯——从长期看是为了自己。
07．运用累积法壮大资产——不求快，不求多，不中断。
08．忍一时，争千秋形势比人强时，必须忍。
09．与其你死我活，不如你活我也活——这就是双赢，是良性竞争。
10．以“播种”的心态经营人际关系——种子播得越早越坚韧。
11．做事切勿率性而为——率性而为只会害了自己。
12．遇到魔鬼型的主管时——接受他的磨练可让你的性格越来越坚韧。
13．不要当众和主管吵架——那会让你无路可走，只有走人。
14．向不同行业的人学习新知识——记住，要用请教的态度。
15．所有的困难都是好事——这是老天爷在磨练你，目的是把重任交给你。
16．用吃亏就是占便宜的心态做人做事——那样你就可以迅速长大。
17．不要在失意者面前谈论你的得意——那样伤害你的人际关系。
18．不要小看守时这件事——守时是对别人的尊重。
19．用时间来看人——时间是检验大师。
20．用打听来看人——把获得的信息汇总，就可以了解那个人。
21．建立一个朋友档案——以免人到用时方恨少。
22．扩大交友圈——主动出击，不要等别人上门找你。
23．保持交友的弹性——敌人也可以变成朋友。
24．要交喜欢“修理”你的朋友——这种朋友是你的人生导师。
25．毛遂自荐，好处多多——让别人看到你，知道你的存在，知道你的能力。
26．小心突然升温的友情——对待这种友情的正确态度是：不推不迎，礼尚往来。
27．做老二，不做老大——老大没当好，容易变成老三老四。
28．做人要中规中矩——目的是赢得他人的尊重和信赖。
29．用“物质利”换取“人情利”——“物质利”是一时的，“人情利”是长远的。
30．不要独想荣耀——今天独享荣耀，明天可能就可能独吞苦果。
31．找一位“衣食父母”——那个人可减少你摸索的时间。
32．找一位对手来跟——跟住他，最终超越他。
33．用耐心把冷板凳坐热——冷板凳都坐过了，还有什么好怕的呢？
34．套用别人的成功模式——别人的成功模式可成为一种指引，让你有方向可循。
35．偷偷地把自己当成老板或主管——逐渐培养自己当老板或主管的能力。
36．不要满足于眼前的小成就——问问自己：我这辈子就这样了吗？
37．让自己尽快成为本行的专家——只要功夫深，铁杵磨成针。
38．不要有怀才不遇的想法——怀才不遇多半是自己造成的。
39．跌倒了，一定要爬起来——不爬起来，别人会看不起你，你自己也会失去机会。
40．不要为失败找借口——应该直面失败，并迅速找出失败的原因。
41．改变环境或改变自己——与其改变环境，不如改变自己。
42．不打没有把握的仗——生命是经不起一次次的浪费的。
43．把反省自己当成每日的功课——因为你不是完美的，会说错话，也会做错事。
44．碰到低调时，自己鼓励自己——千万别蚯螅
45．依靠别人的智能做事——一个人的能力是有限的。
46．时时都要有危机意识——别以为你的命好运气也好。
47．主角配角都能演，台上台下都自在——面对人生，要练就能屈能伸的个性。
48．要学会控制自己的情绪——这是成熟的一个最重要的标志。
49．做乌龟，不做兔子——有兔子资质的人容易骄傲，而骄傲会成为成功路上的绊脚石。
50．先做小事，先赚小钱——为做大事赚大钱积累经验。
51．别让过去的失败捆住你的手脚，否则永远难成大事。
52．像蟑螂一样活着——人如果有蟑螂的韧性，还有什么日子不能过呢？
53．靠实力，不靠派系——派系不是永远的，实力才是你能依靠一生的东西。
54．自己发光，不要等别人把你磨光——谁有空、有心情去认真地“磨”你呢？
55．逢山开路，遇水架桥——要学会投其所好。
56．永远跑在下属的前面——权力领导、情义领导都不如才能领导。
57．“新官上任三把火”有必要——要大声地告诉别人：我来了。
58．只要5毛钱，不要1块钱——拿一块钱的机会只有一次。
59．放下架子，路就会越走越宽——架子只会捆住你的手脚。
60．话别说得太圆满——目的是给意外留有余地，以免下不了台。
61．尽快成为你所在的那一行的专家——只要努力就会成功。
62．多赞美别人——不用花钱，就能使人快乐，何乐而不为呢？
63．尊重别人的领土范围——别因为疏忽引发不必要的麻烦。
64．不要轻易吐露你的失意——以免被人认为你软弱无能。
65．人际关系的原则是：有舍才有得——你满足了对方，对方才会满足你。
66．不要忽略面子问题——不给面子的行为最容易引起是非。
67．妥善处理与小人的关系——不要依附他，也不要得罪他。
68．最好不要挡住别人的财路——与其挡人财路，不如自己别辟财路。
69．用低姿态化解别人的嫉妒——嫉妒是烈火，会烧毁一个人。
70．做人做事不必面面俱到——总会有人不满意你。
71．认识并运用人性中的自私——要想办法用别人的自私为自己谋利。
72．顺着毛摸，他就会听你的——脾气再大、城府再深、个性再强的人也吃不消这招。
73．以积极的作为推动否极泰来——坚持住，努力向上，积累能力。
74．以戒备谨慎的心态延缓盛极而衰的时间——很多失败都是在兴盛时埋下的伏笔。
75．万事俱备，花自然会开——努力就行了，花什么时候开由老天爷安排。

Oracle直方图理解与实验

一.Oracle中直方图的作用
直方图是一种对被管理对象某一方面质量进行管理的描述工具，那么在Oracle中自然它也是对Oracle中某个对象质量的描述工具，这个对象就是Oracle中最重要的东西——“数据”。
在Oracle中直方图是一种对数据分布质量情况进行描述的工具。它会按照某一列不同值出现数量多少，以及出现的频率高低来绘制数据的分布情况，以便能够指导优化器根据数据的分布做出正确的选择。在某些情况下，表的列中的数值分布将会影响优化器使用索引还是执行全表扫描的决策。当 where 子句的值具有不成比例数量的数值时，将出现这种情况，使得全表扫描比索引访问的成本更低。这种情况下如果where 子句的过滤谓词列之上上有一个合理的正确的直方图，将会对优化器做出正确的选择发挥巨大的作用，使得SQL语句执行成本最低从而提升性能。
二.Oracle中使用直方图的场合
在分析表或索引时，直方图用于记录数据的分布。通过获得该信息，基于成本的优 化器就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。直方图的使用不受索引的限制，可以在表的任何列上构建直方图。
构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划：例如，如果一到两个值构成了表中的大部分数据(数据偏斜)，相关的索引就可能无法帮助减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时应该根据WHERE子句中的值返回表中80％的记录。
通常情况下在以下场合中建议使用直方图：
（1）、当Where子句引用了列值分布存在明显偏差的列时：当这种偏差相当明显时，以至于 WHERE 子句中的值将会使优化器选择不同的执行计划。这时应该使用直方图来帮助优化器来修正执行路径。（注意：如果查询不引用该列，则创建直方图没有意义。这种错误很常见，许多 DBA 会在偏差列上创建柱状图，即使没有任何查询引用该列。）
（2）、当列值导致不正确的判断时：这种情况通常会发生在多表连接时，例如，假设我们有一个五项的表联接，其结果集只有 10 行。Oracle 将会以一种使第一个联接的结果集（集合基数）尽可能小的方式将表联接起来。通过在中间结果集中携带更少的负载，查询将会运行得更快。为了使中间结果最小化，优化器尝试在 SQL 执行的分析阶段评估每个结果集的集合基数。在偏差的列上拥有直方图将会极大地帮助优化器作出正确的决策。如优化器对中间结果集的大小作出不正确的判断，它可能会选择一种未达到最优化的表联接方法。因此向该列添加直方图经常会向优化器提供使用最佳联接方法所需的信息。
三.Oracle直方图的种类
Oracle利用直方图来提高非均匀数据分布的选择率和技术的计算精度。但是实际上Oracle会采用另种不同的策略来生成直方图：其中一种是针对包含很少不同值的数据集；另一种是针对包含很多不同的数据集。Oracle会针对第一种情况生成频率直方图，针对第二种情况生成高度均衡直方图。通常情况下当BUCTET < 表的NUM_DISTINCT值得到的是HEIGHT BALANCED（高度平衡）直方图，而当BUCTET > 表的NUM_DISTINCT值的时候得到的是FREQUENCY（频率）直方图
四、试验证明（有直方图执行计划更加准确）

SQL> create table t_xff
  2  as select * from dba_objects;
Table created
SQL> create index ind_t_xff on t_xff(object_id) online nologging;
Index created
SQL> SELECT MAX(object_id),MIN(object_id) FROM t_xff;
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         76800              2
SQL>  UPDATE t_xff SET object_id=1000 WHERE object_id>100 AND object_id<76000;
72965 rows updated
SQL> commit;
Commit complete
SQL>
SQL>   BEGIN
  2      DBMS_stats.gather_table_stats(cascade => TRUE,degree => 2
  3      ,estimate_percent => 100,force => TRUE,ownname => USER,tabname => 'T_XFF');
  4   END;
  5  /
PL/SQL procedure successfully completed
SQL> SELECT * FROM user_histograms WHERE table_name='T_XFF' AND column_name='OBJECT_ID';
TABLE COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
----- ---------- --------------- -------------- ----------
T_XFF OBJECT_ID            1          2
T_XFF OBJECT_ID            2          3
……
T_XFF OBJECT_ID            73205          76789
T_XFF OBJECT_ID            73206          76800
SQL>   SELECT COLUMN_NAME,HISTOGRAM FROM USER_TAB_COLS WHERE TABLE_NAME='T_XFF' AND column_name='OBJECT_ID';
COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
OBJECT_ID                      FREQUENCY
--在gather_table_stats方法中，默认的method_opt值为：FOR ALL COLUMNS SIZE AUTO，所以也是会收集直方图的统计信息（和oracle版本相关）
--注意：ENDPOINT_NUMBER ，ENDPOINT_VALUE 的分布情况
SQL> set autot trace exp stat
SQL> select object_name from t_xff where object_id=100;
执行计划
----------------------------------------------------------
Plan hash value: 2950241517
--------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    29 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XFF     |     1 |    29 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_XFF |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=100)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        432  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> select object_name from t_xff where object_id=1000;
已选择72965行。
执行计划
----------------------------------------------------------
Plan hash value: 667573674
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       | 72965 |  2066K|   292   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T_XFF | 72965 |  2066K|   292   (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=1000)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5833  consistent gets
         16  physical reads
          0  redo size
    2487154  bytes sent via SQL*Net to client
      53920  bytes received via SQL*Net from client
       4866  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72965  rows processed
--观察发现，因为有直方图的存在，oracle会只能的选择使用index或者全表扫描
SQL>   BEGIN
  2      DBMS_stats.gather_table_stats(cascade => TRUE,degree => 2
  3      ,estimate_percent => 100,force => TRUE,ownname => USER,tabname => 'T_XFF',method_opt => 'FOR ALL COLUMNS SIZE 1');
  4   END;
  5  /
PL/SQL procedure successfully completed
--删除直方图，设置method_opt：FOR ALL COLUMNS SIZE 1即可
SQL>  SELECT * FROM user_histograms WHERE table_name='T_XFF' AND column_name='OBJECT_ID';
TABLE COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
----- ---------- --------------- -------------- ----------
T_XFF OBJECT_ID                0              2
T_XFF OBJECT_ID                1          76800
SQL> SELECT COLUMN_NAME,HISTOGRAM FROM USER_TAB_COLS WHERE TABLE_NAME='T_XFF' AND column_name='OBJECT_ID';
COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
OBJECT_ID                      NONE
SQL> select object_name from t_xff where object_id=100;
执行计划
----------------------------------------------------------
Plan hash value: 2950241517
--------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   303 |  8787 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XFF     |   303 |  8787 |     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_XFF |   303 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=100)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        432  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> select object_name from t_xff where object_id=1000;
已选择72965行。
执行计划
----------------------------------------------------------
Plan hash value: 2950241517
--------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   303 |  8787 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XFF     |   303 |  8787 |     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_XFF |   303 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=1000)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5833  consistent gets
          0  physical reads
          0  redo size
    2487154  bytes sent via SQL*Net to client
      53919  bytes received via SQL*Net from client
       4866  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72965  rows processed
--没有了直方图，oracle傻瓜的选择也使用index
--虽然两次逻辑读一样，但是全表扫描涉及到一次可以读多块，但是index扫描一次只能读一个数据块

DBMS_STATS比较复杂参数

method_opt
Accepts:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]…]
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
– integer : Number of histogram buckets. Must be in the range [1,254].
– REPEAT : Collects histograms only on the columns that already have histograms.
– AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
– SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
举例说明：
method_opt => ‘FOR COLUMNS size 254 object_id’ 收集objct_id列直方图
method_opt => ‘FOR COLUMNS size 1 object_id’ 删除object_id列直方图
method_opt => ‘for all columns size repeat’ 重新分析现有直方图
method_opt => ‘for all columns size auto’ oracle决定收集哪些列的直方图（需要设置table monitoring）
method_opt => ‘for all columns size skewonly’ oracle分析所有列的分布情况，生成直方图
method_opt => ‘FOR COLUMNS object_id size SKEWONLY’ 收集object_id列分布情况，生成直方图
method_opt => ‘FOR all INDEXED COLUMNS size SKEWONLY’ 收集index列分布情况，并生成直方图
granularity
Granularity of statistics to collect (only pertinent if the table is partitioned).
‘ALL’ – gathers all (subpartition, partition, and global) statistics
‘AUTO’- determines the granularity based on the partitioning type. This is the default value.
‘DEFAULT’ – gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the ‘GLOBAL AND PARTITION’ for this functionality. Note that the default value is now ‘AUTO’.
‘GLOBAL’ – gathers global statistics
‘GLOBAL AND PARTITION’ – gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.
‘PARTITION ‘- gathers partition-level statistics
‘SUBPARTITION’ – gathers subpartition-level statistics.
options
Further specification of which objects to gather statistics for:
GATHER: Gathers statistics on all objects in the schema.
GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.
GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale.
GATHER EMPTY: Gathers statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics.
LIST AUTO: Returns a list of objects to be processed with GATHER AUTO.
LIST STALE: Returns list of stale objects as determined by looking at the *_tab_modifications views.
LIST EMPTY: Returns list of objects which currently have no statistics.

BITMAP CONVERSION FROM ROWIDS

在有些执行计划中，可以会看到 BITMAP CONVERSION FROM ROWIDS这样的东东，也许你会感觉奇怪，我没有使用位图索引怎么出现了bitmap。
我通过一个sql和大家分析下原因：
sql语句为：

SELECT *
  FROM (SELECT a.*, ROWNUM rn
          FROM (  SELECT module_no, MAX (id) AS id
                    FROM vasoa.tab_moa_historypage t
                   WHERE t.company_id = :1 AND t.user_id = :2 AND t.TYPE = :3
                GROUP BY module_no
                ORDER BY id DESC) a
         WHERE ROWNUM <= :4) b
WHERE b.rn >= :5

执行计划为：

表结构为：

目前索引情况：

通过观察执行计划的图片可以发现，使用了 BITMAP CONVERSION FROM ROWIDS，其目的是：.oracle将btree索引中获得的rowid信息通过BITMAP CONVERSION FROM ROWIDS的步骤转换成bitmap进行匹配,然后匹配完成后通过BITMAP CONVERSION TO ROWIDS再转换出rowid获得数据或者回表获得数据。这种想象出现的原因为：oracle的cbo是根据cost来决定大小来选择合适的执行计划，当它计算获得通过bitmap的方式执行的时候cost会更小，它就会选择使用这样的执行计划。一般出现这样的情况，都是因为对表建立的不适当的index导致，特别是对表中的唯一度不高的列建立了index，然后oracle就有可能选择两个这样的列转为为bitmap来执行。根据oracle的执行计划，肯定是cost最小的，但是它很多时候忽略了一致性读等其他条件，导致这个执行计划并非像oracle想象的那样最优，因为把btree index转为为bitmap index执行，需要消耗更多的cpu，特别是在cpu比较紧张的系统中，所以这样的情况如果发生在oltp系统中，一般都需要解决。
本例的解决办法是删除唯一度低的index，建立组合index。分别删除COMPANY_ID与USER_ID上的index，建立COMPANY_ID和USER_ID的组合index，执行计划如下：

bbed 修改datafile header

SQL> startup
ORACLE instance started.
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/opt/oracle/oradata/xifenfei/xff01.dbf'
提示数据文件2需要恢复，首选是recover datafile 2;，如果失败，可以考虑bbed修改scn的办法
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;
     FILE# TO_CHAR(CHECK
---------- -------------
         1  107374278108
         2  107374278108
         3  107374278108
         4  107374278108
         5  107374278108
         6  107374278108
         7  107374278108
         8  107374278108
         9  107374278108
        10  107374278108
        11  107374278108
11 rows selected.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
         2 ONLINE   107374277136
[oracle@localhost tmp]$ bbed parfile=/tmp/parfile.cnf
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Mon Aug 22 06:05:34 2011
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /opt/oracle/oradata/xifenfei/system01.dbf                         1280
     2  /opt/oracle/oradata/xifenfei/xff01.dbf                            1280
BBED> set dba 1,1
        DBA             0x00400001 (4194305 1,1)
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @140
   struct kcvcpscn, 8 bytes                 @140
*    ub4 kscnbas                           @140      0x000175dc
      ub2 kscnwrp                           @144      0x0019
* ub4 kcvcptim                             @148      0x2d49fbbb
   ub2 kcvcpthr                             @152      0x0001
   union u, 12 bytes                        @156
      struct kcvcprba, 12 bytes             @156
         ub4 kcrbaseq                       @156      0x00000005
         ub4 kcrbabno                       @160      0x00008e05
         ub2 kcrbabof                       @164      0x0010
      struct kcvcptr, 12 bytes              @156
         struct kcrtrscn, 8 bytes           @156
            ub4 kscnbas                     @156      0x00000005
            ub2 kscnwrp                     @160      0x8e05
         ub4 kcrtrtim                       @164      0x09110010
   ub1 kcvcpetb[0]                          @168      0x02
   ub1 kcvcpetb[1]                          @169      0x00
   ub1 kcvcpetb[2]                          @170      0x00
   ub1 kcvcpetb[3]                          @171      0x00
   ub1 kcvcpetb[4]                          @172      0x00
   ub1 kcvcpetb[5]                          @173      0x00
   ub1 kcvcpetb[6]                          @174      0x00
   ub1 kcvcpetb[7]                          @175      0x00
BBED> p kcvfhcpc
*ub4 kcvfhcpc                                @176      0x0000007a
BBED> p kcvfhccc
*ub4 kcvfhccc                                @184      0x00000079
星号表示使用bbed修改datafile header scn需要考虑的地方
SQL> select to_char(to_number('19000175dc','xxxxxxxxxxxx'),'999999999999') from dual;
TO_CHAR(TO_NU
-------------
 107374278108
证实system01.dbf的scn为107374278108和v$datafile查询到的一致
BBED> set dba 2,1
        DBA             0x00800001 (8388609 2,1)
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @140
   struct kcvcpscn, 8 bytes                 @140
      ub4 kscnbas                           @140      0x00017210
      ub2 kscnwrp                           @144      0x0019
   ub4 kcvcptim                             @148      0x2d49fa27
   ub2 kcvcpthr                             @152      0x0001
   union u, 12 bytes                        @156
      struct kcvcprba, 12 bytes             @156
         ub4 kcrbaseq                       @156      0x00000005
         ub4 kcrbabno                       @160      0x00006f18
         ub2 kcrbabof                       @164      0x0010
      struct kcvcptr, 12 bytes              @156
         struct kcrtrscn, 8 bytes           @156
            ub4 kscnbas                     @156      0x00000005
            ub2 kscnwrp                     @160      0x6f18
         ub4 kcrtrtim                       @164      0x09110010
   ub1 kcvcpetb[0]                          @168      0x02
   ub1 kcvcpetb[1]                          @169      0x00
   ub1 kcvcpetb[2]                          @170      0x00
   ub1 kcvcpetb[3]                          @171      0x00
   ub1 kcvcpetb[4]                          @172      0x00
   ub1 kcvcpetb[5]                          @173      0x00
   ub1 kcvcpetb[6]                          @174      0x00
   ub1 kcvcpetb[7]                          @175      0x00
BBED> p kcvfhcpc
ub4 kcvfhcpc                                @176      0x00000034
BBED> p kcvfhccc
ub4 kcvfhccc                                @184      0x00000033
SQL> select to_char(to_number('1900017210','xxxxxxxxxxxx'),'999999999999') from dual;
TO_CHAR(TO_NU
-------------
 107374277136
和v$recover_file视图中查询出来一致
BBED> set dba 2,1 offset 140
        DBA             0x00800001 (8388609 2,1)
        OFFSET          140
BBED> show
        FILE#           2
        BLOCK#          1
        OFFSET          140
        DBA             0x00800001 (8388609 2,1)
        FILENAME        /opt/oracle/oradata/xifenfei/xff01.dbf
        BIFILE          bifile.bbd
        LISTFILE        /tmp/list
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           32
        LOGFILE         log.bbd
        SPOOL           No
BBED> m /x dc750100
 File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
 Block: 1                Offsets:  140 to  171           Dba:0x00800001
------------------------------------------------------------------------
 dc750100 19000000 27fa492d 01000000 05000000 186f0000 10001109 02000000
 <32 bytes per line>
BBED> m /x bbfb492d offset 158
 File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
 Block: 1                Offsets:  158 to  189           Dba:0x00800001
------------------------------------------------------------------------
 bbfb492d 00001000 11090200 00000000 00003400 00004cc0 492d3300 00000000
 <32 bytes per line>
BBED> m /x 7a000000 offset 176
 File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
 Block: 1                Offsets:  176 to  207           Dba:0x00800001
------------------------------------------------------------------------
 7a000000 4cc0492d 33000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> m /x 79000000 offset 184
 File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
 Block: 1                Offsets:  184 to  215           Dba:0x00800001
------------------------------------------------------------------------
 79000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
注意：p打印出来的值和m修改的值可能不是完全一致（和cpu的计算类型有关，常用的intel cpu中是倒序存储），可以选择先dump正确的相关值，然后m修改
如：p打印出来的值为：80084d1f时，m修改时要为： 1f4d0880
BBED> sum apply dba 2,1
Check value for File 2, Block 1:
current = 0x7ece, required = 0x7ece
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: '/opt/oracle/oradata/xifenfei/xff01.dbf'
ORA-01207: file is more recent than controlfile - old controlfile
提示控制文件太老，需要重建控制文件
SQL> shutdown abort
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XIFENFEI" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/opt/oracle/oradata/xifenfei/redo01.log'  SIZE 100M,
  GROUP 2 '/opt/oracle/oradata/xifenfei/redo02.log'  SIZE 100M,
  GROUP 3 '/opt/oracle/oradata/xifenfei/redo03.log'  SIZE 100M
DATAFILE
  '/opt/oracle/oradata/xifenfei/system01.dbf',
  '/opt/oracle/oradata/xifenfei/xff01.dbf',
  '/opt/oracle/oradata/xifenfei/cwmlite01.dbf',
  '/opt/oracle/oradata/xifenfei/drsys01.dbf',
  '/opt/oracle/oradata/xifenfei/example01.dbf',
  '/opt/oracle/oradata/xifenfei/indx01.dbf',
  '/opt/oracle/oradata/xifenfei/odm01.dbf',
  '/opt/oracle/oradata/xifenfei/tools01.dbf',
  '/opt/oracle/oradata/xifenfei/users01.dbf',
  '/opt/oracle/oradata/xifenfei/xdb01.dbf',
  '/opt/oracle/oradata/xifenfei/UNDOTBS01.dbf'
CHARACTER SET ZHS16GBK
;
ORACLE instance started.
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20
Control file created.
SQL> alter database open;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/xifenfei/temp01.dbf'
  2       SIZE 32505856  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
重建控制文件后，需要添加临时文件

补充说明：
1、datafile 的file header 存储在第一个block里
2、Oracle considers four attributes of this data structure when determining if a datafile is sync with the other data files of the database:（不同oracle版本offset可能不同）
（1）kscnbas (at offset 140) – SCN of last change to the datafile.
（2）kcvcptim (at offset 148) -Time of the last change to the datafile.
（3）kcvfhcpc (at offset 176) – Checkpoint count.
（4）kcvfhccc (at offset 184) – Unknown, but is always 1 less than thecheckpoint point count.
Oracle有4个属性来判断datafile 是否和其他的datafile 一致，如果都一致，可以正常操作，如果不一致，那么会报ORA-01113错误

通过bbed查看数据块结构

BBED> map /v
 File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
 Block: 530                                   Dba:0x00800212
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
    ub1 type_kcbh                           @0
    ub1 frmt_kcbh                           @1
    ub1 spare1_kcbh                         @2
    ub1 spare2_kcbh                         @3
    ub4 rdba_kcbh                           @4
    ub4 bas_kcbh                            @8
    ub2 wrp_kcbh                            @12
    ub1 seq_kcbh                            @14
    ub1 flg_kcbh                            @15
    ub2 chkval_kcbh                         @16
    ub2 spare3_kcbh                         @18
 struct ktbbh, 72 bytes                     @20
    ub1 ktbbhtyp                            @20
    union ktbbhsid, 4 bytes                 @24
    struct ktbbhcsc, 8 bytes                @28
    b2 ktbbhict                             @36
    ub1 ktbbhflg                            @38
    ub1 ktbbhfsl                            @39
    ub4 ktbbhfnx                            @40
    struct ktbbhitl[2], 48 bytes            @44
 struct kdbh, 14 bytes                      @92
    ub1 kdbhflag                            @92
    b1 kdbhntab                             @93
    b2 kdbhnrow                             @94
    sb2 kdbhfrre                            @96
    sb2 kdbhfsbo                            @98
    sb2 kdbhfseo                            @100
    b2 kdbhavsp                             @102
    b2 kdbhtosp                             @104
 struct kdbt[1], 4 bytes                    @106
    b2 kdbtoffs                             @106
    b2 kdbtnrow                             @108
 sb2 kdbr[101]                              @110
 ub1 freespace[4270]                        @312
 ub1 rowdata[3606]                          @4582
 ub4 tailchk                                @8188
BBED>
////////////////////////////////////////////////////////////////////////////////////////
//////////// Block Header Structure,  20 bytes//////////////////////////////////////////
BBED> p kcbh
struct kcbh, 20 bytes                       @0
    ub1 type_kcbh                           @0    -- Block Type
                                                  -- 01 - Undo segment header
                                                  -- 02 - Undo data block
                                                  -- 03 - Save undo header
                                                  -- 04 - Save undo data block
                                                  -- 05 - Data segment header
                                                  -- 06 - Trans data, KTB managed data block(with ITL)
                                                  -- 07 - Temp table data block (no ITL)
                                                  -- 08 - Sort key
                                                  -- 09 - Sort Run
                                                  -- 10 - Segment free list block
                                                  -- 11 - Data file header
    ub1 frmt_kcbh                           @1    -- Block Format 1=Oracle7, 2=Oracle8+
    ub1 spare1_kcbh                         @2    -- Not used, filler field
    ub1 spare2_kcbh                         @3    -- Not used, filler field
    ub4 rdba_kcbh                           @4    -- RDBA (4 bytes) - Relative Data Block Address
    ub4 bas_kcbh                            @8    -- SCN Base (4 bytes)
    ub2 wrp_kcbh                            @12   -- SCN Wrap (2 bytes)
    ub1 seq_kcbh                            @14   -- Sequence Number, incremented for every change made to the block at the same SCN
    ub1 flg_kcbh                            @15   -- Flag:
                                                  -- 0x01 New Block
                                                  -- 0x02 Delayed Logging Chang advanced SCN/seq
                                                  -- 0x04 Check value saved - block XOR's to Zero
                                                  -- 0x08 Temporary block
    ub2 chkval_kcbh                         @16   -- Optional block checksum (if DB_BLOCK_CHECKSUM=TRUE)
    ub2 spare3_kcbh                         @18   -- Not used, filler field
 /////////////////////////////////////////////////////////////////////////////////////////
/////////Transaction Fixed Header Structure, 72 Bytes////////////////////////////////////
BBED> p ktbbh
 struct ktbbh, 72 bytes                     @20
    ub1 ktbbhtyp                            @20     -- Block type (1=DATA, 2=INDEX)
    union ktbbhsid, 4 bytes                 @24     -- Segment/Object ID
    struct ktbbhcsc, 8 bytes                @28     -- SCN at last block cleanout
    b2 ktbbhict                             @36     -- Number of ITL slots
    ub1 ktbbhflg                            @38     -- 0=on the freelist
    ub1 ktbbhfsl                            @39     -- ITL TX freelist slot
    ub4 ktbbhfnx                            @40     -- DBA of next block on the freelist
    struct ktbbhitl[2], 48 bytes            @44     -- ITL list index, each ITL takes up 24 bytes
//////////////////////////////////////////////////////////////////////////////////////////
///////////////Data Header Structure, 14 bytes////////////////////////////////////////////
BBED> p kdbh
 struct kdbh, 14 bytes                      @100
    ub1 kdbhflag                            @100    -- N=pctfree hit(clusters)
                                                    -- F=do not put on freelist
                                                    -- K=flushable cluster keys
    b1 kdbhntab                             @101    -- Number of tables (>1 in clusters)
    b2 kdbhnrow                             @102    -- Number of rows (2 bytes)
    sb2 kdbhfrre                            @104    -- First free row entry index; -1=you have to add one
    sb2 kdbhfsbo                            @106    -- Freespace begin offset
    sb2 kdbhfseo                            @108    -- Freespace end offset
    b2 kdbhavsp                             @110    -- Available space in the block
    b2 kdbhtosp                             @112    -- Total available space when all TXs commit
////////////////////////////////////////////////////////////////////////////////////////
/////////////////////Table Directory Entry Structure, 4 bytes///////////////////////////
BBED> p kdbt
 struct kdbt[1], 4 bytes                    @114
    b2 kdbtoffs                             @114
    b2 kdbtnrow                             @116
////////////////////////////////////////////////////////////////////////////////////////
////////////////// Row Directory ///////////////////////////////////////////////////////
BBED> p kdbr[100]
 sb2 kdbr[100]                                @310
////////////////////////////////////////////////////////////////////////////////////////
///////////////// Free Space ///////////////////////////////////////////////////////////
BBED> p freespace[4269]
 ub1 freespace[4269]                        @4581
///////////////////////////////////////////////////////////////////////////////////////
/////////////////////Row Data//////////////////////////////////////////////////////////
BBED> p rowdata[3605]
ub1 rowdata[3605]                           @8187     0x00
//////////////////////////////////////////////////////////////////////////////////////
/////////////////////Block Tail Check, 4 bytes////////////////////////////////////////
BBED> p tailchk
ub4 tailchk                                 @8188     0x24500601

说明事宜：
1、tailchk=Lower order two bytes of SCN Base(bas_kcbh) + Block Type(type_kcbh) + SCN Seq(seq_kcbh)
2、块的scn为：scn=wrp_kcbh+bas_kcbh
求scn语句：select to_char(to_number(‘scn’,’xxxxxxxxxx’),’999999999999′) from dual;
3、dba求文件号，块号为：

set serveroutput on
declare
   p_dba   VARCHAR2 (255) :='0x00800212';
   l_str   VARCHAR2 (255) DEFAULT NULL;
BEGIN
    l_str :=
         'datafile# is:'
      || DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'))
      || chr(10)||'datablock is:'
      || DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'));
   dbms_output.put_line(l_str);
END;

bbed恢复删除数据

一、创建模拟环境
SQL> create table hr.xifenfei (id number,name varchar2(20)) tablespace xff;
Table created.
SQL> insert into hr.xifenfei values(1,’xifenfei’);
1 row created.
SQL> insert into hr.xifenfei values(2,’xff’);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from hr.xifenfei;
ID NAME
———- ——————–
1 xifenfei
2 xff
SQL> select rowid,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from hr.xifenfei;
ROWID REL_FNO BLOCKNO ROWNO
—————— ———- ———- ———-
AAAHy3AACAAAAISAAA 2 530 0
AAAHy3AACAAAAISAAB 2 530 1
查询file#，block，后面恢复要用
SQL> delete from hr.xifenfei where id=2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from hr.xifenfei;
ID NAME
———- ——————–
1 xifenfei
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
二、bbed恢复删除数据
[oracle@localhost ~]$ bbed parfile=/tmp/parfile.cnf
Password:
BBED: Release 2.0.0.0.0 – Limited Production on Mon Aug 22 01:52:52 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> show all
FILE# 2
BLOCK# 1
OFFSET 0
DBA 0x00800001 (8388609 2,1)
FILENAME /opt/oracle/oradata/xifenfei/xff01.dbf
BIFILE bifile.bbd
LISTFILE /tmp/list
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> set dba 2,530
DBA 0x00800212 (8389138 2,530)
BBED> find /c xff
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8170 to 8191 Dba:0x00800212
————————————————————————
7866662c 000202c1 02087869 66656e66 65690106 80e2
<32 bytes per line>
BBED> dump /v
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8170 to 8191 Dba:0x00800212
——————————————————-
7866662c 000202c1 02087869 66656e66 l xff,……xifenf
65690106 80e2 l ei….
<16 bytes per line>
BBED> dump /v offset 8160
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8160 to 8191 Dba:0x00800212
——————————————————-
0000003c 020202c1 03037866 662c0002 l …<......xff,.. 02c10208 78696665 6e666569 010680e2 l ....xifenfei.... <16 bytes per line>
BBED> dump /v offset 8164
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8164 to 8191 Dba:0x00800212
——————————————————-
020202c1 03037866 662c0002 02c10208 l ……xff,……
78696665 6e666569 010680e2 l xifenfei….
<16 bytes per line>
BBED> dump /v offset 8162
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8162 to 8191 Dba:0x00800212
——————————————————-
003c0202 02c10303 7866662c 000202c1 l .<......xff,.... 02087869 66656e66 65690106 80e2 l ..xifenfei.... <16 bytes per line>
BBED> dump /v offset 8163
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8163 to 8191 Dba:0x00800212
——————————————————-
3c020202 c1030378 66662c00 0202c102 l <......xff,..... 08786966 656e6665 69010680 e2 l .xifenfei.... <16 bytes per line>
通过尝试，推断出来3c的offset
BBED> modify /x 2c
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8163 to 8191 Dba:0x00800212
————————————————————————
2c020202 c1030378 66662c00 0202c102 08786966 656e6665 69010680 e2
<32 bytes per line>
修改3c为2c
BBED> sum apply
Check value for File 2, Block 530:
current = 0xb1b9, required = 0xb1b9
三、核对结果
SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select * from hr.xifenfei;
ID NAME
———- ——————–
1 xifenfei
2 xff
说明：
1）如果数据未删除：row flag的值为 32+8+4=44或者0x2c
2）如果数据被删除：row flag的值为 32+16+8+4=60或者0x3c

监控Index注意事项

1、监控语句
ALTER INDEX owner.index_name MONITORING USAGE;
2、查看监控情况
select * from v$object_usage;
3、使用sys用户无法查询到监控对象问题解决
3.1）查询建立v$object_usage的语句

CREATE OR REPLACE FORCE VIEW v$object_usage (index_name,
table_name,
MONITORING,
used,
start_monitoring,
end_monitoring
)
AS
SELECT io.NAME, t.NAME, DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES'),
DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES'), ou.start_monitoring,
ou.end_monitoring
FROM SYS.obj$ io, SYS.obj$ t, SYS.ind$ i, SYS.object_usage ou
WHERE io.owner# = USERENV ('SCHEMAID')
AND i.obj# = ou.obj#
AND io.obj# = ou.obj#
AND t.obj# = i.bo#;

3.2）从该语句中发现，只有当前用户才可以查询到index的监控情况，即使sys也不能查看到
3.3）解决sys可以查询到index监控情况可以除掉io.owner# = USERENV (‘SCHEMAID’)即可

SELECT *
  FROM (SELECT USERNAME OWNER,
               IO.NAME INDEX_NAME,
               T.NAME TABLE_NAME,
               DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING,
               DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED,
               OU.START_MONITORING START_MONITORING,
               OU.END_MONITORING END_MONITORING
          FROM SYS.OBJ$         IO,
               SYS.OBJ$         T,
               SYS.IND$         I,
               SYS.OBJECT_USAGE OU,
               DBA_USERS        U
         WHERE I.OBJ# = OU.OBJ#
           AND IO.OBJ# = OU.OBJ#
           AND T.OBJ# = I.BO#
           AND U.USER_ID = IO.OWNER#)
--USED='YES'--表示有使用
--USED='NO'--表示没使用
 WHERE USED = 'YES';

如果想查询方便，可以创建sys下面的下个视图
4、停止监控
ALTER INDEX owner.index_name NOMONITORING USAGE;