Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Some of you may find this useful

Re: Some of you may find this useful

From: Stephane Faroult <>
Date: Sun, 12 Jan 2003 07:33:46 -0800
Message-ID: <>

Naveen Nahata wrote:
> And how does one find out more information about such cryptic, undocumented
> tables??
> experience? R&D? be in company of more experienced people?
> wat else?
> Regards
> Naveen

Doc which should not have left Oracle? In practice, the meaning of names you cannot guess but by grabbing information which leaks from Oracle. But the really useful stuff you get by trial and error. Call it R&D if you want, but I have a higher opinion of R&D. Typically, if you query V$FIXED_VIEW_DEFINITION you can get, by checking how GV$ views are defined, a good number of relationships between (G)V$ and X$ views. It can help document say around 40% of all the X$ columns. This unfortunately lets out in the cold a good number of X$ which are listed in V$FIXED_TABLE without seemingly being used anywhere. Just to tell you about X$KGLRD I have for some time being looking for how to relate commands of type 47 (PL/SQL stuff) which appear in V$SQL and V$SQLAREA to the regular SELECTs, INSERTs, UPDATEs, DELETEs they perform and which _also_ appear in the stats - for one thing, in order to interpret figures correctly, and also in order to be able to spot rotten algorithms, which I see as the next frontier in terms of SQL tuning. When you check V$FIXED_VIEW_DEFINITION you notice that V$SQL, V$SQLTEXT and family revolve around mostly X$KGL views - X$KGLOB, X$KGLNA and the like. I have therefore queried V$FIXED_TABLE for all X$KGL tables and described them. VARCHAR columns are rare enough for my eye to have been caught immediately by X$KGLRD (unreferenced by any V$), hence my post. Continuing my work afterwards, I have turned my attention to RAW columns and found that (kglrdhdl, kglnadhv) in this view were indeed the (address, hash_value) of the statement (I usually generate brute force joins on the RAW columns and see what returns something). Which means a) that you can get the full text from V$SQLTEXT when it is longer than 512 characters
b) that when you spot a really ugly query in V$SQL, or a query which is executed an insane number of times, you can work out from X$KGLRD which procedure(s) call(s) it, which is not always easy otherwise (bar the LIKE of death on DBA_SOURCE, which will not work if say the query is dynamically built) - moreover it may also work with wrapped procedures.   

My aim, remember, was to relate a PL/SQL block to the statements it issues, so X$KGLRD is not the final answer. But I am still working on it and closing in ... X$KGLDP seems promising ...

Please see the official ORACLE-L FAQ:
Author: Stephane Faroult

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Sun Jan 12 2003 - 09:33:46 CST

Original text of this message