Saturday, November 24, 2007

Dynamic Performance Tables

Dynamic Performance Tables

Throughout its operation, Oracle maintains a set of virtual tables that record current
database activity. These tables are created by Oracle and are called dynamic
performance tables.

Database administrators can query and create views on the tables and grant access
to those views to other users. These views are called fixed views because they
cannot be altered or removed by the database administrator.

SYS owns the dynamic performance tables. By default, they are available only to
the user SYS and to users granted SELECT ANY TABLE system privilege, such as
SYSTEM. Their names all begin with V_$. Views are created on these tables, and
then public synonyms are created for the views. The synonym names begin
with V$.

Each view belongs to one of the following categories:

1.Current State Views

2.Counter/Accumulator Views

3. Information Views

Current State Views

The views listed in Table 24–1 give a picture of what is currently happening on the

Counter/Accumulator Views
These views keep track of how many times some activity has occurred since
instance/session startup. Select from the view directly to see activity since startup.

Table 24–1 Current State Views

Fixed View Description

V$LOCK Locks currently held/requested on the instance
V$LATCHHOLDER Sessions/processes holding a latch
V$OPEN_CURSOR Cursors opened by sessions on the instance
V$SESSION Sessions currently connected to the instance
V$SESSION_WAIT Different resources sessions are currently waiting for

Counter/Accumulator Views

These views keep track of how many times some activity has occurred since
instance/session startup. Select from the view directly to see activity since startup.

If you are interested in activity happening in a given time interval, then take a
snapshot before and after the time interval, and the delta between the two
snapshots provides the activity during that time interval. This is similar to how
operating system utilities like sar, vmstat, and iostat work. Tools provided by
Oracle, like Statspack and BSTAT/ESTAT, do this delta to provide a report of
activity in a given interval.

Note: Snapshots should be taken during steady-state, not
immediately after system startup. Extra overhead is incurred
during system ramp-up, which may not accurately reflect the
performance of the system at steady-state.

Table 24–2 Summary Since Session Startup

Fixed View Description

V$DB_OBJECT_CACHE Object level statistics in shared pool
V$FILESTAT File level summary of the I/O activity
V$LATCH Latch activity summary
V$LATCH_CHILDREN Latch activity for child latches
V$LIBRARYCACHE Namespace level summary for shared pool
Summary of the current memory use of the library
cache, by library cache object type
V$MYSTAT Resource usage summary for your own session
V$ROLLSTAT Rollback segment activity summary
V$ROWCACHE Data dictionary activity summary
User-friendly DBA view for real-time monitoring of
segment-level statistics
V$SEGSTAT High-efficiency view for real-time monitoring of
segment-level statistics
V$SESSION_EVENT Session-level summary of all the waits for current
V$SESSTAT Session-level summary of resource usage since
session startup
Simulation of the shared pool's LRU list mechanism
V$SQL Child cursor details for V$SQLAREA
V$SQLAREA Shared pool details for statements/anonymous
V$SYSSTAT Summary of resource usage
V$SYSTEM_EVENT Instance wide summary of resources waited for
V$UNDOSTAT Histogram of undo usage. Each row represents a
10-minute interval.
V$WAITSTAT Break down of buffer waits by block class

Information ViewsIn information views, the information is not as dynamic as in the current state view.
Hence, it does not need to be queried as often as the current state views

Table 24–3 Information Views
Fixed View Description
Advisory information collected by MTTR advisory,
Parameters values for your session
Instance wide parameter values
V$PROCESS Server processes (background and foreground)
V$SEGSTAT_NAME Statistics property view for segment-level statistics
V$SQL_PLAN Execution plan for cursors that were recently
Execution statistics of each operation in the
execution plan
Concatenates information in V$SQL_PLAN with
execution statistics from V$SQL_PLAN_
V$SQLTEXT SQL text of statements in the shared pool
Status of the statistics or advisories controlled by the STATISTICS_LEVEL initialization parameter


Anonymous said...

Can anyone recommend the well-priced Network Management utility for a small IT service company like mine? Does anyone use or How do they compare to these guys I found recently: [url=] N-able N-central service management
[/url] ? What is your best take in cost vs performance among those three? I need a good advice please... Thanks in advance!

Anonymous said...

Hello! I'm newbie in Internet, can you give me some useful links? I know only about Yahoo [url=]Yahoo[/url] Yahoo

Anonymous said...

I really like what you guys are usually up
too. This kind of clever work and reporting! Keep up the amazing works guys
I've added you guys to my blogroll.

my site; airplane games

Xia Zhang said...

oakleyses said...

