Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

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.

View Name

Description

V$SQLTEXT

Contains full SQL text of stored SQL area

V$SYSSTAT

Contains overall system level statistics

V$SESSTAT

Contains session statistics

V$SESSION

Contains information on current sessions

V$ROLLSTAT

Contains statistics on rollback segments

V$ROLLNAME

Contains rollback segment names

V$SGA

Contains information on SGA areas (rollup from V$SGASTAT)

V$SGASTAT

Contains information on SGA segment sizes

V$INSTANCE

Contains information on the instance

V$PARAMETER

Contains settings for all initialization parameters

V$DB_OBJECT_CACHE

Contains information on the SGA Object Caches

V$LIBRARYCACHE

Contains information on the shared pool library caches

V$LOG

Contains information on all current redo logs

V$LOG_HIST(ORY)

Contains information on redo log switches

V$LOGFILE

Contains locations of all redo log files

V$FIXED_VIEW_DEFINITION

Contains the definitions for all V$ and GV$ views

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.

Click HERE for additional information on this topic.

 


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.

 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.