Solution when the database slows down

Chapter 1 Check System Status
1.1 Use sar to check whether the operating system has IO Problems
1.2 Focus on memory vmstat
1.3 find the sessions and statements executed by Oracle with extremely large resources
1.4 search for the top 10 SQL statements with poor performance
Chapter 2 Check session Status
What should we do when the database slows down?

When the application administrator notices that the application is slow and the database is slow, when Oracle DBA makes several sample Select statements on the database and finds the same problem, sometimes it will not start, because DBAs believe that all kinds of life-saving rates of databases meet the suggestions of Oracle documents. In fact, today's optimization has been transformed to optimization wait (waits). In reality, the most fundamental appearance of performance optimization is also concentrated on I/O, which is the most important aspect that affects performance, it is a good way to find out the deficiencies in the Oracle database and the unreasonable utilization of some operating system resources by waiting in the system. I will share some practical experience with you. This article focuses on Unix environments.
Chapter 1 Check System Status
Check the status of the system by using some operating system tools, such as CPU, memory, swap, and disk utilization. Based on experience or compared with the normal status of the system, sometimes the system looks idle on the surface, which may not be a normal status, because the cpu may be waiting for the completion of IO. In addition, you should also observe the processes that occupy system resources (cpu and memory.
1.1 Use sar to check whether the operating system has IO Problems
# Sar-u 2 10-check every two seconds. A total of 20 times are performed.
Result example:
Note: in redhat, % system is the so-called % wio.
Linux 2.4.21-20. ELsmp (YY075) 05/19/2005
10:36:07 am cpu % user % nice % system % idle
10:36:09 AM all 0.00 0.00 0.13 99.87
10:36:11 AM all 0.00 0.00 0.00 100.00
10:36:13 AM all 0.25 0.00 0.25 99.49
10:36:15 AM all 0.13 0.00 0.13 99.75
10:36:17 AM all 0.00 0.00 0.00 100.00
Where:
Ø % usr indicates the percentage of cpu resources used by the user process;
Ø % sys indicates the percentage of cpu resources used by system resources;
% Wio refers to the percentage waiting for io completion, which is worth noting;
% Idle indicates the percentage of idle resources.
If the value of the wio column is large, for example, more than 35%, it indicates that there is a bottleneck in the system IO, And the CPU spends a lot of time waiting for the completion of I/O. If the Idle is small, the CPU usage is very busy. As shown in the preceding example, the average wio value is 11, indicating that I/O has no special problems, and the idle value is zero, indicating that the cpu is fully loaded.
When the system has an I/O problem, you can solve it in the following aspects:
Ø contact the technical support of the corresponding operating system to optimize this aspect. For example, hp-ux strip the volume group.
Search for unreasonable SQL statements in Oracle and optimize them;
In addition to properly indexing tables with frequent accesses to Oracle, these tables are stored in tablespaces to avoid hotspot access. In addition, the tables are partitioned reasonably.
1.2 Focus on memory
The common tool is vmstat. for hp-unix, glance can be used. Topas can be used for Aix. When we find that the pi column in vmstat is non-zero, the free column value in memory is very small, and the memory usage in glance and topas is more than 80%, we should adjust the memory. The methods include the following:
Ø the memory allocated to Oracle should not exceed 1/2 of the system memory, which is generally guaranteed as 40% of the system memory.
Increase the system memory;
If you have many connections, you can use MTS;
Complete patches to prevent memory vulnerabilities.
1.3 find the sessions and statements executed by Oracle with extremely large resources
Hp-unix can use glance or top. Topas can be used in ibm aix. In addition, you can use the ps command.
Through these programs, you can find the process numbers of these processes with extremely large system resources. Then, you can use the following SQL statement to find the SQL statement that the pid is executing, it is best to execute this SQL statement in pl/SQL developer, toad, and other software:
SELECT a. username, a. machine, a. program, a. sid, a. serial #, a. status,
C. piece, c. SQL _text
FROM v $ session a, v $ process B, v $ sqltext c
WHERE B. spid = 'orcl'
AND B. addr = a. paddr
AND a. SQL _address = c. address (+)
Order by c. piece;
You can analyze the obtained SQL to see if its execution plan is indexed. Optimize the SQL statements to avoid full table scans to reduce IO waits and speed up statement execution.
Tip: when optimizing SQL statements, you often encounter statements that use in. In this case, you must use exists to replace the statements, because Oracle performs Or when processing in statements, even if you use an index, it will be slow. For example:
SELECT col1, col2, col3 FROM table1
WHERE a. col1 not in (SELECT col1 FROM table2)
Can be changed:
SELECT col1, col2, col3 FROM table1
WHERE NOT EXISTS
(SELECT 'x' FROM table2 B WHERE a. col1 = B. col1)
1.4 search for the top 10 SQL statements with poor performance
SELECT * FROM (SELECT parsing_user_id, executions, sorts, command_type,
Disk_reads, SQL _text FROM v $ sqlarea
Order by disk_reads DESC)
Where rownum <10;
Chapter 2 Check session Status
To quickly find out the cause of Oracle Server performance problems, you can turn to the v $ session_wait view to see what the system's sessions are waiting for and how much I/O is used. The following is a reference script:
-- Script Description: view the running sessions that account for a large I/O:
SELECT se. sid, se. serial #, pr. spid, se. username, se. status, se. terminal,
Se. program, se. module, se. SQL _address, st. event, st. p1text,
Si. physical_reads, si. block_changes
FROM v $ session se, v $ session_wait st, v $ sess_io si, v $ process pr
WHERE st. sid = se. sid AND st. sid = si. sid
AND se. PADDR = pr. ADDR
AND se. sid> 6
AND st. wait_time = 0
AND st. event not like '% SQL %'
Order by physical_reads DESC;
Description of the retrieved results:
1. The above is the order of the physical read rows that have occurred for each pending session, because it is related to the actual I/O.
2. Can you see what these waiting processes are busy? Is the statement reasonable?
SELECT SQL _address FROM v $ session WHERE sid =;
SELECT * FROM v $ sqltext WHERE address =;
Run the preceding two statements to obtain the session Statement.
Also, use alter system kill session 'sid, serial # '; to kill this session.
3. Check the event column. This is the key column for optimization. The following briefly describes the common events:
1) The buffer busy waits and free buffer waits parameters identify whether dbwr is sufficient and are highly IO-related, when the number of free buffer wait entries in v $ session_wait is small or absent, it indicates that the dbwr process in the system is sufficient and does not need to be adjusted. There are many free buffer wait entries, and the system will feel very slow, at this time, it indicates that dbwr is not enough, and the wio generated by dbwr has become the bottleneck of database performance. The solution is as follows:
Add the write process and adjust the db_block_lru_latches parameter:
Example: modify or add the following two parameters
Db_writer_processes = 4
Db_block_lru_latches = 8
Enable asynchronous IO. IBM is much simpler in this regard, and hp is a little more troublesome. You can contact Hp engineers.
2) db file sequential read refers to sequential read, that is, full table scan. This is also the part to be minimized. The solution is to use indexes and SQL optimization, you can also increase the value of db_file_multiblock_read_count.
3) the db file scattered read parameter refers to reading through indexes. You can also increase the performance by adding the db_file_multiblock_read_count parameter.
4) latch free is related to bolts and needs special adjustment.
5) Other parameters can be ignored.