Data | database | Performance failure phenomenon
June 8, 2004 10:00, Inner Mongolia, Pakistan Union netcom users reflected in the OSS system interface, "Talk single query" Query single users five days of the statement is particularly slow, the query for a long time without results.
For example: In the OSS system interface "Comprehensive query", click "-〉", type "user number, start time: 2004-01-01 00:00:00, End time: 2004-06-01 23:00:00", click Query, IE progress bar slow, A long time does not return the result.
Analysis of fault analysis, this phenomenon and the performance of the database, mainly the database initialization parameter adjustment unreasonable caused by low performance. The detailed analysis steps are as follows:
1. First, query the index of the conversation table is not valid, because the failed index will result in poor SQL query efficiency.
Sql>select index_name,status from user_ind_partitions where status!= ' USABLE ';
No rows selected.
The results show that there is no invalidation of the single table index.
2. Use the top command to see that the available physical memory is very low, leaving only 100M, a large amount of swap memory is in use, an Oracle single session consumes a lot of memory, after viewing the value of the Oracle initialization parameter Shared_pool_size set too high, should be readjusted.
Results of top:
Last pid:4565; Load averages:0.15, 0.20, 0.20
10:09:56
3. To further analyze Oracle's performance, use Oracle's own diagnostic Tool Statspack for performance snapshot analysis, with a statistical time period of 1 hours, from 17:00 to 18:00. This period of time business is busy, select in this section of the entire system performance analysis, can get more accurate information.
To install the Statspack profiling tool:
Sql>connect Internal
Sql>alter system Set Timed_statistics=true (collecting timing information for the operating system)
After analyzing the report, we find that some unreasonable initialization parameters need to be adjusted, and recommend the following adjustments:
1. The report found that the full table scan is particularly numerous, so it is recommended that you avoid using full table scans in the program.
Reduce IO waiting, which speeds up the execution of statements.
The following statement needs to be optimized:
Sql>select Count (*) as totalcount from Localusage where SE
rviceid=: "Sys_b_0" and Starttime>=to_date (: "Sys_b_1",: "sys_b_2")
and StartTime <=to_date (: "Sys_b_3",: "Sys_b_4") and (Localroami
Ngcharge: "Sys_b_5" or Localcharge: "Sys_b_6" or Urbancharge
: "Sys_b_7" or Ruralcharge: "Sys_b_8");
2. Adjust db_file_multiblock_read_count=16
This parameter specifies the maximum number of blocks read during an I/O operation for a fully sequential scan. The increase in IO is improved, especially when you are doing full table scan, you can reduce the number of IO.
3. Adjust db_block_lru_latches=2
This parameter specifies the upper limit of the number of LRU latch sets. The number of LRU locks is used internally to manage database buffering within an Oracle database, and it relies heavily on the number of CPUs on the server, which is typically set to half the Cpu_count on the server, and increasing this value helps improve disk I/O performance.
4. Adjust session_cached_cursors=200
This parameter specifies the number of session cursors to cache, and after multiple parsing of the same SQL statement, its session cursor is moved to the cursor cache of that session. Increasing this value can shorten the parsing time because cursors are cached and need not be reopened.
5. Adjust log_buffer=1048576
Parameter Log_buffer Specifies the amount of memory that is used to cache these entries before LGWR writes the contents of the redo log buffer to the redo log file. This parameter is in bytes and is affected by Cpu_count, Log_buffer if it is set too high (for example, greater than 1MB), this can cause performance problems because a large capacity result will make write synchronization (for example, log sync wait events are very high).
According to Hangzhou's plan, Oracle eventually runs up to 1/2 of the physical memory. The main two parameters are:
Db_block_buffers: Its configuration principle is that the final data block cache occupies 1/3 of the memory.
Shared_pool_size: Its configuration principle is that the basic control is around 200-500m.
7. The five events that the system waits for the most serious from the report are: DB file sequential read,log file Sync,log file parallel write,db file parallel write and db file scattered Read.
(1) For DB file sequential read wait event, the general problem appears on the read index, it is recommended that the Wacos table space and Wacos Index table space be stored separately under different physical volumes to improve the I/O performance of the disk.
(2) For DB file scattered read wait events, it is recommended that you avoid using full table-scanned statements, or you can increase the value of db_file_multiblock_read_count, increase the speed of reading blocks once a full table scan, and reduce disk I/O.
(3) For DB file parallel write wait events, the DBWR process is waiting for the contents of the buffer to be written in parallel to the data file, and the wait will continue until all I/O is complete. It is recommended that you increase the value of the db_writer_processes in the initialization parameter to 4.
(4) For log file sync waiting event, indicating that whenever a thing commits, it will notify LGWR to write log_buffer to the log file, if this part takes longer time, should reduce the number of commits, it is recommended to put the redo log on the faster disk for storage.
(5) For log file parallel write wait event, it is recommended that the redo log be stored on a faster disk.
Fault Handling
Adjust the unreasonable parameters in the Initorcl.ora, the specific adjustment is:
process=200
log_buffer=1048576
session_cached_cursors=200
db_block_lru_latches=2
Shared_pool_size= 262144000
Db_block_buffers = 200000
Sort_area_size = 6553600
Sort_area_retained_size = 6553600
Db_file_multiblock_read_count = 16
Processing results
After the reset DB was adjusted, all the queries were found to be normal and the results were quickly returned.
Summary database initialization parameters set unreasonable, too little memory, resulting in database operation using a large number of swap space, database performance is poor, resulting in the interface through the OSS query is slow. You need to fix the problem by adjusting the database initialization parameters. From the performance aspect, the database server is best able to spare 300-500m above memory.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
