Focus on Oracle: May 2009 Archives

关于10g中的X$KCVFH（续2）

By cui hua on May 31, 2009 3:52 PM | No Comments

在"关于10g中的X$KCVFH"和"关于10g中的X$KCVFH（续）"这两篇文章里，我介绍了X$KCVFH中各个字段的详细含义，这里我们再进一步来分析一下X$KCVFH的来源。

在"关于10g中的X$KCVFH（续）"这篇文章里，我是通过把data file #1在X$KCVFH中的记录的内容和data file #1的文件头dump出来的内容做了一个对比从而完善了X$KCVFH中各个字段的含义，这里我们再次定位data file #1。

$ bbed parfile=par.txt

Password:

BBED: Release 2.0.0.0.0 - Limited Production on Sun May 31 14:23:56 2009

************* !!! For Oracle Internal Use only !!! ***************

BBED> set file 1

FILE# 1

BBED> p kcvfh

struct kcvfh, 676 bytes @0

struct kcvfhbfh, 20 bytes @0

ub1 type_kcbh @0 0x0b

ub1 frmt_kcbh @1 0xa2

ub1 spare1_kcbh @2 0x00

ub1 spare2_kcbh @3 0x00

ub4 rdba_kcbh @4 0x00400001

ub4 bas_kcbh @8 0x00000000

ub2 wrp_kcbh @12 0x0000

ub1 seq_kcbh @14 0x01

ub1 flg_kcbh @15 0x04 (KCBHFCKV)

ub2 chkval_kcbh @16 0x339f

ub2 spare3_kcbh @18 0x0000

struct kcvfhhdr, 76 bytes @20

ub4 kccfhswv @20 0x00000000

ub4 kccfhcvn @24 0x0a200100

ub4 kccfhdbi @28 0x5091aeb3

text kccfhdbn[0] @32 I

text kccfhdbn[1] @33 P

text kccfhdbn[2] @34 R

text kccfhdbn[3] @35 A

text kccfhdbn[4] @36 T

text kccfhdbn[5] @37 E

text kccfhdbn[6] @38 S

text kccfhdbn[7] @39 T

ub4 kccfhcsq @40 0x000017b5

ub4 kccfhfsz @44 0x0000f000

s_blkz kccfhbsz @48 0x00

ub2 kccfhfno @52 0x0001

ub2 kccfhtyp @54 0x0003

ub4 kccfhacid @56 0x00000000

ub4 kccfhcks @60 0x00000000

text kccfhtag[0] @64

......省略显示部分内容

text kccfhtag[31] @95

ub4 kcvfhrdb @96 0x00400179

struct kcvfhcrs, 8 bytes @100

ub4 kscnbas @100 0x00000008

ub2 kscnwrp @104 0x0000

ub4 kcvfhcrt @108 0x21a23e84

ub4 kcvfhrlc @112 0x28144e35

struct kcvfhrls, 8 bytes @116

ub4 kscnbas @116 0x00086983

ub2 kscnwrp @120 0x0000

ub4 kcvfhbti @124 0x00000000

struct kcvfhbsc, 8 bytes @128

ub4 kscnbas @128 0x00000000

ub2 kscnwrp @132 0x0000

ub2 kcvfhbth @136 0x0000

ub2 kcvfhsta @138 0x2004 (KCVFHOFZ)

struct kcvfhckp, 160 bytes @484

struct kcvcpscn, 8 bytes @484

ub4 kscnbas @484 0x1697d667

ub2 kscnwrp @488 0x0009

ub4 kcvcptim @492 0x2904b211

ub2 kcvcpthr @496 0x0001

union u, 12 bytes @500

struct kcvcprba, 12 bytes @500

ub4 kcrbaseq @500 0x00000049

ub4 kcrbabno @504 0x00065762

ub2 kcrbabof @508 0x0010

ub1 kcvcpetb[0] @512 0x02

......省略显示部分内容

ub1 kcvcpetb[128] @640 0x00

ub4 kcvfhcpc @140 0x000000c1

ub4 kcvfhrts @144 0x28144e27

ub4 kcvfhccc @148 0x000000c0

struct kcvfhbcp, 160 bytes @152

struct kcvcpscn, 8 bytes @152

ub4 kscnbas @152 0x00000000

ub2 kscnwrp @156 0x0000

ub4 kcvcptim @160 0x00000000

ub2 kcvcpthr @164 0x0000

union u, 12 bytes @168

struct kcvcprba, 12 bytes @168

ub4 kcrbaseq @168 0x00000000

ub4 kcrbabno @172 0x00000000

ub2 kcrbabof @176 0x0000

ub1 kcvcpetb[0] @180 0x00

......省略显示部分内容

ub1 kcvcpetb[128] @308 0x00

ub4 kcvfhbhz @312 0x00000000

struct kcvfhxcd, 16 bytes @316

ub4 space_kcvmxcd[0] @316 0x00000000

ub4 space_kcvmxcd[1] @320 0x00000000

ub4 space_kcvmxcd[2] @324 0x00000000

ub4 space_kcvmxcd[3] @328 0x00000000

word kcvfhtsn @332 0

ub2 kcvfhtln @336 0x0006

text kcvfhtnm[0] @338 S

text kcvfhtnm[1] @339 Y

text kcvfhtnm[2] @340 S

text kcvfhtnm[3] @341 T

text kcvfhtnm[4] @342 E

text kcvfhtnm[5] @343 M

......省略显示部分内容

text kcvfhtnm[29] @367

ub4 kcvfhrfn @368 0x00000001

struct kcvfhrfs, 8 bytes @372

ub4 kscnbas @372 0x00000000

ub2 kscnwrp @376 0x0000

ub4 kcvfhrft @380 0x00000000

struct kcvfhafs, 8 bytes @384

ub4 kscnbas @384 0x00000000

ub2 kscnwrp @388 0x0000

ub4 kcvfhbbc @392 0x00000000

ub4 kcvfhncb @396 0x00000000

ub4 kcvfhmcb @400 0x00000000

ub4 kcvfhlcb @404 0x00000000

