- The main waiting view Select * from v $ session_wait; select * from v $ session_event; select * from v $ system_event; select * from v $ waitstat where count> 0;
- Related View SELECT * FROM V $ session_wait selection * from v $ filestat
SELECT * FROM DBA_DATA_FILES
Select * from dba_extents where owner = 'wang'
Select * from sys.uet $
SELECT * FROM V. LATCH
SELECT * FROM V $ filestat where file # in (SELECT P1 from V $ session_wait)
- Performance optimization method based on wait event
- Frequently waiting for events Select * from V $ SYSTEM_EVENT WHERE EVENT IN ('Buffer Business Waits',' DB File Sequential Read ',' DB File Scattered Read ',' Enqueue ',' Latch Free ", 'log file parallel write', 'log file sync')
- View waiting events that have contributed above
Select B.username, B.Program, B.status, A. Event, A. Total_Waits, A. Total_Timeouts, A.Time_WaITED, A.AVERAGE_WAIT from V $ Session_Event A, V $ Session B Where B.userName Is Not Null and A.SID = B. Sidand a.Event Not Like 'SQL * Net%' and B.Status = 'Active'
- In order to find the current wait event related to the connected session, use the following query. This information is dynamic, in order to view a session, the event is the most waiting, you need to execute this query multiple times.
Select sw.SID, S.Username, sw.Event, sw.seconds_in_wait second, sw.seconds_in_wait sec_in_wait from v $ session s, v $ session_wait swwhere s.username is not null and s.SID = sw.sid And sw.event not like 'sql * net%' order by sw.wait_time desc;
- Query shows additional information about the test.
Select Sid, Event, P1Text, P1, P2Text, P2, P3Text, P3 from V $ Session_Waitwhere Event Not Like '% SQL%' And Event Not Like '% RDBMS%'
- Use the information of P1 and P2 easy to find out what paragraphs
Select Owner, Segment_name, Segment_Type, TableSpace_name from DBA_EXTENTSWHERE File_ID = & FileId_Inand & Blockid_in Between Block_ID and Block_ID Blocks-1
- Access table item_master
--Getsqltxt.sql
CREATE OR REPLACE function GetSQLTxt (HashAddr_in in V $ SQLTEXT.Hash_Value% Type, Addr_in in V $ SQLTEXT.Address% Type) return varchar2 is Temp_SQLTxt varchar2 (32767); cursor SQLPiece_Cur is select Piece, SQL_text from v $ sqltext where Hash_value = HashAddr_in and Address = Addr_in order by Piece; begin for SQLPiece_Rec IN SQLPiece_Cur loop Temp_SQLTxt: = Temp_SQLTxt || SQLPiece_Rec.Sql_Text; end loop return Temp_SQLTxt; end GetSQLTxt; / - to see how much a full table scan occurred since the database started select * from v $! Sysstat where name like '% table scan%'
- Monitor full surface scan
Select Sid, Serial #, OPNAME, TO_CHAR (Start_Time, 'HH: MI: SS') "Start Time", Sofar / Totalwork "% Complete"
From v $ session_longops
*********************************************************** *********************************************************** **************** - Full table Scan time longer resolution process
Current Oracle System Performance Query Musicsong Table, full table query uses 400 seconds query use ____ how much resources
The most basic time spent 1. Database read, put a table into memory 2. Calculate the section (entire CPU), process these table 3. Database
- Determine Oracle System Bottleneck
Select * from v $ system_event where total_timeouts> 0 Order by Total_Timeouts DESC
DROP TABLE BEGIN_SYS_EVENT; DROP TABLE END_SYS_EVENT
/ * CREATE TABLE BEGIN_SYS_EVENT AT TIME T1 * / CREATE TABLE BEGIN_SYS_EVENT AS SELECT * FROM V $ SYSTEM_EVENT
/ * Wait n seconds or n minutes * /
/ * CREATE TABLE END_SYS_EVENT AT TIME T2 * / CREATE TABLE END_SYS_EVENT AS SELECT * FROM V $ SYSTEM_EVENT
SELECT T1.EVENT, (T2.TOTAL_WAITS-T1.TOTAL_WAITAL_WAITS ", (T2.TOTAL_TIMEOUTS-T1.TOTAL_TIMEOMEOMEOUTS", (t2.time_waited-t1.time_waITED) "Delta Time Waite", (T2 . Above_wait-t2.average_wait) "Delta average Wait" from begin_sys_event t1, end_sys_event t2 where t1.event = t2.event and t2.total_waits! = 0;
Select * from V $ Event_Name; SELECT * from V $ SYSTEM_EVENT WHERE EVENT NOT IN '% PMON TIMER%' And Event Not Like '% RDBMS IPC Message%'
- Specifies the upper limit of the number of LRU latch sets. This value is required only if the failure rate in V $ Latch exceeds 3%. SELECT * from V $ latch;
- How to identify the conflict between internal latch
Server Manager Monitor is a tool that is quite useful to monitor Latch waiting, request and conflict. You can also query the relevant data dictionary table: V $ Latch, V $ Latchholder, V $ latchname.
- 4. There are more than 40 species, but as the main concern of DBA, there should be the following:
Cache Buffers CHAINS LATCH: This latch is required when the user process search SGA is looking for Database Cache Buffers.
Cache Buffers LRU Chain Latch: Use this latch when the user process is to search for the LRU (Least Recessly Used) chain including all Dirty Blocks, including the Buffer Cache.
Redo Log Buffer Latch: This Latch Controls Spatial Assignment of each Redo Entries in Redo Log Buffer.
Row Cache Objects Latch: Row Cache Objects Latch will be used when the user process accesses the cache data dictionary
Redo Copy Latch is only applied to a multi-CPU system. In a multi-CPU's instance, if a Redo Entry is too large, more than the log_small_entry_max_size definition value, the "copy on the Redo Allocation Latch" is not possible, and the user process must obtain Redo Copy Latch. There are multiple Redo Copy Latches in an Instance, which is determined by the initial parameter log_simultaneous_copies, the default value is the number of CPUs.
In the case of single CPU, there is no Redo Copy Latch, and all Redo Entry performs "copy of the Redo Allocation Latch" regardless of the size.
Excessive access to Redo Log Buff will lead to the conflict of Redo Log Buffer Latch, Latch conflicts will reduce system performance, and we can detect this Latch conflict by queries:
COL Name for A40
Select Ln.name, Gets, Misses, Immediate_Gets, Immediate_Misses
From v $ latch l, v $ latchname ln
Where ln.name in ('redo allocation ",' redo copy ') and ln.latch # = l.latch #
/
If the proportion of Misses is more than 1% or IMMEDIATE_MISSES with (IMMEDIATE_GETS Immediate_Misses), it should be considered to reduce the conflict of the LATCH.
