问题：使用sqlplus登陆数据库查看当前时间

SYSTEM@10.188.122.250>select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYYMMDDHH24
---------------------------------------------------------------------------
20081113 11:55:27

使用telnet连接到服务器查看系统时间
# date
Thu Nov 13 20:07:24 TAIST 2008两者相差8小时

处理：使用sqlplus查看数据库timezone

SYSTEM@10.188.122.250>select dbtimezone from dual;

DBTIME
------
+00:00

SYSTEM@10.188.122.250>select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------
+08:00由上可见数据库时区与进程时区差了8小时。

修改数据库时区

SYSTEM@10.188.122.250>alter database set time_zone='+8:00';
ALTER DATABASE SET TIME_ZONE='+8.00'
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

修改失败

按照TOM提供的方法

SYSTEM@10.188.122.250>select u.name || '.' || o.name || '.' || c.name TSLTZcolumn
2 from sys.obj$ o, sys.col$ c, sys.user$ u
3 where c.type# = 231
4 and o.obj# = c.obj#
5 and u.user# = o.owner#;

TSLTZCOLUMN
----------------------------------------------------------------------------------
OE.ORDERS.ORDER_DATE

删除该表
1* drop table oe.orders cascade constraintsSYSTEM@10.188.122.250>/Table dropped.

再次检查

SYSTEM@10.188.122.250>select u.name || '.' || o.name || '.' || c.name TSLTZcolumn
2 from sys.obj$ o, sys.col$ c, sys.user$ u
3 where c.type# = 231
4 and o.obj# = c.obj#
5 and u.user# = o.owner#;

no rows selected

现在可以修改了

SYSTEM@10.188.122.250>alter database set time_zone='+8:00';

Database altered.

重启数据库后

SYSTEM@10.188.122.250>select dbtimezone from dual;

DBTIME
------
+08:00

问题解决

你是否想知道一句sql语句如何执行，它是否走索引，是否采用不同得驱动表，是否用nestloop join,hash join…..?这一切对你是否很神秘呢？或许你会说execution plan能看到这些东西，但是你是否清楚execution plan是如何得到？这篇文章就是给出了隐藏在execution plan底下的具体实现。

10053事件
10053事件是oracle提供的用于跟踪sql语句成本计算的内部事件，它能记载CBO模式下oracle优化器如何计算sql成本，生成相应的执行计划。

如何设置10053事件
设置本session的10053
开启：
Alter session set events’10053 trace name context forever[,level {1/2}]’;
关闭：
Alter session set events’10053 trace name context off’;