ub4 kcvfhbcs @408 0x00000000

ub2 kcvfhofb @412 0x000a

ub2 kcvfhnfb @414 0x000a

ub4 kcvfhprc @416 0x21a23e71

struct kcvfhprs, 8 bytes @420

ub4 kscnbas @420 0x00000001

ub2 kscnwrp @424 0x0000

struct kcvfhprfs, 8 bytes @428

ub4 kscnbas @428 0x00000000

ub2 kscnwrp @432 0x0000

ub4 kcvfhtrt @444 0x00000000

这里我们可以从结果里看到X$KCVFH实际上是来源于kcvfh这个struct。

有人曾在itpub上问x$kcvfh中的FHRDB是什么意思？其实FHRDB在10g里就是代表root dba的意思，Root dba: This field only occurs in data file #1, and is the location of blocks required during bootstrapping the data dictionary (bootstrap$)。

从上面的结果里我们可以看到现在的root dba是0x00400179：

SQL> exec sys.cdba('00400179','H');

.

The file is 1

The block is 377

PL/SQL procedure successfully completed

$ bbed parfile=par.txt

Password:

BBED: Release 2.0.0.0.0 - Limited Production on Sun May 31 15:19:56 2009

************* !!! For Oracle Internal Use only !!! ***************

BBED> set file 1

FILE# 1

BBED> set block 377

BLOCK# 377

BBED> p ktemh

struct ktemh, 16 bytes @92

ub4 count_ktemh @92 0x00000001

ub4 next_ktemh @96 0x00000000

ub4 obj_ktemh @100 0x00000038

ub4 flag_ktemh @104 0x40000000

可以看到这里root dba指向的object实际是0x00000038。

SQL> select to_number('38','XX') from dual;

TO_NUMBER('38','XX')

--------------------

56

SQL> select name from sys.obj$ where obj#=56;

NAME

------------------------------

BOOTSTRAP$

这就是10g中X$KCVFH中字段FHRDB的含义。

关于10g中的X$KCVFH（续）

By cui hua on May 26, 2009 6:23 PM | No Comments

在"关于10g中的X$KCVFH"这篇文章里，我分析了X$KCVFH的一些基本结构，但在那篇文章里，还有很多字段我当时也不知道其含义，现在我继续分析了一下，如下是继续分析过的X$KCVFH的结构，这下子X$KCVFH的绝大多数字段的含义都可以参考这篇文章了。

下文中凡是蓝色标记的地方就是我后续的补充内容：

10g中X$KCVFH的结构：

Version 10

Column Name Data Type Description

ADDR RAW(4) ADDRESS

INDX NUMBER INDEX

INST_ID NUMBER INSTANCE ID

HXFIL NUMBER FILE#，Datafile number (from control file)

HXONS NUMBER ONLINE | OFFLINE (from control file),HXONS为 0表示 'OFFLINE',为其他值表示 'ONLINE'

HXSTS VARCHAR2(16)

HXERR NUMBER ERROR，decode(HXERR, 0, NULL, 1,'FILE MISSING',2,'OFFLINE NORMAL', 3,'NOT VERIFIED', 4,'FILE NOT FOUND',5,'CANNOT OPEN FILE', 6,'CANNOT READ HEADER', 7,'CORRUPT HEADER',8,'WRONG FILE TYPE', 9,'WRONG DATABASE', 10,'WRONG FILE NUMBER',11,'WRONG FILE CREATE', 12,'WRONG FILE CREATE', 16,'DELAYED OPEN',14, 'WRONG RESETLOGS', 15,'OLD CONTROLFILE', 'UNKNOWN ERROR')

HXVER NUMBER FORMAT，Indicates the format for the header block. The possible values are 6, 7, 8, or 0. 6 - indicates Oracle Version 6；7 - indicates Oracle Version 7；8 - indicates Oracle Version 8；0 - indicates the format could not be determined (for example, the header could not be read)

FHSWV NUMBER

FHCVN NUMBER Compatibility Vsn

FHDBI NUMBER DBID

FHDBN VARCHAR2(9) DB NAME

FHCSQ NUMBER controlfile sequence number

FHFSZ NUMBER BLOCKS, Current datafile size in blocks

FHBSZ NUMBER datafile block size

FHFNO NUMBER Tablespace datafile number

FHTYP NUMBER Type:

1 control file

2 redo log file

3 vanilla db file; that is, normal data, index, and undo blocks

4 backup control file

5 backup piece

6 temporary db file

FHRDB NUMBER Root dba: This field only occurs in data file #1, and is the location of blocks required during bootstrapping the data dictionary (bootstrap$)

FHCRS VARCHAR2(16) CREATION_CHANGE#，Datafile creation change#

FHCRT VARCHAR2(20) CREATION_TIME，Datafile creation timestamp

FHRLC VARCHAR2(20) RESETLOGS_TIME, Resetlogs timestamp

FHRLC_I NUMBER reset logs count

FHRLS VARCHAR2(16) RESETLOGS_CHANGE#, Resetlogs change#

FHPRC VARCHAR2(20) prev reset logs timestamp

FHPRC_I NUMBER prev reset logs count

FHPRS VARCHAR2(16) prev reset logs SCN

FHBTI VARCHAR2(20) Time the backup started, Updated when executing BEGIN BACKUP on the tablespace. RMAN does not update this field.

FHBSC VARCHAR2(16) System change number when backup started, Updated when executing BEGIN BACKUP on the tablespace. RMAN does not update this field.

FHBTH NUMBER Thread when when backup started, Updated when executing BEGIN BACKUP on the tablespace. RMAN does not update this field.

FHSTA NUMBER The value for the column X$KCVFH.FHSTA (file header status) for an open database with an online datafiles in versions prior to version 10 were all 4, indicating an online fuzzy status. With version 10 of Oracle the first system tablespace datafile will have a different status of 8196 if the datafile is online and the database is open and not in backup mode. In Oracle 10g, the X$KCVFH.FHSTA column will show 8196 for system data file if COMPATIBLE is set to 10.0.0.0 or higher. The value of 8196 is a value of 0x04, as in previous releases, plus an AND'd value of 0x2000 (8192) for internal uses. If COMPATIBLE is set to 9.2.0 (lowest possible value for Oracle 10g), the FHSTA column for system datafile will have a value of 4. In Oracle10g, the COMPATIBLE value is irreversible if advanced to a higher value. So the value of 8196 for the fhsta (status) column for the first system tablespace datafile is normal.

