Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 11 June 2012

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.

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:

0 comments:

Post a Comment