Shulou(Shulou.com)06/01 Report--

1) v$lock

The lock information is given, such as type field, there are three kinds of user type locks: there are many kinds of TM,TX,UL,system type locks, the common ones are: MR,RT,XR,TS, etc. All we care about is the TM,TX lock.

When TM is locked, the id1 field represents object_id;. When TX is locked, trunc (id1/power (2Magne16)) represents the rollback segment number.

Lmode field. There are 6 modes of locks held by session:

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 field, the mode of the lock requested by process, and the value range is the same as that of lmode.

Ctime field, the time the lock has been held or waited.

Block field, whether to block other lock applications, when block=1 indicates that this session blocks other session.

2) v$sqlarea, v$sql, v$sqltext

The sql and some related information stored in the shared pool, such as cumulative execution times (executions), logical reads (buffer_gets), physical reads (disk_reads) and other statistics.

A sql can be located based on address and hash_value. The first 1000 characters of the sql stored in the sql_text field. To find the entire sql, you need to go to v$sqltext or v$sqltext_with_newlines.

3) v$session

All the information about the current session, including some user information such as username,terminal,program,logon_time, etc., and the command field indicates what commands are executed by session.

Related to process through v$session.paddr=v$process.addr, related to trancation through v$session.taddr=v$transaction.addr.

Through sid, you can query various information related to this session in the relevant view, such as session statistics in v$sesstat, io statistics of session in v$sess _ io, waiting information of session in v$session_wait, and information about various events that session has been waiting for in a period of time in v$session_event.

You can query the information about the currently waiting lock according to the lockwait field:

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 current or most recent sql statement being executed by session:

Select from v$sqltext where address = & sql_address and hash_value = & sql_hash_value

4) v$sesstat

According to session id, you can get the statistics of this session:

Select a.name,b.value

From v$statname arecoversesstat b

Where a.STATISTIC#=b.STATISTIC#

And b.sid=&sid

And b.value 0

Order by b.value

5) v$session_wait

According to session id, you can get the wait of this session.

The event field represents a wait event.

P1, p2, and p3 tell us the exact meaning of waiting events: if wait event is db file scattered read,p1=file_id/p2=block_id/p3=blocks, then the hot object can be identified by dba_extents; if it is latch free, p2 is the latch number, which points to v$latch.

The P1RAWP3RAW column corresponds to the hexadecimal value of the P1Magazine P2rec P3, and the P1TEXTp2TEXT P3TEXT column corresponds to the explanation of the P1Magazine P2meme P3 column.

Find the wait 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 not like'% SQL%' and event not like'% rdbms%'

And event not like'% mon%' and sw.p2 = l.latch# (+)

-- looking for 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 both latch and hotspot objects (slow speed)

Select sw.sid,event,l.name,de.segment_name

From v$session_wait sw,v$latch l,dba_extents de

Where event not like'% SQL%' and event not 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 it is a non-idle wait event, you can find out the sql that the session is executing by waiting for the sid of the session.

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

According to session id, you can find information about operating system processes:

Select from v$process where addr in (select paddr from v$session where sid=&sid)

The spid field is the operating system process number, which can be used to perform an operation such as kill-9 spid.

You can find the pid that consumes the most cpu resources from the unix command top, according to the sql that consumes the most resources in pid:

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

According to session id, you can find out the information about the transactions currently being executed by 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 far the transaction has gone:

USED_UBLK

NUMBER

Number of undo blocks used

USED_UREC

NUMBER

Number of undo records used

By repeatedly querying these two values, you can see the change, and you can estimate the progress of the transaction, especially for a long rollback operation. When the two values are 0, the rollback is complete.

8) v$sort_usage

For the usage of temp tablespaces, when temp tablespaces become huge, you can get session id according to session_addr, and sql that is being executed according to sqladdr and sqlhash:

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 pjungle session se,v$sql s

Where p.nameplate blockage 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

Statistics for all instance.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.