V$SESS_IO

V$SESS_IO displays I/O statistics for each user session.

Column	Datatype	Description
SID	NUMBER	Session identifier
BLOCK_GETS	NUMBER	Block gets for this session
CONSISTENT_GETS	NUMBER	Consistent gets for this session
PHYSICAL_READS	NUMBER	Physical reads for this session
BLOCK_CHANGES	NUMBER	Block changes for this session
CONSISTENT_CHANGES	NUMBER	Consistent changes for this session

Note:

1. SID (Session identifier) can be used to join to V$SESSION.SID.
2. See Oracle Process and Session Data Dictionary Views.
3. Use V$SESS_IO to find sessions generating lots of redo:

V$SESS_IO contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo. SELECT s.sid, s.serial#, s.username, s.program, i.block_changes FROM v$session s, v$sess_io i WHERE s.sid = i.sid ORDER BY 5 desc, 1, 2, 3, 4; Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

4. Use V$SESS_IO to find out what users are doing and the resources they are using

To determine what is happening in the system at a point in time: select a.sid, a.username, s.sql_text from v$session a, v$sqltext s where a.sql_address = s.address and a.sql_hash_value = s.hash_value order by a.username, a.sid, s.piece; To recognize users that are performing a large numbers of physical disk or memory reads: select a.username, b.block_gets, b.consistent_gets, b.physical_reads, b.block_changes, b.consistent_changes from v$session a , v$sess_io b where a.sid =b.sid order by a.username;

Oracle data dictionary views

Oracle dynamic performance views

More Oracle DBA tips, please visit Oracle DBA Tips  

V$SESSION_OBJECT_CACHE

V$SESSION_OBJECT_CACHE displays object cache statistics for the current user session on the local server (instance).

Column	Datatype	Description
PINS	NUMBER	Number of object pins or look-ups in the cache
HITS	NUMBER	Number of object pins that found the object already in the cache
TRUE_HITS	NUMBER	Number of object pins that found the object already in the cache and in the desired state (thus, not requiring refresh from the database)
HIT_RATIO	NUMBER	Ratio of HITS / PINS
TRUE_HIT_RATIO	NUMBER	Ratio of TRUE_HITS/PINS
OBJECT_REFRESHES	NUMBER	Number of objects in the cache that were refreshed with a new value from the database
CACHE_REFRESHES	NUMBER	Number of times the whole cache (all objects) were refreshed
OBJECT_FLUSHES	NUMBER	Number of objects in the cache that were flushed to the database
CACHE_FLUSHES	NUMBER	Number of times the whole cache (all objects) were flushed to the database
CACHE_SHRINKS	NUMBER	Number of times the cache was shrunk to the optimal size
CACHED_OBJECTS	NUMBER	Number of objects currently cached
PINNED_OBJECTS	NUMBER	Number of objects currently pinned
CACHE_SIZE	NUMBER	Current size of the cache (in bytes)
OPTIMAL_SIZE	NUMBER	Optimal size of the cache (in bytes)
MAXIMUM_SIZE	NUMBER	Maximum size of the cache (in bytes)

Note:

1.

SELECT pins, hits, hit_ratio, true_hit_ratio, cached_objects, pinned_objects, cache_size, optimal_size, maximum_size

FROM V$SESSION_OBJECT_CACHE;

PINS	HITS	HIT_ RATIO	TRUE_HIT_ RATIO	CACHED_ OBJECTS	PINNED_ OBJECTS	CACHE_ SIZE	OPTIMAL_ SIZE	MAXIMUM_ SIZE
128	99	0.7734375	0.7734375	21	6	43288	102400	112640

Oracle data dictionary views

Oracle dynamic performance views

V$SESSION_FIX_CONTROL

V$SESSION_FIX_CONTROL displays information about Fix Control (enabled/disabled) for the current session.

Column	Datatype	Description
SESSION_ID	NUMBER	Session identifier (can be used to join this view with V$SESSION)
BUGNO	NUMBER	Bug number (as fix control identifier)
VALUE	NUMBER	Current value set for the fix control
SQL_FEATURE	VARCHAR2(64)	Feature control ID
DESCRIPTION	VARCHAR2(64)	Description of the fix control
OPTIMIZER_FEATURE_ENABLE	VARCHAR2(25)	Version on (and after) which the fix is enabled by default
EVENT	NUMBER	Event formerly used to control the fix
IS_DEFAULT	NUMBER	Indicates whether the current value is the same as the default (1) or not (0)

