Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

July 30, 2007

What about the dynamic performance views(V$) performance

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 6:26 am

I was reading each post of Mr.Lewis from his profile on Oracle forums and I saw this thread on “”find Hot Block” query taking long time”. Writing queries against v$ views maybe a challenge especially when you need to work on an active production system with large amount of sessions, locks, latches, sqls etc. data.

v$ views are documented with initialization parameters and static data dictionary views(user%-all%-dba%) in Oracle’s Reference Guide. When you query all_views you will see v$ views with names starting v_$%, there are public synonyms for each view with v$% as a result when granting access to these views we use v_$% objects. gv$% views are for RAC environments, simply they return unioned information from each node for related v$ view.

Behind v$ performance views you will find x$ fixed tables, which are externalizations of Oracle kernel’s memory structures. Since rows can not be inserted, deleted or updated via SQL on x$ objects “Fixed Table” concept and name is argued to come from this behavior. To query x$ data you must be sys, but x$ fixed tables can be made available over views created and granted by sys account.

x$ fixed tables are not documented by Oracle and since Oracle is not an open source application maybe only place you may find quality research information about them are Mr.Steve Adams’s ixora or Mr.Lewis’s company site. Mr.Adam’s researches were done on 8i but in my opinion they are still very valuable today. Here is a list of x$ fixed table categories – Listing 103 – Categories of x$ fixed tables

You can list all v$ and x$ tables from sys.v_$fixed_table, query v$ views definitions from sys.v_$fixed_view_definition and list indexes available on x$ fixed tables from sys.v_$indexed_fixed_table. Below scripts are adapted from http://www.ixora.com.au/scripts/x$tables.htmCode listing 103 – List of v$ and x$ objects

Now lets play with v$session and see what sql*plus autotrace explain feature tell us about x$ access paths – Code listing 103b – access path research on v$session

Below is a little summary for the above tests -


-- first query with a literal over fixed index
select machine from v$session where sid = 1281 ;
FIXED TABLE FIXED INDEX| X$KSUSE (ind:1)

-- what about a range scan over fixed index - - careful #1; remember this is not a b*-tree index behaves more like a hash cluster -
select machine from v$session where sid < 1281 ;
FIXED TABLE FULL       | X$KSUSE

-- ok binding time
select machine from v$session where sid = :x ;
FIXED TABLE FIXED INDEX| X$KSUSE (ind:1)

-- function with bind disabled fixed index usage - careful #2 -
select machine from v$session where sid = round(:x) ;
FIXED TABLE FULL       | X$KSUSE 

-- opps in also disabled fixed index usage - careful #3 -
select machine from v$session where sid in (1281, 1280, 1282) ;
FIXED TABLE FULL       | X$KSUSE 

-- ok, here is what we expected with a little trick :)
select machine from v$session where sid = 1281 union all
select machine from v$session where sid = 1280 union all
select machine from v$session where sid = 1282 ;
FIXED TABLE FIXED INDEX| X$KSUSE (ind:1)

-- careful #4; when it comes to joins, join method and join order are important that you must be aware of
select s.machine, p.spid from v$session s, v$process p where sid = 1281 and s.paddr=p.addr;
NESTED LOOPS
FIXED TABLE FIXED INDEX| X$KSUSE (ind:1)
FIXED TABLE FIXED INDEX | X$KSLED (ind:2)

Since rbo is dead with 10g we also collect statistics on dictionary objects, but for previous releases the optimizer may be unable to determine the best access path, join order or join method and you will need to force him with some appropriate hints. Or you may find that optimizer is not using a fixed index from autotrace or 10046 sql trace output so again hinting will be your friend, but from release to release you must be on alarm with these hard codings(hints).

Last words are for the coming Log Buffer #56: a Carnival of the Vanities for DBAs - this week its my turn to be the Log writer - LGWR :) for the Oracle Blogsphere, after Jeremy's Log buffer #55 I really have to work harder and I hope not to fail Mr.Dave Edwards of Pythian Group.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Refences Used :
Oracle8i Internal Services for Waits, Latches, Locks, and Memory by Steve Adams
Oracle Database Internals FAQ
Oracle SQL High-Performance Tuning by Guy Harrison
Note:2856990.8 - Bug 2856990 - Fixed table indexes are not used in some cases
Oracle X$ Tables
Note:329617.1 - Queries On DBA_JOBS_RUNNING Take Too Long In Oracle10g
Note:272479.1 - Gathering Statistics For All fixed Objects In The Data Dictionary.
ALL_TYPE_ATTRS poor implementation for JDBC calls

About these ads

1 Comment »

  1. Ok the first comment is mine :)

    I always thought on how Mr.Adams was able to decode an x$ fixed table column like in the below query which is adapted from his researches at ixora;

    SELECT
    substr(DECODE(o.kglobtyp, 7, ‘PROCEDURE’, 8, ‘FUNCTION’, 9, ‘PACKAGE’, 12, ‘TRIGGER’, 13, ‘CLASS’),1,15) “TYPE”,
    substr(o.kglnaown,1,30) “OWNER”,
    substr(o.kglnaobj,1,30) “NAME”,
    s.indx “SID”,
    s.ksuseser “SERIAL”
    FROM
    sys.X$KGLOB o,
    sys.X$KGLPN p,
    sys.X$KSUSE s
    WHERE
    o.inst_id = USERENV(‘Instance’) AND
    p.inst_id = USERENV(‘Instance’) AND
    s.inst_id = USERENV(‘Instance’) AND
    o.kglhdpmd = 2 AND
    o.kglobtyp IN (7, 8, 9, 12, 13) AND
    p.kglpnhdl = o.kglhdadr AND
    s.addr = p.kglpnses
    ORDER BY 1, 2, 3

    When x = y + z to know what value x is you need to both y and z, so I believe as an outsider(a person who is never related to Oracle, never able to see the sources) Mr.Adams did a very intensive reverse engineering for the library at ixora. For some experts knowing these details may be useless but I believe these are very valuable efforts for the community.

    Thank you again Mr.Adams, I hope one day the you return back, that day will be the Oracle communities’ Return of the Jedi day :)

    Comment by H.Tonguç Yılmaz — July 30, 2007 @ 7:22 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 79 other followers

%d bloggers like this: