oracle工具箱

--ORACLE日常维护工具箱
DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id=>'58mxk9xtg9h0u',
plan_hash_value=>3617692013,
fixed=>'NO',
enabled=>'YES');
end;
/
select status from t1 where object_id=66;

FUNCTION ALTER_SQL_PLAN_BASELINE RETURNS BINARY_INTEGER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_HANDLE                     VARCHAR2                IN     DEFAULT
 PLAN_NAME                      VARCHAR2                IN     DEFAULT
 ATTRIBUTE_NAME                 VARCHAR2                IN
 ATTRIBUTE_VALUE                VARCHAR2                IN
 
DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
SQL_HANDLE=>'SQL_09b59a32e3d6b9fa',
PLAN_NAME=>'SQL_PLAN_0mdcu6bjxdfgu74b15d2b',
ATTRIBUTE_NAME=>'ACCEPTED',
ATTRIBUTE_VALUE=>'YES'
);
end;
/


declare
k1 clob;
begin
k1:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
sql_handle=>'SQL_09b59a32e3d6b9fa',
plan_name =>'SQL_PLAN_0mdcu6bjxdfgu74b15d2b',
verify=>'NO',
commit=> 'YES');
end;
/


DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
SQL_HANDLE=>'SQL_09b59a32e3d6b9fa',
PLAN_NAME=>'SQL_PLAN_0mdcu6bjxdfgudbd90e8e',
ATTRIBUTE_NAME=>'FIXED',
ATTRIBUTE_VALUE=>'NO'
);
end;
/


--经验前面已经小结了；使用baseline时小心参数fixed=>'YES';
我们接着第2个实验，将FIXED=>'NO'看下是何情况；    
SQL>   declare
  2   aa binary_integer;
  3   begin
  4   aa:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
  5   SQL_HANDLE=>'SQL_78a718d4c98a0dd2',
  6   plan_name=>'SQL_PLAN_7j9ssum4sn3fkb860bcf2',
  7   ATTRIBUTE_NAME=>'FIXED',
  8   ATTRIBUTE_VALUE=>'NO'
  9   );
 10   return ;
 11   end;
 12   /

PL/SQL procedure successfully completed.

SQL>  select sql_handle,sql_text,PLAN_NAME,ENABLED,ACCEPTED,FIXED from dba_sql_plan_baselines;

SQL_HANDLE                     SQL_TEXT                                           PLAN_NAME                      ENA ACC FIX
------------------------------ -------------------------------------------------- ------------------------------ --- --- ---

SQL_78a718d4c98a0dd2           select status from t2 where object_id=55           SQL_PLAN_7j9ssum4sn3fkb860bcf2 YES YES NO

1.SQL跟踪

(1)10046

SQL>  alter session set events '10046 trace name context forever,level 12';

SQL>  alter session set events '10046 trace name context off';

SQL> oradebug setospid 3574
Oracle pid: 19, Unix process pid: 3574, image: oracle@11g3 (TNS V1-V3)
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
SQL> oradebug event 10046 trace name context off;   
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/oracle11gr3/oracle11gr3/trace/oracle11gr3_ora_3574.trc


(2)跟踪其它会话

exec dbms_system.set_sql_trace_in_session(7,184,true);
exec dbms_system.set_sql_trace_in_session(7,184,false);

(3)跟踪当前会话

SQL> ALTER SESSION SET sql_trace=TRUE;
SQL> ALTER SESSION SET sql_trace=FALSE;


2.errorstack

SQL> alter session set events '12899 trace name errorstack forever,level 3';

Session altered.

SQL> insert into a values('cccc');
insert into a values('cccc')
                     *
ERROR at line 1:
ORA-12899: value too large for column "AIKI"."A"."I" (actual: 4, maximum: 2)

SQL> alter session set events '12899 trace name errorstack off';


3.缓冲区dump--如果用户没有相关权限，在UDUMP下不能生成相关文件

SQL> alter session set events 'immediate trace name heapdump level 2';

Session altered.


4.logmnr

SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/kfc/redo03.log',dbms_logmnr.new);
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

create table ta_logmnr  as select * from v$logmnr_contents;

SQL> exec dbms_logmnr.end_logmnr;


5.10053

SQL> alter session set events='10053 trace name context forever,level 1';

Session altered.

SQL> select count(*) from t1;

  COUNT(*)
----------
     50000

SQL>  alter session set events='10053 trace name context off';


--------------------------------------sql plan-----------------
2.查看执行计划

(1).explain plan for

sql;

select * from table(dbms_xplan.display);

