Overview of basic Oracle Performance Optimization Methods

Overview of basic Oracle Performance Optimization Methods

1) set up reasonable performance optimization goals.
2) measure and record the current 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, 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 performance optimization objectives
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.
It takes (hour/minute/second), but requires that it be executed within (hour/minute/second.
Used (resource), 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) You must collect snapshots within a reasonable period of time. Generally, You can take several 15-minute snapshots during the peak period.
Method: Execute statspack
-- Create a performance snapshot tablespace
Sqlplus sys as sysdba
Create tablespace perfstat datafile '/u02/oradata/dbnms/perfstat. dbf' size 500 m extent management local;
-- Install statspack
@ $ ORACLE_HOME/rdbms/admin/spcreate. SQL;
-- Obtain performance data and generate multiple snapshots
Sqlplus perfstat
Execute statspack. Snap;
-- Generate a performance Snapshot Report
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 that have 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 the information of P1 and P2 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;
-- Obtain the SQL statement for the operation 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 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;
-- Now we have found the 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.
Method:
-- For the current session:
Alter session set timed_statistics = true;
Alter session set max_dump_file_size = unlimited;
Alter session set events '2017 trace name context forever, level 12 ';
-- Execute 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 session process ID (spid ). 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 = )*/
-- Trace session applications at a certain interval.
-- Use spid to view the trace file in the directory indicated 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> Administrative Tools> 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
The execution Queue (r) should be clear that the average is less than (2 * Number of CPUs)

6. Optimize the components required (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.