Stephane you have WAY too much free time :) seriously, I let you guys muck around the internals and I learn from your postings. Me, I'm busy enough just trying to keep my developers from designing tables without thought to how Oracle handles things.
--- Stephane Faroult <[EMAIL PROTECTED]> wrote: > 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). > __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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).
