Oracle Concepts -  The V$ View Relations Oracle Tips by Burleson Consulting

The V$ views

Throughout the next two days lectures you will hear about the V$ views. The V$ views provide a continually updated look at internal statistics. You may hear the V$ views called dynamic performance views or tables for this reason. There are approximately 143 or more V$ views in version 8.0.5. The views themselves are actually constructed on top of the C structs that are at the heart of the Oracle executable. The C structs in Oracle are named using a K$ or X$ prefix and general can’t be seen by anyone but the SYS user and then only if a view is created against them. Figure 10 shows a sample of the layout of the V$ tables as extracted via a partial screen shot from the RevealNet  Oracle Administrator Visual Dictionary.

Figure 10: Example of the V$ view relations

How are they used?

The V$ views are used to examine current performance indicators. By using a delta method over several equally timed selects trending can be performed. This delta method is how many of the monitoring tools get their information about Oracle performance. As far as I know only one tool, Precise*SQL from Precise Software Solutions (also sold as DBTuner from EMC) samples externally directly from memory. Quest Software is working on externally sampling but I am not aware if they have it in production as of this writing.

The benefits of external sampling are that increased sample rates (up to 99 per second) can be obtained with little impact on the Oracle database. Internal sampling is prone to the same delays and problems any Oracle internal connection falls prey too, thus limiting the sustained sample ratre that can be obtained without seriously impacting performance.

A V$ view that is very useful for shared pool analysis is called the V$SQLAREA view and as its name implies it is used to show the contents of the shared SQL area in the shared pool. An example view based on the V$SQLAREA view and a companion report are show in Source 34 and 35.

CREATE OR REPLACE VIEW sql_garbage AS
SELECT
  b.username users,
  SUM(a.sharable_mem+a.persistent_mem) Garbage,
  TO_NUMBER(NULL) good
FROM
   sys.v_$sqlarea a,
   dba_users b
WHERE
  (a.parsing_user_id = b.user_id AND a.executions<=1)
GROUP BY b.username
UNION
SELECT DISTINCT
  b.username users,
  TO_NUMBER(NULL) garbage,
  SUM(c.sharable_mem+c.persistent_mem) Good
FROM
   dba_users b,
   sys.v_$sqlarea c
WHERE
  (b.user_id=c.parsing_user_id AND c.executions>1)
GROUP BY b.username;

Listing 34:  Example view using the V$SQLAREA View

COLUMN garbage FORMAT A14 HEADING 'Non-Shared SQL'
COLUMN good FORMAT A14 HEADING 'shared SQL'
COLUMN good_percent FORMAT A14 HEADING 'Percent Shared'
COLUMN users FORMAT A14 HEADING Users
COLUMN nopr NOPRINT
SET FEEDBACK OFF
@title80 'Shared Pool Utilization'
SPOOL rep_out\&db\sql_garbage
SELECT 1 nopr,
a.users users,
TO_CHAR(a.garbage,'9,999,999,999') garbage,
TO_CHAR(b.good,'9,999,999,999') good,
TO_CHAR((b.good/(b.good+a.garbage))*100,'9,999,999.999') good_percent
FROM sql_garbage a, sql_garbage b
WHERE a.users=b.users
AND a.garbage IS NOT NULL AND b.good IS NOT NULL
UNION
select 2 nopr,
'-------------' users,'--------------' garbage,'--------------' good,
'--------------' good_percent FROM dual
UNION
SELECT 3 nopr,
TO_CHAR(COUNT(a.users)) users,
TO_CHAR(SUM(a.garbage),'9,999,999,999') garbage,
TO_CHAR(SUM(b.good),'9,999,999,999') good,
TO_CHAR(((SUM(b.good)/(SUM(b.good)+SUM(a.garbage)))*100),'9,999,999.999') good_percent
FROM sql_garbage a, sql_garbage b
WHERE a.users=b.users
AND a.garbage IS NOT NULL AND b.good IS NOT NULL
ORDER BY 1,3 DESC
/
SPOOL OFF
CLEAR COLUMNS

Source 35:  Example Report using View Based on V$SQLAREA

Example results from the report shown in Source 35 are shown in Listing 34.

Date: 01/28/00                                                   Page:   1
Time: 10:45 AM            Shared Pool Utilization           MAULT
                              dw database
                                                                          

Users          Non-Shared SQL Shared SQL     Percent Shared
-------------- -------------- -------------- --------------               
ASMITH            371,387,006      1,007,366           .271               
NETSPO             10,603,456        659,999          5.860               
DCHUN               6,363,158        151,141          2.320               
DSSUSER             5,363,057        824,865         13.330
MRCHDXD             4,305,330        600,824         12.246               
DWPROC              2,690,086      4,901,400         64.564               
CWOODY                946,199        239,604         20.206               
TTERIFIC              877,644         93,323          9.611               
GCMATCH               604,369      1,637,788         73.045               
MAULT                 445,566      3,737,984         89.350               
PRECISE               205,564     46,342,150         99.558               
BWITE                 154,754         35,858         18.812               
SYS                   146,811      9,420,434         98.465               
SSMITH                102,460      8,523,746         98.812               
MRCHPHP                56,954         59,069         50.911               
MRCHAEM                42,465         65,017         60.491               
-------------  -------------- -------------- --------------               
16                404,553,888     78,358,468         16.226               

Listing 34: Example Output from the SQL GARBAGE Report

Other useful V$ views are shown in Table 11.

Table 11: Views Useful for Monitoring and Tuning

Table 11 only lists those views I use regularly, you will in the presentation on tuning that several others are useful for specific monitoring of the database buffers and locks as well.

This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It’s only $19.95 when you buy it directly from the publisher here.

Need an Oracle Health Check? Do you have bad performance after an upgrade?   Need to certify that your database follows best practices? BC Oracle performance gurus can quickly certify every aspect of your Oracle database and provide a complete verification that your database is fully optimized.