Note:

1.

SELECT * FROM V$SESSION_FIX_CONTROL where session_id = 1480;

SESSION_ID	BUGNO	VALUE	DESCRIPTION	OPTIMIZER_ FEATURE_ ENABLE	EVENT	IS_ DEFAULT
1480	4611850	0	do not clobber predicate during first-k row estimate phase	10.2.0.2	0	1
1480	4663804	0	use smallest table as first table in join card. initial ordering	10.2.0.2	0	1
1480	4663698	0	for cached NL table set tab_cost_io to zero	10.2.0.2	0	1
1480	4545833	0	no selectivity for transitive inequality predicates	10.2.0.2	0	1
1480	3499674	0	enable tiny index improvements: consider small indexes as cachhe		10111	1
1480	4584065	0	do not disable cartesian products if ORDERED hint is used	10.2.0.2	0	1
1480	4602374	0	optimize top-level predicate chain	10.2.0.2	0	1
1480	4569940	0	Use index heuristic for join pred being pushed	10.1.0.5	0	1
1480	4631959	0	Refine criteria for additional phase in JPPD	10.2.0.2	0	1
1480	4519340	0	Generate distinct view in SU if candidate for JPPD	10.2.0.2	0	1
1480	4550003	1	do not consider no sel predicates in join selectivity sanity	10.1.0	0	1
1480	4488689	0	ignore IS NOT NULL predicate as an index filter	10.2.0.2	38077	1
1480	3118776	1	Check for obj# for named view estimated card	9.2.8	38079	1
1480	4519016	1	Pick view card from view qb instead of parent qb	9.2.8	38081	1
1480	4487253	1	make NL comparable to HJ	9.2.8	38081	1
1480	4556762	0	min # of join permutations for starting table, new initial order	10.2.0.2	0	1
1480	4728348	0	consider mjc if equi-joined pred is dropped in kkoipt	10.2.0.2	0	1
1480	4723244	0	CPU cost estimation for unique access	10.2.0.2	0	1
1480	4554846	0	tbl$or$idx$part$num() predicate causing peformance problems	10.2.0.2	0	1
1480	4175830	0	generate transitive predicates across anti join predicates	10.2.0.1	0	1
1480	4722900	0	kkoidc: add SI caching for index branch blocks	10.2.0.1	0	1
1480	4711525	1	Enable code optimization for bitmap access path	9.2.8	38083	1
1480	4273361	0	convert range scan to unique access if possible	10.2.0.2	0	1
1480	4483240	1	Favor unique index in case of cost tie	9.2.8	0	1
1480	4904838	1	allow index skip scan with no index keys	9.2.8	0	1
1480	5084239	0	try for subqueries before forcing in star trans with FACT hint	10.2.0.2	0	1
1480	5449488	0	In bitmap costing use real sel for transitive negated predicates	10.2.0.2	0	1

Oracle data dictionary views

Oracle dynamic performance views

V$SESSION_CURSOR_CACHE

V$SESSION_CURSOR_CACHE displays information on cursor usage for the current session.

Column	Datatype	Description
MAXIMUM	NUMBER	Maximum number of cursors to cache. Once you hit this number, some cursors will need to be closed in order to open more. The value in this column is derived from the initialization parameter SESSION_CACHED_CURSORS.
COUNT	NUMBER	Current number of cursors (whether they are in use or not)
OPENS	NUMBER	Cumulative total of cursor opens minus one. This is because the cursor that is currently open and being used for this query is not counted in the OPENS statistic.
HITS	NUMBER	Cumulative total of cursor open hits
HIT_RATIO	NUMBER	Ratio of the number of times an open cursor was found divided by the number of times a cursor was sought

Note:

1. The V$SESSION_CURSOR_CACHE view is not a measure of the effectiveness of the SESSION_CACHED_CURSORS initialization parameter.

2. To get view definition:

SELECT view_definition FROM v$fixed_view_definition

WHERE view_name='V$SESSION_CURSOR_CACHE';

3.

SELECT * FROM V$SESSION_CURSOR_CACHE;

MAXIMUM	COUNT	OPENED_ONCE	OPEN	OPENS	HITS	HIT_RATIO
50	4	4	0	43	43	1

Oracle data dictionary views

Oracle dynamic performance views