Dynamic Performance view for Tuning

Dynamic Performance view for Tuning

Continue…  Reading from our last post “Dynamic Performance Tables\Views for Tuning”: http://shahiddba.blogspot.com/2012/06/dynamic-performance-tablesviews-for.html

Based on activity these view are categorized into tow: Counter/Accumulator Views and information views. The Counter/Accumulator Views keeps track of number of activity occurred since the instance startup and the information views provide summary or report of the current state. The information is not as dynamic as the current state views.

 

V$DB_OBJECT_CACHE

It is useful for finding active object in shared pool.

SELECT type, kept, COUNT(*), SUM(sharable_mem)

FROM V$DB_OBJECT_CACHE

GROUP BY type, kept;

The above query shows the distribution of shared pool memory across different type of objects. It also shows if any of the objects have been pinned in the shared pool using the procedure DBMS_SHARED_POOL.KEEP().

 

Finding object with large number of loads:

SELECT owner, name sharable_mem, kept, loads

FROM V$DB_OBJECT_CACHE

WHERE loads > 1

--OR invalidations > 0 OR executions> 0

ORDER BY loads DESC;

 

Finding large Unpinned object (object using large amount of memory):

SELECT owner, name, sharable_mem, kept

FROM V$DB_OBJECT_CACHE

WHERE sharable_mem > 102400

AND kept = 'NO' ORDER BY sharable_mem DESC;

V$FILESTAT

This view keeps information on physical I/O activity for each file such as: Number of physical reads and writes, number of block reads and writes and Total I/O for read and writes.

 

Checking datafile I/O:

SELECT NAME, PHYRDS, PHYWRTS

FROM V$DATAFILE df, V$FILESTAT fs

WHERE df.FILE# = fs.FILE#;

The total I/O for a single disk is the sum of PHYRDS and PHYWRTS for all the database files on that disk.

SELECT t.tablespace_name, SUM(a.phyrds-b.phyrds) / MAX(86400*(a.snap_date-b.snap_date)) "Rd/sec",

SUM(a.phyblkrd-b.phyblkrd) / greatest(SUM(a.phyrds-b.phyrds),1) "Blk/rd",

SUM(a.phywrts-b.phywrts) / MAX(86400*(a.snap_date-b.snap_date)) "Wr/sec",

SUM(a.phyblkwrt-b.phyblkwrt) / greatest(SUM(a.phywrts-b.phywrts),1) "Blk/wr"

  FROM snap_filestat a, snap_filestat b, dba_data_files t

 WHERE a.file# = b.file# AND a.snap_id = b.snap_id + 1  AND t.file_id = a.file#

 GROUP BY t.tablespace_name

HAVING sum(a.phyblkrd-b.phyblkrd) / greatest(SUM(a.phyrds-b.phyrds),1) > 1.1

    OR SUM(a.phyblkwrt-b.phyblkwrt) / greatest(SUM(a.phywrts-b.phywrts),1) > 1.1

 ORDER BY 3 DESC, 5 DESC;

The above example is useful for finding tablespaces that might be getting hit by large number of scans.

 

V$LIBRARYCACHE

This view contains information about each type of latch since startup. It is useful for identifying the area within the SGA especially when latch contention is observed in V$SESSION_WAIT. This view can help to identify the specific parts of the library cache (shared pool) and possible causes of problem.

Select * from v$librarycache;

SELECT namespace, gets, gethitratio, pins,  pinhitratio, reloads, invalidations

  FROM V$LIBRARYCACHE  ORDER BY gets DESC; 

GETHITRATIO (GETHITS/GETS) and GETPINRATIO (PINHITS/PINS) can be used if just examining activity since instance startup.

Look for the following when querying this view High RELOADS or INVALIDATIONS (indicates Objects getting swapped out of memory) and low GETHITRATIO or GETPINRATIO (indicates object getting swapped out of the memory and session not finding the cursor shared)

Then after you can use V$DB_OBJECT_CACHE, V$SQLAREA to get more details.

 

V$PARAMETER and V$SYSTEM_PARAMETER

V$PARAMETER is queried during performance tuning to determine the current settings for a parameter. For example, if the buffer cache hit ratio is low, then the value for DB_BLOCK_BUFFERS (or DB_CACHE_SIZE) can be queried to determine the current buffer cache size.

SELECT name, value, isdefault, isses_modifiable, issys_modifiable, ismodified

  FROM V$PARAMETER  WHERE name = 'sort_area_size';

The above query will determine the sort area size.

Note: If you want to see the instance-wide parameters, use V$SYSTEM_PARAMETER view instead of V$PARAMETER.

 

V$ROWCACHE

This view determines:Whether the application is accessing the cache efficiently and determine whether the dictionary cache is adequately sized. If the shared pool is too small, then the dictionary cache is not able to grow to a sufficient size to cache the required information.

SELECT parameter, sum("COUNT") , sum(usage) , sum(gets), sum(getmisses), sum(scans),

sum(scanmisses), sum(modifications), sum(dlm_requests), sum(dlm_conflicts), sum(dlm_releases)

  FROM V$ROWCACHE GROUP BY parameter;

 

V$SEGMENT_STATISTICS, V$SEGSTAT, V$SEGSTAT_NAME

These views allow real time monitoring of segment –level statistics, enabling a DBA to identify performance problems associated with an individual table or index.

