Basic Oracle Performance Optimization Methods

The basic Oracle performance optimization methods include the following steps: 1) set up a reasonable Oracle performance optimization goal. 2) measure and record the current Oracle performance. 3) determine the current Oracle performance bottleneck (what is Oracle waiting for and what SQL statements are components of the waiting event ). 4) record the waiting event to the tracking file. 5) determine the current OS bottleneck. 6) components required for optimization (applications, IO, contention, OS, etc ). 7) Tracking

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 is Oracle waiting for and what SQL statements are components of the waiting event ).

4) record the waiting event to the tracking file.

5) determine the current OS bottleneck.

6) components required for optimization (applications, 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 bottleneck. Focus: Obtain the wait events from the Oracle wait interfaces v $ system_event, v $ session_event, and v $ session_wait, and then find out the objects that affect the performance and SQL statements. 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:

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. 'enable ',

10.

11. 'Free buffer waits ',

12.

13. 'latch free ',

14.

15. 'Log file parallel write ',

16.

17. 'Log file sync ');

18.

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:

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. To view the most waiting events of a session, you need to perform this query multiple times.

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 session wait event details

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) Use P1 and 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) obtain the SQL statement for the operation segment:

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) So far, we have found objects and SQL statements that affect 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. 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 '2017 trace name context forever, level 12 ';

6.

2) execute the application and find the trace file in the directory specified by USER_DUMP_DEST.

3) view all lines in the file starting with the word WAIT.

4) for other sessions

5) determine the session process ID (SPID ). The following query identifies the session process IDs of all users whose names start with:

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) run SQL plus with sysdba

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 context forever, level X/* Where X = )*/

10.

7) trace session applications at a certain interval.

8) Use SPID to view the trace file in the directory indicated by USER_DUMP_DEST.

9) view all lines in the file starting with the word WAIT.

5. Identify the current OS bottleneck 1) Monitoring on Windows NT

Use Control Panel> Administrative Tools> Performance

2) UNIX monitoring

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

6. components required for Oracle performance optimization (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.