欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

x$kccle视图深入剖析

程序员文章站 2022-06-07 08:28:04
...

今天是2014-05-27,实在无聊顺便研究一下x$kccle的内容吧。如下全部是自己分析和实验结果,真实可靠。 1、如何获得v$log的底层表?我们可以通过autotrace完成查看如: SQL set autotrace trace expSQL set linesize 200SQL select * from v$log;Execution Pl

今天是2014-05-27,实在无聊顺便研究一下x$kccle的内容吧。如下全部是自己分析和实验结果,真实可靠。

1、如何获得v$log的底层表?我们可以通过autotrace完成查看如:

SQL> set autotrace trace exp
SQL> set linesize 200
SQL> select * from v$log;

Execution Plan
----------------------------------------------------------
Plan hash value: 2536105608

--------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |     1 |   197 |     0   (0)| 00:00:01 |
|   1 |  NESTED LOOPS            |                 |     1 |   197 |     0   (0)| 00:00:01 |
|*  2 |   FIXED TABLE FULL       | X$KCCLE         |     1 |   148 |     0   (0)| 00:00:01 |
|*  3 |   FIXED TABLE FIXED INDEX| X$KCCRT (ind:1) |     1 |    49 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LE"."LEDUP"0 AND "LE"."INST_ID"=USERENV('INSTANCE'))
   3 - filter("RT"."INST_ID"=USERENV('INSTANCE') AND "LE"."LETHR"="RT"."RTNUM")

SQL> 

可以知道当我们在查看v$log视图的时候,其实就是查看的x$kccle视图字段信息。查看该视图如下:

SQL> desc x$kccle
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER  实例号
 LENUM                                              NUMBER  类似group_number
 LESIZ                                              NUMBER  logfile的size,是操作系统的blocks数目
 LESEQ                                              NUMBER  v$log中的SEQUENCE# 
 LEHWS                                              NUMBER
 LEBSZ                                              NUMBER  block size(操作系统的块)
 LENAB                                              NUMBER
 LEFLG                                              NUMBER  表示日志的状态信息
 LETHR                                              NUMBER  v$log中的THREAD# 
 LELFF                                              NUMBER  下一个logfile
 LELFB                                              NUMBER  前一个logfile
 LELOS                                              VARCHAR2(16) low scn类似v$log FIRST_CHANGE#
 LELOT                                              VARCHAR2(20) low time类似v$log  FIRST_TIME
 LENXS                                              VARCHAR2(16) nex scn 类似v$log中下一个logfile的low FIRST_CHANGE#相同
 LENXT                                              VARCHAR2(20) nex time类似v$log中下一个logfile的low FIRST_TIME 相同
 LEPVS                                              VARCHAR2(16) prev scn, 与上一个logfile的low scn 
 LEARF                                              NUMBER  archive link forward 
 LEARB                                              NUMBER   archive link backward 
 LEFNH                                              NUMBER
 LEFNT                                              NUMBER
 LEDUP                                              NUMBER 等同于v$log中members

SQL> 

下面对比一下:

SQL> select * from v$log;    

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
         1          1       2944  104857600          1 YES INACTIVE             121424676 21-MAY-14
         2          1       2945  104857600          1 NO  CURRENT              121427428 21-MAY-14
         3          1       2943  104857600          1 YES INACTIVE             121421889 21-MAY-14

SQL> 

SQL>  select inst_id,lenum,lesiz,leseq,lehws,lebsz,lebsz*lesiz log_size,lenab,leflg,lethr,lelff,lelfb,lelos from x$kccle;

   INST_ID      LENUM      LESIZ      LESEQ      LEHWS      LEBSZ   LOG_SIZE      LENAB      LEFLG      LETHR      LELFF      LELFB LELOS
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------
         1          1     204800       2944          2        512  104857600     190256          1          1          2          0 121424676
         1          2     204800       2945          1        512  104857600 4294967295          8          1          3          1 121427428
         1          3     204800       2943          2        512  104857600     190256          1          1          0          2 121421889

SQL> 
SQL>  select inst_id,lenum,leseq,lethr,lelot,lenxs,lenxt,lepvs,learf,learb,lefnh,lefnt,ledup from x$kccle;

   INST_ID LENUM LESEQ      LETHR LELOT                LENXS            LENXT                LEPVS                 LEARF      LEARB      LEFNH      LEFNT      LEDUP
---------- ----- ----- ---------- -------------------- ---------------- -------------------- ---------------- ---------- ---------- ---------- ---------- ----------
         1     1  2944          1 05/21/2014 11:26:42  121427428        05/21/2014 11:26:55  121421889                 0          0          1          1          1
         1     2  2945          1 05/21/2014 11:26:55  281474976710655                       121424676                 0          0          2          2          1
         1     3  2943          1 05/21/2014 11:26:30  121424676        05/21/2014 11:26:42  121419052                 0          0          3          3          1

SQL> 

现在单独关注一下leflg字段,当该值为8说明是正在使用的logfile group:

dump出控制文件中的log file信息如下:

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         19   52428800        512          2 NO  CURRENT                1013368 27-MAY-14   2.8147E+14
         2          1         17   52428800        512          2 NO  ACTIVE                 1013348 27-MAY-14      1013365 27-MAY-14
         3          1         18   52428800        512          2 NO  ACTIVE                 1013365 27-MAY-14      1013368 27-MAY-14

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump controlf 12 
Statement processed.