tory burch outlet, polo ralph lauren, ugg boots, chanel handbags, tiffany jewelry, longchamp outlet, longchamp pas cher, gucci handbags, sac longchamp pas cher, uggs on sale, jordan pas cher, michael kors pas cher, ray ban sunglasses, oakley sunglasses, polo outlet, louboutin pas cher, longchamp outlet, burberry pas cher, louis vuitton, cheap oakley sunglasses, replica watches, replica watches, longchamp outlet, christian louboutin outlet, prada handbags, oakley sunglasses wholesale, louis vuitton outlet, oakley sunglasses, ray ban sunglasses, louis vuitton, prada outlet, nike outlet, louis vuitton outlet, christian louboutin, nike free run, nike roshe, ugg boots, nike air max, polo ralph lauren outlet online, kate spade outlet, christian louboutin uk, tiffany and co, nike free, oakley sunglasses, jordan shoes, louis vuitton outlet, nike air max, ray ban sunglasses

oakleyses said...

nike tn, ralph lauren uk, true religion outlet, nike air max uk, burberry handbags, michael kors, abercrombie and fitch uk, nike blazer pas cher, oakley pas cher, timberland pas cher, hogan outlet, uggs outlet, michael kors outlet online, replica handbags, sac vanessa bruno, true religion outlet, converse pas cher, guess pas cher, ray ban pas cher, ray ban uk, true religion outlet, michael kors outlet, mulberry uk, nike roshe run uk, hollister uk, coach outlet, kate spade, nike air max, new balance, true religion jeans, north face, coach outlet store online, uggs outlet, michael kors outlet online, polo lacoste, michael kors outlet, lululemon canada, nike air force, vans pas cher, michael kors outlet, hollister pas cher, michael kors outlet online, sac hermes, michael kors, burberry outlet, michael kors outlet online, north face uk, nike air max uk

oakleyses said...

converse, louboutin, soccer jerseys, ferragamo shoes, hermes belt, longchamp uk, p90x workout, north face outlet, baseball bats, instyler, nike trainers uk, babyliss, mont blanc pens, soccer shoes, nfl jerseys, valentino shoes, oakley, hollister clothing, abercrombie and fitch, chi flat iron, ralph lauren, nike air max, north face outlet, gucci, ray ban, vans, beats by dre, herve leger, jimmy choo outlet, giuseppe zanotti outlet, mac cosmetics, ghd hair, reebok outlet, nike roshe run, hollister, mcm handbags, timberland boots, nike air max, lululemon, bottega veneta, insanity workout, nike huaraches, vans outlet, hollister, iphone cases, wedding dresses, new balance shoes, converse outlet, asics running shoes, celine handbags

oakleyses said...

marc jacobs, ugg,uggs,uggs canada, louis vuitton, swarovski, barbour uk, montre pas cher, moncler outlet, louis vuitton, canada goose outlet, louis vuitton, pandora charms, wedding dresses, juicy couture outlet, moncler, canada goose outlet, louis vuitton, canada goose uk, ugg uk, canada goose, ugg pas cher, coach outlet, hollister, moncler, canada goose outlet, ugg, pandora jewelry, moncler outlet, karen millen uk, pandora jewelry, links of london, louis vuitton, canada goose, canada goose, swarovski crystal, moncler uk, moncler, moncler, doudoune moncler, juicy couture outlet, canada goose jackets, thomas sabo, replica watches, ugg,ugg australia,ugg italia, barbour, pandora uk, supra shoes, lancel, toms shoes

dong dong23 said...

coach outlet online
tory burch outlet
louis vuitton
michael kors handbags
louis vuitton outlet
ray ban outlet
kate spade
vans shoes outlet
louis vuitton outlet
caoch outlet
michael kors outlet
coach factory outlet
michael kors handbags
coach outlet
michael kors outlet
supra sneakers
louis vuitton handbags
tods shoes
jordan 4
coach outlet
cheap jordans
true religion outlet
kobe bryant shoes
polo ralph lauren
toms shoes
nike roshe flyknit
instyler curling iron
michael kors outlet
michael kors outlet online
michael kors handbags
hollister kids
michael kors outlet
adidas outlet store
nike basketball shoes
mont blanc
adidas shoes
louis vuitton outlet
celine handbags
louis vuitton handbags
oakley sunglasses

raybanoutlet001 said...

nike blazer
ugg boots
ugg boots
nike factory outlet
polo ralph lauren
ugg outlet
los angeles lakers jerseys
ugg boots
coach outlet
true religion outlet store

aaa kitty20101122 said...

longchamp handbags
chrome hearts online
yeezy boost
asics running shoes
golden goose sneakers
yeezy boost 350
adidas yeezy boost
links of london
adidas outlet

郑佳颖 said...

adidas neo
cheap jordans
asics sneakers
timberland outlet
nike air force 1
adidas stan smith men
basketball shoes
adidas outlet

jeje said...

ferragamo belt
adidas store
yeezy boost 350 v2
tom ford glasses
gucci belt
ultra boost
kobe 9
nike air force
michael kors factory outlet

jeje said...

asics running shoes
huarache shoes
kyrie irving shoes
vibram fivefingers
vans store
adidas tubular
dior sunglasses
lacoste online shop
patriots jerseys