LOB, sql_id and LCO

Kerry Osborne wrote an interesting case on LOB here.

His post gave me a curiosity, so I made a simple test case to emulate his case with some interesting informations.

1. Make LOB data

create table t1(c1 clob);

insert into t1 values(rpad('x',4000,'x'));

commit;

declare
  dest_lob    clob;
  src_lob     clob;
begin
  select c1 into dest_lob
  from t1
  where rownum = 1
  for update;
  
  for idx in 1 .. 100 loop
    dbms_lob.append(dest_lob, rpad('x',4000,'x'));
  end loop;
end;
/

commit;

2. Read LOB data

select * from t1;

3. While reading LOB data, query v$session in another session. As Kerry pointed out, the PREV_SQL_ID value is sql id of select query. But we have no SQL of sql id ’3aug5b01n1pgg’

UKJA@ukja102> exec print_table('select sql_id, prev_sql_id from v$session where sid=159');
SQL_ID                        : 3aug5b01n1pgg
PREV_SQL_ID                   : 27uhu2q2xuu7r
-----------------

UKJA@ukja102> select sql_text from v$sql where sql_id = '3aug5b01n1pgg';

no rows selected

UKJA@ukja102> select sql_text from v$sql where sql_id = '27uhu2q2xuu7r';

SQL_TEXT
------------------------------------------------------------------------
select * from t1

4. So, where is the object whose sql id is ’3aug5b01n1pgg’? Here it goes.

UKJA@ukja102> exec print_table('select * from sys.xm$kglob where kglobt03 = ''3aug5b01n1pgg''');
ADDR                          : 08BF90BC
INDX                          : 0
INST_ID                       : 1
KGLHDADR                      : 2A968140
KGLHDPAR                      : 2AC9FBF4
KGLHDCLT                      : 1
KGLNAOWN                      :
KGLNAOBJ                      : table_4_9_1473d_0_0_
KGLFNOBJ                      : table_4_9_1473d_0_0_
KGLNADLK                      :
KGLNAHSH                      : 54580719
KGLNAHSV                      : 7b9cdfbab84ebfd03569e5580340d5ef
KGLNATIM                      : 2009/04/20 17:51:06
KGLNAPTM                      :
KGLHDNSP                      : 0
...
KGLOBT03                      : 3aug5b01n1pgg
...
KGLOBDJV                      : 0
-----------------
ADDR                          : 08BFA0C8
INDX                          : 1
INST_ID                       : 1
KGLHDADR                      : 2AC9FBF4
KGLHDPAR                      : 2AC9FBF4
KGLHDCLT                      : 1
KGLNAOWN                      :
KGLNAOBJ                      : table_4_9_1473d_0_0_
KGLFNOBJ                      : table_4_9_1473d_0_0_
KGLNADLK                      :
KGLNAHSH                      : 54580719
KGLNAHSV                      : 7b9cdfbab84ebfd03569e5580340d5ef
KGLNATIM                      : 2009/04/20 17:51:06
KGLNAPTM                      :
KGLHDNSP                      : 0
...
KGLOBT03                      : 3aug5b01n1pgg
...
KGLOBDJV                      : 0
-----------------

x$kglob.kglobt03 is sql id and the object is table_4_9_1473d_0_0_. Here we have 2 LCOs – one is parent and the other is child.

Also note that the namespace(kglhdnsp) is 0(CURSOR). This means that this LCO is not actually LOB itself, but the internal query on LOB.

5. Library cache dump shows another interesting stuff.

-- parent LCO of the object table_4_9_1473d_0_0_0
BUCKET 54767:
  LIBRARY OBJECT HANDLE: handle=2ac9fbf4 mutex=2AC9FCA8(0)
  name=table_4_9_1473d_0_0_0
  hash=7b9cdfbab84ebfd03569e5580340d5ef timestamp=04-20-2009 17:51:06
  namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/SML/DBN/[12010044]
  kkkk-dddd-llll=0001-0001-0001 lock=0 pin=0 latch#=1 hpc=0000 hlc=0000
  lwt=2AC9FC50[2AC9FC50,2AC9FC50] ltm=2AC9FC58[2AC9FC58,2AC9FC58]
  pwt=2AC9FC34[2AC9FC34,2AC9FC34] ptm=2AC9FC3C[2AC9FC3C,2AC9FC3C]
  ref=2AC9FC70[2AC9FC70,2AC9FC70] lnd=2AC9FC7C[2B8C29A4,2B93DDB4]
    DEPENDENCY REFERENCES:
    reference latch flags
    --------- ----- -------------------
     2aa7c468     2 [60]
    LIBRARY OBJECT: object=2a96823c
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    CHILDREN: size=16
    child#    table reference   handle
    ------ -------- --------- --------
         0 2aa7cb18  2aa7c7cc 2a968140 <-- child LCO
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr
    ----- -------- -------- --------- ---- ------ ---
        0 2cfe5b94 2a9682d4 I/P/A/-/-    0 NONE   00 

