Performance Tuning

Some dynamic performance views ( v$views )

User Rating:

/ 0

Details: Parent Category: Articles; Created on Thursday, 18 March 2010 21:41; Last Updated on Monday, 12 March 2012 13:54; Published on Thursday, 18 March 2010 21:41; Written by Guy Lambregts; Hits: 3676

Some dynamic performance views

In order to have a view, in order to have an idea about what is going on at the system level I use : v$system_event , v$sysstat and v$waitstat . To have an idea about the rollback segs I used to query v$rollstat. I use however less often v$rollstat since system managed undo (SMU) has been introduced.

In order to have a view about what is going on at the session level I use v$session_event, v$sesstat and v$mystat, both of them I join with v$statname ( v$event_name exists as well ). If a user claims his or her session hangs and don' t know what is going on I query v$session_wait and sometimes v$session_wait_history Almost always I join the above views with v$session.

I obtain information about redologs in v$log and v$logfile

I obtain detailled information about segments in v$segment_statistics, v$segstat, v$object_usage

I obtain detailled information about latches in v$latch, v$latch_children, v$latch_misses

I obtain information about transactions, undo behaviour in v$transaction, v$undostat , for long running transactions one can query v$session_longops.

I analyze lock issues with selects from v$transaction, v$undostat , v$lock, v$locked_object joined with v$session. ( I appreciate the blocking_session field from v$session. )

I obtain sga info (system global area ) from v$sga , v$sga_dynamic_components, v$sga_dynamic_free_memory and off course v$sgastat whereas for pga info I query v$pgastat

For sort issues I take a look in v$sort_segment, v$sort_usage joined with v$sql and v$session

Library cache info, info about current cached sql statements, cursors I get from v$sql, v$sqlarea, v$sqltext, v$open_cursor, v$librarycache, v$sql_plan, v$sql_plan_statistics, v$sql_shared_cursor whereas for the dictionary cache I query v$rowcache. Sometimes I map v$session with v$sql. v$active_session_history is there from 10G R1 onwards. I appreciate the sql_id entry in many of those views. ( it is actually one of my favourite 10G newly added in memory information )

In order to track down which are the hotfiles I query v$filestat

v$instance_recovery can tell us which event triggers checkpoints.

For ASM - which I use as much as possible - I use v$asm_disk and v$asm_diskgroup

v$resource_limit infrom us about the HWM for processes, sessions, lock resources.