今日上午收到告警:网元名称xxxxxx 告警标题performance 定位信息10xxx8x_数据库核心_xx系统_【严重】实例名:xx等待事件:direct path write temp,平均每秒个数:182.6,数据库存在较多异常等待,请核实处理! 告警时间2017-02-21 10:13:00
执行计划:
SQL> select * from table(dbms_xplan.display_cursor('fwb1a3u55hazz',null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fwb1a3u55hazz, child number 3
-------------------------------------
WHERE ICE_PTN EYS_B_0") FROM
AND C.ENTITYTYPE_ID=:"SYS_B_1"
INSTR(NAME ,:1 )>:"SYS_B_2" and ENTITYTYPE_ID = :2 and
SPECIALTYTYPE_ID = :3 and (ROOM_ID > :"SYS_B_3" OR STRONGHOLD_ID >
:"SYS_B_4") and PREFECTURE_ID in ( :4 )
Plan hash value: 1248788790
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT AGGREGATE | | 1 | | | |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS | | 125 | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | C_DEVICE | 125 | | | |
| 5 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 6 | BITMAP AND | | | | | |
| 7 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 8 | INDEX RANGE SCAN | IDX_C_DEVICE_ESPID | 158K| | | |
| 9 | BITMAP OR | | | | | |
| 10 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 11 | SORT ORDER BY | | | 193M| 4661K| 171M (0)|
|* 12 | INDEX RANGE SCAN | IDX_DEVICE_STRONGHOLD_I | 158K| | | |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 14 | SORT ORDER BY | | | 73M| 2961K| 65M (0)|
|* 15 | INDEX RANGE SCAN | IDX_DEVICE_ROOM_ID | 158K| | | |
|* 16 | INDEX UNIQUE SCAN | PK_EDEVICEPTN | 1 | | | |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:2=:SYS_B_1)
4 - filter(INSTR("NAME",:1)>:SYS_B_2)
8 - access("C"."ENTITYTYPE_ID"=:SYS_B_1 AND "SPECIALTYTYPE_ID"=:3 AND "PREFECTURE_ID"=:4)
12 - access("STRONGHOLD_ID">:SYS_B_4)
filter("STRONGHOLD_ID">:SYS_B_4)
15 - access("ROOM_ID">:SYS_B_3)
filter("ROOM_ID">:SYS_B_3)
16 - access("C"."ID"="E"."EXTENSIONID")
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level