-- child LCO of the object table_4_9_1473d_0_0_0
  LIBRARY OBJECT HANDLE: handle=2a968140 mutex=2A9681F4(0)
  namespace=CRSR flags=RON/KGHP/PN0/[10010000]
  kkkk-dddd-llll=0000-0041-0041 lock=0 pin=0 latch#=1 hpc=0000 hlc=0000
  lwt=2A96819C[2A96819C,2A96819C] ltm=2A9681A4[2A9681A4,2A9681A4]
  pwt=2A968180[2A968180,2A968180] ptm=2A968188[2A968188,2A968188]
  ref=2A9681BC[2AA7C7CC,2AA7C7CC] lnd=2A9681C8[2A9681C8,2A9681C8]
    CHILD REFERENCES:
    reference latch flags
    --------- ----- -------------------
     2aa7c7cc     2 CHL[02]
    LIBRARY OBJECT: object=2aa7c32c
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    DEPENDENCIES: count=1 size=16
    dependency#    table reference   handle position flags
    ----------- -------- --------- -------- -------- -------------------
              0 2aa7be88  2aa7bc68 2bb69938        0 DEP[01]  <-- Dependency
    READ ONLY DEPENDENCIES: count=1 size=16
    dependency#    table reference   handle flags
    ----------- -------- --------- -------- -------------------
              0 2aa7c6e8  2aa7c468 2ac9fbf4 /ROD/KPP[60]
    AUTHORIZATIONS: count=1 size=16 minimum entrysize=16
    00000000 3d000000 00020000 00000000 
    ACCESSES: count=1 size=16
    dependency# types
    ----------- -----
              0 0009
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr
    ----- -------- -------- --------- ---- ------ ---
        0 2aa8466c 2aa7c47c I/-/A/-/-    0 NONE   00 
        6 2aa7bc9c 2aa7b8f0 I/-/A/-/-    0 NONE   00 
        
-- The dependee object is UKJA.T1
BUCKET 91153:
  LIBRARY OBJECT HANDLE: handle=2bb69938 mutex=2BB699EC(0)
  name=UKJA.T1
  hash=3534ea19a12fe932b9932812247d6411 timestamp=04-20-2009 17:51:06
  namespace=TABL flags=KGHP/TIM/SML/[02000000]
  kkkk-dddd-llll=0000-074d-074d lock=0 pin=0 latch#=2 hpc=0004 hlc=0004
  lwt=2BB69994[2BB69994,2BB69994] ltm=2BB6999C[2BB6999C,2BB6999C]
  pwt=2BB69978[2BB69978,2BB69978] ptm=2BB69980[2BB69980,2BB69980]
  ref=2BB699B4[2BB699B4,2BB699B4] lnd=2BB699C0[2BAA6AEC,2B067B7C]
    DEPENDENCY REFERENCES:
    reference latch flags
    --------- ----- -------------------
     2aa7bc68     2 DEP[01]
     2bae61b0     1 DEP[01]
     2aa79778     1 DEP[01]
     2a2dbf8c     1 DEP[01]
     2af9332c     1 DEP[01]
     2bd67bb4     0 DEP[01]
     2aca005c     0 DEP[01]
     2be04a6c     0 DEP[01]
    LIBRARY OBJECT: object=2bfe509c
    type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr
    ----- -------- -------- --------- ---- ------ ---
        0 2bb6448c 2bfe5158 I/-/A/-/-    0 NONE   00 
        2 2bb1bc84 2aca5cdc I/-/A/-/-    0 NONE   00 
        3 2bb1bc10 2bfe658c I/-/A/-/-    0 NONE   00 
        8 2bfe5374 2c7e9de0 I/-/A/-/-    0 NONE   00 
        9 2bfe540c 2b8df62c I/-/A/-/-    0 NONE   00 
  BUCKET 91153 total object count=1
 

Yes, we can even find out which table is being related using the ugly library cache dump. But we still have no direct way to identify the existence of LOB. :(

This was a very interesting case and thank you Kerry for sharing valueable info!

About these ads