Advanced Diagnostic using oradebug dumpvar
oradebug工具是Oracle数据库调优和诊断的利器,合理运用oradebug可以大幅减少我们收集诊断信息所花费的时间。当然前提是合理运用,对于初级DBA有这样一个忠告,不要在生产环境中去接触或修改自己所不熟悉的领域的东西,这一点很重要。并不是说中高级DBA在知识和经验上能达到巨细靡遗的状态,实际上中高级Oracle DBA可能每天都在和新事物打交道。恰恰相反,中高级DBA是对以上忠告最忠实的践行者,在生产环境中绝不随意涉险于不确定、不明确、不熟悉的区域,很多时候这会让人觉得是一种"好奇心丧失"的表现。
言归正传,我们所要介绍的是oradebug的dumpvar命令,该命令用于打印转储固定的PGA/UGA/SGA的变量:,其语法如下:
oradebug dumpvar Print/dump a fixed PGA/SGA/UGA variable. Syntax Parameter oradebug dumpvar <p|s|uga> <variable name> [level] <p|s|uga> PGA,SGA or UGA fixed variable name [level] 使用示例 SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- www.oracledatabase12g.com SQL> show parameter db_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_files integer 200 SQL> oradebug setmypid; Statement processed. SQL> oradebug dumpvar sga kcfdpk kfil kcfdpk_ [698B950, 698B954) = 000000C8 这里的KCFDPK变量保存了db_files参数的值,000000C8 == 200 利用oradebug工具我们不仅可以做到对这些内部变量的窥视,还可以做到修改, 但是这对我们实际的诊断没有益处,仅仅可以用来满足某些模拟实验仅仅了解dumpvar命令的使用方法并没有意义,只有和有意义的内部变量结合起来才能真正起到高级诊断的作用,在这里抛砖引玉罗列出部分诊断变量:
sgauso -- 该变量可以用于判断use_stored_outlines SQL> oradebug setmypid; Statement processed. Default use_stored_outlines=false; SQL> oradebug dumpvar sga sgauso qolprm sgauso_ [060021418, 06002143C) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 SQL> alter system set use_stored_outlines=true; System altered. SQL> oradebug dumpvar sga sgauso qolprm sgauso_ [060021418, 06002143C) = 00000001 45440007 4C554146 00000054 00000000 00000000 00000000 00000000 00000000 45440007 4C554146 ==> DEFAULT,意为优化器使用DEFAULT category中存放的outline. ksmvpa -- the size of the variable part of the pga SQL> oradebug dumpvar pga ksmvpa b4 ksmvpa_ [0068AA6B4, 0068AA6B8) = 0000E920 -- 59680 bytes kkjsre -- The SGA variable kkjsre must be 1 for jobs to execute automatically. SQL> oradebug dumpvar sga kkjsre word kkjsre_ [060025760, 060025764) = 00000001 SQL> exec dbms_ijob.set_enabled(false); PL/SQL procedure successfully completed. SQL> oradebug dumpvar sga kkjsre word kkjsre_ [060025760, 060025764) = 00000000 kcmsmx --the MAX reasonable scn SQL> oradebug dumpvar pga kcmsmx kscn kcmsmx_ [00B7E811C, 00B7E8124) = CA7F0000 00000C6C -- 11.2.0.2 SQL> oradebug dumpvar pga kcmsmx kscn kcmsmx_ [0068AB02C, 0068AB034) = A701C000 00000B3D -- 10.2.0.4 Notice the MAX scn allowed on 11.2 is far higher that that at earlier releases (due to the fix from bug 9254170). Use the fix for 9254170 on ALL DBs with an SCN rate set to match between versions. Avoid excessive SCN generation rates. kcvlcm - logging checkpoints to alert - FALSE SQL> show parameter log_checkpoints_to_alert NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_checkpoints_to_alert boolean TRUE SQL> oradebug dumpvar sga kcvlcm word kcvlcm_ [060019E88, 060019E8C) = 00000001 SQL> alter system set log_checkpoints_to_alert=false; System altered. SQL> oradebug dumpvar sga kcvlcm word kcvlcm_ [060019E88, 060019E8C) = 00000000 kcvcpr - false (a checkpoint is requested) SQL> oradebug dumpvar sga kcvcpr word kcvcpr_ [060019E90, 060019E94) = 00000000 kcvcpf - false (checkpointing fast) kcvgcw -false a global checkpointing is waiting SQL> oradebug dumpvar sga kcvgcw sword kcvgcw_ [060025748, 06002574C) = 00000000 kcfcpd - true if checkpoint writes done kcvblg kcvblg[0] is s 1 incidate some file/s in backup mode SQL> oradebug dumpvar sga kcvblg ub4 * kcvblg_ [060019E18, 060019E20) = 9A248508 00000000 SQL> oradebug peek 0x9A248508 100 [09A248508, 09A24856C) = 00000001 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000002 00000000 00000002 00000002 00000000 00000000 ... alter database begin backup; SQL> oradebug peek 0x9A248508 100 [09A248508, 09A24856C) = 00000001 00000003 00000003 00000003 00000003 00000003 00000003 00000003 00000003 00000003 00000003 00000003 00000003 00000003 ... kcsgscn_ -- current scn SQL> oradebug dumpvar sga kcsgscn_ kcslf kcsgscn_ [0600122B0, 0600122E0) = 01C7DB4B 00000000 00000000 00000000 01C7DB4B -- 29875019 0001E907 00000000 00000000 00000000 00000000 00000000 60011F90 00000000 60011F90 is fixed SQL> select current_scn from v$database; CURRENT_SCN ----------- 29875044 kslwlst_ -- waiter list latch SQL> oradebug dumpvar sga kslwlst ksllt kslwlst_ [200040AC, 20004174) = 00000000 00000009 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 ... kcfdfk -- 2 * db_files SQL> oradebug dumpvar sga kcfdfk kfil kcfdfk_ [060017EF0, 060017EF4) = 00000190 --400 when db_files=200 SQL> alter system set db_files=2000 scope=spfile; System altered. SQL> oradebug dumpvar sga kcfdfk kfil kcfdfk_ [060017EF0, 060017EF4) = 00000FA0 --4000 kcffsz Address of Datafile Size So kcbzib() passes in information about how many blocks to read ( and where to start reading from ) The assertion is if (bno < 2 || bno + nblks - 1 > (kcflsz[fno] ? kcflsz[fno] : kcffsz[fno])) ============================================================== .......NOTE: This section is removed after 9g.................. { /* if the user gave us the block number then this is an external error, * but if the dba was internally generated then this is a corruption of * some kind. */ ASSERTNM5(usrdba, OERINM("kcfrbd_2"), fno, bno, nblks, kcflsz[fno], kcffsz[fno]); SQL> oradebug dumpvar sga kcffsz ub4 * kcffsz_ [060017F20, 060017F28) = 9988E700 00000000 SQL> oradebug peek 0x9988E700 200 [09988E700, 09988E7C8) = 00000001 0007B200 00030980 0000AF00 00067CA0 00003200 00280000 00000500 00000000 00000A00 00000000 00000000 00000600 00040000 ... SQL> select blocks,to_char(blocks,'XXXXXXXX') hex_blocks from v$datafile; BLOCKS HEX_BLOCK ---------- --------- 504320 7B200 199040 30980 44800 AF00 425120 67CA0 12800 3200 2621440 280000 1280 500 0 0 2560 A00 38400 9600 6400 1900 BLOCKS HEX_BLOCK ---------- --------- 1536 600 262144 40000 13 rows selected. kcflsz -- like kcffsz SQL> oradebug dumpvar sga kcflsz ub4 * kcflsz_ [060017F28, 060017F30) = 99892588 00000000 SQL> oradebug peek 0x99892588 200 [099892588, 099892650) = 00000000 0007B200 00030980 0000AF00 00067CA0 00003200 00280000 00000500 00000000 00000A00 00000000 00000000 00000600 00040000 ... THe arguments are file number, block number, number of blocks, kcflsz[fno], kcffsz[fno] Error comes out of kcf.c. Can they try a validate structure cascade on this table and see what happens? Please update the customer field so that it does not read internal.kcf.c kcf.c Kernel Cache Files component. ksl.c Kernel Service layer Latching & Wait-post Implement. ksm.c Kernel Service Memory component implementation. kkj.c Kernel Kompiletime Job queue.
posted on 2013-03-19 00:47 Oracle和MySQL 阅读(308) 评论(0) 编辑 收藏 举报