FHSCN VARCHAR2(16) CHECKPOINT_CHANGE#, Datafile checkpoint change#, Updated on every checkpoint, but not when in Hot backup state (not online backups). This must remain untouched when you are in hot backup mode, because you might get checkpoints between the BEGIN BACKUP and when you actually start the copying process.

FHTIM VARCHAR2(20) CHECKPOINT_TIME, Datafile checkpoint timestamp

FHTHR NUMBER THREAD#

FHRBA_SEQ NUMBER SEQUENCE，即Redo log sequence number

FHRBA_BNO NUMBER Block number，即the redo log file block number

FHRBA_BOF NUMBER Byte offset，the byte offset into the block at which the redo record starts

FHETB RAW(132) enable threads byte，这个我不确定，是猜的。

FHCPC NUMBER CHECKPOINT_COUNT, Datafile checkpoint count

FHRTS VARCHAR2(20) Recoverd timestamp

FHCCC NUMBER Controlfile Checkpoint Count: Saved copy of the control file record of the checkpoint count. Helps detect old control files.

FHBCP_SCN VARCHAR2(16) Backup Checkpoint SCN: Updated with the checkpoint done while file in Hot backup

FHBCP_TIM VARCHAR2(20) Backup Checkpoint TIME: Updated with the checkpoint done while file in Hot backup

FHBCP_THR NUMBER Backup Checkpoint Thread: Updated with the checkpoint done while file in Hot backup

FHBCP_RBA_SEQ NUMBER Backup Checkpoint Sequence, 即Redo log sequence number: Updated with the checkpoint done while file in Hot backup

FHBCP_RBA_BNO NUMBER the redo log file block number, Updated with the checkpoint done while file in Hot backup

FHBCP_RBA_BOF NUMBER Byte offset，the byte offset into the block at which the redo record starts, Updated with the checkpoint done while file in Hot backup

FHBCP_ETB RAW(132) enable threads byte，这个我不确定，是猜的

FHBHZ NUMBER begin hot backup file size

FHXCD RAW(16) External cache id: Used to ensure that concurrent instances access data through consistent external cache

FHTSN NUMBER TS#，Tablespace number

FHTNM VARCHAR2(30) TABLESPACE_NAME, Tablespace name

FHRFN NUMBER RFILE#, Tablespace relative datafile number

FHAFS VARCHAR2(16) absolute fuzzy scn, 即Minimum PITR SCN

FHRFS VARCHAR2(16) The SCN at which the recovery of this file will be complete (no longer fuzzy). Both above fuzzy SCNs must be zero unless a fuzzy flag is set, and must be greater than the checkpoint SCN

FHRFT VARCHAR2(20) The time at which the recovery of this file will be complete (no longer fuzzy).

HXIFZ NUMBER File is fuzzy (YES | NO)，decode(hxifz, 0,'NO', 1,'YES', NULL)

HXNRCV NUMBER File needs media recovery (YES | NO)，decode(hxnrcv, 0,'NO', 1,'YES', NULL)

HXFNM VARCHAR2(513) NAME, Datafile name

FHPOFB NUMBER

FHPNFB NUMBER

FHPRE10 NUMBER

FHFIRSTUNRECSCN VARCHAR2(16) UNRECOVERABLE_CHANGE#, Last unrecoverable change number made to this datafile. If the database is in ARCHIVELOG mode, then this column is updated when an unrecoverable operation completes. If the database is not in ARCHIVELOG mode, this column does not get updated.

FHFIRSTUNRECTIME VARCHAR2(20) UNRECOVERABLE_TIME, Timestamp of the last unrecoverable change. This column is updated only if the database is in ARCHIVELOG mode.

HXLMDBA NUMBER SPACE_HEADER, The amount of space currently being used and the amount that is free, as identified in the space header. decode(hxlmdba, 0, NULL, hxlmdba)

HXLMLD_SCN VARCHAR2(16) LAST_DEALLOC_SCN, Last deallocated SCN

现在我们来看一个实际的例子：

我们现在把DATA FILE #1在x$kcvfh中的内容显示出来，如下所示：

SQL> exec p_sys_print_x_kcvfh;

ADDR:00000001104FA580

INDX:0

INST_ID:1

HXFIL:1

HXONS:1

HXSTS:281474976710655

HXERR:0

HXVER:0

FHSWV:0

FHCVN:169869568

FHDBI:1351724723

FHDBN:IPRATEST

FHCSQ:5895

FHFSZ:61440

FHBSZ:8192

FHFNO:1

FHTYP:3

FHRDB:4194681

FHCRS:8

FHCRT:07/22/2005 00:42:44

FHRLC:12/02/2008 15:09:41

FHRLC_I:672419381

FHRLS:551299

FHPRC:07/22/2005 00:42:25

FHPRC_I:564280945

FHPRS:1

FHBTI:

FHBSC:0

FHBTH:0

FHSTA:8196

FHSCN:39033396980

FHTIM:05/26/2009 15:46:18

FHTHR:1

FHRBA_SEQ:71

FHRBA_BNO:2

FHRBA_BOF:16

FHETB:02000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

FHCPC:189

FHRTS:12/02/2008 15:09:27

FHCCC:188

FHBCP_SCN:0

FHBCP_TIM:

FHBCP_THR:0

FHBCP_RBA_SEQ:0

FHBCP_RBA_BNO:0

FHBCP_RBA_BOF:0

FHBCP_ETB:00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

FHBHZ:0

FHXCD:00000000000000000000000000000000

FHTSN:0

FHTNM:SYSTEM

FHRFN:1

FHAFS:0

FHRFS:

FHRFT:

HXIFZ:1

HXNRCV:0

