Oracle's Common dynamic views

1) V$lock
Given the lock information, such as the Type field, the user type locks has 3 kinds: tm,tx,ul,system Type locks There are many, common are: Mr,rt,xr,ts and so on. We only care about TM,TX locks.
When TM locks, the Id1 field represents object_id; when the TX lock, Trunc (Id1/power (2,16)) represents the rollback segment number.
Lmode Field, the session holds the lock mode, there are 6 kinds:
0-none
1-null (NULL)
2-row-s (SS)
3-row-x (SX)
4-share (S)
5-s/row-x (SSX)
6-exclusive (X)
The request field, the mode of the lock that the process requests, has the same value range as Lmode.
CTime the time that a field has been held or waiting to be locked.
Block field, whether to block other lock applications, when block=1 that the session blocked the other session.

2) V$sqlarea, V$sql, V$sqltext
The SQL stored in the shared pool and some related information, such as the cumulative number of executions (executions), logical reads (buffer_gets), physical reads (disk_reads), and other statistical information.
You can locate a SQL based on address and hash_value. Sql_text Word Gencun The first 1000 characters of this SQL. Finding the entire SQL also needs to go to V$sqltext or v$sqltext_with_newlines.

3) V$session
All current session information, including some user information such as username,terminal,program,logon_time, command field indicates what commands the session executes.
Through v$session.paddr=v$process.addr and process related, through v$session.taddr=v$transaction.addr and trancation related.
The SID can be used to query the relevant view of the various information related to this session, such as V$sesstat in the session of the statistics, v$ Sess_io have session of the IO statistics, v$session_ Wait for the session in the waiting information, v$session_event in the session for a period of time to wait for the various events information.
Depending on the Lockwait field, you can query information about the lock that is currently waiting:
Select from v$lock where kaddr in (select Lockwait from v$session where Sid=&sid);
(Sql_address,sql_hash_value), (Prev_sql_addr,prev_hash_value) based on these two sets of fields, you can query the details of the SQL statement being executed or most recently executed by the current session:
Select from v$sqltext where address = &sql_address and Hash_value = &sql_hash_value;

4) V$sesstat
Depending on the session ID, you can get the statistics for this session:
Select A.name,b.value
From V$statname A,v$sesstat b
where a.statistic#=b.statistic#
and B.sid=&sid
and B.value <>0
Order BY B.value;

5) v$session_wait
Depending on the session ID, you can get the wait for this session.
The event field represents a wait event.
P1, p2, p3 tell us what the exact meaning of the wait event is, and if wait is the db file scattered read,p1=file_id/p2=block_id/p3=blocks, then Dba_ Extents can determine the hot object, if it is latch free, p2 is the latch number, which points to v$latch.
The P1raw,p2raw,p3raw column corresponds to the hexadecimal value of the P1,P2,P3, and the P1text,p2text,p3text column corresponds to the P1,P2,P3 column's interpretation.
--The waiting event and its corresponding latch
Col Event format A32
Col name format A32
Select Sid,event,p1 as file_id, p2 as "Block_id/latch", p3 as Blocks,l.name
From V$session_wait Sw,v$latch l
Where event is not a like '%sql% ' and an event not a like '%rdbms% '
and event not like '%mon% ' and sw.p2 = l.latch# (+);
--waiting events and their hot objects
Col owner Format A18
Col segment_name format A32
Col segment_type format A32
Select Owner,segment_name,segment_type
From Dba_extents
where file_id = &file_id and &block_id between block_id
and block_id + &blocks-1;
--Combine the above two SQL to display latch and hot objects (slower)
Select Sw.sid,event,l.name,de.segment_name
From V$session_wait sw,v$latch l,dba_extents de
Where event is not a like '%sql% ' and an event not a like '%rdbms% '
and event not like '%mon% ' and sw.p2 = l.latch# (+) and SW.P1 = de.file_id (+) and P2 between de.block_id and de.block_id + de.blocks-1;
--If a non-idle wait event, the SID that waits for the session can find out the SQL that the session is executing
Select Sql_text
From V$sqltext_with_newlines st,v$session SE
where St.address=se.sql_address and St.hash_value=se.sql_hash_value
and Se.sid =&wait_sid order by piece;

6) V$process
can find information about the operating system process based on the session ID:
Select from v$process where addr in (select Paddr from V$session whe Re sid=&sid); The
SPID field is the operating system process number that can be used to perform such an operation as the kill-9 spid. The
can find the most CPU-intensive PID of Unix command top, based on the PID most resource-consuming sql:
SELECT??/ + ORDERED */
???????? Sql_text
??? From V$sqltext a
?? WHERE (A.hash_value, a.address) in (
??????????? SELECT DECODE (sql_hash_value,
?????????????????????????? 0, Prev_hash_value,
?????????????????????????? sql_ Hash_value
?????????????????????????),
?????????????????? DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
????????????? From V$session b
???????????? WHERE b.paddr = (SELECT addr
??????????????????????????????? From v$process C
?????????????????????????????? WHERE c.spid = ' &pid '))
ORDER by piece ASC
/

7) V$transaction
Depending on the session ID, you can find the transaction information that is executing at the current session:
SELECT * from V$transaction where addr in (select Taddr from v$session where Sid=&sid);
Look at the following two fields to see how the transaction is going:
Used_ublk
Number
Number of undo blocks used

Used_urec
Number
Number of undo records used
Repeating the two values, you can see the change, you can estimate the progress of the transaction, especially the long rollback operation, when the two values are 0, the rollback is complete.

8) V$sort_usage
Temp table space usage, when the temp table space becomes huge, according to SESSION_ADDR can get session ID, according to SQLADDR and Sqlhash can get the SQL that is executing:
Select Se.username,se.sid,su.extents,su.blocks*to_number (RTrim (P.value)) as Space,tablespace,segtype,sql_text
From V$sort_usage su,v$parameter p,v$session se,v$sql s
where P.name= ' db_block_size '
and SU.SESSION_ADDR=SE.SADDR
and S.hash_value=su.sqlhash
and S.ADDRESS=SU.SQLADDR
Order BY Se.username,se.sid;

9) V$sysstat
Statistical information for all instance.

Oracle's Common dynamic views