Posts Tagged ‘SQL_ID’
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!
Dion Cho - Oracle Performance Storyteller 