| |
 |
|
Oracle Tips by Burleson |
Oracle Internal Fixed Tables
To understand the structure of a Oracle
database, we have to remember that Oracle is just a large C program.
As Oracle starts, the C programs allocate two-dimensional arrays to
hold internal information. These internal arrays are mapped to x$
fixed tables. As such the x$ tables are not really tables at all, but
instead they are C array structures, mapped to look like a table. In
Oracle 10g, the x$ arrays are highly normalized and there are 352 x$
fixed tables. The following query will list the names of all of the
x$ fixed tables:
select
*
from
v$fixed_table
where
name like 'X%';
The x$ fixed tables all have cryptic names,
and Oracle created v$ views on the x$ tables to make it easier to see
Oracle internal details. In Oracle it is very easy to see the v$ view
names and the x$ tables that make-up the view.
Copy the query below onto your server and run
it. This query is against the x$kqfvi fixed table, which is the
master index of the names of all v$ and gv$ views on the x$ tables
select
kqfvinam name
from
x$kqfvi
order by
kqfvinam;
So, now that we know the names of the v$
views, how do we see the x$ fixed tables? Oracle provides an easy
method to do this.
Seeing the source for the v$ views
You can easily see the source for all of the
v$ views by querying the v$fixed_view_definition view. This view will
give you the SQL that was used to create the v$ view. In the example
below, we seek the SQL that generates the v$session view. Run the
following query on your server.
select
*
from
v$fixed_view_definition
where
view_name = 'GV$SESSION';
Here is the output. We see that the gv$session
and v$session views are derived from the x$ksuse fixed table.
VIEW_NAME
------------------------------
VIEW_DEFINITION
-------------------------------------------------------------------------------GV$SESSION
select inst_id,addr,indx,ksuseser,ksuudses,ksusepro,
ksuudlui,ksuudlna,ksuudoct,
ksusesow, decode(ksusetrn,hextoraw('00'),null,ksusetrn),
decode(ksqpswat,hextor
aw('00'),null,ksqpswat), decode(bitand(ksuseidl,11),1,'ACTIVE',0,
decode(bitand(
ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,'SNIPED',
'KILLED'),
decode(k
sspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE'),
ksuudsid,ksuudsna,ksuseunm,
ksusepid,ksusemnm,ksusetid,ksusepnm,
decode(bitand(ksuseflg,19),17,'BACKGROUND',
1,'USER',2,'RECURSIVE','?'), ksusesql, ksusesqh, ksusepsq, ksusepha,
ksuseapp,
k
suseaph, ksuseact, ksuseach, ksusecli, ksusefix, ksuseobj, ksusefil,
ksuseblk,
ksuseslt, ksuseltm, ksusectm, decode(bitand(ksusepfl, 16),0,'NO','YES'),
deco
de(ksuseft, 2,'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'),
decode(ksusefm,1,
'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE'), decode(ksusefs, 1, 'YES',
'NO'),
ks
usegrp, decode(bitand(ksusepfl,16),16,'ENABLED',
decode(bitand(ksusepfl,32),3
2,'FORCED','DISABLED')), decode(bitand(ksusepfl,64),64,'FORCED',
decode(bitan
d(ksusepfl,128),128,'DISABLED','ENABLED')),
decode(bitand(ksusepfl,512),512,'FO
RCED', decode(bitand(ksusepfl,256),256,'DISABLED','ENABLED')),
ksusecqd,
ksus
eclid from x$ksuse where bitand(ksspaflg,1)!=0 and
bitand(ksuseflg,1)!=0
Now that we see how the v$ views are created
from the x$ fixed tables, let review some of the most important fixed
tables.
Important x$ tables
While Oracle 10g has encapsulated most of the
x$ fixed tables into easy-to-user v$ views, there are several main x$
fixed views that are still commonly-used by the DBA. These “important”
fixed views are those that can be used to provide insights that are
unavailable in the v$ views. Fortunately, out of the 350+ x$ fixed
tables, there are only a few that are regularly used.
* x$ksmsp – This show the RAM inside
the shared pool
* x$ksmlru - This view shows us the
aging within the least-recently used area of the shared pool
* x$ksppi - This table contains all of
the Oracle parameters and maps to the v$parameter view.
* x$bh - This important table shows the
internal storage associated with the RAM data buffers.
Of these foxed tables, the x$bh is the most
important. Let’s examine some uses for x$bh, and its close cousin,
the v$bh view.
Using the x$bh fixed table
The x$bh fixed views provide a wealth of
internal information about the behavior of the data buffer caches, and
the columns TCH and TIM are especially important. Let's take a closer
look at each of these undocumented columns and discuss their purpose
within Oracle 10g.
* TIM column - The TIM column is
connected with the _db_aging_touch_time hidden parameter, and the TIM
metric is also associated with the new midpoint data buffer insertion
algorithm that Oracle8i introduced. The TIM duration between RAM
touches in the data buffer is related to Oracle's internal movement of
the data block to the most-recently-used movement inside the buffer.
* TCH column - The TCH column measures
the number of times that a SQL query has touched a specific data
block. By design, the Oracle 10g data buffers segregate data into hot
regions and cold regions, and Oracle uses the internal processes to
segregate data blocks according to the frequency that they've been
touched. The TCH column is related to the hidden Oracle 10g parameter,
_db_aging_hot_criteria, which determines the threshold for a block to
be considered “hot”. You can use the TCH column to infer the relative
popularity of data blocks from each table or index and then use this
data to make decisions about the segregation of these Oracle database
objects into their separate data buffers.
In addition, the x$bh fixed table can be used
with the obj$ table to show the number of data block per table inside
the Oracle database.
Take a close look at the code below, copy it
to your server. Buffer-up your pubs Oracle database by running a few
queries, and then run this script.
connect sys/change_on_install
as sysdba
select
decode(
pd.bp_id,
1,'KEEP',
2,'RECYCLE',
3,'DEFAULT',
4,'2K CACHE',
5,'4K CACHE',
6,'8K CACHE',
7,'16K CACHE',
8,'32K CACHE',
'UNKNOWN') subcache,
bh.object_name,
bh.blocks
from
x$kcbwds
ds,
x$kcbwbpd
pd,
(select /*+ use_hash(x) */ set_ds,o.name object_name, count(*)
BLOCKS
from obj$ o, x$bh x where o.dataobj# = x.obj
and x.state !=0 and
o.owner# !=0
group by set_ds,o.name)
bh
where
ds.set_id >= pd.bp_lo_sid
and
ds.set_id <= pd.bp_hi_sid
and
pd.bp_size != 0
and
ds.addr=bh.set_ds;
In the output we see a complete list of all objects within each
RAM data buffer pool, and the number of blocks consumed by each table
and index. This is super-useful information when deciding how to
re-allocate tables and indexes between RAM data buffers.
UBCACHE
OBJECT_NAME BLOCKS
------------ ------------------------------
----------
DEFAULT AUTH_LAST_NAME
4,342
DEFAULT BOOK
288
DEFAULT JOB
1,353
DEFAULT PLAN_TABLE
7,265
16K CACHE AUTHOR
2,564
16K CACHE AUTH_LAST_NAME_IDX 1,203
8K CACHE ITEM_TABLE 254
8K CACHE PRODUCT_TABLE
9,234
As we can see, the x$bh table provides great
insight into the internals working of the data buffer caches. Oracle
has created a matching v$ view called v$bh that is also quite useful
for data buffer internals.
Using the v$bh view
If you have defined multiple buffer pools in
Oracle 10g, you can now use the v$bh view to see how well the data
buffers are caching the tables block and make the most of your RAM
resources within the SGA.
The v$bh view is built upon the x$bh fixed
table, but has extra data. Unlike x$bh, v$bh has a “status” column
that indicates the lock mode for each data block in the data buffer.
For non-Oracle Parallel Server (OPS) and non-Real Application Clusters
(RAC) databases, you'll see three lock modes:
* XCUR column - This is a RAM block
that has an exclusive lock.
* CR column - This mode indicates a
downgraded RAM block, which was one in XCUR mode.
* FREE column - This is an unused RAM
block. It's extremely important because you can reallocate free blocks
in the RAM data buffers to other SGA regions.
Running the following query will show you the
number of RAM blocks that are in each of these three states. Run some
queries against your sample database and then run this query.
column c1
heading 'Status' format a10
column c2 heading 'Number|of Data|Buffers' format 999,999,999
select
status c1,
count(1) c2
from
v$bh
group by
status
order by
count(1) desc;
Here's an example of the output. Here we see
the total number of RAM blocks in each of the three status conditions.
Number
of Data
Status Buffers
---------- ------------
xcur 311,967
free 270,731
cr 17,302
Note that the “status” column of v$bh is
extremely important in determining when RAM data buffers are
over-allocated. As long as the RAM data buffers are smaller than the
Oracle database, the number of free blocks will decline and disk I/O
continues.
As we know from the Webinar, one of the
challenges that Oracle DBAs face is to identify candidates for each of
the eight data pools within Oracle 10g. There's the DEFAULT pool, the
KEEP pool, the RECYCLE pool, as well as instantiated pools for each
supported block size on the Oracle server.
Here's a sample query that shows data buffer
utilization for individual objects in the Oracle database.
Note that this script uses an Oracle 10g
scalar subquery, and it won't work in systems prior to Oracle 10g
unless you comment out column c3.
column c0
heading 'Owner' format a15
column c1 heading 'Object|Name' format a30
column c2 heading 'Number|of|Buffers' format 999,999
column c3 heading 'Percentage|of Data|Buffer' format 999,999,999
select
owner c0,
object_name c1,
count(1) c2,
(count(1)/(select count(*) from v$bh)) *100 c3
from
dba_objects o,
v$bh bh
where
o.object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM','AURORA$JIS$UTILITY$')
group by
owner,
object_name
order by
count(1) desc
;
Below is an actual listing from an Oracle
Financials database. We can easily see the most popular tables in our
data buffers and the amount of RAM that they consume. This is
invaluable information when planning multiple data buffer pools in
Oracle 10g.
Number
Object of
Owner Name Buffers
--------------- ------------------------------ --------
INV MTL_SYSTEM_ITEMS 7,098
WIP WIP_TRANSACTION_ACCOUNTS_N1 6,583
PERFSTAT STATS$ERROR_LOG 5,635
OE SO_LINES_ALL 5,091
OE SO_LINES_ALL 5,091
INV MTL_DEMAND 4,924
INV MTL_ITEM_CATEGORIES 3,098
MRP MRP_FORECAST_DATES 2,248
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
