Oracle performance adjustment note

-- 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
 
Select * 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 events

-- Common wait events
Select * from V $ system_event
Where event in ('buffer busy waits ', 'free buffer waits', 'db file sequential read ',
'Db file scattered read', 'enqueue ', 'latch free', 'Log file parallel write', 'Log file sync ')

-- View the wait events that contribute to the above display

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. Sid
And a. event not like 'SQL * Net %'
And B. Status = 'active'

-- To locate the current wait event related to the connected session, use the following query. this information is dynamic. To view the most waiting events of a session, you need to execute this query multiple times.

Select Sw. Sid, S. username, SW. event, SW. wait_time,
Sw. State, SW. seconds_in_wait sec_in_wait
From v $ session S, V $ session_wait SW
Where S. username is not null
And S. Sid = Sw. Sid
And Sw. event not like 'SQL * Net %'
Order by Sw. wait_time DESC;

-- The query displays other information about the test wait event.

Select Sid, event, p1text, P1, p2text, P2, p3text, p3
From v $ session_wait
Where event not like '% SQL %'
And event not like '% RDBMS %'

-- Using P1 and P2 information, you can easily find out which segment is

Select owner, segment_name, segment_type, tablespace_name from dba_extents
Where file_id = & fileid_in
And & blockid_in between block_id and block_id + blocks-1

-- Access Table item_master

-- Getsqltxt. SQL

Create or replace function getsqltxt (hashaddr_in v $ sqltext. hash_value % type, addr_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;
/

-- Check how many full table scans have occurred since the database was started!
Select * from V $ sysstat where name like '% table scan %'

-- Monitor full table Scan

Select Sid, serial #, opname, to_char (start_time, 'hh: MI: ss') "Start time", SOFAR/totalwork "% complete"

From v $ session_longops

**************************************** **************************************** ************************************
-- A long Solution Process for full table Scan

Current ORACLE System Performance
It takes 400 seconds to query the musicsong table.
Query the ________ resources used

Basic time spent
1. Read the database and load a table into the memory.
2. The computing part (the entire CPU) is used to process these tables.
3. Database write back

-- Identify Oracle system bottlenecks

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
Select * from V $ system_event

/* Wait n seconds or n minutes */

/* Create Table end_sys_event at time t2 */
Create Table end_sys_event
Select * from V $ system_event

Select t1.event, (t2.total _ waits-T1.total_waits) "delta waits ",
(T2.total _ timeouts-T1.total_timeouts) "delta timeouts ",
(T2.time _ waited-T1.time_waited) "delta time waited ",
(T2.average _ 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 %'

-- Specify the maximum number of LRU locks. This value must be increased only when the failure rate in V $ latch exceeds 3%.
Select * from V $ latch;

-- How to identify internal latch conflicts

Server Manager monitor is a useful tool for monitoring latch waits, requests, and conflicts.
You can also query related data dictionary tables:
V $ latch, V $ latchholder, V $ latchname.

-- There are more than 40 latch types, but DBAs should focus on the following types:

Cache buffers chains latch: This latch is used when the user process searches for SGA database cache buffers.

Cache buffers LRU chain latch: This latch is used when a user process needs to search for the LRU (least recently used) chain of buffer cache including all dirty blocks.

Redo log buffer latch: This latch controls the space allocation of each redo entries in the redo log buffer.

Row cache objects latch: when a user's process accesses the cached data dictionary value, the row cache objects latch is used.

Redo copy latch is only applicable to multiple CPU Systems. In a multi-CPU instance, if a redo entry is too large to exceed the defined value of log_small_entry_max_size, "copy on redo allocation latch" cannot be performed ", in this case, the user process must obtain redo copy latch. An instance can have multiple redo copy latch. The number is determined by the initial parameter log_simultaneous_copies. The default value is the number of CPUs.

In the case of a single CPU, there is no redo copy latch. All redo entries, regardless of their size, are copied on the redo allocation latch ".

Excessive access to the redo log buffer will lead to a redo log buffer latch conflict. Latch conflicts will reduce system performance. We can detect this latch conflict through the following query:

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 ratio of misses to gets exceeds 1% or the ratio of immediate_misses to (immediate_gets + immediate_misses) exceeds 1%, measures should be taken to reduce latch conflicts.