Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: Some of you may find this useful

From: Naveen Nahata <naveen_nahata_at_mindtree.com>
Date: Sun, 12 Jan 2003 21:48:37 -0800
Message-ID: <F001.0052D64A.20030112214837@fatcity.com>


Thanx a lot for a very comprehensive answer.

More than the result, I learnt the method. thanx a lot

Regards
Naveen

-----Original Message-----
Sent: Sunday, January 12, 2003 9:04 PM
To: Multiple recipients of list ORACLE-L

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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Naveen Nahata
  INET: naveen_nahata_at_mindtree.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 - 23:48:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US