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: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