HXFNM:/iprat02/ipratest/system01.dbf

FHPOFB:10

FHPNFB:10

FHPRE10:0

FHFIRSTUNRECSCN:0

FHFIRSTUNRECTIME:

HXLMDBA:4194306

HXLMLD_SCN:0

PL/SQL procedure successfully completed

再把DATA FILE #1的文件头给dump出来，如下所示：

DATA FILE #1:

(name #7) /iprat02/ipratest/system01.dbf

creation size=0 block size=8192 status=0xe head=7 tail=7 dup=1

tablespace 0, index=1 krfil=1 prev_file=0

unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:189 scn: 0x0009.16925ef4 05/26/2009 15:46:18

Stop scn: 0xffff.ffffffff 05/22/2009 22:10:41

Creation Checkpointed at scn: 0x0000.00000008 07/22/2005 00:42:44

thread:0 rba:(0x0.0.0)

enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000

......省略显示部分内容

Offline scn: 0x0000.00086982 prev_range: 0

Online Checkpointed at scn: 0x0000.00086983 12/02/2008 15:09:41

thread:1 rba:(0x1.2.0)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

......省略显示部分内容

Hot Backup end marker scn: 0x0000.00000000

aux_file is NOT DEFINED

V10 STYLE FILE HEADER:

Compatibility Vsn = 169869568=0xa200100

Db ID=1351724723=0x5091aeb3, Db Name='IPRATEST'

Activation ID=0=0x0

Control Seq=5895=0x1707, File size=61440=0xf000

File Number=1, Blksiz=8192, File Type=3 DATA

Tablespace #0 - SYSTEM rel_fn:1

Creation at scn: 0x0000.00000008 07/22/2005 00:42:44

Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0

reset logs count:0x28144e35 scn: 0x0000.00086983 reset logs terminal rcv data:0x0 scn: 0x0000.00000000

prev reset logs count:0x21a23e71 scn: 0x0000.00000001 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000

recovered at 12/02/2008 15:09:27

status:0x2004 root dba:0x00400179 chkpt cnt: 189 ctl cnt:188

begin-hot-backup file size: 0

Checkpointed at scn: 0x0009.16925ef4 05/26/2009 15:46:18

thread:1 rba:(0x47.2.10)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

......省略显示部分内容

Backup Checkpointed at scn: 0x0000.00000000

thread:0 rba:(0x0.0.0)

enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000

......省略显示部分内容

External cache id: 0x0 0x0 0x0 0x0

Absolute fuzzy scn: 0x0000.00000000

Recovery fuzzy scn: 0x0000.00000000 01/01/1988 00:00:00

Terminal Recovery Stamp 01/01/1988 00:00:00

Platform Information: Creation Platform ID: 6

Current Platform ID: 6 Last Platform ID: 6

两边一对比，以前一些怎么也猜不出来含义的字段的含义就很清晰了。

有兴趣的朋友可以根据我这篇文章里的内容自己尝试解析出DATA FILE #1在x$kcvfh的那条记录里每一个column的值的含义。

如何看执行计划的执行顺序

By cui hua on May 21, 2009 2:23 PM | 6 Comments

有同事问我如何看执行计划的执行顺序。这个问题我想很多朋友都会不屑一顾的，这么简单的问题，谁不会看？

但是这里我还是想说一说我是怎样看执行计划的执行顺序的。

我看执行计划的执行顺序的原则就是先从最开头一直往右看，直到看到最右边的并列的地方，对于不并列的，靠右的先执行；如果见到并列的，就从上往下看，对于并列的，靠上的先执行。

好了，我们以上述原则来看一个实例：

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 5月 21 13:09:27 2009

SQL> conn ipra/acca@ipradev;

已连接。

SQL> select count(*) from (select o.oatfna,o.oatprf,o.oatfrm,o.oattkt,o.oatcpn,o.oatpst,substr(u.ute

dsp,1,5) as errnum,o.oatcdt,o.oatcid from owbatn o,uplter u where (o.oatcer='Y' and o.oatcpn='0' and

u.utetkt=o.oattkt and u.rowid= (select max(rowid) from uplter where utetkt = o.oattkt and utefna=o

.oatfna)) or o.oatpst='D' or o.oatpst='I' or o.oatpst='F');

COUNT(*)

----------

17809

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

SQL_ID 01nxqdvn71mx5, child number 0

-------------------------------------

select count(*) from (select o.oatfna,o.oatprf,o.oatfrm,o.oattkt,o.oatcpn,o.oatpst,substr(u.u

tedsp,1,5) as errnum,o.oatcdt,o.oatcid from owbatn o,uplter u where (o.oatcer='Y' and

o.oatcpn='0' and u.utetkt=o.oattkt and u.rowid= (select max(rowid) from uplter where utetkt

= o.oattkt and utefna=o.oatfna)) or o.oatpst='D' or o.oatpst='I' or o.oatpst='F')

Plan hash value: 2105702960

----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 3082 (100)| |

| 1 | SORT AGGREGATE | | 1 | 54 | | |

| 2 | CONCATENATION | | | | | |

| 3 | MERGE JOIN CARTESIAN | | 470K| 24M| 1969 (4)| 00:00:24 |

|* 4 | TABLE ACCESS FULL | OWBATN | 1 | 34 | 851 (5)| 00:00:11 |

| 5 | BUFFER SORT | | 316K| 6175K| 1118 (3)| 00:00:14 |

| 6 | TABLE ACCESS FULL | UPLTER | 316K| 6175K| 1118 (3)| 00:00:14 |

|* 7 | FILTER | | | | | |

|* 8 | HASH JOIN | | 37522 | 1978K| 1109 (6)| 00:00:14 |

|* 9 | TABLE ACCESS FULL | OWBATN | 20956 | 695K| 852 (5)| 00:00:11 |

| 10 | INDEX FAST FULL SCAN | IDX_UPLTER_UTETKT | 316K| 6175K| 246 (6)| 00:00:03 |

| 11 | SORT AGGREGATE | | 1 | 38 | | |

|* 12 | TABLE ACCESS BY INDEX ROWID| UPLTER | 1 | 38 | 4 (0)| 00:00:01 |

|* 13 | INDEX RANGE SCAN | IDX_UPLTER_UTETKT | 2 | | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - filter(("O"."OATPST"='D' OR "O"."OATPST"='F' OR "O"."OATPST"='I'))

7 - filter("U".ROWID=)

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

8 - access("U"."UTETKT"="O"."OATTKT")

9 - filter(("O"."OATCPN"=0 AND "O"."OATCER"='Y' AND LNNVL("O"."OATPST"='I') AND

LNNVL("O"."OATPST"='F') AND LNNVL("O"."OATPST"='D')))

12 - filter("UTEFNA"=:B1)

13 - access("UTETKT"=:B1)

已选择39行。

好了，我们现在根据上述原则来解析一下上述执行计划的执行顺序。

先从最开头一直往右看，直到看到最右边的并列的地方，对于不并列的，靠右的先执行：

从上述执行计划的开头一直往右看，直到找到最右边并列的。也就是从SELECT STATEMENT开始往右找，一直找到TABLE ACCESS FULL，这时候发现BUFFER SORT和它并列。

此时我们已经可以知道TABLE ACCESS FULL一定是比BUFFER SORT（包括其右边的所有语句）先执行，因为对于并列的，靠上的先执行。

而对于BUFFER SORT，它右边又有"TABLE ACCESS FULL | UPLTER"，此时根据上述原则我们也可以知道，它比BUFFER SORT先执行。

这样对于上述执行计划里的这三条语句：

|* 4 | TABLE ACCESS FULL | OWBATN | 1 | 34 | 851 (5)| 00:00:11 |

| 5 | BUFFER SORT | | 316K| 6175K| 1118 (3)| 00:00:14 |

| 6 | TABLE ACCESS FULL | UPLTER | 316K| 6175K| 1118 (3)| 00:00:14 |

正确的执行顺序是：

先执行TABLE ACCESS FULL | OWBATN | 1 | 34 | 851 (5)| 00:00:11 |

再执行TABLE ACCESS FULL | UPLTER | 316K| 6175K| 1118 (3)| 00:00:14 |

最后执行BUFFER SORT | | 316K| 6175K| 1118 (3)| 00:00:14 |

这样我们就解析出来了最内层上述三条语句的执行顺序，然后我们再应用上述原则解析其外层语句，一层一层剥离，就很容易得到了整个执行计划的顺序，这里我把按照上述原则解析出来的执行计划的顺序paste出来，如下所示：

第一步：TABLE ACCESS FULL | OWBATN | 1 | 34 | 851 (5)| 00:00:11 |

第二步：TABLE ACCESS FULL | UPLTER | 316K| 6175K| 1118 (3)| 00:00:14 |

第三步：BUFFER SORT | | 316K| 6175K| 1118 (3)| 00:00:14 |

第四步：MERGE JOIN CARTESIAN | | 470K| 24M| 1969 (4)| 00:00:24 |

第五步：TABLE ACCESS FULL | OWBATN | 20956 | 695K| 852 (5)| 00:00:11 |

第六步：INDEX FAST FULL SCAN | IDX_UPLTER_UTETKT | 316K| 6175K| 246 (6)| 00:00:03 |

第七步：HASH JOIN | | 37522 | 1978K| 1109 (6)| 00:00:14 |

第八步：INDEX RANGE SCAN | IDX_UPLTER_UTETKT | 2 | | 3 (0)| 00:00:01 |

第九步：TABLE ACCESS BY INDEX ROWID| UPLTER | 1 | 38 | 4 (0)| 00:00:01 |

第十步：SORT AGGREGATE | | 1 | 38 | | |

第十三步：SORT AGGREGATE | | 1 | 54 | | |

第十四步：SELECT STATEMENT | | | | 3082 (100)| |

如果把握了上述原则，则无论多复杂的执行计划也能知道其正确的执行顺序。

这里给还不知道怎样看执行计划顺序的朋友留一个作业，请应用上述原则，正确解析出下述执行计划的执行顺序：

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                |  Name                       | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                             |     1 |   220 | 17191 |       |       |
|   1 |  NESTED LOOPS                            |                             |     1 |   220 | 17191 |       |       |
|   2 |   NESTED LOOPS                           |                             |     1 |   199 | 17189 |       |       |
|   3 |    NESTED LOOPS                          |                             |     1 |   169 | 17187 |       |       |
|   4 |     NESTED LOOPS                         |                             |     1 |   155 | 17186 |       |       |
|*  5 |      HASH JOIN SEMI                      |                             |     1 |   116 | 17184 |       |       |
|   6 |       NESTED LOOPS                       |                             |   411 | 42333 | 17017 |       |       |
|   7 |        MERGE JOIN                        |                             |   411 | 27126 | 16195 |       |       |
|   8 |         PARTITION RANGE ALL              |                             |       |       |       |     1 |    14 |
|*  9 |          TABLE ACCESS FULL               | SERV_ACCT                   |  8667K|   223M| 16177 |     1 |    14 |
|* 10 |         SORT JOIN                        |                             |     1 |    39 |    18 |       |       |
|  11 |          TABLE ACCESS BY INDEX ROWID     | SYS_DOMAIN                  |     1 |    39 |     2 |       |       |
|* 12 |           INDEX RANGE SCAN               | IDX_SYS_DOMAIN_1            |     1 |       |     1 |       |       |
|  13 |        PARTITION RANGE ITERATOR          |                             |       |       |       |   KEY |   KEY |
|* 14 |         TABLE ACCESS BY LOCAL INDEX ROWID| SERV                        |     1 |    37 |     2 |   KEY |   KEY |
|* 15 |          INDEX UNIQUE SCAN               | PK_SERV                     |     1 |       |     1 |   KEY |   KEY |
|  16 |       VIEW                               | VW_NSO_1                    |  4977 | 64701 |   166 |       |       |
|  17 |        TABLE ACCESS BY LOCAL INDEX ROWID | SERV_ATTR                   |  1991 | 37829 |    53 |     1 |     1 |
|  18 |         NESTED LOOPS                     |                             |  4977 |   136K|   166 |       |       |
|* 19 |          TABLE ACCESS FULL               | A_QUERY_ACCT_ATTR           |     3 |    27 |     7 |       |       |
|  20 |          PARTITION RANGE ALL             |                             |       |       |       |     1 |    14 |
|* 21 |           INDEX RANGE SCAN               | IDX_SERV_ATTR_INTERNET_NEW  |  1991 |       |    42 |     1 |    14 |
|  22 |      TABLE ACCESS BY INDEX ROWID         | SYS_DOMAIN                  |     1 |    39 |     2 |       |       |
|* 23 |       INDEX RANGE SCAN                   | IDX_SYS_DOMAIN_1            |     1 |       |     1 |       |       |
|  24 |     TABLE ACCESS BY INDEX ROWID          | PRODUCT                     |     1 |    14 |     1 |       |       |
|* 25 |      INDEX UNIQUE SCAN                   | PK_PRODUCT                  |     1 |       |       |       |       |
|* 26 |    TABLE ACCESS BY GLOBAL INDEX ROWID    | ACCT                        |     1 |    30 |     2 | ROWID | ROW L |
|* 27 |     INDEX UNIQUE SCAN                    | PK_ACCT                     |     1 |       |     1 |       |       |
|  28 |   TABLE ACCESS BY GLOBAL INDEX ROWID     | CUST                        |     1 |    21 |     2 | ROWID | ROW L |
|* 29 |    INDEX UNIQUE SCAN                     | PK_CUST                     |     1 |       |     1 |       |       |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("A"."SERV_ID"="VW_NSO_1″."SERV_ID")
   9 - filter("B"."STATE"='10A')
  10 - access("B"."STATE"="SYS_DOMAIN"."DOMAIN")
       filter("B"."STATE"="SYS_DOMAIN"."DOMAIN")
  12 - access("SYS_DOMAIN"."TABLE_NAME"='SERV_ACCT' AND "SYS_DOMAIN"."FIELD_NAME"='STATE' AND
              "SYS_DOMAIN"."DOMAIN"='10A')
  14 - filter("A"."STATE"='2HA' OR "A"."STATE"='2HC' OR "A"."STATE"='2HD' OR "A"."STATE"='2HE' OR "A"."STATE"='2HH' OR
              "A"."STATE"='2HN' OR "A"."STATE"='2HS')
  15 - access("A"."SERV_ID"="B"."SERV_ID")
  19 - filter("A_QUERY_ACCT_ATTR"."STATE"='A0A' AND "A_QUERY_ACCT_ATTR"."ATTR_TYPE"='ACT')
  21 - access("SERV_ATTR"."ATTR_VAL"='0xx833xxxxx' AND "SERV_ATTR"."ATTR_ID"="A_QUERY_ACCT_ATTR"."ATTR_ID")
  23 - access("SYS_ALIAS_0000″."TABLE_NAME"='SERV' AND "SYS_ALIAS_0000″."FIELD_NAME"='STATE' AND
              "A"."STATE"="SYS_ALIAS_0000″."DOMAIN")
       filter("SYS_ALIAS_0000″."DOMAIN"='2HA' OR "SYS_ALIAS_0000″."DOMAIN"='2HC' OR "SYS_ALIAS_0000″."DOMAIN"='2HD' OR
              "SYS_ALIAS_0000″."DOMAIN"='2HE' OR "SYS_ALIAS_0000″."DOMAIN"='2HH' OR "SYS_ALIAS_0000″."DOMAIN"='2HN' OR
              "SYS_ALIAS_0000″."DOMAIN"='2HS')
  25 - access("A"."PRODUCT_ID"="H"."PRODUCT_ID")
  26 - filter("C"."STATE"='10A')
  27 - access("B"."ACCT_ID"="C"."ACCT_ID")
  29 - access("A"."CUST_ID"="D"."CUST_ID")

关于10g中的X$KCVFH

By cui hua on May 12, 2009 2:07 PM | No Comments

有朋友问我X$KCVFH的详细结构，我找遍了也没有发现任何关于X$KCVFH的现成的资料。只好自己动手来分析一下。

The fixed X$ tables are no real tables; you will not find them in any database schema.

These virtual tables provide a SQL interface to Oracle memory structures; that is, you

can retrieve (real-time) information from memory structures using SQL queries.

X$表在oracle的不同版本里很可能是不一样的，这里是以 10.2.0.1为例来研究一下X$KCVFH的结构，X$KCVFH中各个字段的含义大多数都有据可寻，下文中凡是为空白的地方就代表我也不清楚其含义是什么。

10g中X$KCVFH的结构：

Version 10

Column Name Data Type Description

ADDR RAW(4) ADDRESS

INDX NUMBER INDEX

INST_ID NUMBER INSTANCE ID

HXFIL NUMBER FILE#，Datafile number (from control file)

HXONS NUMBER ONLINE | OFFLINE (from control file),HXONS为 0表示 'OFFLINE',为其他值表示 'ONLINE'

HXSTS VARCHAR2(16)

HXERR NUMBER ERROR，decode(HXERR, 0, NULL, 1,'FILE MISSING',2,'OFFLINE NORMAL', 3,'NOT VERIFIED', 4,'FILE NOT FOUND',5,'CANNOT OPEN FILE', 6,'CANNOT READ HEADER', 7,'CORRUPT HEADER',8,'WRONG FILE TYPE', 9,'WRONG DATABASE', 10,'WRONG FILE NUMBER',11,'WRONG FILE CREATE', 12,'WRONG FILE CREATE', 16,'DELAYED OPEN',14, 'WRONG RESETLOGS', 15,'OLD CONTROLFILE', 'UNKNOWN ERROR')

HXVER NUMBER FORMAT，Indicates the format for the header block. The possible values are 6, 7, 8, or 0. 6 - indicates Oracle Version 6；7 - indicates Oracle Version 7；8 - indicates Oracle Version 8；0 - indicates the format could not be determined (for example, the header could not be read)

FHSWV NUMBER

FHCVN NUMBER

FHDBI NUMBER DBID

FHDBN VARCHAR2(9) DB NAME

FHCSQ NUMBER controlfile sequence number

FHFSZ NUMBER BLOCKS, Current datafile size in blocks

FHBSZ NUMBER datafile block size

FHFNO NUMBER Tablespace datafile number

FHTYP NUMBER Type

FHRDB NUMBER

FHCRS VARCHAR2(16) CREATION_CHANGE#，Datafile creation change#

FHCRT VARCHAR2(20) CREATION_TIME，Datafile creation timestamp

FHRLC VARCHAR2(20) RESETLOGS_TIME, Resetlogs timestamp

FHRLC_I NUMBER

FHRLS VARCHAR2(16) RESETLOGS_CHANGE#, Resetlogs change#

FHPRC VARCHAR2(20)

FHPRC_I NUMBER

FHPRS VARCHAR2(16)

FHBTI VARCHAR2(20) Time the backup started

FHBSC VARCHAR2(16) System change number when backup started

FHBTH NUMBER

FHSTA NUMBER The value for the column X$KCVFH.FHSTA (file header status) for an open database with an online datafiles in versions prior to version 10 were all 4, indicating an online fuzzy status. With version 10 of Oracle the first system tablespace datafile will have a different status of 8196 if the datafile is online and the database is open and not in backup mode. In Oracle 10g, the X$KCVFH.FHSTA column will show 8196 for system data file if COMPATIBLE is set to 10.0.0.0 or higher. The value of 8196 is a value of 0x04, as in previous releases, plus an AND'd value of 0x2000 (8192) for internal uses. If COMPATIBLE is set to 9.2.0 (lowest possible value for Oracle 10g), the FHSTA column for system datafile will have a value of 4. In Oracle10g, the COMPATIBLE value is irreversible if advanced to a higher value. So the value of 8196 for the fhsta (status) column for the first system tablespace datafile is normal.

FHSCN VARCHAR2(16) CHECKPOINT_CHANGE#, Datafile checkpoint change#

FHTIM VARCHAR2(20) CHECKPOINT_TIME, Datafile checkpoint timestamp

FHTHR NUMBER THREAD#

FHRBA_SEQ NUMBER SEQUENCE，即Redo log sequence number

FHRBA_BNO NUMBER

FHRBA_BOF NUMBER

FHETB RAW(132)

FHCPC NUMBER CHECKPOINT_COUNT, Datafile checkpoint count

FHRTS VARCHAR2(20)

FHCCC NUMBER

FHBCP_SCN VARCHAR2(16)

FHBCP_TIM VARCHAR2(20)

FHBCP_THR NUMBER

FHBCP_RBA_SEQ NUMBER

FHBCP_RBA_BNO NUMBER

FHBCP_RBA_BOF NUMBER

FHBCP_ETB RAW(132)

FHBHZ NUMBER

FHXCD RAW(16)

FHTSN NUMBER TS#，Tablespace number

FHTNM VARCHAR2(30) TABLESPACE_NAME, Tablespace name

FHRFN NUMBER RFILE#, Tablespace relative datafile number

FHAFS VARCHAR2(16) absolute fuzzy scn, 即Minimum PITR SCN

FHRFS VARCHAR2(16)

FHRFT VARCHAR2(20)

HXIFZ NUMBER File is fuzzy (YES | NO)，decode(hxifz, 0,'NO', 1,'YES', NULL)

HXNRCV NUMBER File needs media recovery (YES | NO)，decode(hxnrcv, 0,'NO', 1,'YES', NULL)

HXFNM VARCHAR2(513) NAME, Datafile name

FHPOFB NUMBER

FHPNFB NUMBER

FHPRE10 NUMBER

FHFIRSTUNRECSCN VARCHAR2(16) UNRECOVERABLE_CHANGE#, Last unrecoverable change number made to this datafile. If the database is in ARCHIVELOG mode, then this column is updated when an unrecoverable operation completes. If the database is not in ARCHIVELOG mode, this column does not get updated.

FHFIRSTUNRECTIME VARCHAR2(20) UNRECOVERABLE_TIME, Timestamp of the last unrecoverable change. This column is updated only if the database is in ARCHIVELOG mode.

HXLMDBA NUMBER SPACE_HEADER, The amount of space currently being used and the amount that is free, as identified in the space header. decode(hxlmdba, 0, NULL, hxlmdba)

HXLMLD_SCN VARCHAR2(16) LAST_DEALLOC_SCN, Last deallocated SCN

我们现在来看一下X$KCVFH的常见用法：

1、判断datafile是否需要recover，如果需要recover，那么需要至少需要recover到什么SCN:

即执行如下查询：

SQL> select max(fhafs) from x$kcvfh;

MAX(FHAFS)

----------------

0

如果返回返回结果大于0，则表示数据库处于不一致的状态，需要recover到上述查询结果中的SCN。

2、判断datafile做recover需要的archive log的sequence是多少，也就是说做recover到这个sequence，那么control file和datafile header中的记录就一致了。

即执行如下查询：

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name, FHSCN SCN, FHRBA_SEQ Sequence from X$KCVFH;

......显示结果省略

人为构造system回滚段错误一例

By cui hua on May 6, 2009 3:06 PM | No Comments

这个例子是我在了解undo block的结构的时候顺便做的一个例子。

在这个例子里，我通过手工修改ktuxcfbp[0]. kubadba，人为构造出来了ORA-600 [4193]错误。

如下是整个构造过程：

SQL> conn dras/astca@astcatest;

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0

Connected as dras

SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='SYSTEM';

HEADER_FILE HEADER_BLOCK

----------- ------------

1 9

我们现在dump 1-9这个block，dump出来的内容如下：

Start dump data blocks tsn: 0 file#: 1 minblk 9 maxblk 9

buffer tsn: 0 rdba: 0x00400009 (1/9)

scn: 0x0008.a9f7d8fc seq: 0x01 flg: 0x04 tail: 0xd8fc0e01

frmt: 0x02 chkval: 0x6cf3 type: 0x0e=KTU UNDO HEADER W/UNLIMITED EXTENTS

Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 spare2: 0 #extents: 6 #blocks: 47

last map 0x00000000 #maps: 0 offset: 4128

Highwater:: 0x004001a0 ext#: 5 blk#: 7 ext size: 8

#blocks in seg. hdr's freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 5

Unlocked

Map Header:: next 0x00000000 #extents: 6 obj#: 0 flag: 0x40000000

Extent Map

-----------------------------------------------------------------

0x0040000a length: 7

0x00400011 length: 8

0x00400181 length: 8

0x00400189 length: 8

0x00400191 length: 8

0x00400199 length: 8

TRN CTL:: seq: 0x0035 chd: 0x0060 ctl: 0x0002 inc: 0x00000000 nfb: 0x0001

mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)

uba: 0x004001a0.0035.15 scn: 0x0008.a9e6062f

Version: 0x01

FREE BLOCK POOL::

uba: 0x004001a0.0035.15 ext: 0x5 spc: 0xccc

uba: 0x00000000.0032.07 ext: 0x2 spc: 0x1d08

uba: 0x00000000.0030.3e ext: 0x0 spc: 0x476

uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0

TRN TBL::

......后续内容省略

我们现在把上述free block pool中的0x004001a0改成0x004001a1。

先shutdown上述数据库，然后就可以动手改了：

$ bbed parfile=par.bbd

Password:

BBED: Release 2.0.0.0.0 - Limited Production on Wed May 6 15:40:46 2009

************* !!! For Oracle Internal Use only !!! ***************

BBED> set file 1

FILE# 1

BBED> set block 9

BLOCK# 9

BBED> p ktuxc

struct ktuxc, 104 bytes @4148

struct ktuxcscn, 8 bytes @4148

ub4 kscnbas @4148 0xa9e60631

ub2 kscnwrp @4152 0x0008

struct ktuxcuba, 8 bytes @4156

ub4 kubadba @4156 0x004001a0

ub2 kubaseq @4160 0x0035

ub1 kubarec @4162 0x16

sb2 ktuxcflg @4164 1 (KTUXCFSK)

ub2 ktuxcseq @4166 0x0035

sb2 ktuxcnfb @4168 1

ub4 ktuxcinc @4172 0x00000000

sb2 ktuxcchd @4176 81

sb2 ktuxcctl @4178 96

ub2 ktuxcmgc @4180 0x8002

ub4 ktuxcopt @4188 0x7ffffffe

struct ktuxcfbp[0], 12 bytes @4192

struct ktufbuba, 8 bytes @4192

ub4 kubadba @4192 0x004001a0

ub2 kubaseq @4196 0x0035

ub1 kubarec @4198 0x1f

sb2 ktufbext @4200 5

sb2 ktufbspc @4202 1296

struct ktuxcfbp[1], 12 bytes @4204

struct ktufbuba, 8 bytes @4204

ub4 kubadba @4204 0x00000000

ub2 kubaseq @4208 0x0032

ub1 kubarec @4210 0x07

sb2 ktufbext @4212 2

sb2 ktufbspc @4214 7432

struct ktuxcfbp[2], 12 bytes @4216

struct ktufbuba, 8 bytes @4216

ub4 kubadba @4216 0x00000000

ub2 kubaseq @4220 0x0030

ub1 kubarec @4222 0x3e

sb2 ktufbext @4224 0

sb2 ktufbspc @4226 1142

struct ktuxcfbp[3], 12 bytes @4228

struct ktufbuba, 8 bytes @4228

ub4 kubadba @4228 0x00000000

ub2 kubaseq @4232 0x0000

ub1 kubarec @4234 0x00

sb2 ktufbext @4236 0

sb2 ktufbspc @4238 0

struct ktuxcfbp[4], 12 bytes @4240

struct ktufbuba, 8 bytes @4240

ub4 kubadba @4240 0x00000000

ub2 kubaseq @4244 0x0000

ub1 kubarec @4246 0x00

sb2 ktufbext @4248 0

sb2 ktufbspc @4250 0

BBED> set offset 4192

OFFSET 4192

BBED> dump

File: /dras11/oradata/astca/system01.dbf (1)

Block: 9 Offsets: 4192 to 4703 Dba:0x00400009

------------------------------------------------------------------------

004001a0 00351f00 00050510 00000000 00320700 00021d08 00000000 00303e00

00000476 00000000 00000000 00000000 00000000 00000000 00000000 0000002d

0040019e a9e7d39a 00085e38 0900001a 00000000 00000000 00000000 00000001

00000000 0000002d 0040019d a9e7d38a 00085e38 09000005 00000000 00000000

00000000 00000001 00000000 0000002e 004001a0 a9f7d8fc 00085e38 09000060

00000000 00000000 00000000 00000001 00000000 0000002d 0040019e a9f46b87

00085e38 09000012 00000000 00000000 00000000 00000001 00000000 0000002d

0040019e a9f46b7d 00085e38 09000023 00000000 00000000 00000000 00000001

00000000 0000002d 0040019d a9e7d38b 00085e38 0900000a 00000000 00000000

00000000 00000001 00000000 0000002d 0040019d a9e60649 00085e38 09000055

00000000 00000000 00000000 00000001 00000000 0000002d 0040019f a9f7d754

00085e38 09000032 00000000 00000000 00000000 00000001 00000000 0000002d

0040019d a9e7d396 00085e38 09000015 00000000 00000000 00000000 00000001

00000000 0000002d 0040019d a9e7d387 00085e38 0900005a 00000000 00000000

00000000 00000001 00000000 0000002d 0040019d a9e7d38c 00085e38 0900004f

00000000 00000000 00000000 00000001 00000000 0000002d 0040019e a9f46b75

<32 bytes per line>

BBED> modify /x 004001a1

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

File: /dras11/oradata/astca/system01.dbf (1)

Block: 9 Offsets: 4192 to 4703 Dba:0x00400009

------------------------------------------------------------------------

004001a1 00351f00 00050510 00000000 00320700 00021d08 00000000 00303e00