异常sql处理
时间：2021-07-01 10:21:17
帮助过：14人阅读
/*+ GATHER_PLAN_STATISTICS */ *FROM SAMS_CHECKINOUT scINNER JOIN ( SELECT badgenumber,NAME,deptid FROM SAMS_USERINFO UNION SELECT badgenumber ,NAME ,deptid FROM SAMS_USERINFO_DIMISSION sd WHERE 1 = 1 AND sd.deptid IN ( SELECT Deptid FROM SAMS_DEPARTMENTS T2 start WITH T2.Deptid = ‘360710‘ connect BY prior T2.DEPTID = T2.SUPDEPTID ) ) su ON su.badgenumber = sc.badgenumberINNER JOIN SAMS_DEPARTMENTS sd ON sd.deptid = su.deptidLEFT JOIN SAMS_ICLOCK sl ON sl.sn = sc.snWHERE 1 = 1 AND sc.checktime >= to_date(‘2017-03-01‘, ‘yyyy-MM-dd‘) AND sc.checktime <= to_date(‘2017-03-22‘, ‘yyyy-MM-dd‘) + 1 AND sd.deptid IN ( SELECT Deptid FROM SAMS_DEPARTMENTS T2 start WITH T2.Deptid = ‘360710‘ connect BY prior T2.DEPTID = T2.SUPDEPTID ) AND ( su.badgenumber = ‘36071000000600‘ OR su.NAME LIKE ‘%36071000000600%‘ OR sl.sn = ‘36071000000600‘ )ORDER BY sc.checktime,su.NAME DESC;/*+ GATHER_PLAN_STATISTICS */ SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ‘IOSTATS‘));-------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |-------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | | 16 |00:00:22.70 | 18614 | 2897 || 1 | SORT ORDER BY | | 2 | 40 | 16 |00:00:22.70 | 18614 | 2897 ||* 2 | FILTER | | 2 | | 16 |00:00:22.67 | 18614 | 2897 ||* 3 | HASH JOIN OUTER | | 2 | 40 | 10826 |00:00:22.53 | 18614 | 2897 || 4 | NESTED LOOPS | | 2 | 802 | 10826 |00:00:17.98 | 15790 | 2897 || 5 | NESTED LOOPS | | 2 | 1472 | 11598 |00:00:00.08 | 4216 | 45 || 6 | NESTED LOOPS | | 2 | 92 | 2304 |00:00:00.04 | 666 | 8 || 7 | NESTED LOOPS | | 2 | 3 | 38 |00:00:00.01 | 84 | 0 || 8 | VIEW | VW_NSO_2 | 2 | 3 | 38 |00:00:00.01 | 28 | 0 || 9 | HASH UNIQUE | | 2 | 3 | 38 |00:00:00.01 | 28 | 0 ||* 10 | CONNECT BY WITH FILTERING (UNIQUE) | | 2 | | 38 |00:00:00.01 | 28 | 0 || 11 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 2 | 1 | 2 |00:00:00.01 | 6 | 0 ||* 12 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 2 | 1 | 2 |00:00:00.01 | 4 | 0 || 13 | NESTED LOOPS | | 6 | 2 | 36 |00:00:00.01 | 22 | 0 || 14 | CONNECT BY PUMP | | 6 | | 38 |00:00:00.01 | 0 | 0 || 15 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 38 | 2 | 36 |00:00:00.01 | 22 | 0 ||* 16 | INDEX RANGE SCAN | SUPDEPTID_IDX | 38 | 2 | 36 |00:00:00.01 | 18 | 0 || 17 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 38 | 1 | 38 |00:00:00.01 | 56 | 0 ||* 18 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 38 | 1 | 38 |00:00:00.01 | 18 | 0 || 19 | VIEW | | 38 | 31 | 2304 |00:00:00.11 | 582 | 8 || 20 | SORT UNIQUE | | 38 | | 2304 |00:00:00.11 | 582 | 8 || 21 | UNION-ALL PARTITION | | 38 | | 2304 |00:00:00.02 | 582 | 8 ||* 22 | INDEX RANGE SCAN | IDX1_SAMS_USERINFO | 38 | 68 | 1368 |00:00:00.01 | 168 | 0 ||* 23 | HASH JOIN | | 38 | 3 | 936 |00:00:00.07 | 414 | 8 ||* 24 | VIEW | VW_NSO_1 | 38 | 3 | 38 |00:00:00.01 | 288 | 0 || 25 | SORT UNIQUE | | 38 | 3 | 722 |00:00:00.01 | 288 | 0 ||* 26 | CONNECT BY WITH FILTERING (UNIQUE)| | 38 | | 722 |00:00:00.01 | 288 | 0 || 27 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 38 | 1 | 38 |00:00:00.01 | 86 | 0 ||* 28 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 38 | 1 | 38 |00:00:00.01 | 48 | 0 || 29 | NESTED LOOPS | | 114 | 2 | 684 |00:00:00.01 | 202 | 0 || 30 | CONNECT BY PUMP | | 114 | | 722 |00:00:00.01 | 0 | 0 || 31 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 722 | 2 | 684 |00:00:00.01 | 202 | 0 ||* 32 | INDEX RANGE SCAN | SUPDEPTID_IDX | 722 | 2 | 684 |00:00:00.01 | 126 | 0 ||* 33 | INDEX RANGE SCAN | IDX_SAMS_USERINFO_DIMISSION | 38 | 44 | 936 |00:00:00.06 | 126 | 8 ||* 34 | INDEX RANGE SCAN | IDX_SAMS_CHECKINOUT | 2304 | 16 | 11598 |00:00:00.27 | 3550 | 37 ||* 35 | TABLE ACCESS BY GLOBAL INDEX ROWID | SAMS_CHECKINOUT | 11598 | 9 | 10826 |00:00:22.08 | 11574 | 2852 || 36 | TABLE ACCESS FULL | SAMS_ICLOCK | 2 | 6306 | 12624 |00:00:00.08 | 2824 | 0 |-------------------------------------------------------------------------------------------------------------------------------------------------下面是查询到的绑定变量值，可以通过查看v$sql_bind_capture视图来查看变量的实际值，如果时间比较久，可以使用如下的语句查看历史的绑定变量信息
- :1 360710 VARCHAR2(32) 23-MAR-17
- :2 2017-03-01 VARCHAR2(32) 23-MAR-17
- :3 2017-03-23 VARCHAR2(32) 23-MAR-17
- :4 360710 VARCHAR2(32) 23-MAR-17
- :5 36071000000600 VARCHAR2(32) 23-MAR-17
- :6 %36071000000600% VARCHAR2(32) 23-MAR-17
- :7 36071000000600 VARCHAR2(32) 23-MAR-17
- :8 10 NUMBER 23-MAR-17
- :9 0 NUMBER 23-MAR-17
- select NAME,VALUE_STRING,DATATYPE_STRING,LAST_CAPTURED from dba_hist_sqlbind where sql_id=‘99vaabs5ptktb‘ and LAST_CAPTURED between
- to_date(‘2017-03-23 09:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘) and to_date(‘2017-03-23 12:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘);
以下是开启了autotrace 选项跟踪的手工执行情况，从执行效率上看是没有问题的。
- ---------------------------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
- ---------------------------------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 40 | 24280 | 1315 (1)| 00:00:16 | | |
- | 1 | SORT ORDER BY | | 40 | 24280 | 1315 (1)| 00:00:16 | | |
- |* 2 | FILTER | | | | | | | |
- |* 3 | HASH JOIN OUTER | | 40 | 24280 | 1314 (1)| 00:00:16 | | |
- | 4 | NESTED LOOPS | | 802 | 263K| 1231 (1)| 00:00:15 | | |
- | 5 | NESTED LOOPS | | 1472 | 263K| 1231 (1)| 00:00:15 | | |
- | 6 | NESTED LOOPS | | 92 | 18860 | 104 (1)| 00:00:02 | | |
- | 7 | NESTED LOOPS | | 3 | 432 | 10 (10)| 00:00:01 | | |
- | 8 | VIEW | VW_NSO_2 | 3 | 66 | 7 (15)| 00:00:01 | | |
- | 9 | HASH UNIQUE | | 3 | 192 | 7 (15)| 00:00:01 | | |
- |* 10 | CONNECT BY WITH FILTERING (UNIQUE) | | | | | | | |
- | 11 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 38 | 2
