20180328 性能监控显示cqmes cpu使用率超过阈值：
critical: cpu usage per sec 4.54 exceed thresholds 2.14
查看top sql，sql_id: 51gg2h8q0t65r 执行次数和消耗cpu较多，优化索引解决。
1、查看sql执行频率和平均执行时间：
SQL> @sql_exec_profile.sql
指定时间段（单位小时）内，sql的执行情况 ,time_per_exec 单位：s
BEGIN_TIME END_TIME SQL_ID EXEC_D GETS_D TIME_D ROWS_D TIME_PER_EXEC GETS_PER_EXEC ROWS_PER_EXEC
------------ ------------ ------------- --------------- ----------- --------- ---------- ------------- ------------- -------------
2018032814 2018032816 51gg2h8q0t65r 12368 829734297 15740 12359 1.2 67087 1
2小时内执行12368次，平均执行1.2s，平均逻辑读 67087
2、查看sql执行计划
SQL> select * from table(xplan.display_cursor('51gg2h8q0t65r',0,'ADVANCED'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID 51gg2h8q0t65r, child number 0
-------------------------------------
SELECT COUNT(1) FROM ( SELECT BILLSTYPE FROM ( SELECT E.BILLSTYPE FROM
TBLWHOUTSTORAGE_DETAIL E WHERE E.BARCODE = (SELECT IMEI FROM (SELECT
A.IMEI FROM TBLPRODUCTINFO A WHERE (A.PCB = :B1 OR A.RCARD = :B1 OR
A.IMEI = :B1 OR A.IMEI2 = :B1 ) ORDER BY A.MDATE * 1000000 + A.MTIME
DESC) WHERE ROWNUM = 1) ORDER BY E.MDATE * 1000000 + E.MTIME DESC )
WHERE ROWNUM = 1 ) WHERE BILLSTYPE NOT IN (7, 8,10,11,23)
Plan hash value: 3300554844
----------------------------------------------------------------------------------------------------------------------------
| Id | Order | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------
| 0 | 20 | SELECT STATEMENT | | | | 100K(100)| |
| 1 | 19 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | 18 | VIEW | | 1 | 13 | 100K (1)| 00:20:09 |
|* 3 | 17 | COUNT STOPKEY | | | | | |
| 4 | 16 | VIEW | | 1 | 13 | 100K (1)| 00:20:09 |
|* 5 | 15 | SORT ORDER BY STOPKEY | | 1 | 30 | 100K (1)| 00:20:09 |
| 6 | 14 | TABLE ACCESS BY INDEX ROWID | TBLWHOUTSTORAGE_DETAIL | 1 | 30 | 100K (1)| 00:20:09 |
|* 7 | 13 | INDEX SKIP SCAN | PK_TBLWHOUTSTORAGE_DETAIL | 1 | | 100K (1)| 00:20:09 |
|* 8 | 12 | COUNT STOPKEY | | | | | |
| 9 | 11 | VIEW | | 4 | 168 | 22 (5)| 00:00:01 |
|* 10 | 10 | SORT ORDER BY STOPKEY | | 4 | 268 | 22 (5)| 00:00:01 |
| 11 | 9 | CONCATENATION | | | | | |
| 12 | 2 | TABLE ACCESS BY INDEX ROWID| TBLPRODUCTINFO | 1 | 67 | 5 (0)| 00:00:01 |
|* 13 | 1 | INDEX SKIP SCAN | PK_TBLPRODUCTINFO | 1 | | 4 (0)| 00:00:01 |
|* 14 | 4 | TABLE ACCESS BY INDEX ROWID| TBLPRODUCTINFO | 1 | 67 | 6 (0)| 00:00:01 |
|* 15 | 3 | INDEX SKIP SCAN | IND_TBLPRODUCTINFO_6 | 1 | | 5 (0)| 00:00:01 |
|* 16 | 6 | TABLE ACCESS BY INDEX ROWID| TBLPRODUCTINFO | 1 | 67 | 4 (0)| 00:00:01 |
|* 17 | 5 | INDEX RANGE SCAN | IND_TBLPRODUCTINFO_7 | 1 | | 3 (0)| 00:00:01 |
|* 18 | 8 | TABLE ACCESS BY INDEX ROWID| TBLPRODUCTINFO | 1 | 67 | 6 (0)| 00:00:01 |
|* 19 | 7 | INDEX SKIP SCAN | IND_TBLPRODUCTINFO_5 | 1 | | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------
3、获取sql_text，手动执行，收集执行计划，发现执行时间和逻辑读主要消耗在对 BARCODE 列的索引扫描 PK_TBLWHOUTSTORAGE_DETAIL,扫描方式为 INDEX SKIP SCAN
SELECT /*+ gather_plan_statistics */ COUNT(1)
FROM (
SELECT BILLSTYPE
FROM (
SELECT E.BILLSTYPE
FROM mes.TBLWHOUTSTORAGE_DETAIL E
WHERE E.BARCODE =
(
SELECT IMEI
FROM (
SELECT A.IMEI
FROM mes.TBLPRODUCTINFO A
WHERE
(
A.PCB = '2613858194'
OR A.RCARD = 'C165K0000081'
OR A.IMEI = '860871033995492'
OR A.IMEI2 = '860871033995484'
)
ORDER BY A.MDATE * 1000000 + A.MTIME DESC
)
WHERE ROWNUM = 1
)
ORDER BY E.MDATE * 1000000 + E.MTIME DESC
)
WHERE ROWNUM = 1
)
WHERE BILLSTYPE NOT IN (7, 8, 10, 11, 23);
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALL IOSTATS LAST'));
Plan hash value: 488009633
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.13 | 67203 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.13 | 67203 | | | |
|* 2 | VIEW | | 1 | 1 | 1 |00:00:01.13 | 67203 | | | |
|* 3 | COUNT STOPKEY | | 1 | | 1 |00:00:01.13 | 67203 | | | |
| 4 | VIEW | | 1 | 1 | 1 |00:00:01.13 | 67203 | | | |
|* 5 | SORT ORDER BY STOPKEY | | 1 | 1 | 1 |00:00:01.13 | 67203 | 2048 | 2048 | 1/0/0|
| 6 | TABLE ACCESS BY INDEX ROWID | TBLWHOUTSTORAGE_DETAIL | 1 | 1 | 1 |00:00:01.13 | 67203 | | | |
|* 7 | INDEX SKIP SCAN | PK_TBLWHOUTSTORAGE_DETAIL | 1 | 1 | 1 |00:00:01.13 | 67202 | | | |
|* 8 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 30 | | | |
| 9 | VIEW | | 1 | 4 | 1 |00:00:00.01 | 30 | | | |
|* 10 | SORT ORDER BY STOPKEY | | 1 | 4 | 1 |00:00:00.01 | 30 | 2048 | 2048 | 1/0/0|
| 11 | CONCATENATION | | 1 | | 1 |00:00:00.01 | 30 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID| TBLPRODUCTINFO | 1 | 1 | 1 |00:00:00.01 | 8 | | | |
|* 13 | INDEX SKIP SCAN | PK_TBLPRODUCTINFO | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID| TBLPRODUCTINFO | 1 | 1 | 0 |00:00:00.01 | 4 | | | |
|* 15 | INDEX RANGE SCAN | IND_TBLPRODUCTINFO_7 | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
|* 16 | TABLE ACCESS BY INDEX ROWID| TBLPRODUCTINFO | 1 | 1 | 0 |00:00:00.01 | 9 | | | |
|* 17 | INDEX SKIP SCAN | IND_TBLPRODUCTINFO_6 | 1 | 1 | 1 |00:00:00.01 | 8 | | | |
|* 18 | TABLE ACCESS BY INDEX ROWID| TBLPRODUCTINFO | 1 | 1 | 0 |00:00:00.01 | 9 | | | |
|* 19 | INDEX SKIP SCAN | IND_TBLPRODUCTINFO_5 | 1 | 1 | 1 |00:00:00.01 | 8 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
4、查看表TBLWHOUTSTORAGE_DETAIL的相关情况，发现组合索引的第一列BATCHID数据分布不均衡，导致index range scan时逻辑读比较高
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN PAR ANALYZED EST_M
---------------- ------------------------ ---------- ---------- ----------- --- ---------------- ----------
MES TBLWHOUTSTORAGE_DETAIL 18990951 458688 152 NO 20180314 22:01 3058.77744
—索引列
INDEX_OWNER INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------ ------------------------------------ ------------------------ ---------------
MES PK_TBLWHOUTSTORAGE_DETAIL BATCHID 1
MES PK_TBLWHOUTSTORAGE_DETAIL BARCODE 2
—索引列数据分布
OWNER COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_DISTINCT NUM_NULLS ANALYZED
---------------- ------------------------ ------------ --------------- ------------ ---------- ------------------
MES BARCODE 18550784 NONE 18550784 0 20180314 22:01
MES BATCHID 802240 HEIGHT BALANCED 802240 0 20180314 22:01
5、针对BARCODE列新建单列索引
SQL> create index mes.idx_TBLWHOUT_barcode on mes.TBLWHOUTSTORAGE_DETAIL(barcode) parallel 4 nologging tablespace MES_MOVE;
Index created.
SQL> alter index mes.idx_TBLWHOUT_barcode noparallel logging;
Index altered.
6、重新执行sql，发现执行时间降为 0.01s，逻辑读降为34 ，优化完成。
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | O/1/M |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 34 | 2 | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 34 | 2 | | |
|* 2 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 34 | 2 | | |
|* 3 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 34 | 2 | | |
| 4 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 34 | 2 | | |
|* 5 | SORT ORDER BY STOPKEY | | 1 | 1 | 1 |00:00:00.01 | 34 | 2 | 2048 | 2048 | 1/0/0|
| 6 | TABLE ACCESS BY INDEX ROWID | TBLWHOUTSTORAGE_DETAIL | 1 | 1 | 1 |00:00:00.01 | 34 | 2 | | |
|* 7 | INDEX RANGE SCAN | IDX_TBLWHOUT_BARCODE | 1 | 1 | 1 |00:00:00.01 | 33 | 2 | | |
|* 8 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 30 | 0 | | |
| 9 | VIEW | | 1 | 4 | 1 |00:00:00.01 | 30 | 0 | | |
|* 10 | SORT ORDER BY STOPKEY | | 1 | 4 | 1 |00:00:00.01 | 30 | 0 | 2048 | 2048 | 1/0/0|
| 11 | CONCATENATION | | 1 | | 1 |00:00:00.01 | 30 | 0 | | |
| 12 | TABLE ACCESS BY INDEX ROWID| TBLPRODUCTINFO | 1 | 1 | 1 |00:00:00.01 | 8 | 0 | | |
|* 13 | INDEX SKIP SCAN | PK_TBLPRODUCTINFO | 1 | 1 | 1 |00:00:00.01 | 7 | 0 | | |
|* 14 | TABLE ACCESS BY INDEX ROWID| TBLPRODUCTINFO | 1 | 1 | 0 |00:00:00.01 | 4 | 0 | | |
|* 15 | INDEX RANGE SCAN | IND_TBLPRODUCTINFO_7 | 1 | 1 | 1 |00:00:00.01 | 3 | 0 | | |
|* 16 | TABLE ACCESS BY INDEX ROWID| TBLPRODUCTINFO | 1 | 1 | 0 |00:00:00.01 | 9 | 0 | | |
|* 17 | INDEX SKIP SCAN | IND_TBLPRODUCTINFO_6 | 1 | 1 | 1 |00:00:00.01 | 8 | 0 | | |
|* 18 | TABLE ACCESS BY INDEX ROWID| TBLPRODUCTINFO | 1 | 1 | 0 |00:00:00.01 | 9 | 0 | | |
|* 19 | INDEX SKIP SCAN | IND_TBLPRODUCTINFO_5 | 1 | 1 | 1 |00:00:00.01 | 8 | 0 | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
35
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
