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 <sfaroult_at_oriole.com> 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: sfaroult_at_oriole.com
>
> 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: ListGuru_at_fatcity.com (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: wisernet100_at_yahoo.com
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: ListGuru_at_fatcity.com (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 - 13:08:37 CST