Problem Description: The engineer of Binzhou Netcom in the morning of October 25th reported that the OSS application system was running slowly. The specific operation was through the OSS system to check the order. For a long time, it can return the results, which seriously affects the normal use of customers.
Problem processing: 1. Log in to the database host, use the SAR command to see the value of idle is 0, the resource of the CPU has been exhausted: bz_db1 # sar 2 4 Sunos Kest 5.8 generic_108528-19 Sun4u 10/26/04 10:56:46 % USR% SYS% WIO% IDLE10: 56: 48 1 4 95 010: 56: 50 1 5 94 010: 56: 52 0 6 93 010: 56: 54 1 6 93 0 Average 1 5 94 0 2. Using TOP commands Seeing having two obvious CPU utilization, the following is the result of the TOP command: bz_db1 # TOP Last PID: 1664;
Load Averages: 3.26, 3.24, 3.69 159 Processes: 152 Sleeping, 2 Running, 2 Zombie, 1 Stopped, 2 On CPucpu States: 1.5% iDle, 72.5% User, 17.9% Kernel, 8.0% iowait, 0.0% SwapMemory: 2.0g Real, 233M Free, 2.0G SWAP in Use, 3.4G Swap Free Pid Username Thr Pr NCE Size Res State Time FLTS CPU Command 27420 Oracle 1 10 0 1.3G 1.2G CPU01 22.9H 2 31.94% Oracle 27418 Oracle 1 10 0 1.3G 1.2G Run 23.0H 6 26.86% Oracle 5943 Oracle 1 59 0 1.3G 1.2G Sleep 25:26 37 4.92% Oracle 6295 Oracle 1 55 0 1.3G 1.2G Run 25:14 74 4.90% Oracle 7778 Oracle 1 43 0 1.3G 1.2G Sleep 11:43 110 4.86% Oracle 13270 Oracle 1 59 0 1.3G 1.2G Sleep 210.6H 0 0.96% Oracle 13056 Oracle 1 48 0 1.3G 1.2G Sleep 303: 30 0 0.37% Oracle 10653 Root 1 58 0 2560K 1624K CPU00 0:00 0 0.32% TOP 18827 Oracle 1 58 0 1.3G 1.2G Sleep 18.4H 0 0.31% Oracle 12748 Oracle 258 58 0 1.3G 1.2G Sleep 555: 14 0 0.21% Oracle 10634 Oracle 1 59 0 1.3G 1.2G Sleep 0:01 0 0.21% Oracle 28458 Oracle 1 58 0 1.3G 1.2G Sleep 535: 02 0 0.18% Oracle 13075 Oracle 1 59 0 1.3G 1.2 G Sleep 326: 33 0 0.15% Oracle 13173 Oracle 1 58 0 1.3G 1.2G Sleep 593: 07 0 0.13% Oracle 4927 Oracle 1 59 0 1.3G 1.2G Sleep 33.4H 0 0.11% Oracle can see these two processes They are 27420 and 27418. 3. Capture the SQL statement that occupies the CPU utilization: SQL statement I summarized: SQL> Set line 240sql> set verify offsql> Column Sid Format 999SQL> Column Pid Format 999 SQL> Column S_ # format 999sql> Column UserName Format A9 Heading "ORA User" SQL> Column Program Format A29SQL> Column SQL Format A60SQL> Column OsName Format A9 Heading "
OS User "SQL> SELECT P.PID PID, S.SID SID, P.SPID SPID, S.Username Username, S. OSUSER OSNAME, P.SERIAL # S _ #, P.Terminal, P.Program Program, P.Background , S.Status, RTRIM (Substr (A.SQL_Text, 1, 80)) SQLFROM V $ Process P, V $ Session S, V $ SQLAREA A where p.addr = s.paddrand s.sql_address = a.address ( ) And P.SPID LIKE '% & 1%; Enter Value for 1: 27420 (note that the PID corresponding to the process of the highest CPU should be entered) Get the following SQL statement: SELECT NVL (SUM (Localcharge), 0), NVL ( sum (usage), 0) from LOCALUSAGE where to_char (ENDTIME, 'YYYYMMDD') = 20041016and LOCALCHARGE> 0 and caller like '0543886%'; 27418 process corresponding to the SQL statement is as follows: select nvl (sum (LOCALCHARGE), 0) from Localusage where to_char (endtime, 'yyyyymmmdd') = 20041016 and Caller Like '0543888%'; 4. Use the related users to connect to the database, check their execution plan: SQL> Connect Wacos / OsSconnected. Sql> @? / Rdbms / admin / utlxplan.sqlTable created. SQL> set autotrace on SQL> set timing on SQL> Select nvl (sum (LOCALCHARGE), 0), nvl (sum (usage), 0) from LOCALUSAGE where to_char (ENDTIME, 'YYYYMMDD') = 20041016 AND localcharge> 0 and Caller Like '0543886%'; NVL (SUM (Localcharge), 0) NVL (SUM (Usage), 0) --------------------------------------- 0 0 Elapsed: 00:02: 56.37 Execution Plan ----------------------------------------------- ----------- 0 Select Statement Optimizer = Choose (COST = 13435 Card = 1 Bytes = 5 3) 1 0 Sort (aggregate) 2 1 Partition Range (ALL) 3 2 Table Access (Full) OF 'Localusage' (COST = 13435 card = 1 81 bytes =
9593) Statistics --------------------------------- ----------- 258 recursive calls 0 db block gets 88739 consistent gets 15705 physical reads 0 redo size 580 bytes sent via SQL * Net to client 651 bytes received via SQL * Net from client 2 SQL * Net roundtrips TO / FROM Client 8 Sorts (Memory) 0 Sorts (Disk) 1 Rows Processed found a full-table scan for the localusage table, and no return actually used 2 minutes. SQL> SELECT NVL (SUM (Localcharge), 0) from localusage where to_char (endtime, 'yyyyymmdd') = 20040816 and Caller Like '0543888%; NVL (SUM (Localcharge), 0) -------- --------------- 27.6 ELAPSED: 00: 03: 56.46 Execution Plan ------------------------- --------------------------------- 0 Select Statement Optimizer = choose (cost = 13435 card = 1 bytes = 4 0 1 0 Sort (aggregate) 2 1 Partition Range (all) 3 2 Table Access (Full) of 'localusage' (COST = 13435 card = 3 615 bytes = 144600) statistics ------------ ---------------------------------------------- 0 Recursive Calls 0 db block gets 88588 consistent gets 15615 physical reads 0 redo size 507 bytes sent via SQL * Net to client 651 bytes received via SQL * Net from client 2 SQL * Net roundtrips to / from client 0 sorts (memory) 0 sorts (disk) 1 Rows Processed This SQL statement has a result returned, and it is also a full mete-scan for the localusage table, but the speed is also very slow, 3 minutes more . SQL> SELECT Count (*) from localusage; count (*) ---------- 5793776 This table has more than 5.79 million records, the amount of data is large, and the full table scan is no longer suitable.
5. Check the type of the table: SQL> SELECT INDEX_NAME, TABLE_NAME, STATUS, Partitioned from user_indexes where table_name = 'localusage'; index_name table_name status par ------------------- ------------------------------------------------ --I_LOCALUSAGE_SID LOCALUSAGE N / A YESUI_LOCALUSAGE_ST_SEQ LOCALUSAGE N / A YESSQL> SELECT index_name, table_name, locality FROM user_part_indexes where table_name = 'LOCALUSAGE'; iNDEX_NAME tABLE_NAME LOCALI ------------------ ------------ ---------------------------------- I_LOCALUSAGE_SID LOCALUSAGE LocalUI_localusage_st_seq localusage local discovers that the table is a partition table and established a partition index on the ServiceID, Startime, and CDRSEquence columns. The index type is the local index. 6. Check the index key partition index: SQL> select INDEX_NAME, COLUMN_NAME, INDEX_OWNER from dba_ind_columns where TABLE_NAME = 'LOCALUSAGE'; INDEX_NAME COLUMN_NAME INDEX_OWNER -------------------- ------------------------------------------------------------------------------- I_localusage_sid serviceid wacosui_localusage_st_seq starttime wacosui_localusage_st_seq CDRSEQUENCE WAS found that there is no index on the EndTime and Caller columns, which is also the final cause of the SQL statement to make a full table scan.
7. decided to create a new partition index to eliminate full table scan: (1) First check localusage table partitioning:. SQL> select PARTITION_NAME, tablespace_name from user_tab_partitions where table_name = 'LOCALUSAGE'; PARTITION_NAME TABLESPACE_NAME -------- -------------------------------------------------------------------------------------------------------------------------------------------------------- .. --LOCALUSAGE_200312 WACOSLOCALUSAGE_200401 WACOSLOCALUSAGE_200402 WACOSLOCALUSAGE_200404 WACOSLOCALUSAGE_200405 WACOSLOCALUSAGE_200406 WACOSLOCALUSAGE_200407 WACOSLOCALUSAGE_200409 WACOSLOCALUSAGE_200410 WACOSLOCALUSAGE_200411 WACOSLOCALUSAGE_200403 WACOSLOCALUSAGE_200408 WACOSLOCALUSAGE_200412 WACOS 13 rows selected (2) create local partitioned indexes on the caller column: SQL> set timing onSQL> create index I_LOCALUSAGE_CALLER on localusage (caller) lOCAL (pARTITION Localusage_200312, Partition Localusage_200401, Partition Localusage_200404, Partition Localusage_200 405, PARTITION LOCALUSAGE_200406, PARTITION LOCALUSAGE_200407, PARTITION LOCALUSAGE_200409, PARTITION LOCALUSAGE_200410, PARTITION LOCALUSAGE_200411, PARTITION LOCALUSAGE_200403, PARTITION LOCALUSAGE_200408, PARTITION LOCALUSAGE_200412) TABLESPACE wacosSTORAGE (INITIAL 6553600 NEXT 6553600 MAXEXTENTS unlimited PCTINCREASE 0) PCTFREE 5 NOLOGGING; Index created Elapsed:. 00:06 : 27.90 (Due to the data volume, 6 minutes) 8. Review the execution plan again: SQL> SELECT NVL (SUM (Localcharge), 0), NVL (SUM (USAGE), 0) from localusage where to_char (endtime, 'YYYYMMDD') = 20041016and Localcharge> 0 and Caller Like '0543886%';
NVL (SUM (Localcharge), 0) NVL (SUM (Usage), 0) ------------------------------- -------- 0 0 elapsed: 00: 00: 03.00 Execution Plan ------------------------------- --------------------------- 0 Select Statement Optimizer = choose (cost = 22 card = 1 bytes = 53) 1 0 sort (aggregate) 2 1 Partition Range (All) 3 2 Table Access (By Local Index Rowid) of 'Localusage' (COST = 22 Card = 181 BYtes = 9593) 4 3 Index (Range Scan) of 'i_localusage_caller' (Non-Unique) (COST = 14 card = 65063) statistics ------------------------------------------------------------------------------------------------------------------------------------ --------------- 0 Recursive Calls 0 DB Block Gets 16813 Consistent Gets 569 Physical Reads 0 Redo Size 580 Bytes Sent Via Sql * Net To Client 651 BYtes Received Via SQL * Net from Clom Cliant 2 SQL * NET ROUNDTRIPS TO / FROM Client 0 Sorts (Memory) 0 Sorts (Disk) 1 Rows Processed This time, the speed is significantly higher, and the result is returned for 3 seconds.