(2).select * from table(dbms_xplan.display_cursor('5udc2pp1r6ua7',null,'BASIC')); --利用存储包

(3).select * from table(dbms_xplan.display_awr('6ujc0p8bzm409')); --利用AWR脚本

3.10046跟踪

oradebug setmypid
oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
oradebug TRACEFILE_NAME
执行sql
oradebug EVENT 10046 trace name context off

4.分析表 -- exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);

5.修改sequence的cache值:alter sequence seq_share cache 1000;

6.dbms_job(包体)

7.查询UNDO使用情况以及具体SQL占用的UNDO块

SELECT DISTINCT tablespace_name,STATUS "状态",
                COUNT(*) "EXTENT数量",
                SUM(BYTES) / 1024 / 1024 / 1024 "UNDO大小"
  FROM DBA_UNDO_EXTENTS
 GROUP BY STATUS,tablespace_name
 order by 1,2;

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

select s.username, u.name, s.sql_id, t.used_ublk, sq.sql_text
from v$transaction t, v$rollstat r, v$rollname u, v$session s, v$sql sq
where s.taddr = t.addr
and t.xidusn = r.usn
and r.usn = u.usn
and sq.sql_id = nvl(s.sql_id, s.PREV_SQL_ID)
order by s.username;

8.停止JOB

begin
DBMS_JOB.BROKEN (6718,true);
end;

commit;

9.查询SHARE POOL字池的空闲空间

select subpool,name,sum(bytes),round(sum(bytes)/1048576,2)mb
from (select 'shared pool('||decode(to_char(ksmdsidx),'0','0-Unused',ksmdsidx)||'):' subpool,ksmssnam name,
ksmsslen bytes
from x$ksmss where ksmsslen>0
and lower(ksmssnam) like lower('%free memory%'))
group by subpool,name order by subpool asc,sum(bytes) desc

10.查询对象ITL waits事件统计排名

select t.owner,t.object_name,t.object_type,statistic_name,t.value value
from v$segment_statistics t
where t.statistic_name='ITL waits'
AND t.value>10
order by value

11.PGA优化使用目标

workarea execution-optimal >=90%

workarea execution-multipass=0%

select name,value,100*(value/decode((select sum(value) from v$sysstat
where name like 'workarea executions%'),0,null,(select sum(value) from v$sysstat
where name like 'workarea executions%'))) pct
from v$sysstat where name like 'workarea executions%'


1.     SQL

SELECT
   a.sql_text,
   b.name,
   b.position,
   b.datatype_string,
   b.value_string
FROM
  v$sql_bind_capture b,
  v$sqlarea          a
WHERE
   b.sql_id = 'dpf3w96us2797'
AND

   b.sql_id = a.sql_id;


2.

You can run a 10046 level 4 or 12 trace to debug code with bind variables and see the bind variable values as it steps through the code:

EXECUTE SYS.dbms_system.set_ev (42, 45529,10046,12,'');

然后在跟踪文件里可以看到绑定变量的值;

(1 = no binds,no waits, 4 = binds, no waits, 8 = no binds, waits, 12 = binds, waits)


3.注意事项

This view is populated only when you set STATISTICS_LEVEL parameter to ALL/TYPICAL . Bind capturing is disabled when the STATISTICS_LEVEL initialization parameter is set to BASIC.


--相看分区表的相关列和类型
select t.table_name,kc.column_name,t.partitioning_type
        from dba_part_key_columns kc,dba_part_tables t
        where kc.owner=t.owner
        and kc.name=t.table_name
        and t.table_name='XXX'';
--物化视图

1.根据要找的物化视图名查找其JOB_NAME；
select owner,job_name,job_action,schedule_name,repeat_interval from  dba_scheduler_jobs where upper(job_action) like '%MV_T1%'

2.查询物化视图通过scheduler跑的日志
select job_name,owner,log_date,status  from dba_scheduler_job_log where job_name='JOB_T1' and  log_date=(select max(log_date) from dba_scheduler_job_log
where job_name='JOB_T1');

3.根据scheduler_name来查找其定义
select owner,schedule_name,repeat_interval from dba_scheduler_schedules;

4.查看所有的物化视图运行的最后时间
select owner,job_name,job_action,start_date,schedule_name,last_start_date,next_run_date from dba_scheduler_jobs

5.查询物化视图最后一次运行的状态
select job_name,owner,log_date,status  from dba_scheduler_job_log
where (job_name,log_date) in (select  job_name,max(log_date) from dba_scheduler_job_log
group by job_name);

6.查看物化视图的定义
select mview_name,query,master_link  from dba_mviews;