注意:也可以使用alter system set events ‘immediate trace name controlf level 12';完成控制文件的dump操作:

LOG FILE RECORDS
***************************************************************************
 (size = 72, compat size = 72, section max = 16, section in-use = 3,
  last-recid= 17, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
  name #5: /u01/app/oracle/oradata/RHYS/onlinelog/o1_mf_1_9r1z70w0_.log
  name #6: /u01/app/oracle/fast_recovery_area/RHYS/onlinelog/o1_mf_1_9r1z70z2_.log
 Thread 1 redo log links: forward: 2 backward: 0
 siz: 0x19000 seq: 0x00000013 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 2
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000f7675
 Low scn: 0x0000.000f7678 05/27/2014 17:54:08
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #2:
  name #3: /u01/app/oracle/oradata/RHYS/onlinelog/o1_mf_2_9r1z726s_.log
  name #4: /u01/app/oracle/fast_recovery_area/RHYS/onlinelog/o1_mf_2_9r1z7291_.log
 Thread 1 redo log links: forward: 3 backward: 1
 siz: 0x19000 seq: 0x00000011 hws: 0x2 bsz: 512 nab: 0x14 flg: 0x0 dup: 2
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000f75ec
 Low scn: 0x0000.000f7664 05/27/2014 17:54:01
 Next scn: 0x0000.000f7675 05/27/2014 17:54:05
LOG FILE #3:
  name #1: /u01/app/oracle/oradata/RHYS/onlinelog/o1_mf_3_9r1z73jf_.log
  name #2: /u01/app/oracle/fast_recovery_area/RHYS/onlinelog/o1_mf_3_9r1z73lp_.log
 Thread 1 redo log links: forward: 0 backward: 2
 siz: 0x19000 seq: 0x00000012 hws: 0x2 bsz: 512 nab: 0x4 flg: 0x0 dup: 2
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000f7664
 Low scn: 0x0000.000f7675 05/27/2014 17:54:05
 Next scn: 0x0000.000f7678 05/27/2014 17:54:08

对应一下:

SQL> select inst_id,lenum,leseq,lethr,lelot,lenxs,lenxt,lepvs,learf,learb,lefnh,lefnt,ledup from x$kccle;

   INST_ID LENUM LESEQ      LETHR LELOT                LENXS            LENXT                LEPVS                 LEARF      LEARB      LEFNH      LEFNT      LEDUP
---------- ----- ----- ---------- -------------------- ---------------- -------------------- ---------------- ---------- ---------- ---------- ---------- ----------
         1     1    19          1 05/27/2014 17:54:08  281474976710655                       1013365                   0          0          5          6          2
         1     2    17          1 05/27/2014 17:54:01  1013365          05/27/2014 17:54:05  1013228                   0          0          3          4          2
         1     3    18          1 05/27/2014 17:54:05  1013368          05/27/2014 17:54:08  1013348                   0          0          1          2          2

SQL>  select inst_id,lenum,lesiz,leseq,lehws,lebsz,lebsz*lesiz log_size,lenab,leflg,lethr,lelff,lelfb,lelos from x$kccle;

   INST_ID LENUM      LESIZ LESEQ      LEHWS      LEBSZ   LOG_SIZE      LENAB      LEFLG      LETHR      LELFF      LELFB LELOS
---------- ----- ---------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------
         1     1     102400    19          1        512   52428800 4294967295          8          1          2          0 1013368
         1     2     102400    17          2        512   52428800         20          0          1          3          1 1013348
         1     3     102400    18          2        512   52428800          4          0          1          0          2 1013365

SQL> 

拿group3做一个分析:如下:

LOG FILE #3:(代表第三个group)
name #1: /u01/app/oracle/oradata/RHYS/onlinelog/o1_mf_3_9r1z73jf_.log(第一个日志文件)
name #2: /u01/app/oracle/fast_recovery_area/RHYS/onlinelog/o1_mf_3_9r1z73lp_.log(第二个日志文件)
Thread 1(线程1x$kccle.lethr) redo log links: forward: 0(对应x$kccle的LELFF为0) backward: 2(对应x$kccle的lelfb为2)
siz: 0x19000(对应x$kccle的lesiz转为10进制正好是102400): seq: 0x00000012(转为10进制为18对应x$kccle的leseq): hws: 0x2(对应x$kccle的lehws为2) bsz: 512(数据块对应x$kccle的lebsz)
nab: 0x4(对应lenab为4) flg: 0x0(对应x$kccle的leflg) dup: 2(对应x$kccle的ledup)
Archive links: fwrd: 0(前一个链表x$kccle的learf) back: 0 (对应x$kccle的learb)Prev scn: 0x0000.000f7664(转为10进制scn为1013348,对应lepvs)
Low scn: 0x0000.000f7675 05/27/2014 17:54:05(对应x$kccle的lelos)
Next scn: 0x0000.000f7678 05/27/2014 17:54:08(对应x$kccle的lenxt)

由此可知x$kccle正式抓取的是控制文件中的内容,这也是为什么数据块在mount状态下,可以查看v$log的原因了。