Explanation of nine basic Oracle performance optimization methods (1)

BKJIA database channel recommends the Oracle database debugging and performance optimization topic for you to better understand this article.

The basic Oracle performance optimization methods include:

1) set up reasonable Oracle performance optimization goals.

2) measure and record the current Oracle performance.

3) determine the current Oracle performance bottleneck what Oracle waits for and what SQL statements are the elements of the wait event ).

4) record the waiting event to the tracking file.

5) determine the current OS bottleneck.

6) Optimize the components required for applications, databases, I/O, contention, OS, etc ).

7) Track and implement the change control process.

8) measure and record the current performance

9) Repeat steps 3 to 7 until the optimization goal is met.

The following is a detailed description.

1. Set up reasonable Oracle performance optimization goals

Key: the most important thing about setting goals is that they must be quantifiable and achievable.

Method: The target must be a statement of the current performance and required performance.

2. measure and record the current Oracle performance highlights:

1) The current system performance snapshot needs to be obtained during peak activity time

2) the key is to collect information during the time when performance problems occur.

3) The Snapshots must be collected within a reasonable period of time. Generally, several 15-minute snapshots are taken during the peak period.

3. determine the current Oracle performance bottleneckImportant: Obtain the waiting event from the Oracle wait interface v $ system_event, v $ session_event, and v $ session_wait, and then find the objects and SQL statements that affect the performance. The method is as follows:

1) First, use the v $ system_event view to execute the following query to view some common wait events in the database:

 
 

  
  
select * from v$system_event  
  
  
where event in ('buffer busy waits',  
  
  
'db file sequential read',  
  
  
'db file scattered read',  
  
  
'enqueue',  
  
  
'free buffer waits',  
  
  
'latch free',  
  
  
'log file parallel write',  
  
  
'log file sync'); 
 
 

2) then, we will use the following queries for the v $ session_event and v $ session Views to study sessions that have contributed to the events shown above:

 
 

  
  
select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait  
  
  
from v$session s,v$session_event se  
  
  
where s.sid = se.sid  
  
  
and se.event not like 'SQL*Net%' 
  
  
and s.status = 'ACTIVE' 
  
  
and s.username is not null; 
 
 

3) use the following query to find the current wait event related to the connected session. This information is dynamic. To view the most waiting events of a session, you need to perform 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.sid = sw.sid  
  
  
and sw.event not like 'SQL*Net%' 
  
  
and s.username is not null 
  
  
order by sw.wait_time desc; 
 
 

4) query session wait event details

 
 

  
  
select sid,event,p1text,p1,p2text,p2,p3text,p3  
  
  
from v$session_wait  
  
  
where sid between &1 and &2  
  
  
and event not like '%SQL%' 
  
  
and event not like '%rdbms%'; 
 
 

5) Use P1 and P2 information to find the relevant segments of the waiting event.

 
 

  
  
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; 
 
 

6) obtain the SQL statement for the operation segment:

 
 

  
  
select sid, getsqltxt(sql_hash_value,sql_address)  
  
  
from v$session  
  
  
where sid = &sid_in; 
 
 

7) getsqltxt Function

8) So far, we have found objects and SQL statements that affect performance and can optimize them accordingly.