V$SEGMENT_STATISTICS

V$SEGMENT_STATISTICS displays information about segment-level statistics.

Column	Datatype	Description
OWNER	VARCHAR2(30)	Owner of the object
OBJECT_NAME	VARCHAR2(30)	Name of the object
SUBOBJECT_NAME	VARCHAR2(30)	Name of the subobject
TABLESPACE_NAME	VARCHAR2(30)	Name of the table space to which the object belongs
TS#	NUMBER	Tablespace number
OBJ#	NUMBER	Dictionary object number of the object
DATAOBJ#	NUMBER	Data object number of the object
OBJECT_TYPE	VARCHAR2(18)	Type of the object
STATISTIC_NAME	VARCHAR2(64)	Name of the statistic
STATISTIC#	NUMBER	Statistic number
VALUE	NUMBER	Statistic value

Note:

1. Segment Waits

select      object_name ,     obj# ,     statistic_name ,     value from  v$segment_statistics where owner like '&owner' and   statistic_name like '%waits%' and   value > 0 order by statistic_name ,     value desc;

-- find the database table that is accessed the most: query the V$SEGMENT_STATISTICS view for this information by looking for tables that have a high number of physical reads and logical reads SELECT t.owner,t.table_name,lr.value+pr.value AS total_reads FROM  (SELECT owner, object_name, value FROM v$segment_statistics         WHERE statistic_name='logical reads') lr,       (SELECT owner, object_name, value FROM v$segment_statistics         WHERE statistic_name='physical reads') pr,        dba_tables t WHERE lr.owner=pr.owner AND lr.object_name=pr.object_name   AND lr.owner=t.owner AND lr.object_name=t.table_name ORDER BY 3 desc;

Oracle dynamic performance views

Oracle data dictionary views

Last updated: 2009-11-05 Thursday

DBA_DDL_LOCKS

Oracle 11gR1

DBA_DDL_LOCKS lists all DDL locks held in the database and all outstanding requests for a DDL lock.

Column	Datatype	NULL	Description
SESSION_ID	NUMBER		Session identifier
OWNER	VARCHAR2(30)		Owner of the lock
NAME	VARCHAR2(30)		Name of the lock
TYPE	VARCHAR2(40)		Lock type: · Cursor · Table/Procedure/Type · Body · Trigger · Index · Cluster · Java Source · Java Resource · Java Data
MODE_HELD	VARCHAR2(9)		Lock mode: · None · Null · Share · Exclusive
MODE_REQUESTED	VARCHAR2(9)		Lock request type: · None · Null · Share · Exclusive

Note:

1. Show all ddl locks in the system

select ses.username , ddl.session_id , ses.serial# , owner || '.' || ddl.name object , ddl.type , ddl.mode_held from dba_ddl_locks ddl , v$session ses where owner like '%userid%' and ddl.session_id = ses.sid;

Oracle dynamic performance views

Oracle data dictionary views

DBA_LOCK

Oracle 11gR1

DBA_LOCK lists all locks or latches held in the database, and all outstanding requests for a lock or latch.

Column	Datatype	NULL	Description
SESSION_ID	NUMBER		Session holding or acquiring the lock
LOCK_TYPE	VARCHAR2(26)		Lock type See Also: For a listing of lock types, see Appendix D, "Oracle Enqueue Names"
MODE HELD	VARCHAR2(40)		Lock mode
MODE REQUESTED	VARCHAR2(40)		Lock mode requested
LOCK_ID1	VARCHAR2(40)		Type-specific lock identifier, part 1
LOCK_ID2	VARCHAR2(40)		Type-specific lock identifier, part 2
LAST_CONVERT	NUMBER		The last convert
BLOCKING_OTHERS	VARCHAR2(40)		Whether the lock is currently blocking others

Note:

1.

Oracle dynamic performance views

Oracle data dictionary views

V$LOCKED_OBJECT

Oracle 11gR1

V$LOCKED_OBJECT lists all locks acquired by every transaction on the system. It shows which sessions are holding DML locks (that is, TM-type enqueues) on what objects and in what mode.

Column	Datatype	Description
XIDUSN	NUMBER	Undo segment number
XIDSLOT	NUMBER	Slot number
XIDSQN	NUMBER	Sequence number
OBJECT_ID	NUMBER	Object ID being locked
SESSION_ID	NUMBER	Session ID
ORACLE_USERNAME	VARCHAR2(30)	Oracle user name
OS_USER_NAME	VARCHAR2(30)	OS user name
PROCESS	VARCHAR2(12)	OS process ID
LOCKED_MODE	NUMBER	Lock mode