设置其他session的10053
开启：
SYS.DBMS_SYSTEM.SET_EV (<sid>, <serial#>, 10053, {1|2}, '')
关闭：
SYS.DBMS_SYSTEM.SET_EV (<sid>, <serial#>, 10053,0, '')

跟其他跟踪事件不同，10053提供了两个跟踪级别，但是级别2的跟踪信息比级别1少（其他跟踪事件如10046跟踪级别越高信息越多），跟踪信息将被记录到user_dump_dest目录底下。注意，要实现跟踪必须满足两个条件：sql语句必须被hard parse并且必须使用CBO优化器模式。如果sql语句已经被parse过，那么10053不生成跟踪信息。如果你使用RULE优化器，那么10053也不会生成跟踪信息。


跟踪内容
跟踪文件包括6部分：
Sql语句
优化器相关参数
基本统计信息
基本表访问成本
综合计划
特殊功能的成本重计算

这篇文章将会涉及到前4项和一部分第5项的内容，我们将会用以下语句作为例子：

select dname, ename from emp, dept
where emp.deptno = dept.deptno
and ename = :b1




sql语句：
这部分是整个跟踪文件里最容易理解的部分，包括了所执行的sql语句，如果你采用RULE模式优化器，那么除了这一部分外将不会有多余信息出现在跟踪文件里。











优化器相关参数：
记载了所有影响成本计算的参数

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
OPTIMIZER_FEATURES_ENABLE = 8.1.6
OPTIMIZER_MODE/GOAL = Choose
OPTIMIZER_PERCENT_PARALLEL = 0
HASH_AREA_SIZE = 131072
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 0
OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = FALSE
STAR_TRANSFORMATION_ENABLED = FALSE
_COMPLEX_VIEW_MERGING = FALSE
_PUSH_JOIN_PREDICATE = FALSE
PARALLEL_BROADCAST_ENABLED = FALSE
OPTIMIZER_MAX_PERMUTATIONS = 80000
OPTIMIZER_INDEX_CACHING = 0
OPTIMIZER_INDEX_COST_ADJ = 100
QUERY_REWRITE_ENABLED = TRUE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = FALSE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = FALSE
_NEW_INITIAL_JOIN_ORDERS = FALSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = FALSE
_PUSH_JOIN_UNION_VIEW = FALSE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = FALSE
_NESTED_LOOP_FUDGE = 100
_NO_OR_EXPANSION = FALSE
_QUERY_COST_REWRITE = TRUE
QUERY_REWRITE_EXPRESSION = TRUE
_IMPROVED_ROW_LENGTH_ENABLED = TRUE
_USE_NOSEGMENT_INDEXES = FALSE
_ENABLE_TYPE_DEP_SELECTIVITY = TRUE
_IMPROVED_OUTERJOIN_CARD = TRUE
_OPTIMIZER_ADJUST_FOR_NULLS = TRUE
_OPTIMIZER_CHOOSE_PERMUTATION = 0
_USE_COLUMN_STATS_FOR_FUNCTION = FALSE
_SUBQUERY_PRUNING_ENABLED = TRUE
_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
_SUBQUERY_PRUNING_COST_FACTOR = 20
_LIKE_WITH_BIND_AS_EQUALITY = FALSE
_TABLE_SCAN_COST_PLUS_ONE = FALSE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
DB_FILE_MULTIBLOCK_READ_COUNT = 32
SORT_AREA_SIZE = 131072



基本统计信息:
下一部分是所有表和索引的基本统计信息
基本统计信息包括

表:
Trace label dba_tables column
CDN NUM_ROWS 表记录数
NBLKS BLOCKS 高水位以下的block数
TABLE_SCAN_CST 全表扫描的I/O成本
AVG_ROW_LEN AVG_ROW_LEN 平均行长

索引:
Trace label dba_indexes column
Index#, col# 索引号及表列号
LVLS BLEVEL BTREE索引高度
#LB LEAF_BLOCKS 索引叶块数
#DK DISTINCT_KEYS 不重复索引关键字
LB/K AVG_LEAF_BLOCKS_PER_KEY 叶块/关键字
DB/K AVG_DATA_BLOCKS_PER_KEY 数据块/关键字
CLUF CLUSTERING_FACTOR 索引聚合因子


***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats Table: DEPT Alias: DEPT
TOTAL :: CDN: 16 NBLKS: 1 TABLE_SCAN_CST: 1 AVG_ROW_LEN: 20
-- Index stats
INDEX#: 23577 COL#: 1
TOTAL :: LVLS: 0 #LB: 1 #DK: 16 LB/K: 1 DB/K: 1 CLUF: 1
***********************
Table stats Table: EMP Alias: EMP
TOTAL :: CDN: 7213 NBLKS: 85 TABLE_SCAN_CST: 6 AVG_ROW_LEN: 36
-- Index stats
INDEX#: 23574 COL#: 1
TOTAL :: LVLS: 1 #LB: 35 #DK: 7213 LB/K: 1 DB/K: 1 CLUF: 4125
INDEX#: 23575 COL#: 2
TOTAL :: LVLS: 1 #LB: 48 #DK: 42 LB/K: 1 DB/K: 36 CLUF: 1534
INDEX#: 23576 COL#: 8
TOTAL :: LVLS: 1 #LB: 46 #DK: 12 LB/K: 3 DB/K: 34 CLUF: 418
***************************************






基本表访问成本：
这里开始CBO将会计算单表访问的成本

单表访问路径
SINGLE TABLE ACCESS PATH .........................................................................................................................................1
Column: ENAME Col#: 2 Table: EMP Alias: EMP.....................................................................2
NDV: 42 NULLS: 0 DENS: 2.3810e-002 ...........................................................................3
TABLE: EMP ORIG CDN: 7213 CMPTD CDN: 172 ........................................................................................4
Access path: tsc Resc: 6 Resp: 6............................................................................................................5
Access path: index (equal) ...............................................................................................................................6
INDEX#: 23575 TABLE: EMP ...........................................................................................................................7
CST: 39 IXSEL: 0.0000e+000 TBSEL: 2.3810e-002.......................................................................8
BEST_CST: 6.00 PATH: 2 Degree: 1..............................................................................................................9

我们看一下上面是什么意思。首先CBO列出了ename列的统计信息(第2，3行),这些统计信息来自dba_tab_columns。
列的统计信息和dba_tab_columns中对应的列名如下
Trace label dba_tables column
NDV NUM_DISTINCT 列的不重复值数
NULLS NUM_NULLS 列的空行数
DENS DENSITY 列密度，没有直方图的情况下= 1/NDV
LO LOW_VALUE 列的最小值 (只对数字列)
HI HIGH_VALUE 列的最大值 (只对数字列)
第4行出现了表的行数ORIG CDN和计算过的行数 CMPTD CDN (computed cardinality). 计算公司如下，
CMPTD CDN = ORIG CDN * FF
在这里 FF 表示过滤因子（Filter Factor）。我们稍后再来看FF是什么及如何计算的。
第5行表示了全表扫描的成本。 这里的成本是62, 是由NBLKS和db_file_multi_block_read_count初始化参数计算出来的。.
第6－8行是索引访问的成本。
第9行是总结了以上信息并选出了最优的访问路径为全表扫描，成本为6。





表扫描成本
让我们来看一下全表扫描成本(tsc)是如何计算的 这里有其他两个大表的基本统计信息。
TOTAL :: CDN: 115630 NBLKS: 4339 TABLE_SCAN_CST: 265 AVG_ROW_LEN: 272
TOTAL :: CDN: 454503 NBLKS: 8975 TABLE_SCAN_CST: 548 AVG_ROW_LEN: 151
你可能曾经看到过全表扫描成本= 访问的块数目/ db_file_multi_block_read_count. 看起来这个等式很有意义因为oracle在做全表扫描时每个I/O请求将会读取db_file_multi_block_read_count个块。但是，我们计算以上统计信息得到
NBLKS / TABLE_SCAN_CST = 4339 / 265 = 16.373 ≠ db_file_multi_block_read_count(这里的值是32，可以看前面参数那一页)
另外一个表为
NBLKS / TABLE_SCAN_CST = 8975 / 548 = 16.377


全表扫描成本和db_file_multi_block_read_count

CBO将会根据NBLKS和db_file_multiblock_read_count来估计全表扫描成本，但是db_file_multiblock_read_count通常会被打上折扣。实际上我们可以认为等式会是
TABLE_SCAN_CST = NBLKS / k
我们来看一下k和db_file_multiblock_read_count 究竟有什么规律可寻。我们来做一个实验，使用不同的
db_file_multiblock_read_count值4, 6,8, 12,16, 24,32来测试。

图请见WORD版本

横轴为db_file_multiblock_read_count，纵轴为K。
注意参数K仅仅用在全表扫描或快速索引扫描上，实际的I/O成本还与其他因数有关，比如说需要访问的表已经在内存中的块及块的数量。




过滤因子(FF)
为了理解索引访问成本我们需要了解一下过滤因子。 过滤因子是一个介于0和1之间的数字，反映了记录的可选择性。如果一个列有10种不同的值，我们需要查询等于其中某一个值的记录时，如果这10种值平均分布的话，你将得到1/10的行数。如果没有直方图，过滤因子为FF = 1/NDV = density

再来看一下过滤因子和查询条件的关系
不使用绑定变量的情况:
predicate Filter factor
c1 = value 1/c1.num_distinct4
c1 like value 1/c1.num_distinct
c1 > value (Hi - value) / (Hi - Lo)
c1 >= value (Hi - value) / (Hi - Lo) + 1/c1.num_distinct
c1 < value (value - Lo) / (Hi - Lo)
c1 <= value (value - Lo) / (Hi - Lo) + 1/c1.num_distinct
c1 between val1 and val2 (val2 – val1) / (Hi - Lo) + 2 * 1/c1.num_distinct
使用绑定变量的情况（8i）:
predicate Filter factor
col1 = :b1 col1.density
col1 {like | > | >= | < | <=} :b1 {5.0000e-02 | col1.
col1 between :b1 and :b2 5.0000e-02 * 5.0000e-
包含and和or的情况:
predicate Filter factor
predicate 1 and predicate 2 FF1 * FF2
predicate 1 or predicate 2 FF1 + FF2 – FF1 * FF2


包含直方图的列：
如果一个列包含了直方图信息，那么它的density就来自于直方图。关于直方图的内容请参考官方手册，这里不在细述。由于直方图的存在FF并不是简单的等于1/NDV,而是来自于直方图中各个列的density，所有有直方图的话CBO将可能采取不一样的执行路径。













索引访问成本：
现在我们知道了聚合因子的概念，我们再来看一看索引访问的成本
SINGLE TABLE ACCESS PATH .........................................................................................................................................1
Column: ENAME Col#: 2 Table: EMP Alias: EMP.....................................................................2
NDV: 42 NULLS: 0 DENS: 2.3810e-002 ...........................................................................3
TABLE: EMP ORIG CDN: 7213 CMPTD CDN: 172 ........................................................................................4
Access path: tsc Resc: 6 Resp: 6............................................................................................................5
Access path: index (equal) ...............................................................................................................................6
INDEX#: 23575 TABLE: EMP ...........................................................................................................................7
CST: 39 IXSEL: 0.0000e+000 TBSEL: 2.3810e-002.......................................................................8
BEST_CST: 6.00 PATH: 2 Degree: 1..............................................................................................................9

我们来看6-8行，这里表示了索引访问的成本。第6行表示这里采取索引equal的方法来访问，再来回忆一下索引的基本统计信息
INDEX#: 23575 COL#: 2
TOTAL :: LVLS: 1 #LB: 48 #DK: 42 LB/K: 1 DB/K: 36 CLUF: 1534

根据索引成本计算公式
blevel + FF*leaf_blocks + FF*clustering_factor
1 + 2.3810e-002-2*48 + 2.3810e-002-2*1534 = 1 + 1.1429 + 36.5245 = 38.6674
这里的FF就等于TBSEL=DENS=2.3810e-002,由于我们的查询条件为ename = :b1所以得出FF为ENAME列的DENS，
其实索引访问方式的成本计算公式
• Unique scan blevel+1
• Fast full scan leaf_blocks / k ( k = 1.6765x0.6581 )
• Index-only blevel + FF*leaf_blocks


让我们用别的例子证明一下索引成本计算，语句为
select … from tbl a
where a.col#1 = :b1
and a.col#12 = :b2
and a.col#8 = :b3

索引和列的基本统计数据如下
INDEX# COL# LVLS #LB #DK LB/K DB/K CLUF
8417 27,1 1 13100 66500 1 22 1469200
8418 1,12,7 2 19000 74700 1 15 1176500
8419 3,1,4,2 2 31000 49700 1 2 118000
15755 1,12,8 1 12600 18800 1 30 1890275
8416 1,2,33,4,5,6 2 25800 1890300 1 1 83900
Col#: 1 NDV: 10 NULLS: 0 DENS: 1.0000e-001-1
Col#: 12 NDV: 8 NULLS: 0 DENS: 1.2500e-001
Col#: 8 NDV: 33 NULLS: 0 DENS: 3.0303e-001

Access path: index (scan)...................................................................................................................................1
INDEX#: 8418 CST: 14947 IXSEL: 1.2500e-002 TBSEL: 1.2500e-002 ........................................2
Access path: index (equal) ...............................................................................................................................3
INDEX#: 15755 CST: 7209 IXSEL: 0.0000e+000 TBSEL: 3.7879e-003 ......................................4
Access path: index (scan) .................................................................................................................................5
INDEX#: 8416 CST: 10972 IXSEL: 1.0000e-001 TBSEL: 1.0000e-001 ........................................6
5个索引中，索引(#8417 and #8419) 将不会被考虑因为他们的首列不出现在查询条件中。.

INDEX# 8418
索引包含的3个列中只有2列出现在查询条件中，所以只用2列的DENS来计算过滤因子
FF = 1.0000e-001 * 1.2500e-001= 1.2500e-002
cost = lvl + FF*#LB + FF*clustering factor
= 2 + 19,000*1.2500e-002 + 1176500*1.2500e-002
= 2 + 237.5 + 14706.25 = 14945.75
INDEX# 15755
索引包含的3列都出现在查询条件中，用3列的DENS计算过滤因子
FF = 1.0000e-001 * 1.2500e-001 * 3.0303e-001 = 3.7879e-003
cost = lvl + FF*#LB + FF*clustering factor
= 1 + 12,600*3.7879e-003 + 1,890,275*3.7879e-003
= 2 + 47.73 + 7160.13 = 7208.86
INDEX# 8416
索引包含的3个列中只有1列出现在查询条件中，所以只用1列的DENS来计算过滤因子
FF = 1.0000e-001
cost = lvl + FF*#LB + FF*clustering factor
= 2 + 25,800*1.0000e-001+ 83,900*1.0000e-001
= 2 + 2580 + 8390 = 10972
虽然索引8416只有一列出现在查询条件中，但是它的成本还是低于索引8418，因为它的聚合因子(clustering factor)比较低，所以统计出来成本也比较低。关于聚合因子可以参考oracle官方文档。








综合计划：
这一部分开始是10053最大的一部分，在这里CBO会评估各种JOIN方式及顺序的成本。

1. NL - NESTED LOOP JOIN
join cost = cost of accessing outer table
+ (row number of outer table * cost of accessing inner table )
2. SM – SORT MERGE JOIN
join cost = (cost of accessing outer table + outer sort cost)
+ (cost of accessing inner table + inner sort cost)
3. HA – HASH JOIN
join cost = (cost of accessing outer table)
+ (cost of building hash table)
+ (cost of accessing inner table )


JOIN ORDER [N]

Join order[1]: DEPT [DEPT] EMP [EMP]
Now joining: EMP [EMP] *******
JOINS – NL
NL Join ..............................................................................................................................................................................1
Outer table: cost: 1 cdn: 16 rcz: 13 resp: 1..................................................................................2
Inner table: EMP ......................................................................................................................................................3
Access path: tsc Resc: 6 ...............................................................................................................................4
Join resc: 97 Resp: 97 ...............................................................................................................................5
Access path: index (join stp) ...........................................................................................................................6
INDEX#: 23575 TABLE: EMP ...........................................................................................................................7
CST: 39 IXSEL: 0.0000e+000 TBSEL: 2.3810e-002.......................................................................8
Join resc: 625 resp:625 .............................................................................................................................9
Access path: index (join index).....................................................................................................................10
INDEX#: 23576 TABLE: EMP .........................................................................................................................11
CST: 37 IXSEL: 0.0000e+000 TBSEL: 8.3333e-002.....................................................................12
Join resc: 593 resp:593 ...........................................................................................................................13
Access path: and-equal...................................................................................................................................14
CST: 19 ...............................................................................................................................................................15
Join resc: 305 resp:305 ...........................................................................................................................16
Join cardinality: 172 = outer (16) * inner (172) * sel (6.2500e-002) [flag=0].................17
Best NL cost: 97 resp: 97...............................................................................................................................18
第1行为JOIN方式
第2行为驱动表的成本，行数，行大小。这里的行数为16，平均行长原本为20，但是因为DEPT表包含(DEPTNO, DEPT, and LOC)3列但仅有DEPTNO,DEPT等2列需要被join，所以计算后平均行长为16，所以在这里也被称为low row size.
第3行到16行通过NL JOIN的成本计算公式，计算出几种不同join方法的成本。
1. Tablescan of EMP at a cost of 6:
cost = cost of outer + cardinality of outer * cost of inner = 1 + 16 * 6 = 97 lines 3 to 5
2. Scan of index 23575 on ENAME at a cost of 39:
cost = 1 + 16 * 39 = 625 lines 6 to 9
3. Scan of index 23576 on DEPTNO at a cost of 37:
cost = 1 + 16 * 37 = 593 lines 10 to 13
4. An “and-equal” access at a cost of 19:
cost = 1 + 16 * 19 = 305 lines 14 to 16
第17行CBO估算出这个JOIN结果集的记录数，它将被最为下一次join的输入。它的计算公式为
Join cardinality:= outer * inner * join selectivity
而join selectivity为
join selectivity = 1/max[ NDV(t1.c1), NDV(t2.c2) ]
* [ (card t1 - # t1.c1 NULLs) / card t1 ]
* [ (card t2 - # t2.c2 NULLs) / card t2 ]
Join cardinality只会被用于NL JOIN中，其他JOIN会采取不同办法。
最后在18行，CBO将会列出成本最低的NL JOIN的方法。

JOINS - SM
SM Join
Outer table:
resc: 1 cdn: 16 rcz: 13 deg: 1 resp: 1
Inner table: EMP
resc: 6 cdn: 172 rcz: 9 deg: 1 resp: 6
SORT resource Sort statistics
Sort width: 3 Area size: 43008 Degree: 1
Blocks to Sort: 1 Row size: 25 Rows: 16
Initial runs: 1 Merge passes: 1 Cost / pass: 2
Total sort cost: 2
SORT resource Sort statistics
Sort width: 3 Area size: 43008 Degree: 1
Blocks to Sort: 1 Row size: 20 Rows: 172
Initial runs: 1 Merge passes: 1 Cost / pass: 2
Total sort cost: 2
Merge join Cost: 10 Resp: 10
SM Join (with index on outer)
Access path: index (no sta/stp keys)
INDEX#: 23577 TABLE: DEPT
CST: 2 IXSEL: 1.0000e+000 TBSEL: 1.0000e+000
Outer table:
resc: 2 cdn: 16 rcz: 13 deg: 1 resp: 2
Inner table: EMP
resc: 6 cdn: 172 rcz: 9 deg: 1 resp: 6
SORT resource Sort statistics
Sort width: 3 Area size: 43008 Degree: 1
Blocks to Sort: 1 Row size: 20 Rows: 172
Initial runs: 1 Merge passes: 1 Cost / pass: 2
Total sort cost: 2
Merge join Cost: 10 Resp: 10
在SM JOIN中成本为
Cost of outer + cost of inner + sort cost for outer + sort cost for inner = 1+ 6 + 2 + 2 = 11.
在这里CBO减去1所以最终等于10。在第2个SM JOIN的方法下通过了已经排序的索引，所以成本为 2 + 6 + 0 (no sort on outer) + 2 = 10.

JOINS – HA
HA Join
Outer table:
resc: 1 cdn: 16 rcz: 13 deg: 1 resp: 1
Inner table: EMP
resc: 6 cdn: 172 rcz: 9 deg: 1 resp: 6
Hash join one ptn: 1 Deg: 1
hash_area: 32 buildfrag: 33 probefrag: 1 ppasses: 2
Hash join Resc: 8 Resp: 8
Join result: cost: 8 cdn: 172 rcz: 22
根据HA JOIN公式，计算出成本为
(cost of accessing outer table)+ (cost of building hash table)+ (cost of accessing inner table )
=1+6+1=8


所以在这里HA JOIN会被选做最优化的执行路径，SQL语句将会最终走HA JOIN.



多重JOIN:
如果出现大于两个表进行JOIN的情况，那么会有更多的join顺序被考虑，4个表join的话会有24种join顺序，5个表的话会有120个join顺序，n个表会有n!个join顺序。由于估算每种join顺序都会耗费cpu，所以oracle用一个初始化参数optimizer_max_permutations来限制最大计算join顺序。若想了解多重join的更多信息，请搜索相关sql调整的资料。


结论：
10053是一个很好的理解CBO工作机制的工具，如果辅以10046事件查看执行计划，那么整个sql语句从解析到执行的过程都一目了然了。


reference:
A LOOK UNDER THE HOOD OF CBO: THE 10053 EVENT
Wolfgang Breitling, Centrex Consulting Corporation

oracle执行计划中cost, card的含义

作者:oliver 日期:2007-07-20

Oracle为RDBMS提供了多种的诊断工具，诊断事件(Event)是其中一种常用、好用的方法，它使DBA可以方便的转储数据库各种结构及跟踪特定事件的发生.
一、Event的通常格式及分类

1、 通常格式如下：
EVENT="<事件名称><动作><跟踪项目><范围限定>"

2、 Event分类
诊断事件大体上可以分为四类：
a． 转储类事件：它们主要用于转储Oracle的一些结构，例如转储一下控制文件、数据文件头等内容。
b． 捕捉类事件：它们用于捕捉一些Error事件的发生，例如捕捉一下ORA-04031发生时一些Rdbms信息，以判断是Bug还是其它原因引起的这方面的问题。
c． 改变执行途径类事件：它们用于改主一些Oracle内部代码的执行途径，例如设置10269将会使Smon进程不去合并那些Free的空间。
d． 跟踪类事件：这们用于获取一些跟踪信息以用于Sql调优等方面，最典型的便是10046了，将会对Sql进行跟踪。
3、 说明:
a． 如果immediate放在第一个说明是无条件事件，即命令发出即转储到跟踪文件。
b． trace name位于第二、三项，除它们外的其它限定词是供Oracle内部开发组用的。
c． level通常位于1-10之间(10046有时用到12)，10意味着转储事件所有的信息。例如当转储控制文件时，level1表示转储控制文件头，而level 10表明转储控制文件全部内容。
d． 转储所生成的trace文件在user_dump_dest初始化参数指定的位置。
二、说一说设置的问题了

可以在init.ora中设置所需的事件，这将对所有会话期打开的会话进行跟踪，也可以用alter session set event 等方法设置事件跟踪，这将打开正在进行会话的事件跟踪。

1、 在init.ora中设置跟踪事件的方法
a． 语法
EVENT=”event 语法|,level n|：event 语法|,level n|…”
b． 举例
event=”10231 trace name context forever,level 10’
c． 可以这样设置多个事件：
EVENT="
10231 trace name context forever, level 10:
10232 trace name context forever, level 10"

2、 通过Alter session/system set events这种方法
举个例子大家就明白了
Example:
Alter session set events ‘immediate trace name controlf level 10’;
Alter session set events ‘immediate trace name blockdump level 112511416’; (*)
在oracle8x及之上的版本也有这样的语句：
Alter system dump datafile 13 block 15;实现的功能与(*)是类似的。

3、 使用DBMS_SYSTEM.SET_EV的方法
a． 过和定义如下
DBMS_SYSTEM.SET_EV(
SI Binary_integer,
SE Binary_integer,
EV Binary_integer,
LE Binary_integer,
NM Binary_integer);

SI: 即v$session中的sid
SE:即v$session中的serial#
EV:要设置的事件
LE:要设置事件的级别
NM:名称
b． 举个例子，以10046为例
SQL> EXECUTE SYS.DBMS_SYSTEM.SET_EV(sid,serial#,10046,12,'')；

4、 使用Oradebug来设置诊断事件的方法
同样举个例子大家就明白了：
a． 找到spid
SQL>select username, sid, serial#, paddr from v$session where username='qiuyb';

USERNAME SID SERIAL# PADDR
--------------------------------------------------------
HRB3 265 910 C000000084435AD8

SQL>SELECT ADDR,PID,SPID FROM V$PROCESS WHERE ADDR= C000000084435AD8';
ADDR PID SPID
------------------------------------------
C000000084435AD8 91 4835

b． 设置事件，以10046为例
sqlplus /nolog
SQL>connect / as sysdba;
SQL>oradebug setospid 4835
SQL>oradebug unlimit　　　--不限制转储文件的大小
SQL> oradebug event 10046 trace name context forever,level 12 --设置事件进行sql跟踪

SQL> oradebug event 10046 trace name context off --关闭跟踪

注意不要用oradug去跟踪oracle的smon,pmon等几个进程，操作不当可能会杀掉这几个后台进和引起宕库。

三、你可能的问题

1、 我如何知道在系统中设置了哪些event?
回答：
a． 如果你的事件是在init.ora中设置的可以用
SQL>show parameter event;
来查看
b． Michael R.Ault给的SQL
serveroutput on size 1000000
declare
event_level number;
begin
for i in 10000..10999 loop
sys.dbms_system.read_ev(i,event_level);
if (event_level > 0) then
dbms_output.put_line('Event '||to_char(i)||' set at level '||
to_char(event_level));
end if;
end loop;
end;
/

2、 在oracle9i中使用spfile的那种如何设置诊断事件呢？
回答：
简单，Alter system命令就可以完成
alter system set event='10046 trace name context forever, level 12' scope=spfile;
重启一下就生效了。

3、 坏了，我的9i设置完诊断事件，起不来了，报ORA-02194错怎么办?
回答：
那你一定是在使用Alter system时把某一项写错了，比如把context写成了conetxt了，可以做如下的解决：
a．由spfile生成pfile
SQL>create pfile from spfile;
File created.

b．编辑pfile以修正错误
Change... *.event='10046 trace name conetxt forever, level 12'
-to- *.event='10046 trace name context forever, level 12'
c．用pfile启动
SQL>startup pfile=/.....
d.重新生成 SPFILE.
SQL>create spfile from pfile;
File created.

Oracle诊断事件列表

ORA-10000: controlfile debug event, name 'control_file' ORA-10001: controlfile crash event1 ORA-10002: controlfile crash event2 ORA-10003: controlfile crash event3 ORA-10004: controlfile crash event4 ORA-10005: trace latch operations for debugging ORA-10006: testing - block recovery forced ORA-10007: log switch debug crash after new log select, thread ORA-10008: log switch debug crash after new log header write, thread ORA-10009: log switch debug crash after old log header write, thread ORA-10010: Begin Transaction ORA-10011: End Transaction ORA-10012: Abort Transaction ORA-10013: Instance Recovery ORA-10014: Roll Back to Save Point ORA-10015: Undo Segment Recovery ORA-10016: Undo Segment extend ORA-10017: Undo Segment Wrap ORA-10018: Data Segment Create ORA-10019: Data Segment Recovery ORA-10020: partial link restored to linked list (KSG) ORA-10021: latch cleanup for state objects (KSS) ORA-10022: trace ktsgsp ORA-10023: Create Save Undo Segment ORA-10024: Write to Save Undo ORA-10025: Extend Save Undo Segment ORA-10026: Apply Save Undo ORA-10027: Specify Deadlock Trace Information to be Dumped ORA-10028: Dump trace information during lock / resource latch cleanup ORA-10029: session logon (KSU) ORA-10030: session logoff (KSU) ORA-10031: sort debug event (S*) ORA-10032: sort statistics (SOR*) ORA-10033: sort run information (SRD*/SRS*) ORA-10035: parse SQL statement (OPIPRS) ORA-10036: create remote row source (QKANET) ORA-10037: allocate remote row source (QKARWS) ORA-10038: dump row source tree (QBADRV) ORA-10039: type checking (OPITCA) ORA-10040: dirty cache list ORA-10041: dump undo records skipped ORA-10042: trap error during undo application ORA-10043: check consistency of owner/waiter/converter lists in KSQ ORA-10044: free list undo operations ORA-10045: free list update operations - ktsrsp, ktsunl ORA-10046: enable SQL statement timing ORA-10047: trace switching of sessions ORA-10048: Undo segment shrink ORA-10049: protect library cache memory heaps ORA-10050: sniper trace ORA-10051: trace OPI calls ORA-10052: don't clean up obj$ ORA-10053: CBO Enable optimizer trace ORA-10054: trace UNDO handling in MLS ORA-10055: trace UNDO handing ORA-10056: dump analyze stats (kdg) ORA-10057: suppress file names in error messages ORA-10058: use table scan cost in tab$.spare1 ORA-10059: simulate error in logfile create/clear ORA-10060: CBO Enable predicate dump ORA-10061: disable SMON from cleaning temp segment ORA-10062: disable usage of OS Roles in osds ORA-10063: disable usage of DBA and OPER privileges in osds ORA-10064: thread enable debug crash level , thread ORA-10065: limit library cache dump information for state object dump ORA-10066: simulate failure to verify file ORA-10067: force redo log checksum errors - block number ORA-10068: force redo log checksum errors - file number ORA-10069: Trusted Oracle test event ORA-10070: force datafile checksum errors - block number ORA-10071: force datafile checksum errors - file number ORA-10072: protect latch recovery memory ORA-10073: have PMON dump info before latch cleanup ORA-10074: default trace function mask for kst ORA-10075: CBO Disable outer-join to regular join conversion ORA-10076: CBO Enable cartesian product join costing ORA-10077: CBO Disable view-merging optimization for outer-joins ORA-10078: CBO Disable constant predicate elimination optimization ORA-10079: trace data sent/received via SQL*Net ORA-10080: dump a block on a segment list which cannot be exchanged ORA-10081: segment High Water Mark has been advanced ORA-10082: free list head block is the same as the last block ORA-10083: a brand new block has been requested from space management ORA-10084: free list becomes empty ORA-10085: free lists have been merged ORA-10086: CBO Enable error if kko and qka disagree on oby sort ORA-10087: disable repair of media corrupt data blocks ORA-10088: CBO Disable new NOT IN optimization ORA-10089: CBO Disable index sorting ORA-10090: invoke other events before crash recovery ORA-10091: CBO Disable constant predicate merging ORA-10092: CBO Disable hash join ORA-10093: CBO Enable force hash joins ORA-10094: before resizing a data file ORA-10095: dump debugger commands to trace file ORA-10096: after the cross instance call when resizing a data file ORA-10097: after generating redo when resizing a data file ORA-10098: after the OS has increased the size of a data file ORA-10099: after updating the file header with the new file size ORA-10100: after the OS has decreased the size of a data file ORA-10101: atomic redo write recovery ORA-10102: switch off anti-joins ORA-10103: CBO Disable hash join swapping ORA-10104: dump hash join statistics to trace file ORA-10105: CBO Enable constant pred trans and MPs w WHERE-clause ORA-10106: CBO Disable evaluating correlation pred last for NOT IN ORA-10107: CBO Always use bitmap index ORA-10108: CBO Don't use bitmap index ORA-10109: CBO Disable move of negated predicates ORA-10110: CBO Try index rowid range scans ORA-10111: Bitmap index creation switch ORA-10112: Bitmap index creation switch ORA-10113: Bitmap index creation switch ORA-10114: Bitmap index creation switch ORA-10115: CBO Bitmap optimization use maximal expression ORA-10116: CBO Bitmap optimization switch ORA-10117: CBO Disable new parallel cost model ORA-10118: CBO Enable hash join costing ORA-10119: QKA Disable GBY sort elimination ORA-10120: generate relative file # different from absolute ORA-10121: CBO Don't sort bitmap chains ORA-10122: Disable transformation of count(col) to count(*) ORA-10123: QKA Disable Bitmap And-EQuals ORA-10124: Force creation of segmented arrays by kscsAllocate ORA-10125: Disable remote sort elimination ORA-10126: Debug oracle java xa ORA-10127: Disable remote query block operation ORA-10128: Dump Partition Pruning Information ORA-10129: Alter histogram lookup for remote queries ORA-10130: sort disable readaheads ORA-10131: use v$sql_plan code path for explain plan ORA-10132: dump plan after compilation ORA-10133: testing for SQL Memory Management ORA-10134: tracing for SQL Memory Management for session ORA-10135: CBO do not count 0 rows partitions ORA-10136: CBO turn off fix for bug 1089848 ORA-10137: CBO turn off fix for bug 1344111 ORA-10138: CBO turn off fix for bug 1577003 ORA-10139: CBO turn off fix for bug 1386119 ORA-10140: CBO turn off fix for bug 1332980 ORA-10141: CBO disable additional keys for inlist in bitmap optimization ORA-10142: CBO turn off advanced OR-expansion checks ORA-10143: CBO turn off hints ORA-10144: CBO turn off cost based selection of bji over bsj subquery ORA-10145: test auditing network errors ORA-10146: enable Oracle TRACE collection ORA-10147: enable join push through UNION view ORA-10148: Use pre-7.3.3 random generator ORA-10149: allow the creation of constraints with illegal date constants ORA-10150: import exceptions ORA-10151: Force duplicate dependency removal ORA-10152: CBO don't consider function costs in plans ORA-10153: Switch to use public synonym if private one does not translate ORA-10154: Switch to disallow synonyms in DDL statements ORA-10155: CBO disable generation of transitive OR-chains ORA-10156: CBO disable index fast full scan ORA-10157: CBO disable index access path for in-list ORA-10158: CBO preserve predicate order in post-filters ORA-10159: CBO disable order-by sort pushdown into domain indexes ORA-10160: CBO disable use of join index ORA-10161: CBO recursive semi-join on/off-switch ORA-10162: CBO join-back elimination on/off-switch ORA-10163: CBO join-back elimination on/off-switch ORA-10164: CBO disable subquery-adjusted cardinality fix ORA-10165: mark session to be aborted during shutdown normal ORA-10166: trace long operation statistics updates ORA-10167: CBO use old index MIN/MAX optimization ORA-10168: CBO disable single-table predicate predicate generation ORA-10169: CBO disable histograms for multi partitions ORA-10170: CBO use old bitmap costing ORA-10171: CBO disable transitive join predicates ORA-10172: CBO force hash join back ORA-10173: CBO no constraint-based join-back elimination ORA-10174: view join-back elimination switch ORA-10175: CBO star transformation switch ORA-10176: CBO colocated join switch ORA-10177: CBO colocated join switch ORA-10178: CBO turn off hash cluster filtering through memcmp ORA-10179: CBO turn off transitive predicate replacement ORA-10180: temp table transformation print error messages ORA-10181: CBO disable multi-column in-list processing ORA-10182: CBO disable generation of implied predicates ORA-10183: CBO disable cost rounding ORA-10184: CBO disable OR-exp if long inlist on bitmap column ORA-10185: CBO force index joins ORA-10186: CBO disable index join ORA-10187: CBO additional index join switch ORA-10188: CBO additional index join switch ORA-10189: CBO turn off FFS null fix ORA-10190: Analyze use old frequency histogram collection and density ORA-10191: Avoid conversion of in-lists back to OR-expanded form ORA-10192: nopushdown when number of groups exceed number of rows ORA-10193: Force repeatable sampling with specified seed ORA-10194: CBO disable new LIKE selectivity heuristic ORA-10195: CBO don't use check constraints for transitive predicates ORA-10196: CBO disable index skip scan ORA-10197: CBO force index skip scan ORA-10198: check undo record ORA-10199: set parameter in session ORA-10200: consistent read buffer status ORA-10201: consistent read undo application ORA-10202: consistent read block header ORA-10203: block cleanout ORA-10204: signal recursive extend ORA-10205: row cache debugging ORA-10206: transaction table consistent read ORA-10207: consistent read transactions' status report ORA-10208: consistent read loop check ORA-10209: enable simulated error on controlfile ORA-10210: check data block integrity ORA-10211: check index block integrity ORA-10212: check cluster integrity ORA-10213: crash after controlfile write ORA-10214: simulate write errors on controlfile ORA-10215: simulate read errors on controlfile ORA-10216: dump controlfile header ORA-10217: debug sequence numbers ORA-10218: dump uba of applied undo ORA-10219: monitor multi-pass row locking ORA-10220: show updates to the transaction table ORA-10221: show changes done with undo ORA-10222: row cache ORA-10223: transaction layer - turn on verification codes ORA-10224: index block split/delete trace ORA-10225: free/used extent row cache ORA-10226: trace CR applications of undo for data operations ORA-10227: verify (multi-piece) row structure ORA-10228: trace application of redo by kcocbk ORA-10229: simulate I/O error against datafiles ORA-10230: check redo generation by copying before applying ORA-10231: skip corrupted blocks on _table_scans_ ORA-10232: dump corrupted blocks symbolically when kcbgotten ORA-10233: skip corrupted blocks on index operations ORA-10234: trigger event after calling kcrapc to do redo N times ORA-10235: check memory manager internal structures ORA-10236: library cache manager ORA-10237: simulate ^C (for testing purposes) ORA-10238: instantiation manager ORA-10239: multi-instance library cache manager ORA-10240: dump dba's of blocks that we wait for ORA-10241: remote SQL execution tracing/validation ORA-10242: suppress OER 2063 (for testing distrib w/o different error log) ORA-10243: simulated error for test of K2GTAB latch cleanup ORA-10244: make tranids in error msgs print as 0.0.0 (for testing) ORA-10245: simulate lock conflict error for testing PMON ORA-10246: print trace of PMON actions to trace file ORA-10247: Turn on scgcmn tracing. (VMS ONLY) ORA-10248: turn on tracing for dispatchers ORA-10249: turn on tracing for multi-stated servers ORA-10250: Trace all allocate and free calls to the topmost SGA heap ORA-10251: check consistency of transaction table and undo block ORA-10252: simulate write error to data file header ORA-10253: simulate write error to redo log ORA-10254: trace cross-instance calls ORA-10255: pl/sql parse checking ORA-10256: turn off shared server load balancing ORA-10257: trace shared server load balancing ORA-10258: force shared servers to be chosen round-robin ORA-10259: get error message text from remote using explicit call ORA-10260: Trace calls to SMPRSET (VMS ONLY) ORA-10261: Limit the size of the PGA heap ORA-10262: Don't check for memory leaks ORA-10263: Don't free empty PGA heap extents ORA-10264: Collect statistics on context area usage (x$ksmcx) ORA-10265: Keep random system generated output out of error messages ORA-10266: Trace OSD stack usage ORA-10267: Inhibit KSEDMP for testing ORA-10268: Don't do forward coalesce when deleting extents ORA-10269: Don't do coalesces of free space in SMON ORA-10270: Debug shared cursors ORA-10271: distributed transaction after COLLECT ORA-10272: distributed transaction before PREPARE ORA-10273: distributed transaction after PREPARE ORA-10274: distributed transaction before COMMIT ORA-10275: distributed transaction after COMMIT ORA-10276: distributed transaction before FORGET ORA-10277: Cursor sharing (or not) related event (used for testing) ORA-10278: Internal testing ORA-10279: Simulate block corruption in kdb4chk ORA-10280: Internal testing - segmentation fault during crash recovery ORA-10281: maximum time to wait for process creation ORA-10282: Inhibit signalling of other backgrounds when one dies ORA-10283: simulate asynch I/O never completing ORA-10284: simulate zero/infinite asynch I/O buffering ORA-10285: Simulate controlfile header corruption ORA-10286: Simulate controlfile open error ORA-10287: Simulate archiver error ORA-10288: Do not check block type in ktrget ORA-10289: Do block dumps to trace file in hex rather than fromatted ORA-10290: kdnchk - checkvalid event - not for general purpose use. ORA-10291: die in tbsdrv to test controlfile undo ORA-10292: dump uet entries on a 1561 from dtsdrv ORA-10293: dump debugging information when doing block recovery ORA-10294: enable PERSISTENT DLM operations on non-compliant systems ORA-10295: die after file header update durning cf xact ORA-10296: disable ORA-379 ORA-10297: customize dictionary object number cache ORA-10298: ksfd i/o tracing ORA-10299: Trace prefetch tracking decisions made by CKPT ORA-10300: disable undo compatibility check at database open

ORA-10301: Enable LCK timeout table consistency check
ORA-10302: trace create or drop internal trigger
ORA-10303: trace loading of library cache for internal triggers
ORA-10304: trace replication trigger
ORA-10305: trace updatable materialized view trigger
ORA-10306: trace materialized view log trigger
ORA-10307: trace RepCat execution
ORA-10308: replication testing event
ORA-10309: Trigger Debug event
ORA-10310: trace synchronous change table trigger
ORA-10311: Disable Flashback Table Timestamp checking
ORA-10312: Allow disable to log rows into the mapping table
ORA-10313: Allow Row CR operations for single instance
ORA-10314: Enable extra stats gathering for CR
ORA-10316: Events for extensible txn header, non zero ext header size
ORA-10317: Events for extensible txn header, zero ext header size
ORA-10318: Trace extensible txn header movements
ORA-10319: Trace PGA statistics maintenance
ORA-10320: Enable data layer (kdtgrs) tracing of space management calls
ORA-10321: Datafile header verification debug failure.
ORA-10322: CBO don't simplify inlist predicates
ORA-10323: before committing an add datafile command
ORA-10324: Enable better checking of redo logs errors
ORA-10325: Trace control file record section expand and shrink operations
ORA-10326: clear logfile debug crash at , log
ORA-10327: simulate ORA-00235 error for testing
ORA-10328: disable first-to-mount split-brain error, for testing
ORA-10329: simulate lost write, test detection by two-pass recovery
ORA-10330: clear MTTR statistics in checkpoint progress record
ORA-10331: simulate resilvering during recovery
ORA-10332: force ALTER SYSTEM QUIESCE RESTRICTED command to fail
ORA-10333: dump MTTR statistics each time it is updated
ORA-10334: force FG to wait to be killed during MTTR advisory simulation
ORA-10336: Do remote object transfer using remote SQL
ORA-10337: enable padding owner name in slave sql
ORA-10338: CBO don't use inlist iterator with function-based indexes
ORA-10339: CBO disable DECODE simplification
ORA-10340: Buffer queues sanity check for corrupted buffers
ORA-10341: Simulate out of PGA memory in DBWR during object reuse
ORA-10342: Raise unknown exception in ACQ_ADD when checkpointing
ORA-10343: Raise an out of memory exception-OER 4031 in ACQ_ADD
ORA-10344: Simulate kghxal returning 0 in ACQ_ADD but no exception
ORA-10345: validate queue when linking or unlinking a buffer
ORA-10346: check that all buffers for checkpoint have been written
ORA-10347: dump active checkpoint entries and checkpoint buffers
ORA-10348: test abnormal termination of process initiating file checkpoint
ORA-10349: do not allow ckpt to complete
ORA-10350: Simulate more than one object & tsn id in object reuse
ORA-10351: size of slots
ORA-10352: report direct path statistics
ORA-10353: number of slots
ORA-10354: turn on direct read path for parallel query
ORA-10355: turn on direct read path for scans
ORA-10356: turn on hint usage for direct read
ORA-10357: turn on debug information for direct path
ORA-10358: Simulate out of PGA memory in cache advisory reset
ORA-10359: turn off updates to control file for direct writes
ORA-10360: enable dbwr consistency checking
ORA-10365: turn on debug information for adaptive direct reads
ORA-10370: parallel query server kill event
ORA-10371: disable TQ hint
ORA-10372: parallel query server kill event proc
ORA-10373: parallel query server kill event
ORA-10374: parallel query server interrupt (validate lock value)
ORA-10375: turn on checks for statistics rollups
ORA-10376: turn on table queue statistics
ORA-10377: turn off load balancing
ORA-10378: force hard process/range affinity
ORA-10379: direct read for rowid range scans (unimplemented)
ORA-10380: kxfp latch cleanup testing event
ORA-10381: kxfp latch cleanup testing event
ORA-10382: parallel query server interrupt (reset)
ORA-10383: auto parallelization testing event
ORA-10384: parallel dataflow scheduler tracing
ORA-10385: parallel table scan range sampling method
ORA-10386: parallel SQL hash and range statistics
ORA-10387: parallel query server interrupt (normal)
ORA-10388: parallel query server interrupt (failure)
ORA-10389: parallel query server interrupt (cleanup)
ORA-10390: Trace parallel query slave execution
ORA-10391: trace PX granule allocation/assignment
ORA-10392: parallel query debugging bits
ORA-10393: print parallel query statistics
ORA-10394: generate a fake load to test adaptive and load balancing
ORA-10395: adjust sample size for range table queues
ORA-10396: circumvent range table queues for queries
ORA-10397: suppress verbose parallel coordinator error reporting
ORA-10398: enable timeouts in parallel query threads
ORA-10399: trace buffer allocation
ORA-10400: turn on system state dumps for shutdown debugging
ORA-10401: turn on IPC (ksxp) debugging
ORA-10402: turn on IPC (skgxp) debugging
ORA-10403: fake CPU number for default degree of parallelism
ORA-10404: crash dbwr after write
ORA-10405: emulate broken mirrors
ORA-10406: enable datetime TIMESTAMP, INTERVAL datatype creation
ORA-10407: enable datetime TIME datatype creation
ORA-10408: disable OLAP builtin window function usage
ORA-10409: enable granule memset and block invalidation at startup
ORA-10410: trigger simulated communications errors in KSXP
ORA-10411: simulate errors in IMR
ORA-10412: trigger simulated errors in CGS/CM interface
ORA-10413: force simulated error for testing purposes
ORA-10414: simulated error from event level
ORA-10425: enable global enqueue service open event trace
ORA-10426: enable global enqueue service convert event trace
ORA-10427: enable global enqueue service traffic controller event trace
ORA-10428: enable tracing of global enqueue service distributed resource
ORA-10429: enable tracing of global enqueue service IPC calls
ORA-10430: enable tracing of global enqueue service AST calls
ORA-10431: enable verification messages on pi consistency
ORA-10432: enable tracing of global cache service fusion calls
ORA-10433: global enqueue service testing event
ORA-10434: enable tracing of global enqueue service multiple LMS
ORA-10435: enable tracing of global enqueue service deadlock detetction
ORA-10436: enable global cache service duplicate ping checking
ORA-10437: enable trace of global enqueue service S optimized resources
ORA-10442: enable trace of kst for ORA-01555 diagnostics
ORA-10450: signal ctrl-c in kdddca (drop column) after n rows
ORA-10496: Turn off fix for bug 2554178
ORA-10498: Trim blank characters including contol characters
ORA-10499: revert to old scale behaviour
ORA-10500: turn on traces for SMON
ORA-10501: periodically check selected heap
ORA-10502: CBO disable the fix for bug 2098120
ORA-10503: enable user-specified graduated bind lengths
ORA-10504: CBO disable the fix for bug 2607029
ORA-10510: turn off SMON check to offline pending offline rollback segment
ORA-10511: turn off SMON check to cleanup undo dictionary
ORA-10512: turn off SMON check to shrink rollback segments
ORA-10513: turn off wrap source compression
ORA-10515: turn on event to use physical cleanout
ORA-10520: recreate package/procedure/view only if definition has changed
ORA-10550: signal error during create as select/create index after n rows
ORA-10560: block type ''
ORA-10561: block type '', data object#
ORA-10562: Error occurred while applying redo to data block (file# , block# )
ORA-10563: Test recovery had to corrupt data block (file# , block# ) in order to proceed
ORA-10564: tablespace
ORA-10565: Another test recovery session is active
ORA-10566: Test recovery has used all the memory it can use
ORA-10567: Redo is inconsistent with data block (file# , block# )
ORA-10568: Failed to allocate recovery state object: out of SGA memory
ORA-10570: Test recovery complete
ORA-10571: Test recovery canceled
ORA-10572: Test recovery canceled due to errors
ORA-10573: Test recovery tested redo from change to
ORA-10574: Test recovery did not corrupt any data block
ORA-10575: Give up restoring recovered datafiles to consistent state: out of memory
ORA-10576: Give up restoring recovered datafiles to consistent state: some error occurred
ORA-10577: Can not invoke test recovery for managed standby database recovery
ORA-10578: Can not allow corruption for managed standby database recovery
ORA-10579: Can not modify control file during test recovery
ORA-10580: Can not modify datafile header during test recovery
ORA-10581: Can not modify redo log header during test recovery
ORA-10582: The control file is not a backup control file
ORA-10583: Can not recovery file renamed as missing during test recovery
ORA-10584: Can not invoke parallel recovery for test recovery
ORA-10585: Test recovery can not apply redo that may modify control file
ORA-10586: Test recovery had to corrupt 1 data block in order to proceed
ORA-10587: Invalid count for ALLOW n CORRUPTION option
ORA-10588: Can only allow 1 corruption for normal media/standby recovery
ORA-10589: Test recovery had to corrupt data blocks in order to proceed
ORA-10590: kga (argus debugger) test flags
ORA-10591: kga (argus debugger) test flags
ORA-10592: kga (argus debugger) test flags
ORA-10593: kga (argus debugger) test flags
ORA-10594: kga (argus debugger) test flags
ORA-10595: kga (argus debugger) test flags
ORA-10596: kga (argus debugger) test flags
ORA-10597: kga (argus debugger) test flags
ORA-10598: kga (argus debugger) test flags
ORA-10599: kga (argus debugger) test flags
ORA-10600: check cursor frame allocation
ORA-10601: turn on debugging for cursor_sharing (literal replacement)
ORA-10602: cause an access violation (for testing purposes)
ORA-10603: cause an error to occur during truncate (for testing purposes)
ORA-10604: trace parallel create index
ORA-10605: enable parallel create index by default
ORA-10606: trace parallel create index
ORA-10607: trace index rowid partition scan
ORA-10608: trace create bitmap index
ORA-10609: trace for array index insertion
ORA-10610: trace create index pseudo optimizer
ORA-10611: causes migration to fail - testing only
ORA-10612: prints debug information for auto-space managed segments
ORA-10613: prints debug information for auto-space managed segments
ORA-10614: Operation not allowed on this segment
ORA-10615: Invalid tablespace type for temporary tablespace
ORA-10616: Operation not allowed on this tablespace
ORA-10617: Cannot create rollback segment in dictionary managed tablespace
ORA-10618: Operation not allowed on this segment
ORA-10619: Avoid assertions when possible
ORA-10620: Operation not allowed on this segment
ORA-10621: data block does not belong to the segment
ORA-10622: test/trace online index (re)build
ORA-10623: Enable Index range scan Prefetch - testing only
ORA-10624: Disable UJV invalidation on drop index
ORA-10625: Turn off redo log dump for the index when OERI 12700
ORA-10627: Dump the content of the index leaf block
ORA-10628: Turn on sanity check for kdiss index skip scan state
ORA-10640: Operation not permitted during SYSTEM tablespace migration
ORA-10641: Cannot find a rollback segment to bind to
ORA-10642: Found rollback segments in dictionary managed tablespaces
ORA-10643: Database should be mounted in restricted mode and Exclusive mode
ORA-10644: SYSTEM tablespace cannot be default temporary tablespace
ORA-10645: Recursive Extension in SYSTEM tablespace during migration
ORA-10646: Too many recursive extensions during SYSTEM tablespace migration
ORA-10647: Tablespace other than SYSTEM,, not found in read only mode
ORA-10650: disable cache-callback optimisation
ORA-10651: incorrect file number block number specified
ORA-10666: Do not get database enqueue name
ORA-10667: Cause sppst to check for valid process ids
ORA-10690: Set shadow process core file dump type (Unix only)
ORA-10691: Set background process core file type (Unix only)
ORA-10700: Alter access violation exception handler
ORA-10701: Dump direct loader index keys
ORA-10702: Enable histogram data generation
ORA-10703: Simulate process death during enqueue get
ORA-10704: Print out information about what enqueues are being obtained
ORA-10705: Print Out Tracing information for every I/O done by ODSs
ORA-10706: Print out information about global enqueue manipulation
ORA-10707: Simulate process death for instance registration
ORA-10708: print out trace information from the RAC buffer cache
ORA-10709: enable parallel instances in create index by default
ORA-10710: trace bitmap index access
ORA-10711: trace bitmap index merge
ORA-10712: trace bitmap index or
ORA-10713: trace bitmap index and
ORA-10714: trace bitmap index minus
ORA-10715: trace bitmap index conversion to rowids
ORA-10716: trace bitmap index compress/decompress
ORA-10717: trace bitmap index compaction trace for index creation
ORA-10718: event to disable automatic compaction after index creation
ORA-10719: trace bitmap index dml
ORA-10720: trace db scheduling
ORA-10721: Internal testing - temp table transformation
ORA-10722: set parameters for CPU frequency calculation (debug)
ORA-10723: Internal testing - release buffer for buffer cache shrink
ORA-10724: trace cross-instance broadcast
ORA-10730: trace row level security policy predicates
ORA-10731: dump SQL for CURSOR expressions
ORA-10740: disables fix for bug 598861
ORA-10750: test rollback segment blksize guessing for index array insert
ORA-10800: disable Smart Disk scan
ORA-10801: enable Smart Disk trace
ORA-10802: reserved for Smart Disk
ORA-10803: write timing statistics on cluster database recovery scan
ORA-10804: reserved for ksxb
ORA-10806: Switch to 7.3 mode when detaching sessions
ORA-10807: Disable user id check when switching to a global transaction
ORA-10810: Trace snapshot too old
ORA-10811: Trace block cleanouts
ORA-10812: Trace Consistent Reads
ORA-10826: enable upgrade/downgrade error message trace
ORA-10827: database must be opened with MIGRATE option
ORA-10830: Trace group by sort row source
ORA-10831: Trace group by rollup row source
ORA-10841: Default un-inintialized charact set form to SQLCS_IMPLICIT
ORA-10842: Event for OCI Tracing and Statistics Info
ORA-10850: Enable time manager tracing
ORA-10851: Allow Drop command to drop queue tables
ORA-10852: Enable tracing for Enqueue Dequeue Operations
ORA-10853: event for AQ statistics latch cleanup testing
ORA-10856: Disable AQ propagator from using streaming
ORA-10857: Force AQ propagator to use two-phase commit
ORA-10858: Crash the AQ propagator at different stages of commit
ORA-10859: Disable updates of message retry count
ORA-10860: event for AQ admin disable new name parser
ORA-10861: disable storing extended message properties
ORA-10862: resolve default queue owner to current user in enqueue/dequeue
ORA-10871: dump file open/close timestamp during media recovery
ORA-10900: extent manager fault insertion event #
ORA-10902: disable seghdr conversion for ro operation
ORA-10903: Force tablespaces to become locally managed
ORA-10904: Allow locally managed tablespaces to have user allocation
ORA-10905: Do cache verification (kcbcxx) on extent allocation
ORA-10906: Unable to extend segment after insert direct load
ORA-10907: Trace extent management events
ORA-10908: Trace temp tablespace events
ORA-10909: Trace free list events
ORA-10910: inject corner case events into the RAC buffer cache
ORA-10911: Locally managed SYSTEM tablespace bitmaps can be modified only under the supervision of Oracle Support
ORA-10912: Used to perform admin operations on locally managed SYSTEM tablespace
ORA-10913: Create locally managed database if compatible > 920 by default
ORA-10924: import storage parse error ignore event
ORA-10925: trace name context forever
ORA-10926: trace name context forever
ORA-10927: trace name context forever
ORA-10928: trace name context forever
ORA-10929: trace name context forever
ORA-10930: trace name context forever
ORA-10931: trace name context forever
ORA-10932: trace name context forever
ORA-10933: trace name context forever
ORA-10934: Reserved. Used only in version 7.x.
ORA-10935: Reserved. Used only in version 7.x.
ORA-10936: trace name context forever
ORA-10937: trace name context forever
ORA-10938: trace name context forever
ORA-10939: trace name context forever
ORA-10940: trace name context forever
ORA-10941: trace name context forever
ORA-10943: trace name context forever
ORA-10944: trace name context forever
ORA-10945: trace name context forever
ORA-10970: backout event for bug 2133357
ORA-10975: trace execution of parallel propagation
ORA-10976: internal package related tracing
ORA-10977: trace event for RepAPI
ORA-10979: trace flags for join index implementation
ORA-10980: prevent sharing of parsed query during Materialized View query generation
ORA-10981: dscn computation-related event in replication
ORA-10982: event to turn off CDC-format MV Logs
ORA-10983: event to enable Create_Change_Table debugging
ORA-10984: subquery materialized view-related event
ORA-10985: event for NULL refresh of materialized views
ORA-10986: donot use HASH_AJ in refresh
ORA-10987: event for the support of caching table with object feature
ORA-10988: event to get exclusive lock during materialized view refresh in IAS
ORA-10989: event to internally create statistics MV
ORA-10996: event to make a process hold a latch in ksu
ORA-10999: do not get database enqueue name

在oracle9i中我们知道能够使用跳跃式索引扫描(index skip scan).然而,能利用跳跃式索引扫描的情况其实是有些限制的. 从oracle的文档中我们可以找到这样的话: index skip scans index skip scans improve index scans by nonprefix columns. often, scanning index blocks is faster than scanning table data blocks. skip scanning lets a composite index be split logically into smaller subindexes. in skip scanning, the initial column of the composite index is not specified in the query. in other words, it is skipped. the number of logical subindexes is determined by the number of distinct values in the initial column. skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index. 也可以这样说,优化器根据索引中的前导列(索引到的第一列)的唯一值的数量决定是否使用skip scan. 我们首先做个测试： sql> create table test as 2 select rownum a,rownum-1 b ,rownum-2 c,rownum-3 d,rownum-4 e 3 from all_objects 4 / sql> select distinct count (a) from test; count(a) ---------- 28251 表已创建。 sql> sql> create index test_idx on test(a,b,c) 2 / 索引已创建。 sql> analyze table test compute statistics 2 for table 3 for all indexes 4 for all indexed columns 5 / 表已分析。 sql> set autotrace traceonly explain sql> select * from test where b = 99 2 / execution plan ---------------------------------------------------------- 0 select statement optimizer=choose (cost=36 card=1 bytes=26) 1 0 table access (full) of test (cost=36 card=1 bytes=26) --可见这里cbo选择了全表扫描. --我们接着做另一个测试: sql> drop table test; 表已丢弃。 sql> create table test 2 as 3 select decode(mod(rownum,2), 0, 1, 2 ) a, 4 rownum-1 b, 5 rownum-2 c, 6 rownum-3 d, 7 rownum-4 e 8 from all_objects 9 / 表已创建。 sql> set autotrace off sql> select distinct a from test; a -- 1 2 --a列只有两个唯一值 sql> create index test_idx on test(a,b,c) 2 / 索引已创建。 sql> analyze table test compute statistics 2 for table 3 for all indexes 4 for all indexed columns 5 / 表已分析。 sql> set autotrace traceonly explain sql> select * from test where b = 99 2 / execution plan ---------------------------------------------------------- 0 select statement optimizer=choose (cost=4 card=1 bytes=24) 1 0 table access (by index rowid) of test (cost=4 card=1 bytes=24) 2 1 index (skip scan) of test_idx (non-unique) (cost=3 card=1) oracle的优化器(这里指的是cbo)能对查询应用index skip scans至少要有几个条件: 1 优化器认为是合适的. 2 索引中的前导列的唯一值的数量能满足一定的条件. 3 优化器要知道前导列的值分布(通过分析/统计表得到) 4 合适的sql语句 ...... 更多信息请参考: http://www.itpub.net/showthread.php?threadid=85948 http://www.cnoug.org/bin/ut/topic_show.cgi?id=608&h=1&bpg=1&age=100 http://www.itpub.net/showthread.php?s=&postid=985602#post985602 oracle9i database performance tuning guide and reference release 2 (9.2) part number a96533-02 感谢参加讨论的各位高手. 文章整理：西部数码--专业提供域名注册、虚拟主机服务 http://www.west263.com 以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息，谢谢!

作者：eygle | English Version 【转载时请以超链接形式标明文章出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2008/06/9208_10204_patch.html

---

站内相关文章|Related Articles

11g Patchset 1 11.1.0.7 For Linux发布

10g 10.2.0.4 PatchSet AIX5L版本发布

10g 10.2.0.4 PatchSet Windows版发布

Solaris如何查看安装版本及Patch列表

Oracle关于Patchset 10.2.0.4的提醒邮件

上周在大庆客户现场，帮助用户将3个数据库从Oracle 9.0.1.0升级到Oracle 9.2.0.8，现在已经很少有人在使用Oracle 9.0版本的数据库了。
而9.2.0.8是Oracle 9i的最后一个修正补丁，使用9i的用户可以开始考虑评估将9i迁移到Oracle10g了。

为了方便，将9.2.0.8 及常用的 10.2.0.4 的补丁下载地址列举在这里，如果没有Metalink帐号，你可以通过迅雷来尝试下载这些Patch.
oracle 9208 patch:

ftp://updates.oracle.com/4547809/p4547809_92080_AIX64-5L.zip
ftp://updates.oracle.com/4547809/p4547809_92080_AXP.zip
ftp://updates.oracle.com/4547809/p4547809_92080_HP64.zip
ftp://updates.oracle.com/4547809/p4547809_92080_HPUX-IA64.zip
ftp://updates.oracle.com/4547809/p4547809_92080_LINUX-S390.zip
ftp://updates.oracle.com/4547809/p4547809_92080_LINUX.zip
ftp://updates.oracle.com/4547809/p4547809_92080_Linux-IA64.zip
ftp://updates.oracle.com/4547809/p4547809_92080_Linux-x86-64.zip
ftp://updates.oracle.com/4547809/p4547809_92080_MVS.zip
ftp://updates.oracle.com/4547809/p4547809_92080_SOLARIS.zip
ftp://updates.oracle.com/4547809/p4547809_92080_SOLARIS64.zip
ftp://updates.oracle.com/4547809/p4547809_92080_TRU64.zip
ftp://updates.oracle.com/4547809/p4547809_92080_WINNT.zip
ftp://updates.oracle.com/4547809/p4547809_92080_WINNT64.zip

oracle 10.2.0.4 patch:

ftp://updates.oracle.com/6810189/p6810189_10204_AIX5L.zip
ftp://updates.oracle.com/6810189/p6810189_10204_HPUX-IA64.zip
ftp://updates.oracle.com/6810189/p6810189_10204_HPUX-64.zip
ftp://updates.oracle.com/6810189/p6810189_10204_Solaris-64.zip
ftp://updates.oracle.com/6810189/p6810189_10204_Linux-x86-64.zip
ftp://updates.oracle.com/6810189/p6810189_10204_Linux-x86.zip
ftp://updates.oracle.com/6810189/p6810189_10204_Win32.zip
ftp://updates.oracle.com/6810189/p6810189_10204_MSWIN-x86-64.zip

-The End-

Oracle Undocumented Parameters

select  ksppinm 
from    x$ksppi  
where   substr(ksppinm,1,1) = '_';  
  
-----------------------------------------------------------------------
  
The following is a list of undocumented parameters.  
  
1. _db_block_cache_protect    
    On VMS, the DB_BLOCK_CACHE_PROTECT mechanism has been made much  
    faster.  During normal use, having it turned on shouldn't be    
    noticeable (the degradation is less than 1%).  Developers who    
    link non-share will need PSWAPM privilege to use this feature.    
    When DB_BLOCK_CACHE_PROTECT is turned on, developers may either    
    use the VMS mailboxes with the M (MEMORY_LOG) command    
    or they may just examine the ring buffer in the PGA (index    
    SMPREI_, array SMPREB_) to determine what buffer requests have    
    been made recently.    
    DB_BLOCK_CACHE_PROTECT will prevent certain corruption from getting to    
    disk; although, it may crash the foreground of the instance.  It will help  
   
    catch stray writes in the cache. When you try to write past the buffer     
    size in the sga, it will fail first with a stack violation.    
    It seems that the db_block_cache_protect has a significant performance    
    overhead.  Preliminary testing shows that it has considerable overhead    
    (a single update took twice as long with the parameter set to TRUE).    
    
2. _db_block_compute_checksums    
    There is another new init.ora parameter, DB_BLOCK_COMPUTE_CHECKSUMS, that  
    controls whether a checksum is put into every block before the block is  
    written to disk.  The default is FALSE.  A block read validates an  
    exiting checksum whether or not this option is enabled.  A block is marked  
    as corrupt if a checksum fails.  
    It helps determine corruption due to hardware problems.  The incarnation  
    number and the sequence number are added to the end of the block to help   
    catch corruption.  
    If the problem (corruption) is in the middle of the block  
    this test will not detect it. To detect this problem a checksum may be  
    generated in the block header before every write and verified on every
    read.  
    
3. _db_block_hash_buckets= "Number of database block hash buckets"    
    The number of hash buckets is  
    a) by default to be set to a prime number;    
    b) coerced to a prime if there is an init.ora parameter setting.    
    The value, if not a prime number > 2, is rounded up to the next highest   
    prime.  
    I would tend not to change it unless there is latch contention on the hash  
    chains. raising it to equal the number of buffers would clearly remove any  
    contention (basically, this is just saying that each buffer lives on its  
    own hash chain).  Having it set too small would mean that we might have to 
    scan over lots of buffers to find the one we want. I think the default is to   
    make it 1/4 of the total number of buffers  
    
4. _db_block_multiple_hashchain_latches    
    "Use one latch per hash chain"  
    
5. _db_handles    
    "System-wide simultaneous buffer operations"  
    
6. _db_handles_cached    
    "Buffer handles cached each process"  
    
7. _wait_for_sync    
    " Wait for sync on commit "  
    Wait_for_sync is an oracle generic parameter which, when set to  
    false, will allow the system to complete commits without waiting  
    for the redo-log buffer flushes to complete.  
  
8. _db_block_max_scan_cnt="Maximum number of buffers to inspect when  
    looking for free buffer"  
    DB_BLOCK_MAX_SCAN_CNT is an init.ora parameter which specifies  
    the number of unavailable buffers a process should scan before signaling  
    DBWR to write dirty buffers from the buffer cache to disk.  
    
9. _db_writer_scan_depth    
    "Number of LRU buffers for dbwr to scan when looking for dirty buffers"  
    
10a. _db_writer_scan_depth_increment    
    "Add to dbwr scan depth when dbwr is behind"  
    
10b. _db_writer_scan_depth_decrement  
    Subtract from dbwr scan depth when dbwr is working too hard  
    
11. _db_large_dirty_queue  
    "Number of buffers which force dirty queue to be written  
    
12. _db_block_write_batch    
    Number of blocks to group in each DB Writer IO  
    specifies the no of blocks to be written to the disk in one write operation.  
    Should be increased till write wait time and write complete waits starts 
    to increase.  
    DBWR Free Low is the number of times DBWR is invoked because a user  
    process found at least DB_BLOCK_WRITE_BATCH/2 buffers on the dirty list.  
    This parameter specifies the number of blocks which should be written to   
    disk at one time.  
    This parameter should only be increased until the statistics  
    Write Complete Waits and Write Wait Time show growth.  Write Complete   
    Waits is the number of times a process waited for DBWR   
    to write a current block before making a change to a buffer.  
  
13. _db_block_cache_clone  
    "Always clone data blocks on get (for debugging)"  
    This parameter setting has a significantly adverse affect on performance  
    and we were told to run without it.  
    
14. _controlfile_enqueue_timeout  
    /* use parameter value (default is 900) */  
    /* System Parameter: control file enqueue timeout in seconds */  
    
15. _db_no_mount_lock    
    add hidden parameter to not acquire mount lock  
    If hidden int.ora parameter _db_no_mount_lock is set to TRUE  
    then no mount locks are acquired when the the database is mounted  
    exlusive. This allows two databases with the same name to be  
    simultaneously mounted. Note that this circumvents the mechanism  
    to prevent two simultaneous startups of the same database, and is  
    thus a dangerous parameter to set. This only affects ports that  
    ar compiled with the SYS_DFS option enabled (i.e. VMS only).  
    It does not get a mount lock on the database name during startup.  
    This allows 2 instances with the same name to run on one machine  
  
16. _log_checkpoint_recovery_check  
    Check redo log after checkpoints.  
    Add debugging code to check the red log after a checkpoint.  This  
    code is intended to help find a problm that is generating ora-600 [3020]  
    during recovery.  This code is enabed with a new init.ora parameter:  
    _log_checkpoint_recovery_check=XXX, where XXX is the number of redo  
    blocks to check.   This is called in LGWR after every checkpoint. If the  
    init.ora parameter "_log_checkpoint_recovery_check" is zero (default)  
    it does nothing. If it is a positive value then that many blocks of  
    redo are scanned to see that the data file blocks on disk could be  
    recovered if there was an immediate crash. This code was introduced  
    to catch an elusive bug that results in OERI(3020) errors  
    occasionally during crash recovery.  
  
17. _switch_on_stuck_recovery  
    REDO GENERATION HAS BEEN CHANGED SO THAT WE SHOULD NEVER ENCOUNTER  
    A CHANGE IN THE FUTURE OF THE BLOCK. APPLYING REDO IN SCN ORDER NOW  
    INSURES THAT CHANGES ARE APPLIED IN INCARNATION/SEQUENCE # ORDER. THERE  
    IS A LOT OF CODE IN RECOVERY THAT IS NO LONGER NEEDED BECAUSE OF THIS  
    CHANGE. THIS INCLUDES THE CODE TO BACKUP AND RESCAN AFTER REPAIRING  
    CORRUPTIONS. THE CODE IS BEING LEFT IN TO AVOID LARGE CHANGES JUST BEFORE  
    V7.0 PRODUCTION RELEASE. THE FOLLOWING WILL INSURE THE UNNEEDED CODE  
    IS NEVER EXECUTED UNLESS REDO WAS GENERATED BY A BETA RELEASE OR THE SECRET  
    INIT.ORA PARAMETER _SWITCH_ON_STUCK_RECOVERY IS SET TO TRUE.  
    Check redo Log contents after checkpoint. This is called in LGWR after  
    every checkpoint. If this parameter is zero (default) it does nothing.   
    If it is a positive value then that many blocks of redo are scanned to see 
    that the data file blocks on disk could be recovered if there was an   
    immediate crash. This code was introduced to catch an elusive bug that  
    results in OERI(3020) errors occasionally during crash recovery.  
    Checkpoint recovery check: this is the number of redo blocks that   
    kcracl will verify after every LGWR generated checkpoint. Defaults to zero 
    for no checking.   When opening the named offline log for redo application 
    and to recalculate future change thread switching this parameter is used.  
      
18. _log_io_size=redo log IO chunk size (blocks/write)    
    /* System Parameter: IO chunk size */  
    1. that the value is o/s dependent.  
    2. if left at 0, the value will be automatically determined for each log  
       file.  
    
19. _log_buffers_debug    
    /* debugging: fill redo buffers with [well known] junk after writes */  
    "debug redo buffers (slows things down)"  
  
20. _log_debug_multi_instance    
    /* debugging : pretend multi-instance */  
    "debug redo multi instance code"  
    
21. _log_entry_prebuild_threshold    
    /* redo entry pre-build threshold */  
    /* this is a bad idea for a uniprocessor , and is only helpful for a  
    multiprocessor when there is latch contention */  
    LOG_ENTRY_PREBUILD_THRESHOLD determines the maximum size of a redo entry  
    to prebuild before the copy to the log buffer.  Increasing this parameter  
    reduces the time that the redo copy latch is held.  This parameter should  
    not be modified if it is a single processor environment or there will be  
    memory contention.  
    
22. _disable_logging    
    If this is true, redo records will not be generated -- no recovery is possible  
    if the instance crashes.  It is mainly used for getting good benchmarking  
    results.  
    Default is false  
    
23. _log_blocks_during_backup    
    TRUE value implies before any change is made to a db_block in the buffer   
    cache, a *complete image* of the block is copied to the redo   
    redo log.  (This explains why excessive redo would be generated for   
    datafiles excessive redo would be generated for datafiles in hot backup  
    mode.) There is a new init.ora parameter, LOG_BLOCKS_DURING_BACKUP,  
    that controls whether block images ar written to the redo log during  
    hot backup.    
    Default is TRUE for VM, but port specific with the default defined in  
    sparams.h.  This may beset to FALSE if the Oracle block size equals  
    the O/S physical sector sie or if it is otherwise ensured that hot backup  
    reads consistent versios of blocks even if those blocks are being written  
    at the time.  Put anther way, this may be set to FALSE on machines that  
    can guarantee the aomicity of a single block I/O request.  
    Default is true  
    Problem is due to split blocks.  
    
24. _allow_resetlogs_corruption    
    Added new secret init.ora parameter to override error 1138.    
    When set to TRUE the  
    resetlogs option will be allowed even if there are hot backups that need  
    more redo applied. Unless you are certain that absolutely all redo, includig  
    the online logs, has been applied, then a full export and import mst be  
    done to insure the database is internally consistant.  
    
    from 6.0 code  
    /* if we crashed/shutdown during a hot backup it is over now and we  
    ** are no longer logging blocks. If they did a manual recovery  
    ** followed by a NORESETLOGS then the hot backup recovery flag can  
    ** be cleared. The hot backup recovery flag may alsoneed clearing  
    ** if RESETLOGS was allowed because of _allow_resetlogs_corruption  
    ** parameter. */  
    
    from the c file kcv.c 7.0 code  
   /* if the resetlogs option is in effect we save the highest checkpoint    
   ** scn that we see, to be used as the incomplete recovery scn. It only    
   ** gets used if a resetlogs is done without any recovery. Only backup    
   ** control file recovery will be allowed and it always rewrites the    
   ** incomplete recovery scn. We use the highest scn we can find so that    
   ** new resetlogs scn will be greater than anything in the database.    
   ** This may not be strictly true if the user sets    
   ** _allow_resetlogs_corruption, and there is a fuzzy file. Note that   
   ** since resetlogs does not look at the log file headers, datafile    
   ** checkpoints are all we have to look at.   
  
25. _reuse_index_loop  
    "number of blocks being examine for index block reuse"  
    /* secret system parameter to control how agressive we should walk the free  
    ** list when attempting to reuse block - default is 5.  
    ** Set to 0 for fast index operation which is susceptible to growth,  
    ** Set to > 5 for slower index op but more agressive in reusing blocks  */  
	Controls the amount of work done when looking for a block to reusse
    for n index entry.  The value determines the number of blocks to   
	check on the freelist when looking for a reusable block.    
  
26. _mts_load_constants    
    /* hidden init.ora to set server load balancing constants */  
    /* fill in load balancing parameters (from _mts_load_constants) */  
    * PID Controller - calculate control on number of servers using:  
    * control = Kp * err + Kd * delta(err) + Ki * sum(err)  
    * where Kp = proportional, Kd = derivative constant, Ki  = integral constant  
    * Kp,Kd,Ki can be changed with the hidden parameter _mts_load_constants  
    * in order to tune the system as desired.  
    This values should only be changed after gathering enough information to  
    determine that the mts is not optimal.  
  
27. _mts_fastpath  
    /* hidden init.ora to enable dispatcher fastpath */  
    default is false  
     * Return TRUE if the system should implement the dispatcher network  
     * fastpath.  When enabled, the dispatcher fastpath causes network i/o  
     * to be buffered and only flushed when absolutely necessary.  When not  
     * enabled, flushes will occur for every dirty virtual circuit buffer.  
    
  
*** The following parameters are from the Kernel SQL Library manager  
    
28. _kgl_multi_instance_lock    
    Only for debugging.  all the  _kgl_multi_instance_xxxx  
    "whether KGL to support multi-instance locks"  
    Default is 0  
  
29. _kgl_multi_instance_pin  
    "whether KGL to support multi-instance pins"  
    Default is 0.  
    
30. _kgl_multi_instance_invalidation  
    "whether KGL to support multi-instance invalidations"  
    Default is 0.  
    
31. _row_cache_instance_locks    
    Kernel SQL Row cache management component, number of row cache instance   
    locks  
    default is 100  
  
32. _row_cache_buffer_size    
    "size of row cache circular buffer"  
    default is 200  
    
33. _messages  
   " message queue resources - dependent on # processes "  
   The asynchronous message mechanism allows  processes  to  send    
   messages  to each other.   A process  may send a  message to a    
   specified other  process (always without waiting for a reply),    
   may wait for  a message to arrive on its queue, and may obtain    
   the next message.   Messages sent  to  detached processes  are    
   reliably delivered.  Messages sent to foreground processes are    
   reliably  delivered  as  long as  the  process is active.  The    
   mechanism also permits sending of a simple "reply", which is a    
   one-byte message without  queuing.  It should normally be used    
   to  reply to  asynchronous  messages,  and  this  is  a  safer    
   technique  than regular messages for  responding to foreground    
   processes.   This mechanism is not used in single process mode.    
    
34. _cpu_count    
    ksb - Kernel Service Background processes  
    "number of cpu's for this instance"  
    CPU_COUNT has to be set on some platforms in order for Oracle to take   
    advantage of multi-processor system, on others it does not have effect on   
    performance since load balancing between processors is handled by the o/s.  
    
35. _debug_sga    
    /* Debug SGA,  don't make the SGA a global section so we can set watchpoints  
    
36. _enqueue_locks    
    ksq1 - Kernal Service enQueues (1)  
    Definitions for enqueues client objects, "locks for managed enqueues"  
    
37. _enqueue_hash    
    "enqueue hash table length"  
    
38. _enqueue_debug_multi_instance    
    "debug enqueue multi instance"  
     KST is a trace facility used for "realtime" tracing of events.  Below  
     are defined KST macros that will enable the tracing of such things as    
     latch operations, memory assignments, etc.  Tracing is done to a per    
     process circular buffer stored in the SGA.  Access to these buffers    
     is via fixed tables.  Tracing is enabled for classes of events,    
     particular events, and ranges of events.  
     The tracing state may be dynamically changed with the following syntax    
     "ALTER TRACING"    
        - "ON"    
          - Processes begin logging events using the current enabled events    
      - "OFF"    
          - Processes stop tracing    
        - "ENABLE"     
          - Add in the events indicated in  to those which are    
            being traced.    
        - "DISABLE"     
          - No longer trace events specified by     
    
39._trace_buffers_per_process    
    Note that by default, tracing is NOT enabled.  In order to enable tracing  
    at instance startup time, add _trace_buffers_per_process = 1  
    
40. _trace_block_size    
    _trace_xxxxx (xxxxx = buffers_per_process, block_size, archive_start,  
                 flushing, enabled, get_time_every, archive_dest etc.)    
    These parameters are only there for debugging purposes. Customers  
    or support will never have to use them.  
    
41. _trace_archive_start  
    "start trace process on SGA initialization"  
    
42. _trace_flushing    
    "TRWR should try to keep tracing buffers clean"  
    
43. _trace_enabled  
    By default, tracing is NOT enabled.  In order to enable tracing,   
    _trace_enabled = true  
    
44. _trace_events    
    If you wish to enable tracing of waits at instance startup time, you can   
    either  
    add the line '_trace_events = "WAT,ALL"' to init.ora or execute   
    'alter tracing enable "WAT,ALL"' in a sqldba session.  
    If you wish to limit tracing to specific events, you can use the  
    the following syntax:  
    alter tracing enable "WAT,,..."  
    where "id" is either a specific event number, or an event range  
    (event number 1 - event number 2).  
    
45. _trace_archive_dest    
    "trace archival destination"  
    
46. _trace_file_size    
    "trace file size"  
    default is 10000 blocks  
    
47. _trace_write_batch_size    
    "trace write batch size"  
    default is 32  
    
48. _rollback_segment_initial    
    "starting undo segment number"  
    Default is 1. DO NOT SPECIFY 0 AS THAT HAPPENS TO BE THE SYSTEM ROLLBACK  
    
49. _rollback_segment_count    
    "number of undo segments"  
    default is 0  
    
50. _offline_rollback_segments    
   If a rollback segment is not accessible because the file it is in    
   is offline or corrupted, one can force the system to come up without    
   the rollback segment by specifying the rollback segment in init.ora    
   paramater '_offline_rollback_segments'. The system will come up by    
   estimating the current system commit time since it cannot access    
   transaction tble in the rollback segment. The system commit number    
   is a conservative guess based on current time, the database creation    
   time and the assumed transaction  rate of 8000 tps. ONE MUST MAKE    
   SURE THAT THE SYSTEM TIME IS SET CORRECTLY WHEN FORCING THE SYSTEM UP    
   USING '_OFFLINE_ROLLBACK_SEGENTS'. A trace is written with information    
   about the estimated system commit number.    
  
51. _corrupted_rollback_segments    
    Mark a rollback segment as corrupted.  
  
52. _label_tag_cache_size    
    /* hidden size of the SGA label tag comparison cache (bitcache) */  
     "number of tags in the label tag comparison cache"  
    default is 200  
  
53. _trace_get_time_every    
    "Number of trace sequence numbers per call to slgcs()"  
    default is 200  
    
54. _vms_bg_priority    
    "VMS process priority to give to background processes"  
    default is 4  
    
55. _sort_use_os_files_as_temporaries    
     Use O/S files rather than temp segments for sorting.   
    
56. _log_checkpoints_to_alert    
    Whether checkpoint messages should be written to alert.log or not. Turned   
    off in benchmarks.  
       
57. _large_memory_system  :    
    Used in internal benchmarks. Doesn't concern us.  
    "Configure system to use memory and save cpu cycles, paging, etc  
    default is false  
  
58. _wakeup_timeout  
   This is WMONs sleeptime between checks of it's queue of processes to wake.   
  
59. _latch_wait_posting  
   enable posting of processes that may be waiting for a latch after a process  
   
   frees the same latch (set this parameter to a value greater than one  
   this parameter to a value greater than one for it to take effect).  
  
60. _sql_connect_capability_code  
   allows database links from a pre-7.0.15 release to work with release 7.1.  
   It is necessary to set this parameter for database links from a V6 ?

这里理清下ORACLE的认证问题:

ORACLE认证分为:

1.操作系统认证:

条件:

由$ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA

文件中的:

SQLNET.AUTHENTICATION_SERVICES

来指定

其中

=(NONE)不使用操作系统认证

=(NTS)启用操作系统认证

此外,登陆OS的用户必须属于ORA_DBA组(WINDOWS)

DBA(AIX)

2.密码文件认证:

相关初试参数:REMOTE_LOGIN_PASSWORDFILE

NONE

Oracle ignores any password file. Therefore, privileged users must be authenticated by the operating system.

SHARED

More than one database can use a password file. However, the only user recognized by the password file is SYS.

EXCLUSIVE

The password file can be used by only one database and the password file can contain names other than SYS.

当值为NONE时,不使用密码文件认证

实验:

SYS口令改为SYS

SQL> alter user sys identified by "sys";

用户已更改。

SCENE1#

修改

SQLNET.AUTHENTICATION_SERVICES=(NTS)

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

结果:

C:Documents and SettingsAdministrator>sqlplus "fdas/fdas as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 12月 18 15:57:59 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> SHOW USER
USER 为"SYS"

SCENE2#

修改

SQLNET.AUTHENTICATION_SERVICES=(NONE)

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

结果:

C:Documents and SettingsAdministrator>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 12月 18 16:00:53 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-01031: insufficient privileges

请输入用户名:

C:Documents and SettingsAdministrator>sqlplus "fdas/fdas as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 12月 18 15:59:37 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-01031: insufficient privileges

请输入用户名:
ERROR:
ORA-01017: 无效的用户名/口令；拒绝登录

C:Documents and SettingsAdministrator>sqlplus "sys/sys as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 12月 18 15:59:47 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> --使用正确的口令成功

C:Documents and SettingsAdministrator>sqlplus "sys/sys@mydb as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 12月 18 16:01:59 2006
--使用远程登陆也可以成功

SCENE3#

修改:

SQLNET.AUTHENTICATION_SERVICES=(NONE)

SQL> alter system set remote_login_passwordfile=NONE scope=spfile;

系统已更改。

SQL> STARTUP FORCE
--修改remote_login_passwordfile要重起

结果:

C:Documents and SettingsAdministrator>sqlplus "sys/sys as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 12月 18 15:34:52 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

C:Documents and SettingsAdministrator>sqlplus "sys/sys@mydb as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 12月 18 15:34:52 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

--如果操作系统和密码文件认证均关闭,无法使用SYSDBA角色登陆.

但是普通用户可以通过数据库认证登陆

library load lock:

The session tries to find the load lock for the database object so that it can load the object.
The load lock is always obtained in Exclusive mode, so that no other process can load the same object.

If the load lock is busy the session will wait on this event until the lock becomes available.

library cache lock

This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either:


One client can prevent other clients from accessing the same object

The client can maintain a dependency for a long time (for example, no other client can change the object)
This lock is also obtained to locate an object in the library cache.

library cache pin

This event manages library cache concurrency. Pinning an object causes the heaps to be loaded into memory. If a client wants to modify or examine the object, the client must acquire a pin after the lock.

library中的对象，首先有一个handle结构，相当于一个头信息结构，library cache lock锁定的就是这个，然后这个handle指向该object具体的存储部分，这些部分就是heap，library cache pin保护的就是这些heap

library cache pin 主要是sql语句execute 的时候,必须保证所有的handle 对应的lco 在library cache 中,这个pin 就是避免其他的进程将lco 对应的heap 交换出内存,当然如果lco 不在library cahce 中，就会进行load

8.1.6 子集/超集列表
===========================
A.当前字符集 B.可以修改到的新字符集(A的超集)
------------------- --------------------------------
US7ASCII WE8DEC
US7ASCII US8PC437
US7ASCII WE8PC850
US7ASCII IN8ISCII
US7ASCII WE8PC858
US7ASCII WE8ISO8859P1
US7ASCII EE8ISO8859P2
US7ASCII SE8ISO8859P3
US7ASCII NEE8ISO8859P4
US7ASCII CL8ISO8859P5
US7ASCII AR8ISO8859P6
US7ASCII EL8ISO8859P7
US7ASCII IW8ISO8859P8
US7ASCII WE8ISO8859P9
US7ASCII NE8ISO8859P10
US7ASCII TH8TISASCII
US7ASCII BN8BSCII
US7ASCII VN8VN3
US7ASCII VN8MSWIN1258
US7ASCII WE8ISO8859P15
US7ASCII WE8NEXTSTEP
US7ASCII AR8ASMO708PLUS
US7ASCII EL8DEC
US7ASCII TR8DEC
US7ASCII LA8PASSPORT
US7ASCII BG8PC437S
US7ASCII EE8PC852
US7ASCII RU8PC866
US7ASCII RU8BESTA
US7ASCII IW8PC1507
US7ASCII RU8PC855
US7ASCII TR8PC857
US7ASCII CL8MACCYRILLICS
US7ASCII WE8PC860
US7ASCII IS8PC861
US7ASCII EE8MACCES
US7ASCII EE8MACCROATIANS
US7ASCII TR8MACTURKISHS
US7ASCII EL8MACGREEKS
US7ASCII IW8MACHEBREWS
US7ASCII EE8MSWIN1250
US7ASCII CL8MSWIN1251
US7ASCII ET8MSWIN923
US7ASCII BG8MSWIN
US7ASCII EL8MSWIN1253
US7ASCII IW8MSWIN1255
US7ASCII LT8MSWIN921
US7ASCII TR8MSWIN1254
US7ASCII WE8MSWIN1252
US7ASCII BLT8MSWIN1257
US7ASCII N8PC865
US7ASCII BLT8CP921
US7ASCII LV8PC1117
US7ASCII LV8PC8LR
US7ASCII LV8RST104090
US7ASCII CL8KOI8R
US7ASCII BLT8PC775
US7ASCII WE8DG
US7ASCII WE8NCR4970
US7ASCII WE8ROMAN8
US7ASCII WE8MACROMAN8S
US7ASCII TH8MACTHAIS
US7ASCII HU8CWI2
US7ASCII EL8PC437S
US7ASCII LT8PC772
US7ASCII LT8PC774
US7ASCII EL8PC869
US7ASCII EL8PC851
US7ASCII CDN8PC863
US7ASCII HU8ABMOD
US7ASCII AR8ASMO8X
US7ASCII AR8NAFITHA711T
US7ASCII AR8SAKHR707T
US7ASCII AR8MUSSAD768T
US7ASCII AR8ADOS710T
US7ASCII AR8ADOS720T
US7ASCII AR8APTEC715T
US7ASCII AR8NAFITHA721T
US7ASCII AR8HPARABIC8T
US7ASCII AR8NAFITHA711
US7ASCII AR8SAKHR707
US7ASCII AR8MUSSAD768
US7ASCII AR8ADOS710
US7ASCII AR8ADOS720
US7ASCII AR8APTEC715
US7ASCII AR8MSAWIN
US7ASCII AR8NAFITHA721
US7ASCII AR8SAKHR706
US7ASCII AR8ARABICMACS
US7ASCII LA8ISO6937
US7ASCII JA16VMS
US7ASCII JA16EUC
US7ASCII JA16SJIS
US7ASCII KO16KSC5601
US7ASCII KO16KSCCS
US7ASCII KO16MSWIN949
US7ASCII ZHS16CGB231280
US7ASCII ZHS16GBK
US7ASCII ZHT32EUC
US7ASCII ZHT32SOPS
US7ASCII ZHT16DBT
US7ASCII ZHT32TRIS
US7ASCII ZHT16BIG5
US7ASCII ZHT16CCDC
US7ASCII ZHT16MSWIN950
US7ASCII AL24UTFFSS
US7ASCII UTF8
US7ASCII JA16TSTSET2
US7ASCII JA16TSTSET
8.1.7新增
===============
US7ASCII ZHT16HKSCS
US7ASCII KO16TSTSET
WE8DEC TR8DEC
WE8DEC WE8NCR4970
WE8PC850 WE8PC858
D7DEC D7SIEMENS9780X
I7DEC I7SIEMENS9780X
WE8ISO8859P1 WE8MSWIN1252
AR8ISO8859P6 AR8ASMO708PLUS
AR8ISO8859P6 AR8ASMO8X
IW8EBCDIC424 IW8EBCDIC1086
IW8EBCDIC1086 IW8EBCDIC424
LV8PC8LR LV8RST104090
DK7SIEMENS9780X N7SIEMENS9780X
N7SIEMENS9780X DK7SIEMENS9780X
I7SIEMENS9780X I7DEC
D7SIEMENS9780X D7DEC
WE8NCR4970 WE8DEC
WE8NCR4970 TR8DEC
AR8SAKHR707T AR8SAKHR707
AR8MUSSAD768T AR8MUSSAD768
AR8ADOS720T AR8ADOS720
AR8NAFITHA711 AR8NAFITHA711T
AR8SAKHR707 AR8SAKHR707T
AR8MUSSAD768 AR8MUSSAD768T
AR8ADOS710 AR8ADOS710T
AR8ADOS720 AR8ADOS720T
AR8APTEC715 AR8APTEC715T
AR8NAFITHA721 AR8NAFITHA721T
AR8ARABICMAC AR8ARABICMACT
AR8ARABICMACT AR8ARABICMAC
KO16KSC5601 KO16MSWIN949
WE16DECTST2 WE16DECTST
WE16DECTST WE16DECTST2
9.0.1新增
===============
US7ASCII BLT8ISO8859P13
US7ASCII CEL8ISO8859P14
US7ASCII CL8ISOIR111
US7ASCII CL8KOI8U
US7ASCII AL32UTF8
BLT8CP921 BLT8ISO8859P13
US7ASCII AR8MSWIN1256
UTF8 AL32UTF8 (added in patchset 9.0.1.2)
9.0.1起被废弃的
=======================================================
US7ASCII AR8MSAWIN
AR8ARABICMAC AR8ARABICMACT
9.2.0 新增
===============
US7ASCII JA16EUCTILDE
US7ASCII JA16SJISTILDE
US7ASCII ZHS32GB18030
US7ASCII ZHT32EUCTST
WE8ISO8859P9 TR8MSWIN1254
LT8MSWIN921 BLT8ISO8859P13
LT8MSWIN921 BLT8CP921
BLT8CP921 LT8MSWIN921
AR8ARABICMAC AR8ARABICMACT
ZHT32EUC ZHT32EUCTST
UTF8 AL32UTF8
9.2.0起被废弃
=======================================================
LV8PC8LR LV8RST104090

此前认为不行，在itpub看到帖子，自己做下试验^ ^

1)建立测试表

sqlplus "/ as sysdba"

SQL> create table changetbs
2 (id number)
3 tablespace system;

表已创建。

SQL> select owner,table_name,tablespace_name from dba_tables
2 where table_name='CHANGETBS';

OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYS CHANGETBS SYSTEM

2）设置测试用户权限

SQL> GRANT DBA TO TEST;

授权成功。

SQL> REVOKE UNLIMITED TABLESPACE FROM TEST;

一定要回收该权限

3）导出和导入

C:Documents and SettingsAdministrator>EXP SYSTEM/SYSTEM TABLES=SYS.CHANGETBS FILE=E:DMP1018

Export: Release 9.2.0.4.0 - Production on 星期三 10月 18 12:20:36 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径 ...
当前的用户已更改为 SYS
. . 正在导出表 CHANGETBS 0 行被导出
在没有警告的情况下成功终止导出。

C:Documents and SettingsAdministrator>IMP SYSTEM/SYSTEM FROMUSER=SYS TOUSER=TEST FILE=E:DMP1018

Import: Release 9.2.0.4.0 - Production on 星期三 10月 18 12:29:10 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件
已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将SYS的对象导入到 TEST
. . 正在导入表 "CHANGETBS" 0行被导入
成功终止导入，但出现警告。

4）验证

SQL> select owner,table_name,tablespace_name from dba_tables
2 where table_name='CHANGETBS';

OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYS CHANGETBS SYSTEM
TEST CHANGETBS USERS

当使用NOCATALOG模式备份的数据，丢失控制文件后，是无法使用RMAN恢复的，这时就需要使用DBMS_BACKUP_RESTORE来进行恢复：

--先备份全库包括控制文件和归档日志，注意保留日志以备用rman target / nocatalog log=e:baklog.txt

RMAN> run
2> {
3> allocate channel c1 type disk;
4> backup
5> format 'e:dbbak_%t_%s_%p'
6> (database include current controlfile);
7> sql 'alter system archive log current';
8> backup
9> format 'e:logbak_%t_%s_%p'
10> (archivelog all delete input);
11> release channel c1;
12> }

————————————————————————

C:Documents and SettingsAdministrator>del E:ORACLEORADATAMYDB*.*
E:ORACLEORADATAMYDB*.*, 是否确认(Y/N)? y

删除所有数据文件


SQL> startup
ORACLE 例程已经启动。

Total System Global Area 160505408 bytes
Fixed Size 454208 bytes
Variable Size 134217728 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-00205: ?????????????????????

启动找不到控制文件

*****************************
开始恢复控制文件
SQLPLUS "/ AS SYSDBA"
STARTUP NOMOUNT

SQL> declare
2 devtype varchar2(256);
3 done boolean;
4 begin
5 devtype:=sys.dbms_backup_restore.deviceAllocate(ident=>'t1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlfileTo('E:control01.ctl');
8 sys.dbms_backup_restore.restoreBackupPiece(handle=>'e:DBBAK_602275590_7_1',done=>done);
9 sys.dbms_backup_restore.deviceDeallocate;
10 end;
11 /

PL/SQL 过程已成功完成。

SQL> HOST DIR E:CONTROL*
驱动器 E 中的卷是 Lib
卷的序列号是 A887-368B

E: 的目录

2006-09-27 19:24 1,585,152 CONTROL01.CTL
1 个文件 1,585,152 字节
0 个目录 3,168,002,048 可用字节

控制文件恢复，将该文件拷贝到原来控制文件目录

SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string E:oracleoradatamydbcontrol
01.ctl, E:oracleoradatamydb
control02.ctl, E:oracleorad
atamydbcontrol03.ctl

SQL> host copy e:control01.ctl E:oracleoradatamydbcontrol01.ctl
已复制 1 个文件。

SQL> host copy e:control01.ctl E:oracleoradatamydbcontrol02.ctl
已复制 1 个文件。

SQL> host copy e:control01.ctl E:oracleoradatamydbcontrol03.ctl
已复制 1 个文件。

SQL> alter database mount;

数据库已更改。

-----控制文件恢复完成

***************************************************
开始恢复数据文件

RMAN> crosscheck backup;

分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=10 devtype=DISK
交叉校验备份段: 找到为 'EXPIRED'
备份段 handle=E:DOWNLOADSRMANBAK_602077220_4_1 recid=1 stamp=602077222
交叉校验备份段: 找到为 'EXPIRED'
备份段 handle=E:DOWNLOADSCTLBAK recid=2 stamp=602081377
交叉校验备份段: 找到为 'EXPIRED'
备份段 handle=E:DOWNLOADSARCH60208150561 recid=3 stamp=602081506
已交叉检验的 3 对象

RMAN> list backup of database;

备份集列表
===================

BS 关键字 类型 LV 大小 设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
1 Full 181M DISK 00:01:23 25-9月 -06
BP 关键字: 1 状态: EXPIRED 标记:TAG20060925T114019
段名:E:DOWNLOADSRMANBAK_602077220_4_1
备份集 1 中的数据文件列表
文件 LV 类型 Ckp SCN Ckp 时间 名称
---- -- ---- ---------- ---------- ----
1 Full 588787 25-9月 -06 E:ORACLEORADATAMYDBSYSTEM01.DBF
2 Full 588787 25-9月 -06 E:ORACLEORADATAMYDBUNDOTBS01.DBF
3 Full 588787 25-9月 -06 E:ORACLEORADATAMYDBINDX01.DBF
5 Full 588787 25-9月 -06 E:ORACLEORADATAMYDBUSERS01.DBF
6 Full 588787 25-9月 -06 E:ORACLEORADATAMYDBBIS_1.DMP
7 Full 588787 25-9月 -06 E:ORACLEORADATAMYDBTESTTBS_01.DBF
8 Full 588787 25-9月 -06 E:ORACLEORADATAMYDBRCVCAT_01.ORA

--备份信息没有记录
（如果控制文件在 Full 备份之后单独做的，接下来关掉实例，拷贝控制文件到具体位置，然后 rman 执行 restore database; 即可。
可是，我们这里的情况有些不同．）

--只能再通过DBMS_BACKUP_RESTORE恢复数据文件和归档日志
SQL> declare
2 devtype varchar2(256);
3 done boolean;
4 begin
5 devtype:=sys.dbms_backup_restore.deviceAllocate(ident=>'t1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreDatafileTo(1,'E:ORACLEORADATAMYDBSYSTEM01.DBF');
8 sys.dbms_backup_restore.restoreDatafileTo(2,'E:ORACLEORADATAMYDBUNDOTBS01.DBF');
9 sys.dbms_backup_restore.restoreDatafileTo(3,'E:ORACLEORADATAMYDBINDX01.DBF');
10 sys.dbms_backup_restore.restoreDatafileTo(5,'E:ORACLEORADATAMYDBUSERS01.DBF');
11 sys.dbms_backup_restore.restoreDatafileTo(6,'E:ORACLEORADATAMYDBBIS_1.DMP');
12 sys.dbms_backup_restore.restoreDatafileTo(7,'E:ORACLEORADATAMYDBTESTTBS_01.DBF');
13 sys.dbms_backup_restore.restoreDatafileTo(8,'E:ORACLEORADATAMYDBRCVCAT_01.ORA');
14 sys.dbms_backup_restore.restoreBackupPiece(handle=>'e:DBBAK_602275590_7_1',done=>done
15 sys.dbms_backup_restore.deviceDeallocate;
16 end;
17 /

PL/SQL 过程已成功完成。

SQL> host dir E:ORACLEORADATAMYDB
驱动器 E 中的卷是 Lib
卷的序列号是 A887-368B

E:ORACLEORADATAMYDB 的目录

2006-09-27 19:35 10,489,856 BIS_1.DMP
2006-09-27 19:24 1,585,152 control
2006-09-27 19:24 1,585,152 control01.ctl
2006-09-27 19:24 1,585,152 control02.ctl
2006-09-27 19:24 1,585,152 control03.ctl
2006-09-27 19:35 26,218,496 INDX01.DBF
2006-09-27 19:36 104,861,696 RCVCAT_01.ORA
2006-09-27 19:36 262,148,096 SYSTEM01.DBF
2006-09-27 19:35 10,489,856 TESTTBS_01.DBF
2006-09-27 19:36 209,719,296 UNDOTBS01.DBF
2006-09-27 19:35 26,218,496 USERS01.DBF
11 个文件 656,486,400 字节
2 个目录 2,511,507,456 可用字节

--下面恢复日志

SQL> declare
2 devtype varchar2(256);
3 done boolean;
4 begin
5 devtype:=sys.dbms_backup_restore.deviceAllocate(ident=>'t1');
6 sys.dbms_backup_restore.restoreSetArchivedLog('E:oracleora92rdbms');
7 sys.dbms_backup_restore.restoreArchivedLogRange;
8 sys.dbms_backup_restore.restoreBackupPiece(handle=>'e:LOGBAK_602275688_8_1',done=>done);
9 sys.dbms_backup_restore.deviceDeallocate;
10 end;
11 /

PL/SQL procedure successfully completed.

*********************************
--完成了数据文件和日志文件的恢复，接下来RECOVER database并打开

SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recoveer database using backup controlfile until cancel;
SP2-0734: unknown command beginning "recoveer d..." - rest of line ignored.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 616790 generated at 09/27/2006 16:43:18 needed for thread 1
ORA-00289: suggestion : E:ORACLEORA92RDBMSARC00035.001
ORA-00280: change 616790 for thread 1 is in sequence #35

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 616823 generated at 09/27/2006 18:48:00 needed for thread 1
ORA-00289: suggestion : E:ORACLEORA92RDBMSARC00036.001
ORA-00280: change 616823 for thread 1 is in sequence #36
ORA-00278: log file 'E:ORACLEORA92RDBMSARC00035.001' no longer needed for this recovery

ORA-00279: change 616827 generated at 09/27/2006 18:48:06 needed for thread 1
ORA-00289: suggestion : E:ORACLEORA92RDBMSARC00037.001
ORA-00280: change 616827 for thread 1 is in sequence #37
ORA-00278: log file 'E:ORACLEORA92RDBMSARC00036.001' no longer needed for this recovery

ORA-00308: cannot open archived log 'E:ORACLEORA92RDBMSARC00037.001'
ORA-27041: unable to open file
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 616827 generated at 09/27/2006 18:48:06 needed for thread 1
ORA-00289: suggestion : E:ORACLEORA92RDBMSARC00037.001
ORA-00280: change 616827 for thread 1 is in sequence #37

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

------------------------OVER

问题：客户报告数据库繁忙时就无法连入，提示ORA-03114错误。

情况：

使用telnet到服务器

sqlplus / as sysdba出现长时间等待无法连入

检查ALERT日志

Errors in file /u05/admin/HSMIS/bdump/hsmis_smon_4468.trc:
ORA-27142: could not create new process
skgpspawn failed:category = 27142, depinfo = 11, op = fork, loc = skgpspawn3
Tue Sep 26 14:43:36 2006
Errors in file /u05/admin/HSMIS/bdump/hsmis_smon_4468.trc:
ORA-27142: could not create new process
skgpspawn failed:category = 27142, depinfo = 11, op = fork, loc = skgpspawn3
Tue Sep 26 14:43:37 2006
Errors in file /u05/admin/HSMIS/bdump/hsmis_smon_4468.trc:
ORA-27142: could not create new process

日志hsmis_smon_4468.trc：

*** 2006-09-26 14:43:35.042
SMON: following errors trapped and ignored:
ORA-27142: could not create new process
kxfpgsg
Error 27142 encountered
*** 2006-09-26 14:43:36.094
SMON: following errors trapped and ignored:
ORA-27142: could not create new process
kxfpgsg
Error 27142 encountered
*** 2006-09-26 14:43:37.122
SMON: following errors trapped and ignored:
ORA-27142: could not create new process

监听日志：

26-SEP-2006 14:53:01 * (CONNECT_DATA=(SERVICE_NAME=hsmis)(CID=(PROGRAM=D:crmH0
TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded
TNS-12560: TNS:protocol adapter error
TNS-00510: Internal limit restriction exceeded
IBM/AIX RISC System/6000 Error: 11: Resource temporarily unavailable
26-SEP-2006 14:53:01 * (CONNECT_DATA=(SERVICE_NAME=hsmis)(CID=(PROGRAM=D:crmH0
TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded
TNS-12560: TNS:protocol adapter error
TNS-00510: Internal limit restriction exceeded
IBM/AIX RISC System/6000 Error: 11: Resource temporarily unavailable

分析：根据前面的日志信息，可以推断为某系统资源不足照成的，有以下2种可能：进程数和内存。

检查系统进程数设置：

Maximum number of PROCESSES allowed per user [128]

# ps -ef | grep ora | wc -l
124
当前ora进程数很接近上限，所以判断问题就出在这，修改PROCESSES数到512，问题解决。