--分区索引与分区表
1.查看表与它的分区信息
select table_owner,table_name,partition_name from DBA_TAB_PARTITIONS;

2.查看分区表及其分区的列
select owner,name,object_type,column_name,column_position from DBA_PART_KEY_COLUMNS;

3.查看分区表及其分区的类型
select owner,table_name,partitioning_type,partition_count from DBA_PART_TABLES  where owner='AIKI';

4.查看分区索引的名称
select owner,index_name,table_name,partitioning_type,partition_count,locality from dba_part_indexes where owner='AIKI';

5.查询分区索引以及所在的分区
select index_owner,index_name,partition_name from dba_ind_partitions
where index_owner='AIKI';



set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
select dbms_sqltune.report_sql_monitor(sql_id=>'SQL_ID',type=>'ACTIVE') from dual;

set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000

CRT->菜单（文件）->记录会话 ->保存为08.html

执行select dbms_sqltune.report_sql_monitor(sql_id=>'g15npk38kt5bc',type=>'ACTIVE') from dual;

->停止记录

打开08.html

 create materialized view mvmvt01 build immediate refresh fast with rowid as select * from mvt01;
exec dbms_mview.refresh(’GDHSC.xxx');



--并行DML
alter session force parallel dml;
和
alter session enable parallel dml & /*+ parallel */二者要联用

--并行查询
alter session force parallel query parallel 8;


--dump进程
oradebug setospid <process id>
oradebug unlimit
oradebug dump processstate 10
oradebug tracefile_name

--查询分区的类型和分区列
select partItioning_type,subpartItioning_type from dba_part_tables where table_name='PT01';
select * from dba_part_key_columns where name='PT01';

--查询隐藏参数
set linesize 132
column name format a30
column value format a25
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 and
  x.ksppinm like '%_&par%'
order by
  translate(x.ksppinm, ' _', ' ')
/


declare
 v1 raw(32):='78710C030B3C23';
v2 date;
begin
dbms_stats.convert_raw_value(v1,v2);
dbms_output.put_line(v2);
end;
--查看RAW的存储内容

--检查未使用绑定变量的SQL

create table shpool_rate as select sql_text,MODULE from v$sqlarea;

alter table shpool_rate add sql_text_wo_constants varchar2(1000);

create or replace function
remove_constants( p_query in varchar2 ) return varchar2
as
    l_query long;
    l_char  varchar2(10);
    l_in_quotes boolean default FALSE;
begin
    for i in 1 .. length( p_query )
    loop
        l_char := substr(p_query,i,1);
        if ( l_char = '''' and l_in_quotes )
        then
            l_in_quotes := FALSE;
        elsif ( l_char = '''' and NOT l_in_quotes )
        then
            l_in_quotes := TRUE;
            l_query := l_query || '''#';
        end if;
        if ( NOT l_in_quotes ) then
            l_query := l_query || l_char;
        end if;
end loop;
    l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
    for i in 0 .. 8 loop
        l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
        l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
    end loop;
    return upper(l_query);
end;
/



update shpool_rate set sql_text_wo_constants = remove_constants(sql_text);


set line 200 pagesize 2000;
col sql_text_wo_constants for a60;
col MODULE for a30
select sql_text_wo_constants,MODULE ,count(*)
  from shpool_rate
 group by sql_text_wo_constants,MODULE
having count(*) > 50000
 order by 3 desc;




drop table shpool_rate;
create table shpool_rate as select sql_text,MODULE from v$sqlarea;
alter table shpool_rate add sql_text_wo_constants varchar2(2000);
update shpool_rate set sql_text_wo_constants = remove_constants(sql_text);
commit;
set line 200 pagesize 2000;
col sql_text_wo_constants for a60;
col MODULE for a30
select sql_text_wo_constants,MODULE ,count(*)
  from shpool_rate
 group by sql_text_wo_constants,MODULE
having count(*) > 50000
 order by 3 desc;

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



--刷新频率写法
SCH_REFRESH_MVIEW_12H0
FREQ=HOURLY;byhour=1,13;BYMINUTE=21

SCH_REFRESH_MVIEW_12H9
FREQ=HOURLY;byhour=1,13;BYMINUTE=19

SCH_REFRESH_MVIEW_1D3
FREQ=DAILY; INTERVAL=1;byhour=3;BYMINUTE=27

SCH_REFRESH_MVIEW_1D6
FREQ=DAILY; INTERVAL=1;byhour=3;BYMINUTE=57

SCH_REFRESH_MVIEW_1D8
FREQ=DAILY; INTERVAL=1;byhour=3;BYMINUTE=14


col program for a30
col machine for a30
set line 180
select sql_id,username,program,machine,last_call_et from v$session where event#=240
--------
--procedure sleep
create or replace procedure sleep(in_var_num number) as
a date;
b date;
c number;
begin
        select to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') into a from dual;
        loop
        select to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') into b from dual;
  c:=trunc((b-a)*86400-0.5);  --5.01
        if c=in_var_num then
        exit;
  end if;
  end loop;
end;
/

--查询阻塞锁情况精确到ROWID
select a.sid,b.DATA_OBJECT_ID,a.ROW_WAIT_FILE#,a.ROW_WAIT_BLOCK#,a.ROW_WAIT_ROW# from v$session a,dba_objects b
where a.sql_id='0gx70ak8bgk1d'
and  a.ROW_WAIT_OBJ#=b.object_id;


select DBMS_ROWID.ROWID_CREATE(1,1067580,576,992711,2) from dual;
select DBMS_ROWID.ROWID_CREATE(1,1073631,91,3746291,2) from dual;

select owner,object_name,subobject_name,object_type from dba_objects where DATA_OBJECT_ID=1067580;
select owner,object_name,subobject_name,object_type from dba_objects where DATA_OBJECT_ID=1073631;

=>oradebug dump library_cache 10;
Bucket: #=3289 Mutex=0x77990f00(0, 3, 0, 6)
  LibraryHandle:  Address=0x7519d9a8 Hash=13220cd9 LockMode=0 PinMode=0 LoadLockMode=0 Status=0
    ObjectName:  Name=$BUILD$.43dc3e47a8324671   
      FullHashValue=f8f3115a92da2dcff388208b13220cd9 Namespace=SQL AREA BUILD(82) Type=CURSOR(00) Identifier=0 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=0 ActiveLocks=0 TotalLockCount=1 TotalPinCount=0
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x7519da58(0, 0, 0, 0) Mutex=0x7519dad8(1, 4, 0, 6)
    Flags=RON/[00010000]
    WaitersLists:  
      Lock=0x7519da38[0x7519da38,0x7519da38]
      Pin=0x7519da18[0x7519da18,0x7519da18]
      LoadLock=0x7519da90[0x7519da90,0x7519da90]
    Timestamp:  
    HandleReference:  Address=0x7519db58 Handle=(nil) Flags=[00]
    
=>oradebug dump row_cache 10;
BUCKET 2968:
  row cache parent object: address=0x74656c88 cid=2(dc_segments)
  hash=5030b97 typ=9 transaction=(nil) flags=00000002
  own=0x74656d50[0x74656d50,0x74656d50] wat=0x74656d60[0x74656d60,0x74656d60] mode=N
  status=VALID/-/-/-/-/-/-/-/-
  data=
  00000001 00000002 00000d02 00000000 00000008 00000001 7ffffffd 00000080
  00000000 00000006 00000008 00000001 00000000 0000003f 00420101 000016de
  00000004 00000000 7ffffffd 00000004 00000000 00000000 00000000 05030b97
  74656c88 00000000 7803f990 00000000 7803f990 00000000
  BUCKET 2968 total object count=1
BUCKET 2996:
  row cache parent object: address=0x745ec878 cid=2(dc_segments)
  hash=83a60bb3 typ=9 transaction=(nil) flags=00000002
  own=0x745ec940[0x745ec940,0x745ec940] wat=0x745ec950[0x745ec950,0x745ec950] mode=N
  status=VALID/-/-/-/-/-/-/-/-
  data=
  00000001 00000002 000114c2 00000000 00000008 00000001 7ffffffd 00000080
  00000000 00000006 00000008 00000001 00000000 0000003f 00420101 00012d55
  00000004 00000000 7ffffffd 00000004 00000000 00000000 00000000 83a60bb3
  745ec878 00000000 7803fb50 00000000 7803fb50 00000000
  BUCKET 2996 total object count=1
 
      

=>SQL> oradebug dump buffers 10;
 Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8     
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x008009ab  ext#: 0      blk#: 3      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 0     
  mapblk  0x00000000  offset: 0     
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x008009ab  ext#: 0      blk#: 3      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 0     
  mapblk  0x00000000  offset: 0     
  Level 1 BMB for High HWM block: 0x008009a8
  Level 1 BMB for Low HWM block: 0x008009a8
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0      
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x008009a9
  Last Level 1 BMB:  0x008009a8
  Last Level II BMB:  0x008009a9
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 1    obj#: 75362  flag: 0x10000000
  Inc # 0
  Extent Map
  -----------------------------------------------------------------
   0x008009a8  length: 8     
 
  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x008009a8 Data dba:  0x008009ab
  --------------------------------------------------------
 
   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x008009a9
 
    BH (0x6e7d8018) file#: 1 rdba: 0x004162f1 (1/90865) class: 1 ba: 0x6e402000
      set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
      dbwrid: 0 obj: 421 objn: 423 tsn: 0 afn: 1 hint: f
      hash: [0x7af88670,0x6bfe3418] lru: [0x6e3e0e50,0x6e3daa90]
      lru-flags: debug_dump
      ckptq: [NULL] fileq: [NULL] objq: [0x6d7e6b18,0x6e7dad18] objaq: [0x6e3f73f8,0x6dff98c8]
      st: XCURRENT md: NULL fpin: 'kdswh06: kdscgr' tch: 1
      flags: block_written_once redo_since_read
      LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
      buffer tsn: 0 rdba: 0x004162f1 (1/90865)
      scn: 0x0000.00242f54 seq: 0x01 flg: 0x06 tail: 0x2f540601
      frmt: 0x02 chkval: 0x3d23 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000006E402000 to 0x000000006E404000
06E402000 0000A206 004162F1 00242F54 06010000  [.....bA.T/$.....]
06E402010 00003D23 00000001 000001A5 00242F51  [#=..........Q/$.]
06E402020 00000000 00030002 00000000 000E0002  [................]
06E402030 00000359 00C01A15 000400D0 00008000  [Y...............]
06E402040 0011D23C 001B0008 0000054F 00C0202A  [<.......O...* ..]

      
 =>oradebug dump enqueues 10;     
      
res: 0x7a970d40 , flags: (0x2) ALO/-/-/-/-/-
  held counts: NULL=0 , SS=0 , SX=1 , S=0 , SSX=0 , X=0 (bmask: 0x8)
  lock value: 00000000 00000000 00000000 00000000
  htb(0x7a970d40): [7a9c2468,7a9c2468]
  own(0x7a970d50): [7a8357e8,7a8357e8]
  cnv(0x7a970d70): [7a970d70,7a970d70]
  wtr(0x7a970d60): [7a970d60,7a970d60]
 
lock summary:
  OWN: L1:SX
  CNV:
  WTR:
  # of incomp w/ SS converters: 0
  # of incomp w/ SS waiters: 0
 
lock details:
 
  lock                     lst hold wait   sid:ser   owner      link
  -------------------------------------------------------------------------
    L1: 0x7a8357d8 OWN   SX   --    13:1     0x7b27bcc0 [7a970d50,7a970d50]
 
-------------------------------------------------------------------------
KD-00000000-00000000      




=> oradebug dump latches 10;
 7a250780 Child simulator hash latch level=7 child#=19
        Location from where latch is held: kcbs.h LINE:799 ID:kcbsacc: lookup dba: dba
        Context saved from call: 4195105
        state=free [value=0] wlstate=free [value=0]
    gotten 721 times wait, failed first 0 sleeps 0
    gotten 0 times nowait, failed: 0
 7a250698 Child simulator hash latch level=7 child#=18
        Location from where latch is held: kcbs.h LINE:837 ID:kcbs_get_or_extract_simbufseq: dba
        Context saved from call: 12591641
        state=free [value=0] wlstate=free [value=0]
    gotten 996 times wait, failed first 0 sleeps 0
    gotten 0 times nowait, failed: 0
 7a2505b0 Child simulator hash latch level=7 child#=17
        Location from where latch is held: kcbs.h LINE:837 ID:kcbs_get_or_extract_simbufseq: dba
        Context saved from call: 12591659
        state=free [value=0] wlstate=free [value=0]
    gotten 1274 times wait, failed first 0 sleeps 0
    gotten 0 times nowait, failed: 0
    
    
    
=>oradebug dump headdump 2

=>oradebug dump systemstate 10;

=>oradebug setospid <pid>
oradebug dump processstate 10

oracle X$表命名约定

 

[plain] 

X$表命名约定  

[plain] 

[K]ernel Layer  

  [2]-Phase Commit  

  [G]lobal [T]ransaction [E]ntry  

    X$K2GTE - Current 2PC tx  

    X$K2GTE2 - Current 2PC tx  

  

[C]ache Layer  

  [B]uffer Management  

    Buffer [H]ash  

      X$BH - Hash Table  

  

Buffer LRU Statistics  

  X$KCBCBH - [C]urrent [B]uffers (buckets) - lru_statistics  

  X$KCBRBH - [R]ecent [B]uffers (buckets) - lru_extended  

  

Buffer [WAIT]s  

  X$KCBWAIT - Waits by block class  

  X$KCBFWAIT - Waits by File  

  

[W]orking Sets - 7.3 or higher  

  X$KCBWDS - Set [D]escriptors  

  

[C]ontrol File Management  

  [C]ontrol [F]ile List - 7.0.16 or higher  

    X$KCCCF - Control File Names & status  

  

[D]atabase [I]nformation  

  X$KCCDI - Database Information  

  

Data [F]iles  

  X$KCCFE - File [E]ntries ( from control file )  

  X$KCCFN - [F]ile [N]ames  

  

[L]og Files  

  X$KCCLE - Log File [E]ntries  

  X$KCCLH - Log [H]istory ( archive entries )  

  

Thread Information  

  X$KCCRT - [R]edo [T]hread Information  

  [F]ile Management  

  X$KCFIO - File [IO] Statistics  

  

[L]ock Manager Component ( LCK )  

  [H]ash and Bucket Tables - 7.0.15 to 7.1.1, and 7.2.0 or higher  

    X$KCLFH - File [H]ash Table  

    X$KCLFI - File Bucket Table  

  

X$LE - Lock [E]lements  

X$LE_STAT - Lock Conversion [STAT]istics  

X$KCLFX - Lock Element [F]ree list statistics - 7.3 or higher  

X$KCLLS - Per LCK free list statistics - 7.3 or higher  

X$KCLQN - [N]ame (hash) table statistics - 7.3 or higher  

  

[R]edo Component  

  [M]edia recovery - kcra.h - 7.3 or higher  

    X$KCRMF - [F]ile context  

    X$KCRMT - [T]hread context  

    X$KCRMX - Recovery Conte[X]t  

  

[F]ile read  

  X$KCRFX - File Read Conte[X]t - 7.3 or higher  

  

Reco[V]ery Component  

  [F]ile [H]eaders  

    X$KCVFH - All file headers  

    X$KCVFHMRR - Files with [M]edia [R]ecovery [R]equired  

    X$KCVFHONL - [ONL]ine File headers  

  

[K]ompatibility Management - 7.1.1 or higher  

  X$KCKCE - [C]ompatibility Segment [E]ntries  

  X$KCKTY - Compatibility [TY]pes  

  X$KCKFM - Compatibility [F]or[M]ats ( index into X$KCKCE )  

  

[D]ata Layer  

  Sequence [N]umber Component  

  X$KDNCE - Sequence [C]ache [E]ntries - 7.2 or lower  

  

[S]equence Enqueues - common area for enqueue objects  

  X$KDNSSC - [C]ache Enqueue Objects - 7.2 or lower  

  X$KDNSSF - [F]lush Enqueue Objects - 7.2 or lower  

  X$KDNST - Cache [ST]atistics - 7.2 or lower  

  

Inde[X] Block Component  

  X$KDXHS - Index [H]i[S]togram  

  X$KDXST - Index [ST]atistics  

  

[G]eneric Layer  

  [H]eap Manager  

    X$KGHLU - State (summary) of [L]R[U] heap(s) - defined in ksmh.h  

  

[I]nstantiation Manager  

  [C]ursor [C]ache  

    X$KGICC - Session statistics - defined in kqlf.h  

    X$KGICS - System wide statistics - defined in kqlf.h  

  

[L]ibrary Cache Manager ( defined and mapped from kqlf )  

  Bind Variables  

    X$KKSBV - Library Object [B]ind [V]ariables  

  

Object Cache  

  X$KGLOB - All [OB]jects  

  X$KGLTABLE - Filter for [TABLE]s  

  X$KGLBODY - Filter for [BODY] ( packages )  

  X$KGLTRIGGER - Filter for [TRIGGER]s  

  X$KGLINDEX - Filter for [INDEX]es  

  X$KGLCLUSTER - Filter for [CLUSTER]s  

  X$KGLCURSOR - Filter for [CURSOR]s  

  

Cache Dependency  

  X$KGLDP - Object [D]e[P]endency table  

  X$KGLRD - [R]ead only [D]ependency table - 7.3 or higher  

  

Object Locks  

  X$KGLLK - Object [L]oc[K]s  

  

Object Names  

  X$KGLNA - Object [NA]mes (sql text)  

  X$KGLNA1 - Object [NA]mes (sql text) with newlines - 7.2.0 or higher  

  

Object Pins  

  X$KGLPN - Object [P]i[N]s  

  

Cache Statistics  

  X$KGLST - Library cache [ST]atistics  

  

Translation Table  

  X$KGLTR - Address [TR]anslation  

  

Access Table  

  X$KGLXS - Object Access Table  

  

Authorization Table - 7.1.5 or higher  

  X$KGLAU - Object Authorization table  

  

Latch Cleanup - 7.0.15 or higher  

  X$KGLLC - [L]atch [C]leanup for Cache/Pin Latches  

  

[K]ompile Layer  

  [S]hared Objects  

    X$KKSAI - Cursor [A]llocation [I]nformation - 7.3.2 or higher  

  

[L]oader  

  [L]ibrary  

    X$KLLCNT - [C]o[NT]rol Statistics  

    X$KLLTAB - [TAB]le Statistics  

  

[M]ulti-Threaded Layer  

  [C]ircuit component  

    X$KMCQS - Current [Q]ueue [S]tate  

    X$KMCVC - [V]irtual [C]ircuit state  

  

[M]onitor Server/dispatcher  

  [D]ispatcher  

    X$KMMDI - [D]ispatcher [I]nfo (status)  

    X$KMMDP - [D]ispatcher Config ( [P]rotocol info )  

  

[S]erver  

  X$KMMSI - [S]erver [I]nfo ( status )  

  X$KMMSG - [SG]a info ( global statistics)  

  X$KMMRD - [R]equest timing [D]istributions  

  

s[Q]l Version and Option Layer  

  Kernel [V]ersions  

    X$VERSION - Library versions  

  

Kernel [O]ptions - 7.1.3 or higher  

  X$OPTION - Server Options  

  

[Q]uery Layer  

  [D]ictionary Cache Management  

  X$KQDPG - [PG]a row cache cursor statistics  

  

[F]ixed Tables/views Management  

  X$KQFCO - Table [CO]lumn definitions  

  X$KQFDT - [D]erived [T]ables  

  X$KQFSZ - Kernel Data structure type [S]i[Z]es  

  X$KQFTA - Fixed [TA]bles  

  X$KQFVI - Fixed [VI]ews  

  X$KQFVT - [V]iew [T]ext definition - 7.2.0 or higher  

  

[R]ow Cache Management  

  X$KQRST - Cache [ST]atistics  

  X$KQRPD - [P]arent Cache [D]efinition - 7.1.5 or higher  

  X$KQRSD - [S]ubordinate Cache [D]efinition - 7.1.5 or higher  

  

[S]ervice Layer  

  [B]ackground Management  

  [D]etached Process  

  X$KSBDD - Detached Process [D]efinition (info)  

  X$KSBDP - Detached [P]rocess Descriptor (name)  

  X$MESSAGES - Background Message table  

  

[I]nstance [M]anagement - 7.3 or higher  

  X$KSIMAT - Instance [AT]tributes  

  X$KSIMAV - [A]ttribute [V]alues for all instances  

  X$KSIMSI - [S]erial and [I]nstance numbers  

  

[L]ock Management  

  [E]vent Waits  

    X$KSLED - Event [D]escriptors  

    X$KSLEI - [I]nstance wide statistics since startup  

    X$KSLES - Current [S]ession statistics  

  

[L]atches  

  X$KSLLD - Latch [D]escriptor (name)  

  X$KSLLT - Latch statistics [ + Child latches @ 7.3 or higher ]  

  X$KSLLW - Latch context ( [W]here ) descriptors - 7.3+  

  X$KSLPO - Latch [PO]st statistics - 7.3 or higher  

  X$KSLWSC- No[W]ait and [S]leep [C]ount stats by Context -7.3+  

  

[M]emory Management  

  [C]ontext areas  

    X$KSMCX - E[X]tended statistics on usage - 7.3.1 or lower  

  

  Heap Areas  

    X$KSMSP - SGA Hea[P]  

    X$KSMPP - [P]GA Hea[P] - 7.3.2 and above  

    X$KSMUP - [U]GA Hea[P] - 7.3.2 and above  

    X$KSMHP - Any [H]ea[P] - 7.3.2 and above  

    X$KSMSPR- [S]hared [P]ool [R]eserved List - 7.1.5 or higher  

  

  [L]east recently used shared pool chunks  

    X$KSMLRU - LR[U] flushes from the shared pool  

  

  [S]GA Objects  

    X$KSMSD - Size [D]efinition for Fixed/Variable summary  

    X$KSMSS - Statistics (lengths) of SGA objects  

  

  SGA [MEM]ory  

    X$KSMMEM - map of the entire SGA - 7.2.0 or higher  

    X$KSMFSV - Addresses of [F]ixed [S]GA [V]ariables - 7.2.1+  

  

[P]arameter Component  

  X$KSPPI - [P]arameter [I]nfo ( Names )  

  X$KSPPCV - [C]urrent Session [V]alues - 7.3.2 or above  

  X$KSPPSV - [S]ystem [V]alues - 7.3.2 or above  

  

En[Q]ueue Management  

  X$KSQDN - Global [D]atabase [N]ame  

  X$KSQEQ - [E]n[Q]ueue Object  

  X$KSQRS - Enqueue [R]e[S]ource  

  X$KSQST - Enqueue [S]tatistics by [T]ype  

  

[U]ser Management  

  [C]ost  

  X$KSUCF - Cost [F]unction (resource limit)  

  

  [L]icence  

    X$KSULL - Licence [L]imits  

  

  [L]anguage Manager  

    X$NLS_PARAMETERS - NLS parameters  

    X$KSULV - NLS [V]alid Values - 7.1.2 or higher  

  

  [MY] [ST]atistics  

    X$KSUMYSTA - [MY] [ST]atisics (current session)  

  

  [P]rocess Info  

    X$KSUPL - Process (resource) [L]imits  

    X$KSUPRLAT - [LAT]ch Holder  

    X$KSUPR - Process object  

  

  [R]esource  

    X$KSURU - Resource [U]sage  

  

  [S]tatistics  

    X$KSUSD - [D]escriptors (statistic names)  

    X$KSUSGSTA - [G]lobal [ST]atistics  

  

  [SE]ssions  

    X$KSUSECST - Session status for events  

    X$KSUSESTA - Session [STA]tistics  

    X$KSUSECON - [CON]nection Authentication - 7.2.1 or higher  

    X$KSUSE - [SE]ssion Info  

    X$KSUSIO - [S]ystem [IO] statistics per session  

  

  [T]imer  

    X$KSUTM - Ti[M]e in 1/100th seconds  

    Instance [X]  

    X$KSUXSINST - [INST]ance state  

  

  [T]race management  

    X$TRACE - Current traced events  

    X$TRACES - All possible traces  

    X$KSTEX - Code [EX]ecution - 7.2.1 or higher  

  

  E[X]ecution Management  

    Device/Node [A]ffinity - 7.3.2 and above  

    X$KSXAFA - Current File/Node Affinity  

  

[T]ransaction Layer  

  Table [A]ccess [D]efinition  

    X$KTADM - D[M]L lock  

  

  [C]ontrol Component  

    X$KTCXB - Transaction O[B]ject  

  

  [S]or[T] Segments - 7.3 or higher  

    X$KTSTSSD - [S]ort [S]egment [D]escriptor - per tablespace stats  

  

  [T]ablespace  

  X$KTTVS - [V]alid [S]aveundo  

  

  [U]ndo  

    X$KTURD - Inuse [D]escriptors  

    X$KTUXE - Transaction [E]ntry (table) - 7.3.2 or above  

  

Performance Layer [V] - 7.0.16 or higher  

  [I]nformation tables  

  X$KVII - [I]nitialisation Instance parameters  

  X$KVIS - [S]izes of structure elements  

  X$KVIT - [T]ransitory Instance parameters  

  

Security Layer [Z]  

  [D]ictionary Component  

    X$KZDOS - [OS] roles  

  

  [S]ecurity State  

    X$KZSPR - Enabled [PR]ivileges  

    X$KZSRO - Enabled [RO]les  

  

  [R]emote Logins - 7.1.1 or higher  

    X$KZSRT - [R]emote Password File [T]able entries  

  

E[X]ecution Layer  

  Parallel Query (Execute [F]ast) - 7.1.1 or higher  

    [P]rocess and Queue Manager  

      Statistics - 7.1.3 or higher  

        X$KXFPYS - S[YS]tem Statistics  

        X$KXFPDP - [D]etached [P]rocess (slave) statistics  

        X$KXFQSROW - Table [Q]ueue Statistics - 7.3.2 or higher  

  

      [C]oordinator Component  

        X$KXFPCST - Query [ST]atistics  

        X$KXFPCMS - [M]essage [S]tatistics  

        X$KXFPCDS - [D]equeue [S]tatistics  

  

      [S]lave Component  

        X$KXFPSST - Query [ST]atistics  

        X$KXFPSMS - [M]essage [S]tatistics  

        X$KXFPCDS - [D]equeue [S]tatistics  

  

  [S]hared Cursor  

    X$KXSBD - [B]ind [D]ata - 7.3.2 and above  

    X$KXSCC - SQL [C]ursor [C]ache Data - 7.3.2 and above  

    [N]etwork Layer - 7.0.15 or higher  

  

Network [CO]nnections  

  X$UGANCO - Current [N]etwork [CO]nnections  

  Network [CO]nnections  

    X$UGANCO - Current [N]etwork [CO]nnections