Fault phenomenon
At 10:00 am, 2004, Inner Mongolia Guiba Leverage Netcom, reflected in the OSS system interface "Copy Query", querying a single user five days, the query has no results for a long time.
For example: Click on "Fees" -> "" Credit Query ", type" User Number, Start Time: 2004-01-01 00:00:00, End Time: 2004-06- 01 23:00:00 ", after clicking the query, the IE schedule is slow, and the result is not returned for a long time.
Fault analysis
After analysis, this phenomenon is related to the performance of the database, mainly due to the unreasonable performance of the database initialization parameter adjustment. The specific analysis process is as follows:
1. First query, the index of the text, is invalid:
SQL> SELECT INDEX_NAME, STATUS from user_ind_partitions where status! = 'Usable'
No rows selected.
The results show that the single table index is not expired.
2. Use the TOP command to see the available physical memory is low, only 100m left, there is a large amount of SWAP, there are many memory, and there are many memory, and the value of the value of the Oracle initialization parameter shared_pool_size is set too high, which should be re-adjusted.
Top Results: Last Pid: 4565; Load Averages: 0.15, 0.20, 0.20 10:09:56
170 Processes: 169 Sleeping, 1 On CPU
CPU States: 84.9% iDLE, 1.6% User, 1.1% Kernel, 12.4% iowait, 0.0% SWAP
Memory: 4096m REAL, 100M Free, 1343M SWAP IN USE, 6851M SWAP FREE
Pid Username THR PRI Nice Size Res State Time CPU Command
10459 Oracle 1 59 0 1978M 1953M Sleep 0:53 0.79% Oracle
2258 Oracle 1 10 0 1976M 1951M Sleep 116: 57 0.65% Oracle
25639 Oracle 1 58 0 1975M 1949M Sleep 1:56 0.27% Oracle
1948 Oracle 1 58 0 1976M 1948M Sleep 3:34 0.18% Oracle
4002 Wacos 6 47 4 9616k 2344K Sleep 27:26 0.18% CDR_Backup
2271 Oracle 1 59 0 1975M 1947M Sleep 15:13 0.16% Oracle
1958 Oracle 1 48 0 1976M 1949M Sleep 2:26 0.13% Oracle
1928 Oracle 1 58 0 1976M 1951M SLEEP 4:28 0.12% Oracle
1926 Oracle 1 58 0 1976m 1949M Sleep 2:06 0.12% Oracle
1956 Oracle 1 58 0 1976M 1949M Sleep 2:23 0.11% Oracle
1952 Oracle 1 59 0 1976M 1949M Sleep 2:19 0.10% Oracle
403 root 10 21 0 4896k 4608k Sleep 16:32 0.09% PICLD1954 Oracle 1 48 0 1976M 1949M Sleep 2:04 0.08% Oracle
2189 Oracle 1 58 0 1976M 1949M Sleep 15:51 0.08% Oracle
3. In order to further analyze the performance of Oracle, use Oracle's own diagnostic tool StatsPack to perform performance snapshot analysis, statistical period is 1 hour, between 17: 00-18: 00 in the afternoon. During this time, the business is relatively busy, and chooses to perform performance analysis of the entire system in this manner and can get more accurate information.
Install a StatsPack Performance Analysis Tool:
SQL> Connect INTERNAL
SQL> ALTER system set timed_statistics = true; (collecting timing information for the operating system)
SQL> @? / Rdbms / admin / spcreate.sql
SQL> EXECUTE Statspack.snap (running once at 17:00)
SQL> EXECUTE Statspack.snap (running once at 18:00)
SQL> @? / Rdbms / admin / spreport (generating performance analysis report)
The part of the intercept report is as follows:
Statspack Report for
DB Name DB ID Instance Instal Release Ops Host
------------ ------------------------- ---- --------------
ORCL 1000277484 ORCL 1 8.1.7.3.0 NO BM_DB1
Snap ID Snap Time sessions
--------------------------------
Begin Snap: 1 08-Jun-04 17:00:15 116
END SNAP: 2 08-Jun-04 18:00:40 116
ELAPSED: 60.42 (MINS)
Cache Sizes
~~~~~~~~~~~
DB_BLOCK_BUFFERS: 180000 log_buffer:
8192000
DB_BLOCK_SIZE: 8192 Shared_pool_size:
314572800
Load Profile
~~~~~~~~~~~~ per second per transaction
-----------------------------
Redo Size: 11,005.01 2,280.39
Logical READS: 65, 704.21 13, 614.83
Block changes: 67.96 14.08
Physical Reads: 1,392.89 288.63
Physical Writes: 11.61 2.40User Calls: 172.63 35.77
Parses: 29.11 6.03
Hard Parses: 0.01 0.00
Sorts: 7.81 1.62
Logons: 0.14 0.03
Executes: 101.44 21.02
Transactions: 4.83
% Blocks Changed Per Read: 0.10 Recursive Call%: 41.29
Rollback Per Transaction%: 0.28 Rows PER SORT: 25.55
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer NOWAIT%: 100.00 Redo NOWAIT%: 100.00
Buffer Hit%: 97.88 in-Memory Sort%: 100.00
Library Hit%: 99.98 Soft Parse%: 99.96
Execute to Parse%: 71.30 Latch Hit%: 99.99
Parse CPU To Parse Elapsd%: 62.24% Non-Parse CPU: 99.99
Shared pool statistics begin end
------ ------
Memory USAGE%: 24.15 24.44
% SQL with Executions> 1: 75.04 76.95
% Memory for SQL W / EXEC> 1: 75.49 79.90
TOP 5 WAIT EVENTS
~~~~~~~~~~~~~~~~~ Wait% Total
Event Waits Time (CS) WT TIME
------------------------------------------------------------------------------------------ -----------------------
DB File Sequential Read 5,030,075 389,071 86.37
Log File Sync 17, 470 21, 187 4.70
Log File Parallel Write 17, 640 18,611 4.13
DB File Parallel Write 1,853 14,930 3.31db File Scattered Read 3,149 2,297.51
After the report analysis, some unreasonable initialization parameters need to be adjusted, and it is recommended to adjust the following:
1. In the report, the statement of full mete scans is particularly much, so it is recommended to avoid using full mete scans.
Reduce the IO waiting, speed the execution speed of the statement.
Similar to 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 a completely consecutive scan for one I / O operation. Its increase is improved to IO, especially when making Full Table Scan, can reduce the number of IOs.
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 to manage database buffers inside the Oracle database. It seriously depends on the number of CPUs on the server. This value is usually set to half of the CPU_count on the server, increase this value to improve the I / O performance of the disk. .
4. Adjust session_cached_cursors = 200
This parameter specifies the number of session cursors to cache, after multiple syntax analysis of the same SQL statement, its session cursor will be moved to the cursor cache of the session. Increasing this value can shorten syntax analysis, because the cursor is caching, no need to be reopened.
5. Adjust log_buffer = 1048576
Parameters log_buffer Specifies to cache the amount of memory for cache these entries before the LGWR writes the contents of the redo log buffer. This parameter is based on bytes, while being affected by CPU_Count, if the log_buffer is set too high (eg, greater than 1MB), this can cause performance issues, because the result of large capacity will make write synchronization (for example, log synchronization Waiting very high).
6. Adjust db_block_buffers = 200000 shared_pool_size = 262144000
According to Hangzhou's plan, Oracle finally runs nearly 1/2 physical memory. The two most important parameters are:
DB_BLOCK_BUFFERS: Its configuration principle is that the final data block cache occupies 1/3 of memory.
Shared_pool_size: Its configuration principle is that the basic control is around 200-500m.
7. From the report, the system is found to be the most serious. For: DB File Sequential Read, Log File Sync, Log File Parallel Write, DB File Parallel Write, and DB File Scattered Read. (1) Waiting for the DB File Sequential Read waiting event, general The problem appears on the read index, it is recommended to separate the WacOS tablespace and WacOS index table spaces in different physical volumes to improve the I / O performance of the disk.
(2) For the DB File Scattered Read Wait, it is recommended to avoid using the full-table scan, or can increase the value of DB_FILE_MULTIBLOCK_READ_COUNT, improve the speed of the full table to read the data block, reduce the disk I / O.
(3) For the DB File Parallel Write Waiting, the DBWR process is waiting to write the contents of the buffer in parallel to the data file, waiting for all I / O all completed. It is recommended to increase the value of DB_Writer_Processes in the initialization parameter, which can be increased to 4. (4) For the Log File Sync Waiting, when a thing is submitted, it will notify the LGWR to write log_buffer to log files. If this part takes up longer, it should reduce the number of commits, it is recommended to put the redo log. Store on a faster disk. (5) For the Log File Parallel Write Waiting, the reform log is put onto a faster disk to store.
Troubleshooting Adjustment 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
process result
After adjusting the restart DB, it is found that the query is normal and will return the result soon. Summary Database Initialization parameter setting is unreasonable, the memory is too small, causing a large amount of SWAP space using a large number of SWAP spaces, resulting in a query bill for querying by the OSS interface. This problem needs to be resolved by adjusting the database initialization parameters. Considering performance from performance, the database server is best to survive 300-500m or more.
