当前位置:Gxlcms > mysql > x$kccle视图深入剖析

x$kccle视图深入剖析

时间:2021-07-01 10:21:17 帮助过:11人阅读

今天是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完成查看如:

  1. SQL> set autotrace trace exp
  2. SQL> set linesize 200
  3. SQL> select * from v$log;
  4. Execution Plan
  5. ----------------------------------------------------------
  6. Plan hash value: 2536105608
  7. --------------------------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  9. --------------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 1 | 197 | 0 (0)| 00:00:01 |
  11. | 1 | NESTED LOOPS | | 1 | 197 | 0 (0)| 00:00:01 |
  12. |* 2 | FIXED TABLE FULL | X$KCCLE | 1 | 148 | 0 (0)| 00:00:01 |
  13. |* 3 | FIXED TABLE FIXED INDEX| X$KCCRT (ind:1) | 1 | 49 | 0 (0)| 00:00:01 |
  14. --------------------------------------------------------------------------------------------
  15. Predicate Information (identified by operation id):
  16. ---------------------------------------------------
  17. 2 - filter("LE"."LEDUP"<>0 AND "LE"."INST_ID"=USERENV('INSTANCE'))
  18. 3 - filter("RT"."INST_ID"=USERENV('INSTANCE') AND "LE"."LETHR"="RT"."RTNUM")
  19. SQL>

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

  1. SQL> desc x$kccle
  2. Name Null? Type
  3. ----------------------------------------- -------- ----------------------------
  4. ADDR RAW(8)
  5. INDX NUMBER
  6. INST_ID NUMBER 实例号
  7. LENUM NUMBER 类似group_number
  8. LESIZ NUMBER logfile的size,是操作系统的blocks数目
  9. LESEQ NUMBER v$log中的SEQUENCE#
  10. LEHWS NUMBER
  11. LEBSZ NUMBER block size(操作系统的块)
  12. LENAB NUMBER
  13. LEFLG NUMBER 表示日志的状态信息
  14. LETHR NUMBER v$log中的THREAD#
  15. LELFF NUMBER 下一个logfile
  16. LELFB NUMBER 前一个logfile
  17. LELOS VARCHAR2(16) low scn类似v$log FIRST_CHANGE#
  18. LELOT VARCHAR2(20) low time类似v$log FIRST_TIME
  19. LENXS VARCHAR2(16) nex scn 类似v$log中下一个logfile的low FIRST_CHANGE#相同
  20. LENXT VARCHAR2(20) nex time类似v$log中下一个logfile的low FIRST_TIME 相同
  21. LEPVS VARCHAR2(16) prev scn, 与上一个logfile的low scn
  22. LEARF NUMBER archive link forward
  23. LEARB NUMBER archive link backward
  24. LEFNH NUMBER
  25. LEFNT NUMBER
  26. LEDUP NUMBER 等同于v$log中members
  27. SQL>

下面对比一下:

  1. SQL> select * from v$log;
  2. GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
  3. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
  4. 1 1 2944 104857600 1 YES INACTIVE 121424676 21-MAY-14
  5. 2 1 2945 104857600 1 NO CURRENT 121427428 21-MAY-14
  6. 3 1 2943 104857600 1 YES INACTIVE 121421889 21-MAY-14
  7. SQL>
  8. SQL> select inst_id,lenum,lesiz,leseq,lehws,lebsz,lebsz*lesiz log_size,lenab,leflg,lethr,lelff,lelfb,lelos from x$kccle;
  9. INST_ID LENUM LESIZ LESEQ LEHWS LEBSZ LOG_SIZE LENAB LEFLG LETHR LELFF LELFB LELOS
  10. ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------
  11. 1 1 204800 2944 2 512 104857600 190256 1 1 2 0 121424676
  12. 1 2 204800 2945 1 512 104857600 4294967295 8 1 3 1 121427428
  13. 1 3 204800 2943 2 512 104857600 190256 1 1 0 2 121421889
  14. SQL>
  15. SQL> select inst_id,lenum,leseq,lethr,lelot,lenxs,lenxt,lepvs,learf,learb,lefnh,lefnt,ledup from x$kccle;
  16. INST_ID LENUM LESEQ LETHR LELOT LENXS LENXT LEPVS LEARF LEARB LEFNH LEFNT LEDUP
  17. ---------- ----- ----- ---------- -------------------- ---------------- -------------------- ---------------- ---------- ---------- ---------- ---------- ----------
  18. 1 1 2944 1 05/21/2014 11:26:42 121427428 05/21/2014 11:26:55 121421889 0 0 1 1 1
  19. 1 2 2945 1 05/21/2014 11:26:55 281474976710655 121424676 0 0 2 2 1
  20. 1 3 2943 1 05/21/2014 11:26:30 121424676 05/21/2014 11:26:42 121419052 0 0 3 3 1
  21. SQL>

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

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

  1. SQL> select * from v$log;
  2. GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
  3. ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
  4. 1 1 19 52428800 512 2 NO CURRENT 1013368 27-MAY-14 2.8147E+14
  5. 2 1 17 52428800 512 2 NO ACTIVE 1013348 27-MAY-14 1013365 27-MAY-14
  6. 3 1 18 52428800 512 2 NO ACTIVE 1013365 27-MAY-14 1013368 27-MAY-14
  7. SQL> oradebug setmypid
  8. Statement processed.
  9. SQL> oradebug dump controlf 12
  10. Statement processed.


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

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

对应一下:

  1. SQL> select inst_id,lenum,leseq,lethr,lelot,lenxs,lenxt,lepvs,learf,learb,lefnh,lefnt,ledup from x$kccle;
  2. INST_ID LENUM LESEQ LETHR LELOT LENXS LENXT LEPVS LEARF LEARB LEFNH LEFNT LEDUP
  3. ---------- ----- ----- ---------- -------------------- ---------------- -------------------- ---------------- ---------- ---------- ---------- ---------- ----------
  4. 1 1 19 1 05/27/2014 17:54:08 281474976710655 1013365 0 0 5 6 2
  5. 1 2 17 1 05/27/2014 17:54:01 1013365 05/27/2014 17:54:05 1013228 0 0 3 4 2
  6. 1 3 18 1 05/27/2014 17:54:05 1013368 05/27/2014 17:54:08 1013348 0 0 1 2 2
  7. SQL> select inst_id,lenum,lesiz,leseq,lehws,lebsz,lebsz*lesiz log_size,lenab,leflg,lethr,lelff,lelfb,lelos from x$kccle;
  8. INST_ID LENUM LESIZ LESEQ LEHWS LEBSZ LOG_SIZE LENAB LEFLG LETHR LELFF LELFB LELOS
  9. ---------- ----- ---------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------
  10. 1 1 102400 19 1 512 52428800 4294967295 8 1 2 0 1013368
  11. 1 2 102400 17 2 512 52428800 20 0 1 3 1 1013348
  12. 1 3 102400 18 2 512 52428800 4 0 1 0 2 1013365
  13. 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的原因了。

人气教程排行