关于Know More About Oracle Row Lock的疑问

gdpr-dba

看了刘大的文章http://www.oracledatabase12g.com/archives/know-more-about-oracle-row-lock.html，感觉有几点不明白的：

1.enqueue结构是存在sga、pga还是shared pool里面的？

2.oradebug dump systemstate 266
266是啥意思？哪里可以查看这些internal命令的用法啊？

3.alter system set "_trace_events"='10000-10999:255:24';
同样，这个参数是怎么用的？具体数字代表什么意思？哪里可以查看这些隐藏参数的用法？

Maclean Liu(刘相兵

FOR QUESTION 1:

ODM TEST:
SQL> select * from v$sgastat where name like '%enq%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  enqueue                        392088
shared pool  enqueue_hash_chain_latche         320
shared pool  enqueue resources              135008
shared pool  enqueue_hash                    18000

enqueue 结构被存放在 sga 的 shared pool中 以 fixed array的形式


FOR QUESTION 2：

问问题之前 请在论坛内先搜索  http://t.askmaclean.com/viewthre ... intable&tid=212

dump systemstate的语法为：

ALTER SESSION SET EVENTS ‘immediate trace name systemstate level 10′;

也可以使用ORADEBUG实现这个功能:

sqlplus -prelim / as sysdba

oradebug setmypid

oradebug unlimit;

oradebug dump systemstate 10

如果希望在数据库发生某种错误时调用systemstate事件，可以在参数文件（spfile或者pfile）中设置event参数，

例如，当系统发生死锁（出现ORA-00060错误）时dump systemstate：

event = “60 trace name systemstate level 10″



LEVEL参数：

10    Dump all processes (IGN state)

5     Level 4 + Dump all processes involved in wait chains (NLEAF state)

4     Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)

3     Level 2 + Dump only processes thought to be in a hang (IN_HANG state)

1-2   Only HANGANALYZE output, no process dump at all


level 266= SYSTEM STATE (level=10, with short stacks) =  level 10 + short stacks

level 266 在level 10的基础上包含了进程的short stacks信息


clarify the level 267 you discussed at the meeting, as well as the
steps for the customer to follow to take the dump. We had given them info
below before.

The levels are defined as follows:

    * 1         Very basic process information only
    * 2         Process + session state objects
    * 10        Most common level - includes state object trees for all
processes
    * Level+256  Adding 256 to the level will try to dump short stack info
for each process.



For Oracle 9.2.0.5 and less use level 10 instead of 266

ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 266′;

Level 266 includes short stacks (Oracle function calls) which are useful for Oracle Developers to determine which Oracle function’s are causing the problem. This is also helpful in matching existing bugs.


systemstate 267                 -- dump systemstate with short callstacks

Processing Oradebug command 'dump systemstate 267'
===================================================
SYSTEM STATE (level=11, with short stacks)

267 = 100001011 = short stacks + Dump all processes + HANGANALYZE output

总结：

level 266= SYSTEM STATE (level=10, with short stacks) =  level 10 + short stacks

level 266 在level 10的基础上包含了进程的short stacks信息

level 10  = Dump all processes (IGN state)

level 267= 100001011 = short stacks + Dump all processes + HANGANALYZE output

FOR Question 3:

这是 KST TRACE的设置方法：

- _trace_events: specify which event(s), level and processes to be traced (default: NULL)

例如"_trace_events"='10000-10999:255:24';

意思是对 PID=24的进程 做 10000-10999 之间所有event 的 255级别 level 的TRACE

gdpr-dba

刘大帮忙看看，谢谢。

aaaaaaaa2000

看错了，你要看的是隐藏参数的用法.....

aaaaaaaa2000

查询隐藏函数
select
    x.ksppinm    name,
    y.ksppstvl    value,
    y.ksppstdf  isdefault,
  decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
  decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj
from
  sys.x$ksppi x,
  sys.x$ksppcv y
where
  x.inst_id = userenv('Instance') and
  y.inst_id = userenv('Instance') and
  x.indx = y.indx  
order by
  translate(x.ksppinm, ' _', ' ')