select OWNER, OBJECT_NAME, TABLESPACE_NAME, STATISTIC_NAME, STATISTIC#, VALUE

 from V$SEGMENT_STATISTICS

where  OWNER='HRMS' AND tablespace_name='MAIN_DBF';

 

V$SESSION_EVENT

As already described in my earlier post  V$SESSION_WAIT shows the current waits for a session while V$SESSION_EVENT provides summary of all the events the session has waited for since it started.

SELECT s.sid, bgp.name

    FROM V$SESSION s, V$BGPROCESS bgp

   WHERE bgp.name LIKE '%DBW%'  AND bgp.paddr = s.paddr;

    

 SELECT event, total_waits waits, total_timeouts timeouts,

       time_waited total_time, average_wait avg

  FROM v$session_event

 WHERE sid = 3 ORDER BY time_waited DESC;

 

V$SQL_PLAN

·         Determining the current execution plan

·         Identifying the effect of creating an index on a table

·         Finding cursors containing a certain access path (for example, full table scan or index range scan)

·         Identifying indexes that are, or are not, selected by the optimizer

·         Determining whether the optimizer selects the particular execution plan (for example, nested loops join) expected by the developer

SELECT OPERATION, OPTIONS, OBJECT_NAME FROM V$SQL_PLAN

WHERE OBJECT_OWNER='HRMS';

 

V$SQLAREA

This view keeps track of all the shared cursors present in the shared pool. It has one row for every SQL statement present in the shared pool. It is an invaluable view for finding the resource usage of a SQL statement.

select hash_value, address, SQL_text  from V$SQLAREA;

From the above query you can get the hash_value and address

  SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls

  FROM V$SQLAREA

 WHERE hash_Value = 3505430014

   AND address = hextoraw('670B4F3C');

The above Query is used to Find Resources Used by a particular SQL Statement.

 

V$SQLTEXT

This view contains the complete SQL text for the SQL statements in the shared pool. You can query and find the complete SQL statement here.

SELECT sql_text FROM V$SQLTEXT

  WHERE hash_value = 3505430014 AND command_type=47

   ORDER BY piece;

 

V$SYSSTAT

This is important view for monitoring system performance, buffer cache hit ratio and soft parse ratio, are computed from this view. The overall goal of this view is monitoring system resource usage and how the system resource usage changes overtime. To examine the system resource usage over an interval, take a snapshot of the data within at the beginning of the interval and another at the end. The difference (end value – begin value) for each statistics (by Statspack) is the resource used during interval.

Instance efficiency ratio calculated from V$SYSSTATE:

Buffer cache hit ratio:

1 - ((physical reads - physical reads direct - physical reads direct (lob)) / session logical reads)

Soft parse ratio:

1 - ( parse count (hard) / parse count (total) )

In-memory sort ratio:

sorts (memory) / ( sorts (memory) + sorts (disk) )

Parse to execute ratio:

1 - (parse count/execute count)

Parse CPU to total CPU ratio:

1 - (parse time cpu / CPU used by this session)

Parse time CPU to parse time elapsed:

parse time cpu / parse time elapsed

To determine the load profile of the system, normalize the following statistics over seconds and over transactions: logonscumulative, parse count (total), parse count (hard), executes, physical reads, physical writes, blockchanges, and redo size.

Block changes for each transaction is calculated by the following:

db block changes / ( user commits + user rollbacks )

Join view on SYSSTAT to find CPU and disk usage already posted and find many more example of this view by searching “SYSSTAT on my blog.

 http://shahiddba.blogspot.com/2012/06/monitor-cpu-usage-of-oracle-processes.html

 

V$UNDOSTAT

This view monitors how undo space and transactions are executed in the current instance. Statistics for undo space consumption, transaction concurrency, and length of queries in the instance are available.

SELECT * FROM V$UNDOSTAT;

END_TIME	UNDOBLKS	TXNCOUNT	MAXQUERYLEN	MAXCONCURRENCY
Jun/11/2012 10:26:20 AM	0	554,898	1	1
Jun/11/2012 10:23:01 AM	2	554,853	102	1
Jun/11/2012 10:13:01 AM	105	554,693	2	1
Jun/11/2012 10:03:01 AM	1	554,499	21	1
Jun/11/2012 09:53:01 AM	0	554,375	3	1
Jun/11/2012 09:43:01 AM	43	554,350	12	1
Jun/11/2012 09:33:01 AM	3	554,247	3	2
Jun/11/2012 09:23:01 AM	0	554,042	4	2
Jun/11/2012 09:13:01 AM	2	554,015	9	1
Jun/11/2012 09:03:01 AM	67	553,835	165	2

Here in the above example (output pasted from toad) first row of the view shows statistics for the current time interval. Each subsequent row represents a ten minute interval. The longest query 165 sec. executed at 09:03 AM.

 

V$WAITSTAT

This view keeps a summary all buffer waits since instance startup. It is useful for breaking down the waits by class if you see a large number of buffer busy waits on the system.

Select * from V$WAITSTAT;

Possible reason for waits:

·         Undo segment header: not enough rollback segments

·         Data segment header/freelist: freelist contention

·         Range scans on indexes with large number of deletions

·         Full table scans on tables with large number of deleted rows

·         Blocks with high concurrency

References:

http://docs.oracle.com/cd/B10500_01/server.920/a96533/sqlviews.htm#17214