What about the dynamic performance views(V$) performance

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.htm – Code 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