Basic approach to Oracle performance optimization

The basic approach to Oracle performance optimization includes several steps, including:

1 set a reasonable target for Oracle performance optimization.

2 measure and record the current Oracle performance.

3 Determine the current Oracle performance bottleneck (what Oracle waits for and which SQL statements are components of the wait event).

4) The waiting event is recorded in the trace file.

5 determine the current OS bottleneck.

6 Optimize the required components (applications, database tutorials, I/O, contention, OS, etc.).

7 Follow up and implement the change control process.

8) measure and record current performance

9 Repeat steps 3 through 7 until the optimization goal is met

Here are one by one details.

1. Set up reasonable Oracle performance optimization objectives

Emphasis: The most important point about setting goals is that they must be quantifiable and achievable.

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

2. Measure and record current Oracle performance focus:

1 need to get current system performance snapshots at peak activity time

2 The key is to collect information in the time period when performance problems occur

3 must be collected in a reasonable time period, typically a few 15-minute snapshots during peak periods

3. Identify current Oracle performance bottlenecks Focus: Get wait events from Oracle wait Interfaces V$system_event, V$session_event, and v$session_wait to identify objects and SQL statements that affect performance. The method is as follows:

1 First, use the V$system_event view to perform the following query to view some common waiting events in the database:

1.select * from V$system_event

2.

3.where event in (' Buffer busy Waits ',

4.

5. ' DB file sequential read ',

6.

7. ' db file scattered read ',

8.

9. ' Enqueue ',

10.

One. ' Free buffer waits ',

12.

' Latch free ',

14.

' Log file parallel write ',

16.

' Log file Sync ');

18.

2 Next, use the following query to the V$session_event and v$session views to study a session that has a contribution to the content shown above:

1.select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait

2.

3.from v$session s,v$session_event SE

4.

5.where S.sid = Se.sid

6.

7.and se.event not like ' sql*net% '

8.

9.and s.status = ' ACTIVE '

10.

11.and s.username is not null;

12.3 Use the following query to find the current wait event related to the connected session. This information is dynamic, and this query needs to be executed multiple times in order to see what is the most waiting event for a session.

1.select sw.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait sec_in_wait

2.

3.from v$session s,v$session_wait SW

4.

5.where S.sid = Sw.sid

6.

7.and sw.event not like ' sql*net% '

8.

9.and S.username is not null

10.

11.order by sw.wait_time Desc;

12.

4 Query the details of the session wait event

1.select SID,EVENT,P1TEXT,P1,P2TEXT,P2,P3TEXT,P3

2.

3.from v$session_wait

4.

5.where sid between &1 and &2

6.

7.and event not like '%sql% '

8.

9.and event not like '%rdbms% ';

10.

5 using P1, P2 information to find the relevant segments of the waiting event

1.select Owner,segment_name,segment_type,tablespace_name

2.

3.from dba_extents

4.

5.where file_id = &fileid_in

6.

7.and &blockid_in between block_id and block_id + blocks-1;8.

6 get the SQL statement that operates the paragraph:

1.select SID, Getsqltxt (sql_hash_value,sql_address)

2.

3.from v$session

4.

5.where sid = &sid_in;

6.

7) Getsqltxt function

8 to this point you have found objects and SQL statements that affect performance and can be targeted to optimize

4. Record the waiting event in the trace file

Important: If you are having trouble keeping track of waiting events on your system for some reason, you can record these wait events in a trace file. The method is as follows:

1 for the current session:

1.alter session Set Timed_statistics=true;

2.

3.alter session Set max_dump_file_size=unlimited;

4.

5.alter session Set Events ' 10046 Trace name Context forever, Level 12 ';

6.

2 Execute the application and locate the trace file in the directory indicated by User_dump_dest.

3 View all rows in the file that start with a word wait.

4 for other sessions

5 Determine the session's process ID (SPID). The following query identifies the session process ID of all users with the name starting with a:

1.select S.username, p.spid from V$session S, v$process P

2.

3.where s.paddr = p.addr and s.username like ' a% ';

4.

6) to sysdba into Sqlplus execution

1.alter session Set Timed_statistics=true;

2.

3.alter session Set max_dump_file_size=unlimited;

4.

5.oradebug Setospid

6.

7.oradebug Unlimit

8.

9.oradebug event 10046 Trace name forever, Level X/* Where x = (1,4,8,12) * *

10.

7 Track a session application at a time interval.

8 Use the SPID to view the trace file in the directory indicated by the User_dump_dest value

9 View all rows in the file that start with a word wait.

5. Determine current OS bottleneck 1 monitoring on Windows NT

Use Control Panel-〉 management tools-〉 Performance

2. Monitoring on Unix

Use general-purpose tools, including SAR, Iostat, Cpustat, MPs tutorial tat, netstat, top, osview, etc.

6. Components required for Oracle performance tuning (applications, databases, I/O, contention, OS, etc.).

7. Track and implement the change control process.

8. Measure and record current Oracle performance

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