What are the table_a_b_c_d_e_f SQLs?

Sometimes when you join v$session.sql_id (or hash_value) to v$sql% family views, you don't see the SQL. But you find the SQL in v$open_cursor. Unfortunately, the SQL looks like table_a_b_c_d_e_f, for example, table_4_200_4139_0_0_0. I've never figured out why Oracle needs to create these pseudo cursors. I even opened an SR ask this (7142902.992, for those who can see), but it turned out to be a waste of time.

On further research, you find that common to all these pseudo SQLs is that the c component here is the object_id in hex for a table that has LOB or object type column(s), and the a component probably refers to the SQL command type, 4 for select, etc. This encouraging news helps you narrow down the actual dependent SQL that created these mysterious pseudo SQLs, because you can search for v$sql% views for sql_text like '%THETABLE%', where THETABLE has the said object_id. But that's far from satisfactory. What if multiple SQLs reference the same LOB-containing SQL? How do you know which one is actually run by the session? This short note explains my preliminary findings.

The heart of the finding is about x$kgltr (kernel generic, librarycache, translation), a fixed table not exposed to any v$ view. The three columns of this table, kglhdadr, kgltrorg, and kgltrfnl, represent the handle address of the alias (not sure if this is a proper name), the address of the referent, and the address of the final referent in case of multiple reference translation. Basically, the pseudo SQL table_a_b_c_d_e_f will be the referent or final referent (maybe just final referent but I'll verify later), and the alias is the actual SQL that indirectly created the cryptic SQL. With this in mind, the query below can help you find the actual SQL

select a.kglnaobj -- distinct may be needed because v$object_dependency may have multiple entries
from x$kglob a, x$kgltr b, v$object_dependency c, dba_objects d
where a.kglhdadr = b.kglhdadr
 and (b.kgltrorg = c.to_address or b.kgltrfnl = c.to_address) -- just b.kgltrfnl... may be enough
 and c.to_name = d.object_name
 and c.to_owner = d.owner
 and d.object_id = to_number('&hexobjid','xxxxxxxx')  -- hexobjid is e.g. 4139
/


Well, not all SQLs can be found this way, for various reasons, the real, dependent, SQL may have been aged out, or my finding is imperfect. I actually wanted to see if I can enumerate all the pseudo SQLs to find their actual SQLs. The following code

with x as (
select /*+ materialize */ to_number(replace(regexp_substr(e.name,'_[^_]+_',1,2), '_'),'xxxxxxxx') oid from v$db_object_cache e where name like 'table%')
select a.kglnaobj
from x$kglob a, x$kgltr b, v$object_dependency c, dba_objects d, x
where a.kglhdadr = b.kglhdadr
 and (b.kgltrfnl = c.to_address or b.kgltrorg = c.to_address)
 and c.to_name = d.object_name
 and c.to_owner = d.owner
 and d.object_id = x.oid;

or

with x as (
 select /*+ materialize */ d.object_name, d.owner
 from v$db_object_cache e, dba_objects d
 where e.name like 'table%'
  and d.object_id = to_number(replace(regexp_substr(e.name,'_[^_]+_',1,2), '_'),'xxxxxxxx')
  and rownum < 11)
select a.kglnaobj
from x$kglob a, x$kgltr b, v$object_dependency c, x
where a.kglhdadr = b.kglhdadr
 and (b.kgltrfnl = c.to_address or b.kgltrorg = c.to_address)
 and c.to_name = x.object_name
 and c.to_owner = x.owner;

takes too long to run. When I have time, I'll come back to this.


Yong Huang

October 2008



Ref: 5500044

"'table_4_d_186748_0_0_0'" is a special internal cursor for getting
permissions on object id 0x186748 = 1599304 decimal."