Note:

1. Show locked objects

select oracle_username || ' (' || s.osuser || ')' username , s.sid || ',' || s.serial# sess_id , owner || '.' || object_name object , object_type , decode( l.block , 0, 'Not Blocking' , 1, 'Blocking' , 2, 'Global') status , decode(v.locked_mode , 0, 'None' , 1, 'Null' , 2, 'Row-S (SS)' , 3, 'Row-X (SX)' , 4, 'Share' , 5, 'S/Row-X (SSX)' , 6, 'Exclusive', TO_CHAR(lmode)) mode_held from v$locked_object v , dba_objects d , v$lock l , v$session s where v.object_id = d.object_id and v.object_id = l.id1 and v.session_id = s.sid order by oracle_username , session_id;

Oracle dynamic performance views

Oracle data dictionary views

V$LOCK

Oracle 11gR1

V$LOCK lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.

Column	Datatype	Description
ADDR	RAW(4 | 8)	Address of lock state object
KADDR	RAW(4 | 8)	Address of lock
SID	NUMBER	Identifier for session holding or acquiring the lock
TYPE	VARCHAR2(2)	Type of user or system lock The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are: TM - DML enqueue TX - Transaction enqueue UL - User supplied The locks on the system types are held for extremely short periods of time. The system type locks are listed below.
ID1	NUMBER	Lock identifier #1 (depends on type)
ID2	NUMBER	Lock identifier #2 (depends on type)
LMODE	NUMBER	Lock mode in which the session holds the lock: · 0 - none · 1 - null (NULL) · 2 - row-S (SS) · 3 - row-X (SX) · 4 - share (S) · 5 - S/Row-X (SSX) · 6 - exclusive (X)
REQUEST	NUMBER	Lock mode in which the process requests the lock: · 0 - none · 1 - null (NULL) · 2 - row-S (SS) · 3 - row-X (SX) · 4 - share (S) · 5 - S/Row-X (SSX) · 6 - exclusive (X)
CTIME	NUMBER	Time since current mode was granted
BLOCK	NUMBER	A value of either 0 or 1, depending on whether or not the lock in question is the blocker.

Values for the TYPE Column: System Types

System Type	Description	System Type	Description
BL	Buffer hash table instance	NA..NZ	Library cache pin instance (A..Z = namespace)
CF	Control file schema global enqueue	PF	Password File
CI	Cross-instance function invocation instance	PI, PS	Parallel operation
CU	Cursor bind	PR	Process startup
DF	datafile instance	QA..QZ	Row cache instance (A..Z = cache)
DL	Direct loader parallel index create	RT	Redo thread global enqueue
DM	Mount/startup db primary/secondary instance	SC	System change number instance
DR	Distributed recovery process	SM	SMON
DX	Distributed transaction entry	SN	Sequence number instance
FS	File set	SQ	Sequence number enqueue
HW	Space management operations on a specific segment	SS	Sort segment
IN	Instance number	ST	Space transaction enqueue
IR	Instance recovery serialization global enqueue	SV	Sequence number value
IS	Instance state	TA	Generic enqueue
IV	Library cache invalidation instance	TS	Temporary segment enqueue (ID2=0)
JQ	Job queue	TS	New block allocation enqueue (ID2=1)
KK	Thread kick	TT	Temporary table enqueue
LA .. LP	Library cache lock instance lock (A..P = namespace)	UN	User name
MM	Mount definition global enqueue	US	Undo segment DDL
MR	Media recovery	WL	Being-written redo log instance

Note:

1. Show sessions that are blocking each other

select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking

from v$lock l1, v$lock l2

where l1.block =1 and l2.request > 0

and l1.id1=l2.id1

and l1.id2=l2.id2;

2. Show locked objects

select oracle_username || ' (' || s.osuser || ')' username

, s.sid || ',' || s.serial# sess_id

, owner || '.' || object_name object

, object_type

, decode( l.block

, 0, 'Not Blocking'

, 1, 'Blocking'

, 2, 'Global') status

, decode(v.locked_mode

, 0, 'None'

, 1, 'Null'

, 2, 'Row-S (SS)'

, 3, 'Row-X (SX)'

, 4, 'Share'

, 5, 'S/Row-X (SSX)'

, 6, 'Exclusive', TO_CHAR(lmode)) mode_held

from v$locked_object v

, dba_objects d

, v$lock l

, v$session s

where v.object_id = d.object_id

and v.object_id = l.id1

and v.session_id = s.sid

order by oracle_username

, session_id;

Oracle dynamic performance views

Oracle data dictionary views