Basic Solution for optimizing Oracle Performance

The following articles mainly describe the basic solution of Oracle performance optimization. The basic solution of Oracle performance optimization accounts for the majority of applications in practice. If you use this technology, if you are curious, the following articles will unveil its mysteries.

1) set up reasonable Oracle performance optimization goals.

2) measure and record the current 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.

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. You only need to enter spaces in the following statements.

Time/minute/second), but it is required to be executed within the hour/minute/second.

Resources used), but it cannot exceed.

2. measure and record the current performance

Important:

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.

Method: Execute STATSPACK

Create Oracle performance snapshot tablespace

 
 

  
  
sqlplus sys as sysdba  
  
  
create tablespace perfstat datafile '/u02/oradata/dbnms/perfstat.dbf' size 500M extent management local; 
 
 

Install STATSPACK

 
 

  
  
@$ORACLE_HOME/rdbms/admin/spcreate.sql; 
 
 

Obtain performance data and generate multiple snapshots

 
 

  
  
sqlplus perfstat  
  
  
execute statspack.snap; 
 
 

Generate performance snapshot reports

 
 

  
  
sqlplus perfstat  
  
  
select min(snap_id) snapid_min, max(snap_id) snapid_max from stats$snapshot;  
  
  
@$ORACLE_HOME/rdbms/admin/spreport; 
 
 

This report contains important performance information, such as the first five waiting events, cache size, hit rate of various memory structures, per second and per transaction logic, number of physical read/write data blocks, and the worst-performing SQL statement.

3. determine the current Oracle performance bottleneck

Important: Obtain the waiting event from the Oracle wait interface v $ system_event, v $ session_event, and v $ session_wait, and then find the objects that affect performance and SQL statements.

Method:

First, run the following query in the v $ system_event view 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'); 
 
 

Next, we will use the queries in the v $ session_event and v $ session Views to study the sessions with the waiting events that contribute to the content 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; 
 
 

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; 
 
 

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%'; 
 
 

Use P1 and P2 information to find the relevant segments of the wait 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; 
 
 

Obtain the SQL statement for this segment:

 
 

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

Getsqltxt Function

 
 

  
  
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; 
 
 

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

4. log the waiting event to the tracking File

Important: If you encounter any trouble in tracking wait events on the system for some reason, you can record these waiting events to a trail file.

Method:

For the current session:

 
 

  
  
alter session set timed_statistics=true;  
  
  
alter session set max_dump_file_size=unlimited;  
  
  
alter session set events '10046 trace name context forever, level 12'; 
 
 

Run the application and find the trace file in the directory specified by USER_DUMP_DEST.

View All rows starting with the word WAIT in the file.

For other sessions

Determine the idspid of the session process ). The following query identifies the session process IDs of all users whose names start with:

 
 

  
  
select S.Username, P.Spid from V$SESSION S, V$PROCESS P  
  
  
where S.PADDR = P.ADDR and S.Username like 'A%'; 
 
 

Run SQL plus with sysdba

 
 

  
  
alter session set timed_statistics=true;  
  
  
alter session set max_dump_file_size=unlimited;  
  
  
oradebug setospid <SPID> 
  
  
oradebug unlimit  
  
  
oradebug event 10046 trace name context forever, level X /* Where X = (1,4,8,12) */ 
 
 

Trace session applications at a certain interval.

Use SPID to view the trace file in the directory specified by USER_DUMP_DEST

View All rows starting with the word WAIT in the file.

5. determine the current OS bottleneck

1) Monitoring on Windows NT

Use Control Panel> Management Tools> Oracle Performance

2) UNIX monitoring

Common tools are used, including sar, iostat, cpustat, mpstat, netstat, top, and osview.

1) CPU usage

Sar-u 5 1000

% Sys and % wio should be less than 10 to 15 percent

2) device usage

Sar-d 5 1000

When % busy exceeds 60%, the optimal device utilization starts to decrease. In a system with sufficient disk cache speed, avserv is considered to be a very high value of 100 milliseconds.

3) virtual memory usage

Vmstat-S 5 1000

Execution queue r) it should be clear that the average number of CPUs is less than 2)

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 Oracle Performance

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