下面是在awr报告里面看到的有问题的sql，是9个变量的，在应用前台属于关联查询，在sqlplus里面手工执行检查实际执行情况如下：
- SELECT /*+ GATHER_PLAN_STATISTICS */ *
- FROM SAMS_CHECKINOUT sc
- INNER 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.badgenumber
- INNER JOIN SAMS_DEPARTMENTS sd ON sd.deptid = su.deptid
- LEFT JOIN SAMS_ICLOCK sl ON sl.sn = sc.sn
- WHERE 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 (0)| 00:00:01 | | |
- |* 12 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | 1 (0)| 00:00:01 | | |
- | 13 | NESTED LOOPS | | 2 | 160 | 4 (0)| 00:00:01 | | |
- | 14 | CONNECT BY PUMP | | | | | | | |
- | 15 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 2 | 76 | 2 (0)| 00:00:01 | | |
- |* 16 | INDEX RANGE SCAN | SUPDEPTID_IDX | 2 | | 1 (0)| 00:00:01 | | |
- | 17 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 122 | 1 (0)| 00:00:01 | | |
- |* 18 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | 0 (0)| 00:00:01 | | |
- | 19 | VIEW | | 31 | 1891 | 60 (0)| 00:00:01 | | |
- | 20 | UNION-ALL PARTITION | | | | | | | |
- |* 21 | INDEX RANGE SCAN | IDX1_SAMS_USERINFO | 68 | 4148 | 4 (0)| 00:00:01 | | |
- |* 22 | HASH JOIN | | 3 | 309 | 10 (10)| 00:00:01 | | |
- |* 23 | VIEW | VW_NSO_1 | 3 | 126 | 7 (15)| 00:00:01 | | |
- | 24 | SORT UNIQUE | | 3 | 192 | 7 (15)| 00:00:01 | | |
- |* 25 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | | | |
- | 26 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 38 | 2 (0)| 00:00:01 | | |
- |* 27 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | 1 (0)| 00:00:01 | | |
- | 28 | NESTED LOOPS | | 2 | 160 | 4 (0)| 00:00:01 | | |
- | 29 | CONNECT BY PUMP | | | | | | | |
- | 30 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 2 | 76 | 2 (0)| 00:00:01 | | |
- |* 31 | INDEX RANGE SCAN | SUPDEPTID_IDX | 2 | | 1 (0)| 00:00:01 | | |
- |* 32 | INDEX RANGE SCAN | IDX_SAMS_USERINFO_DIMISSION | 44 | 2684 | 3 (0)| 00:00:01 | | |
- |* 33 | INDEX RANGE SCAN | IDX_SAMS_CHECKINOUT | 16 | | 3 (0)| 00:00:01 | | |
- |* 34 | TABLE ACCESS BY GLOBAL INDEX ROWID | SAMS_CHECKINOUT | 9 | 1179 | 19 (0)| 00:00:01 | ROWID | ROWID |
- | 35 | TABLE ACCESS FULL | SAMS_ICLOCK | 6306 | 1668K| 83 (0)| 00:00:02 | | |
- ---------------------------------------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("SU"."BADGENUMBER"=U'36071000000600' OR "SU"."NAME" LIKE U'%36071000000600%' AND "SU"."NAME" IS NOT NULL AND
- "SU"."NAME" IS NOT NULL OR "SL"."SN"=U'36071000000600')
- 3 - access("SL"."SN"(+)="SC"."SN")
- 10 - access("T2"."SUPDEPTID"=PRIOR "T2"."DEPTID")
- 12 - access("T2"."DEPTID"=U'360710')
- 16 - access("connect$_by$_pump$_017"."prior T2.DEPTID "="T2"."SUPDEPTID")
- 18 - access("SD"."DEPTID"="DEPTID")
- 21 - access("DEPTID"="SD"."DEPTID")
- 22 - access("SD"."DEPTID"="DEPTID")
- 23 - filter("DEPTID"="SD"."DEPTID")
- 25 - access("T2"."SUPDEPTID"=PRIOR "T2"."DEPTID")
- 27 - access("T2"."DEPTID"=U'360710')
- 31 - access("connect$_by$_pump$_006"."prior T2.DEPTID "="T2"."SUPDEPTID")
- 32 - access("DEPTID"="SD"."DEPTID")
- 33 - access("SU"."BADGENUMBER"="SC"."BADGENUMBER")
- 34 - filter("SC"."CHECKTIME">=TIMESTAMP' 2017-03-01 00:00:00' AND "SC"."CHECKTIME"<=TIMESTAMP' 2017-03-24 00:00:00')
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 8938 consistent gets
- 2467 physical reads
- 0 redo size
- 7901 bytes sent via SQL*Net to client
- 520 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 120 sorts (memory)
- 0 sorts (disk)
- 8 rows processed
从执行计划和表的数据量等方面判断如果sql的开销有问题，应该出现在表SAMS_CHECKINOUT上面，下面检查该表上面索引的创建语句看是否有问题
- CREATE INDEX "SAMS"."IDX_SAMS_CHECKINOUT" ON "SAMS"."SAMS_CHECKINOUT" ("BADGENUMBER")
- CREATE INDEX "SAMS"."IDX1_SAMS_CHECKINOUT" ON "SAMS"."SAMS_CHECKINOUT" ("CHECKTIME", "BADGENUMBER", "ID")
- CREATE INDEX "SAMS"."INDEX_SN_SAMS_CHECKINOUT" ON "SAMS"."SAMS_CHECKINOUT" ("SN")
- CREATE INDEX "SAMS"."IDX2_SAMS_CHECKINOUT" ON "SAMS"."SAMS_CHECKINOUT" ("PROV_BRANCH_CODE")
- CREATE INDEX "SAMS"."IDX3_SAMS_CHECKINOUT" ON "SAMS"."SAMS_CHECKINOUT" (TO_CHAR("CHECKTIME",'YYYY-MM'), "BADGENUMBER")
- CREATE INDEX "SAMS"."IDX4_SAMS_CHECKINOUT" ON "SAMS"."SAMS_CHECKINOUT" ("PROV_BRANCH_CODE", "BADGENUMBER", "CHECKTIME")
从上图可以看到，实际测试出来的执行计划跟awr报告上不同。
现在要对sql做测试
- SELECT /*+ gather_plan_statistics */ *
- FROM (SELECT A.*, ROWNUM RN
- FROM (select sd.deptnumber,
- sd.deptname,
- su.badgenumber,
- su.name,
- to_char(sc.checktime, 'YYYY-MM-DD HH24:MI:SS') as CHECKTIME,
- case
- when rtrim(sc.verifycode) = '0' then
- '密码'
- when rtrim(sc.verifycode) = '1' then
- '指纹'
- when rtrim(sc.verifycode) = '2' then
- '补签'
- when rtrim(sc.verifycode) = '15' then
- '面部'
- when rtrim(sc.verifycode) = 'ZW' then
- '指纹'
- when rtrim(sc.verifycode) = 'RL' then
- '面部'
- when rtrim(sc.verifycode) = 'YD' then
- '移动打卡(GPS)'
- when rtrim(sc.verifycode) = 'EJ' then
- '国寿E家'
- end as verifycode,
- sl.sn || '(' || sl.alias || ')' as devicename,
- to_char(sc.insystime, 'YYYY-MM-DD HH24:MI:SS') as INSYSTIME
- from SAMS_CHECKINOUT sc
- inner join (select badgenumber, name, deptid
- from SAMS_USERINFO
- union all
- 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.badgenumber
- inner join SAMS_DEPARTMENTS sd
- on s d.deptid = su.deptid
- left join SAMS_ICLOCK sl
- on sl.sn = sc.sn
- where 1 = 1
- and sc.checktime >= to_date('2017-03-01', 'yyyy-MM-dd')
- and sc.checktime <= to_date('2017-03-23', '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) A
- WHERE ROWNUM <= 10)
- WHERE RN > 0;
我们通过/*+
gather_plan_statistics */ 收集的相关执行计划及其统计信息与该SQL的AWR报告中的执行计划不同，且逻辑读的数量与AWR报告中的数值也相差巨大。因此，为了更准确的判断问题，按以下方法测试。
1、SQL在生产库（SAMS库的实例 1上，实例名为sams1 ）上，在SQLPLUS中执行。
2、执行后，在同一SQLPLUS窗口中，立即执行以下命令：
select * from table(dbms_xplan.display_cursor('','','allstats projection last'));
结果如下：
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | | 8 |00:00:31.98 | 11130 | 3323 | | | |
- |* 1 | VIEW | | 1 | 2 | 8 |00:00:31.98 | 11130 | 3323 | | | |
- |* 2 | COUNT STOPKEY | | 1 | | 8 |00:00:31.98 | 11130 | 3323 | | | |
- | 3 | VIEW | | 1 | 2 | 8 |00:00:31.98 | 11130 | 3323 | | | |
- |* 4 | SORT ORDER BY STOPKEY | | 1 | 2 | 8 |00:00:31.98 | 11130 | 3323 | 2048 | 2048 | 2048 (0)|
- |* 5 | FILTER | | 1 | | 8 |00:00:31.57 | 11130 | 3323 | | | |
- |* 6 | HASH JOIN OUTER | | 1 | 2 | 5816 |00:00:30.90 | 11130 | 3323 | 1617K| 1078K| 1678K (0)|
- | 7 | NESTED LOOPS | | 1 | 802 | 5816 |00:00:33.75 | 9263 | 3320 | | | |
- | 8 | NESTED LOOPS | | 1 | 1472 | 6140 |00:00:00.90 | 3136 | 77 | | | |
- | 9 | NESTED LOOPS | | 1 | 92 | 1160 |00:00:00.11 | 353 | 18 | | | |
- | 10 | NESTED LOOPS | | 1 | 3 | 19 |00:00:00.01 | 43 | 0 | | | |
- | 11 | VIEW |VW_NSO_2 | 1 | 3 | 19 |00:00:00.01 | 15 | 0 | | | |
- | 12 | HASH UNIQUE | | 1 | 3 | 19 |00:00:00.01 | 15 | 0 | 1263K| 1263K| 1372K (0)|
- |* 13 | CONNECT BY WITH FILTERING (UNIQUE) | | 1 | | 19 |00:00:00.01 | 15 | 0 | 2048 | 2048 | 2048 (0)|
- | 14 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 3 | 0 | | | |
- |* 15 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | |
- | 16 | NESTED LOOPS | | 3 | 2 | 18 |00:00:00.01 | 12 | 0 | | | |
- | 17 | CONNECT BY PUMP | | 3 | | 19 |00:00:00.01 | 0 | 0 | | | |
- | 18 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 19 | 2 | 18 |00:00:00.01 | 12 | 0 | | | |
- |* 19 | INDEX RANGE SCAN | SUPDEPTID_IDX | 19 | 2 | 18 |00:00:00.01 | 9 | 0 | | | |
- | 20 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 19 | 1 | 19 |00:00:00.01 | 28 | 0 | | | |
- |* 21 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 19 | 1 | 19 |00:00:00.01 | 9 | 0 | | | |
- | 22 | VIEW | | 19 | 31 | 1160 |00:00:00.13 | 310 | 18 | | | |
- | 23 | UNION-ALL PARTITION | | 19 | | 1160 |00:00:00.13 | 310 | 18 | | | |
- |* 24 | INDEX RANGE SCAN | IDX1_SAMS_USERINFO | 19 | 68 | 692 |00:00:00.08 | 84 | 10 | | | |
- |* 25 | HASH JOIN | | 19 | 3 | 468 |00:00:00.08 | 226 | 8 | 1393K| 1393K| 445K (0)|
- |* 26 | VIEW | VW_NSO_1 | 19 | 3 | 19 |00:00:00.01 | 163 | 0 | | | |
- | 27 | SORT UNIQUE | | 19 | 3 | 361 |00:00:00.01 | 163 | 0 | 2048 | 2048 | 2048 (0)|
- |* 28 | CONNECT BY WITH FILTERING (UNIQUE)| | 19 | | 361 |00:00:00.01 | 163 | 0 | 2048 | 2048 | 2048 (0)|
- | 29 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 19 | 1 | 19 |00:00:00.01 | 43 | 0 | | | |
- |* 30 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 19 | 1 | 19 |00:00:00.01 | 24 | 0 | | | |
- | 31 | NESTED LOOPS | | 57 | 2 | 342 |00:00:00.01 | 120 | 0 | | | |
- | 32 | CONNECT BY PUMP | | 57 | | 361 |00:00:00.01 | 0 | 0 | | | |
- | 33 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 361 | 2 | 342 |00:00:00.01 | 120 | 0 | | | |
- |* 34 | INDEX RANGE SCAN | SUPDEPTID_IDX | 361 | 2 | 342 |00:00:00.01 | 63 | 0 | | | |
- |* 35 | INDEX RANGE SCAN | IDX_SAMS_USERINFO_DIMISSION | 19 | 44 | 468 |00:00:00.07 | 63 | 8 | | | |
- |* 36 | INDEX RANGE SCAN | IDX_SAMS_CHECKINOUT | 1160 | 16 | 6140 |00:00:00.62 | 2783 | 59 | | | |
- |* 37 | TABLE ACCESS BY GLOBAL INDEX ROWID | SAMS_CHECKINOUT | 6140 | 9 | 5816 |00:00:30.00 | 6127 | 3243 | | | |
- | 38 | TABLE ACCESS FULL | SAMS_ICLOCK | 1 | 6313 | 6328 |00:00:00.03 | 1867 | 3 | | | |
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1、在目录下创建一个脚本文件，用来获取更加相信的信息。
2、在SQLPLUS中，执行以下命令：@sql_rpt 3271368959 1 24114 24115 99vaabs5ptktb
4、执行完成后，在该目录下生成一个HTML文档，拿到更加详细的sql统计信息附带表的数据信息
注：命令参数的说明：
3271368959 为数据库IDDBID
1 为 实例号instance_number
24114 为 快照的开始snap_id
24115为 快照的结束
snap_id
99vaabs5ptktb 为 相关SQL的sql_id
初步分析如下：
1、该SQL执行一次的逻辑读为11130块次，其中第37步的逻辑读为6127块次，占了一半还多。而该步的操作是根据前面的获取到的ROWID，回表SAMS_CHECKINOUT获取"SC".“CHECKTIME"[TIMESTAMP,11], "SC"."VERIFYCODE"[CHARACTER,4],
"SC"."SN"[NVARCHAR2,40], "SC"."INSYSTIME"[TIMESTAMP,11]四列的内容。
2、第38步对SAMS_ICLOCK表的全表扫描，对整个SQL的逻辑读也有较大贡献。但这个不是问题的关键
另外索引上有两个想法：
1、新建组合索引或改造已有索引，按如下顺序构建组合索引：
（BADGENUMBER, CHECKTIME, SN, VERIFYCODE, INSYSTIME）
2、在表SAMS_ICLOCK上创建组合索引，列名及顺序如下：
（SN, ALIAS）
这两个索引先暂时不创建，先从其他方面入手
由于在测试过程中，其生成的执行计划从未与AWR中显示的执行计划一致过。所以，这也许是造成不能模拟出2亿个块次逻辑读的一个原因。因此，把有问题的SQL的执行计划绑定到的测试SQL上。然后执行该测试SQL，并观察和分析测试SQL的执行过程和结果来做出进一步的处理。
为完成上述想法，需要用到ORACLE的SQL
PROFILE在不改变SQL文本的前提下，改变其执行计划。操作方法如下：
1、在SQLPLUS中，生成问题SQL的创建SQL
PROFILE的脚本。该脚本执行后，会要求分别输入SQL_ID和PLAN_HASH_VALUE的值。而我们问题SQL的SQL_ID是99vaabs5ptktb，PLAN_HASH_VALUE的值是4243346097。脚本执行完成后，会在运行SQLPLUS的当前目录中生成一个脚本文件。其名称在执行脚本过程中的结尾有显示。为描述方便，简称该生成的S脚本文件为“问题SQL脚本”。
2、再次执行该脚本，只不过这次输入测试SQL的SQL_ID和PLAN_HASH_VALUE。其SQL_ID为3kys9xsdjrm3b，PLAN_HASH_VALUE的值为561269195。为描述方便，简称该生成的脚本文件为“测试SQL脚本”
3、在文本编辑工具中分别打开上述两个脚本，将问题SQL脚本中出现在以下特征文字之间的文字（不包含特征文字 ）复制并覆盖掉测试SQL脚本中同样位置的原文字：
h
:= SYS.SQLPROF_ATTR(
………
……….
……….
:signature
:= DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
4、将测试SQL脚本另存为一个文件（后缀名为.sql）
5、在SQLPLUS中执行第4步另存后的脚本。
6、在SQLPLUS中原封不动的执行原测试SQL。(注：执行前设置SQLPLUS格式,以避免格式混乱。比如 set lines
200 set pagesize 100 )
7、执行 select * from table(dbms_xplan.display_cursor('','','allstats
projection last'));
如果正常生成脚本，没有报错信息出现在屏幕上，就是生成脚本成功。比如出现下面的提示就是正常的：
“Execute coe_xfr_sql_profile_99vaabs5ptktb_4243346097.sql
on TARGET system in order to create a custom SQL Profile
with plan 4243346097 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.”
如果出现
“ERROR at line 1:
ORA-20100: SQL_TEXT for SQL_ID 3kys9xsdjrm3b was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).
ORA-06512: at line 3”
这样的信息就是生成脚本出错了。
错误的原因是3kys9xsdjrm3b这条SQL_ID的SQL已经不在内存中，而且也没有被捕获到AWR中。解决的方法就是立即把这条SQL重跑一次，执行select * from table(dbms_xplan.display_cursor('','','allstats projection last')); 就可以看到该sql的id和plan hash value，然后再执行这个脚本。
我们把绑定变量替换为实际的值，运行后发现没有发生2亿多个块次的逻辑读。而且，把各种特殊值都试过，也没有重现发生2亿多个块次的逻辑读。
在测试过程中，发现这些执行计划，和从AWR中提取出来的执行计划就没有一样过。由于计划不同，访问相关对象的方法和路径也就不同，就算是用这样的方法模拟出了2亿个块次的逻辑读，也不是我们真正要解决的那个问题。因此，目前的方向是首先模似出一致的执行计划。
或者说，现在怀疑问题可能与执行计划有关。 简单粗暴的办法是把该SQL的CURSOR清除，让数据库重新生成一个新的执行计划。这个操作之前已经做过，将整个的共享池缓存清楚，让sql重新生成执行计划，结果并没有效果。而且数据库打了最新的补丁，也同样没有效果，基本可以排除BUG的顾虑。
我之前操作有误，把执行计划固定到问题sql上了，现在删除掉了
2017年3月29日 星期三 16:06:42
2017年3月30日 星期四 9:54:28
针对其他性能较差的sql进行优化
六个变量的sql是前台没有做关联查询的，执行情况跟9个变量的差不多
- SQL> SELECT name from dba_sql_profiles;
- NAME
- ------------------------------
- coe_99vaabs5ptktb_4243346097
- SQL> BEGIN
- 2 DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_99vaabs5ptktb_4243346097');
- 3 END;
- 4 /
- PL/SQL procedure successfully completed.
- SQL> SELECT name, created,category,sql_Text from dba_sql_profiles ORDER BY created DESC;
- no rows selected
2017年3月29日 星期三 16:06:42
- SQL>SELECT /*+ gather_plan_statistics */ * FROM (SELECT A.*, ROWNUM RN
- FROM (select sd.deptnumber, 2
- 3 sd.deptname,
- 4 su.badgenumber,
- 5 su.name,
- 6 to_char(sc.checktime, 'YYYY-MM-DD HH24:MI:SS') as CHECKTIME,
- 7 case
- 8 when rtrim(sc.verifycode) = '0' then 'A'
- 9 when rtrim(sc.verifycode) = '1' then 'B'
- 10 when rtrim(sc.verifycode) = '2' then 'D'
- 11 when rtrim(sc.verifycode) = '15' then 'C'
- 12 when rtrim(sc.verifycode) = 'ZW' then 'B'
- 13 when rtrim(sc.verifycode) = 'RL' then 'C'
- 14 when rtrim(sc.verifycode) = 'YD' then 'E'
- 15 when rtrim(sc.verifycode) = 'EJ' then 'F'
- 16 end as verifycode,
- 17 sl.sn || '(' || sl.alias || ')' as devicename,
- 18 to_char(sc.insystime, 'YYYY-MM-DD HH24:MI:SS') as INSYSTIME
- 19 from SAMS_CHECKINOUT sc
- 20 inner join (select badgenumber, name, deptid
- 21 from SAMS_USERINFO
- 22 union all
- 23 select badgenumber, name, deptid
- 24 from SAMS_USERINFO_DIMISSION sd
- 25 where 1 = 1
- 26 and sd.deptid in
- 27 (select Deptid
- 28 from SAMS_DEPARTMENTS T2
- 29 start with T2.Deptid = '360710'
- 30 connect by prior T2.DEPTID = T2.SUPDEPTID)) su
- 31 on su.badgenumber = sc.badgenumber
- 32 inner join SAMS_DEPARTMENTS sd
- 33 on sd.deptid = su.deptid
- 34 left join SAMS_ICLOCK sl
- 35 on sl.sn = sc.sn
- 36 where 1 = 1
- 37 and sc.checktime >= to_date('2017-03-01', 'yyyy-MM-dd')
- 38 and sc.checktime <= to_date('2017-03-23', 'yyyy-MM-dd') + 1
- 39 and sd.deptid in
- 40 (select Deptid
- 41 from SAMS_DEPARTMENTS T2
- 42 start with T2.Deptid = '360710'
- 43 connect by prior T2.DEPTID = T2.SUPDEPTID)
- 44 and (su.badgenumber = '36071000000600' or su.name LIKE '%36071000000600%' or sl.sn = '36071000000600')
- 45 order by sc.checktime, su.name desc) A
- 46 WHERE ROWNUM <= 10)
- 47 WHERE RN > 0;
- select * from table(dbms_xplan.display_cursor('','','allstats projection last'));
- DEPTNUMBER
- --------------------------------------------------------------------------------
- DEPTNAME
- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- BADGENUMBER NAME CHECKTIME V
- ------------------------------------------------ ------------------------------------------------ ------------------- -
- DEVICENAME INSYSTIME RN
- ---------------------------------------------------------------------------------------------------------------------------- ------------------- ----------
- 360710002002
- 黄陂营销服务部职场
- 36071000000600 张小明 2017-03-04 08:05:01 B
- 6538992529510(宁都) 2017-03-04 08:05:02 1
- 360710002002
- 黄陂营销服务部职场
- 36071000000600 张小明 2017-03-06 09:19:32 B
- 6538992529510(宁都) 2017-03-06 09:19:31 2
- 360710002002
- 黄陂营销服务部职场
- 36071000000600 张小明 2017-03-12 09:24:00 B
- 6538992529510(宁都) 2017-03-12 09:23:59 3
- 360710002002
- 黄陂营销服务部职场
- 36071000000600 张小明 2017-03-15 09:14:30 B
- 6538992529510(宁都) 2017-03-15 09:14:29 4
- 360710002002
- 黄陂营销服务部职场
- 36071000000600 张小明 2017-03-16 08:22:50 B
- 6538992529510(宁都) 2017-03-16 08:22:50 5
- 360710002002
- 黄陂营销服务部职场
- 36071000000600 张小明 2017-03-16 09:59:05 B
- 6538992529510(宁都) 2017-03-16 09:59:04 6
- 360710002002
- 黄陂营销服务部职场
- 36071000000600 张小明 2017-03-18 08:55:11 B
- 6538992529510(宁都) 2017-03-18 08:55:09 7
- 360710002002
- 黄陂营销服务部职场
- 36071000000600 张小明 2017-03-21 14:57:57 B
- 6538992529510(宁都) 2017-03-21 14:57:57 8
- 8 rows selected.
- SQL>
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- SQL_ID bw0b67268pva8, child number 1
- -------------------------------------
- SELECT /*+ gather_plan_statistics */ * FROM (SELECT A.*, ROWNUM RN
- FROM (select sd.deptnumber, sd.deptname,
- su.badgenumber, su.name,
- to_char(sc.checktime, 'YYYY-MM-DD HH24:MI:SS') as
- CHECKTIME, case when
- rtrim(sc.verifycode) = '0' then 'A' when
- rtrim(sc.verifycode) = '1' then 'B' when
- rtrim(sc.verifycode) = '2' then 'D' when
- rtrim(sc.verifycode) = '15' then 'C' when
- rtrim(sc.verifycode) = 'ZW' then 'B' when
- rtrim(sc.verifycode) = 'RL' then 'C' when
- rtrim(sc.verifycode) = 'YD' then 'E' when
- rtrim(sc.verifycode) = 'EJ' then 'F' end as
- verifycode, sl.sn || '(' || sl.alias || ')' as
- devicename, to_char
- Plan hash value: 3623936353
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | | 8 |00:00:00.15 | 11988 | 1 | | | |
- |* 1 | VIEW | | 1 | 10 | 8 |00:00:00.15 | 11988 | 1 | | | |
- |* 2 | COUNT STOPKEY | | 1 | | 8 |00:00:00.15 | 11988 | 1 | | | |
- | 3 | VIEW | | 1 | 14 | 8 |00:00:00.15 | 11988 | 1 | | | |
- |* 4 | SORT ORDER BY STOPKEY | | 1 | 14 | 8 |00:00:00.15 | 11988 | 1 | 2048 | 2048 | 2048 (0)|
- |* 5 | FILTER | | 1 | | 8 |00:00:00.07 | 11988 | 1 | | | |
- |* 6 | HASH JOIN RIGHT OUTER | | 1 | 14 | 5818 |00:00:00.12 | 11988 | 1 | 1361K| 1361K| 1487K (0)|
- | 7 | TABLE ACCESS FULL | SAMS_ICLOCK | 1 | 6332 | 6336 |00:00:00.07 | 376 | 0 | | | |
- | 8 | NESTED LOOPS | | 1 | 5442 | 5818 |00:00:00.04 | 11612 | 1 | | | |
- | 9 | NESTED LOOPS | | 1 | 11210 | 8062 |00:00:00.01 | 3593 | 0 | | | |
- | 10 | NESTED LOOPS | | 1 | 590 | 1174 |00:00:00.01 | 374 | 0 | | | |
- | 11 | NESTED LOOPS | | 1 | 19 | 19 |00:00:00.01 | 44 | 0 | | | |
- | 12 | VIEW | VW_NSO_2 | 1 | 19 | 19 |00:00:00.01 | 16 | 0 | | | |
- | 13 | HASH UNIQUE | | 1 | 19 | 19 |00:00:00.01 | 16 | 0 | 1263K| 1263K| 1369K (0)|
- |* 14 | CONNECT BY WITH FILTERING (UNIQUE) | | 1 | | 19 |00:00:00.01 | 16 | 0 | 2048 | 2048 | 2048 (0)|
- | 15 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 3 | 0 | | | |
- |* 16 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | |
- | 17 | NESTED LOOPS | | 3 | 2 | 18 |00:00:00.01 | 13 | 0 | | | |
- | 18 | CONNECT BY PUMP | | 3 | | 19 |00:00:00.01 | 0 | 0 | | | |
- | 19 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 19 | 2 | 18 |00:00:00.01 | 13 | 0 | | | |
- |* 20 | INDEX RANGE SCAN | SUPDEPTID_IDX | 19 | 2 | 18 |00:00:00.01 | 9 | 0 | | | |
- | 21 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 19 | 1 | 19 |00:00:00.01 | 28 | 0 | | | |
- |* 22 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 19 | 1 | 19 |00:00:00.01 | 9 | 0 | | | |
- | 23 | VIEW | | 19 | 31 | 1174 |00:00:00.01 | 330 | 0 | | | |
- | 24 | UNION-ALL PARTITION | | 19 | | 1174 |00:00:00.01 | 330 | 0 | | | |
- |* 25 | INDEX RANGE SCAN | IDX1_SAMS_USERINFO | 19 | 69 | 706 |00:00:00.01 | 85 | 0 | | | |
- |* 26 | HASH JOIN | | 19 | 3 | 468 |00:00:00.01 | 245 | 0 | 1393K| 1393K| 716K (0)|
- |* 27 | VIEW | VW_NSO_1 | 19 | 3 | 19 |00:00:00.01 | 182 | 0 | | | |
- | 28 | SORT UNIQUE | | 19 | 3 | 361 |00:00:00.01 | 182 | 0 | 2048 | 2048 | 2048 (0)|
- |* 29 | CONNECT BY WITH FILTERING (UNIQUE)| | 19 | | 361 |00:00:00.01 | 182 | 0 | 2048 | 2048 | 2048 (0)|
- | 30 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 19 | 1 | 19 |00:00:00.01 | 43 | 0 | | | |
- |* 31 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 19 | 1 | 19 |00:00:00.01 | 24 | 0 | | | |
- | 32 | NESTED LOOPS | | 57 | 2 | 342 |00:00:00.01 | 139 | 0 | | | |
- | 33 | CONNECT BY PUMP | | 57 | | 361 |00:00:00.01 | 0 | 0 | | | |
- | 34 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 361 | 2 | 342 |00:00:00.01 | 139 | 0 | | | |
- |* 35 | INDEX RANGE SCAN | SUPDEPTID_IDX | 361 | 2 | 342 |00:00:00.01 | 63 | 0 | | | |
- |* 36 | INDEX RANGE SCAN | IDX_SAMS_USERINFO_DIMISSION | 19 | 45 | 468 |00:00:00.01 | 63 | 0 | | | |
- |* 37 | INDEX RANGE SCAN | IDX_SAMS_CHECKINOUT | 1174 | 19 | 8062 |00:00:00.01 | 3219 | 0 | | | |
- |* 38 | TABLE ACCESS BY GLOBAL INDEX ROWID | SAMS_CHECKINOUT | 8062 | 9 | 5818 |00:00:00.06 | 8019 | 1 | | | |
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("RN">0)
- 2 - filter(ROWNUM<=10)
- 4 - filter(ROWNUM<=10)
- 5 - filter(("SU"."BADGENUMBER"=U'36071000000600' OR ("SU"."NAME" LIKE U'%36071000000600%' AND "SU"."NAME" IS NOT NULL AND "SU"."NAME" IS NOT NULL) OR
- "SL"."SN"=U'36071000000600'))
- 6 - access("SL"."SN"="SC"."SN")
- 14 - access("T2"."SUPDEPTID"=PRIOR NULL)
- 16 - access("T2"."DEPTID"=U'360710')
- 20 - access("connect$_by$_pump$_019"."prior T2.DEPTID "="T2"."SUPDEPTID")
- 22 - access("SD"."DEPTID"="DEPTID")
- 25 - access("DEPTID"="SD"."DEPTID")
- 26 - access("SD"."DEPTID"="DEPTID")
- 27 - filter("DEPTID"="SD"."DEPTID")
- 29 - access("T2"."SUPDEPTID"=PRIOR NULL)
- 31 - access("T2"."DEPTID"=U'360710')
- 35 - access("connect$_by$_pump$_008"."prior T2.DEPTID "="T2"."SUPDEPTID")
- 36 - access("DEPTID"="SD"."DEPTID")
- 37 - access("SU"."BADGENUMBER"="SC"."BADGENUMBER")
- 38 - filter(("SC"."CHECKTIME"<=TIMESTAMP' 2017-03-24 00:00:00' AND "SC"."CHECKTIME">=TIMESTAMP' 2017-03-01 00:00:00'))
- Column Projection Information (identified by operation id):
- -----------------------------------------------------------
- 1 - "from$_subquery$_001"."DEPTNUMBER"[NVARCHAR2,80], "from$_subquery$_001"."DEPTNAME"[NVARCHAR2,400], "from$_subquery$_001"."BADGENUMBER"[NVARCHAR2,48],
- "from$_subquery$_001"."NAME"[NVARCHAR2,48], "from$_subquery$_001"."CHECKTIME"[VARCHAR2,19], "from$_subquery$_001"."VERIFYCODE"[CHARACTER,1],
- "from$_subquery$_001"."DEVICENAME"[NVARCHAR2,124], "from$_subquery$_001"."INSYSTIME"[VARCHAR2,19], "RN"[NUMBER,22]
- 2 - "A"."DEPTNUMBER"[NVARCHAR2,80], "A"."DEPTNAME"[NVARCHAR2,400], "A"."BADGENUMBER"[NVARCHAR2,48], "A"."NAME"[NVARCHAR2,48], "A"."CHECKTIME"[VARCHAR2,19],
- "A"."VERIFYCODE"[CHARACTER,1], "A"."DEVICENAME"[NVARCHAR2,124], "A"."INSYSTIME"[VARCHAR2,19], ROWNUM[4]
- 3 - "A"."DEPTNUMBER"[NVARCHAR2,80], "A"."DEPTNAME"[NVARCHAR2,400], "A"."BADGENUMBER"[NVARCHAR2,48], "A"."NAME"[NVARCHAR2,48], "A"."CHECKTIME"[VARCHAR2,19],
- "A"."VERIFYCODE"[CHARACTER,1], "A"."DEVICENAME"[NVARCHAR2,124], "A"."INSYSTIME"[VARCHAR2,19]
- 4 - (#keys=2) "SC"."CHECKTIME"[TIMESTAMP,11], INTERNAL_FUNCTION("SU"."NAME")[48], "SD"."DEPTNUMBER"[NVARCHAR2,80], "SD"."DEPTNAME"[NVARCHAR2,400],
- "SU"."BADGENUMBER"[NVARCHAR2,48], TO_CHAR(INTERNAL_FUNCTION("SC"."INSYSTIME"),'YYYY-MM-DD HH24:MI:SS')[19], TO_CHAR(INTERNAL_FUNCTION("SC"."CHECKTIME"),'YYYY-MM-DD
- HH24:MI:SS')[19], CASE RTRIM("SC"."VERIFYCODE") WHEN '0' THEN 'A' WHEN '1' THEN 'B' WHEN '2' THEN 'D' WHEN '15' THEN 'C' WHEN 'ZW' THEN 'B' WHEN 'RL' THEN 'C' WHEN
- 'YD' THEN 'E' WHEN 'EJ' THEN 'F' END [1], "SL"."SN"||U'('||"SL"."ALIAS"||U')'[124]
- 5 - "SL"."SN"[NVARCHAR2,40], "SL"."ALIAS"[NVARCHAR2,80], "SD"."DEPTNUMBER"[NVARCHAR2,80], "SD"."DEPTNAME"[NVARCHAR2,400], "SU"."BADGENUMBER"[NVARCHAR2,48],
- "SU"."NAME"[NVARCHAR2,48], "SC"."CHECKTIME"[TIMESTAMP,11], "SC"."VERIFYCODE"[CHARACTER,4], "SC"."INSYSTIME"[TIMESTAMP,11]
- 6 - (#keys=1) "SL"."SN"[NVARCHAR2,40], "SL"."ALIAS"[NVARCHAR2,80], "SD"."DEPTNUMBER"[NVARCHAR2,80], "SD"."DEPTNAME"[NVARCHAR2,400],
- "SU"."BADGENUMBER"[NVARCHAR2,48], "SU"."NAME"[NVARCHAR2,48], "SC"."CHECKTIME"[TIMESTAMP,11], "SC"."VERIFYCODE"[CHARACTER,4], "SC"."INSYSTIME"[TIMESTAMP,11]
- 7 - "SL"."SN"[NVARCHAR2,40], "SL"."ALIAS"[NVARCHAR2,80]
- 8 - "SD"."DEPTNUMBER"[NVARCHAR2,80], "SD"."DEPTNAME"[NVARCHAR2,400], "SU"."BADGENUMBER"[NVARCHAR2,48], "SU"."NAME"[NVARCHAR2,48], "SC"."CHECKTIME"[TIMESTAMP,11],
- "SC"."VERIFYCODE"[CHARACTER,4], "SC"."SN"[NVARCHAR2,40], "SC"."INSYSTIME"[TIMESTAMP,11]
- 9 - "SD"."DEPTNUMBER"[NVARCHAR2,80], "SD"."DEPTNAME"[NVARCHAR2,400], "SU"."BADGENUMBER"[NVARCHAR2,48], "SU"."NAME"[NVARCHAR2,48], "SC".ROWID[ROWID,10]
- 10 - "SD"."DEPTNUMBER"[NVARCHAR2,80], "SD"."DEPTNAME"[NVARCHAR2,400], "SU"."BADGENUMBER"[NVARCHAR2,48], "SU"."NAME"[NVARCHAR2,48]
- 11 - "SD"."DEPTID"[NVARCHAR2,80], "SD"."DEPTNUMBER"[NVARCHAR2,80], "SD"."DEPTNAME"[NVARCHAR2,400]
- 12 - "DEPTID"[NVARCHAR2,80]
- 13 - "DEPTID"[NVARCHAR2,80]
- 14 - "T2"."SUPDEPTID"[NVARCHAR2,80], "T2"."DEPTID"[NVARCHAR2,80], PRIOR NULL[80], LEVEL[4]
- 15 - "T2".ROWID[ROWID,10], "T2"."DEPTID"[NVARCHAR2,80], "T2"."SUPDEPTID"[NVARCHAR2,80]
- 16 - "T2".ROWID[ROWID,10], "T2"."DEPTID"[NVARCHAR2,80]
- 17 - "connect$_by$_pump$_019"."prior T2.DEPTID "[NVARCHAR2,80], "T2".ROWID[ROWID,10], "T2"."DEPTID"[NVARCHAR2,80], "T2"."SUPDEPTID"[NVARCHAR2,80]
- 18 - "connect$_by$_pump$_019"."prior T2.DEPTID "[NVARCHAR2,80]
- 19 - "T2".ROWID[ROWID,10], "T2"."DEPTID"[NVARCHAR2,80], "T2"."SUPDEPTID"[NVARCHAR2,80]
- 20 - "T2".ROWID[ROWID,10], "T2"."SUPDEPTID"[NVARCHAR2,80]
- 21 - "SD"."DEPTID"[NVARCHAR2,80], "SD"."DEPTNUMBER"[NVARCHAR2,80], "SD"."DEPTNAME"[NVARCHAR2,400]
- 22 - "SD".ROWID[ROWID,10], "SD"."DEPTID"[NVARCHAR2,80]
- 23 - "SU"."BADGENUMBER"[NVARCHAR2,48], "SU"."NAME"[NVARCHAR2,48]
- 24 - STRDEF[48], STRDEF[48], STRDEF[80]
- 25 - "BADGENUMBER"[NVARCHAR2,48], "NAME"[NVARCHAR2,48], "DEPTID"[NVARCHAR2,80]
- 26 - (#keys=1) "SD"."DEPTID"[NVARCHAR2,80], "BADGENUMBER"[NVARCHAR2,48], "NAME"[NVARCHAR2,48]
- 27 - "DEPTID"[NVARCHAR2,80]
- 28 - (#keys=1) "DEPTID"[NVARCHAR2,80]
- 29 - "T2"."SUPDEPTID"[NVARCHAR2,80], "T2"."DEPTID"[NVARCHAR2,80], PRIOR NULL[80], LEVEL[4]
- 30 - "T2".ROWID[ROWID,10], "T2"."DEPTID"[NVARCHAR2,80], "T2"."SUPDEPTID"[NVARCHAR2,80]
- 31 - "T2".ROWID[ROWID,10], "T2"."DEPTID"[NVARCHAR2,80]
- 32 - "connect$_by$_pump$_008"."prior T2.DEPTID "[NVARCHAR2,80], "T2".ROWID[ROWID,10], "T2"."DEPTID"[NVARCHAR2,80], "T2"."SUPDEPTID"[NVARCHAR2,80]
- 33 - "connect$_by$_pump$_008"."prior T2.DEPTID "[NVARCHAR2,80]
- 34 - "T2".ROWID[ROWID,10], "T2"."DEPTID"[NVARCHAR2,80], "T2"."SUPDEPTID"[NVARCHAR2,80]
- 35 - "T2".ROWID[ROWID,10], "T2"."SUPDEPTID"[NVARCHAR2,80]
- 36 - "BADGENUMBER"[NVARCHAR2,48], "NAME"[NVARCHAR2,48], "SD"."DEPTID"[NVARCHAR2,80]
- 37 - "SC".ROWID[ROWID,10]
- 38 - "SC"."CHECKTIME"[TIMESTAMP,11], "SC"."VERIFYCODE"[CHARACTER,4], "SC"."SN"[NVARCHAR2,40], "SC"."INSYSTIME"[TIMESTAMP,11]
按照之前的方法重新测试，执行计划还是跟awr上面不一样，手工绑sqlprofile也不行，再寻找其他出路。
2017年3月30日 星期四 9:54:28
使用oracle的sql 自动优化功能，看看是否可以模拟出跟awr报告上面一样的执行计划，看看其结果如何：
- GENERAL INFORMATION SECTION
- -------------------------------------------------------------------------------
- Tuning Task Name : TASK_88857
- Tuning Task Owner : SYS
- Workload Type : Single SQL Statement
- Scope : COMPREHENSIVE
- Time Limit(seconds): 1800
- Completion Status : COMPLETED
- Started at : 03/30/2017 09:20:25
- Completed at : 03/30/2017 09:22:27
- -------------------------------------------------------------------------------
- Schema Name: SAMS
- SQL ID : 99vaabs5ptktb
- SQL Text : SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (select
- sd.deptnumber,sd.deptname,su.badgenumber,su.name,to_char(sc.check
- time,'YYYY-MM-DD HH24:MI:SS') as CHECKTIME,case when
- rtrim(sc.verifycode)='0' then '密码' when rtrim(sc.verifycode)='1'
- then '指纹' when rtrim(sc.verifycode)='2' then '补签' when
- rtrim(sc.verifycode)='15' then '面部' when
- rtrim(sc.verifycode)='ZW' then '指纹' when
- rtrim(sc.verifycode)='RL' then '面部' when
- rtrim(sc.verifycode)='YD' then '移动打卡(GPS)' when
- rtrim(sc.verifycode)='EJ' then '国寿E家' end as
- verifycode,sl.sn||'('||sl.alias||')' as
- devicename,to_char(sc.insystime,'YYYY-MM-DD HH24:MI:SS') as
- INSYSTIME from SAMS_CHECKINOUT sc inner join (select
- badgenumber, name, deptid from SAMS_USERINFO union all 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 = :1 connect by prior T2.DEPTID
- =T2.SUPDEPTID)) su on su.badgenumber = sc.badgenumber inner join
- SAMS_DEPARTMENTS sd on sd.deptid = su.deptid left join
- SAMS_ICLOCK sl on sl.sn=sc.sn where 1=1 and sc.checktime>=
- to_date(:2 ,'yyyy-MM-dd') and sc.checktime<= to_date(:3
- ,'yyyy-MM-dd')+1 and sd.deptid in(select Deptid from
- SAMS_DEPARTMENTS T2 start with T2.Deptid = :4 connect by prior
- T2.DEPTID =T2.SUPDEPTID) and (su.badgenumber=:5 or su.name LIKE
- :6 or sl.sn=:7 ) order by sc.checktime,su.name desc ) A WHERE
- ROWNUM <= :8 ) WHERE RN > :9
- Bind Variables :
- 1 - (VARCHAR2(32)):350627
- 2 - (VARCHAR2(32)):2017-03-01
- 3 - (VARCHAR2(32)):2017-03-30
- 4 - (VARCHAR2(32)):350627
- 5 - (VARCHAR2(32)):35062700001791
- 6 - (VARCHAR2(32)):%35062700001791%
- 7 - (VARCHAR2(32)):35062700001791
- 8 - (NUMBER):10
- 9 - (NUMBER):0
- -------------------------------------------------------------------------------
- FINDINGS SECTION (1 finding)
- -------------------------------------------------------------------------------
- 1- Alternative Plan Finding
- ---------------------------
- Some alternative execution plans for this statement were found by searching
- the system's real-time and historical performance data.
- The following table lists these plans ranked by their average elapsed time.
- See section "ALTERNATIVE PLANS SECTION" for detailed information on each
- plan.
- id plan hash last seen elapsed (s) origin note
- -- ---------- -------------------- ------------ --------------- ----------------
- 1 3018912096 2017-03-30/09:20:49 0.203 Cursor Cache original plan
- 2 205839464 2017-03-28/12:00:08 1.492 AWR
- 3 4243346097 2017-03-30/08:00:34 33323.697 AWR
- Information
- -----------
- - The Original Plan appears to have the best performance, based on the
- elapsed time per execution. However, if you know that one alternative
- plan is better than the Original Plan, you can create a SQL plan baseline
- for it. This will instruct the Oracle optimizer to pick it over any other
- choices in the future.
- execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_88857',
- owner_name => 'SYS', plan_hash_value => xxxxxxxx);
- -------------------------------------------------------------------------------
- ADDITIONAL INFORMATION SECTION
- -------------------------------------------------------------------------------
- - The optimizer could not merge the view at line ID 3 of the execution plan.
- The optimizer cannot merge a view that contains an "ORDER BY" clause unless
- the statement is a "DELETE" or an "UPDATE" and the parent query is the top
- most query in the statement.
- - The optimizer could not merge the view at line ID 1 of the execution plan.
- The optimizer cannot merge a view that contains a "ROWNUM" pseudo column.
- -------------------------------------------------------------------------------
- EXPLAIN PLANS SECTION
- -------------------------------------------------------------------------------
- 1- Original
- -----------
- Plan hash value: 3018912096
- ------------------------------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
- ------------------------------------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 3 | 1209 | 1604 (1)| 00:00:20 | | |
- |* 1 | VIEW | | 3 | 1209 | 1604 (1)| 00:00:20 | | |
- |* 2 | COUNT STOPKEY | | | | | | | |
- | 3 | VIEW | | 3 | 1170 | 1604 (1)| 00:00:20 | | |
- |* 4 | SORT ORDER BY STOPKEY | | 3 | 804 | 1604 (1)| 00:00:20 | | |
- |* 5 | FILTER | | | | | | | |
- |* 6 | HASH JOIN OUTER | | 3 | 804 | 1603 (1)| 00:00:20 | | |
- | 7 | NESTED LOOPS | | 1088 | 233K| 1520 (1)| 00:00:19 | | |
- | 8 | NESTED LOOPS | | 1767 | 233K| 1520 (1)| 00:00:19 | | |
- | 9 | NESTED LOOPS | | 93 | 14043 | 104 (1)| 00:00:02 | | |
- | 10 | NESTED LOOPS | | 3 | 270 | 10 (10)| 00:00:01 | | |
- | 11 | VIEW | VW_NSO_2 | 3 | 66 | 7 (15)| 00:00:01 | | |
- | 12 | HASH UNIQUE | | 3 | 192 | 7 (15)| 00:00:01 | | |
- |* 13 | FILTER | | | | | | | |
- |* 14 | CONNECT BY WITH FILTERING (UNIQUE) | | | | | | | |
- | 15 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 38 | 2 (0)| 00:00:01 | | |
- |* 16 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | 1 (0)| 00:00:01 | | |
- | 17 | NESTED LOOPS | | 2 | 160 | 4 (0)| 00:00:01 | | |
- | 18 | CONNECT BY PUMP | | | | | | | |
- | 19 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 2 | 76 | 2 (0)| 00:00:01 | | |
- |* 20 | INDEX RANGE SCAN | SUPDEPTID_IDX | 2 | | 1 (0)| 00:00:01 | | |
- | 21 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 68 | 1 (0)| 00:00:01 | | |
- |* 22 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | 0 (0)| 00:00:01 | | |
- | 23 | VIEW | | 31 | 1891 | 60 (0)| 00:00:01 | | |
- | 24 | UNION-ALL PARTITION | | | | | | | |
- |* 25 | INDEX RANGE SCAN | IDX1_SAMS_USERINFO | 69 | 4209 | 4 (0)| 00:00:01 | | |
- |* 26 | HASH JOIN | | 3 | 309 | 10 (10)| 00:00:01 | | |
- |* 27 | VIEW | VW_NSO_1 | 3 | 126 | 7 (15)| 00:00:01 | | |
- | 28 | SORT UNIQUE | | 3 | 192 | 7 (15)| 00:00:01 | | |
- |* 29 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | | | |
- | 30 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 38 | 2 (0)| 00:00:01 | | |
- |* 31 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | 1 (0)| 00:00:01 | | |
- | 32 | NESTED LOOPS | | 2 | 160 | 4 (0)| 00:00:01 | | |
- | 33 | CONNECT BY PUMP | | | | | | | |
- | 34 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 2 | 76 | 2 (0)| 00:00:01 | | |
- |* 35 | INDEX RANGE SCAN | SUPDEPTID_IDX | 2 | | 1 (0)| 00:00:01 | | |
- |* 36 | INDEX RANGE SCAN | IDX_SAMS_USERINFO_DIMISSION | 46 | 2806 | 3 (0)| 00:00:01 | | |
- |* 37 | INDEX RANGE SCAN | IDX_SAMS_CHECKINOUT | 19 | | 3 (0)| 00:00:01 | | |
- |* 38 | TABLE ACCESS BY GLOBAL INDEX ROWID | SAMS_CHECKINOUT | 12 | 828 | 23 (0)| 00:00:01 | ROWID | ROWID |
- | 39 | TABLE ACCESS FULL | SAMS_ICLOCK | 6337 | 297K| 83 (0)| 00:00:01 | | |
- ------------------------------------------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("RN">:9)
- 2 - filter(ROWNUM<=:8)
- 4 - filter(ROWNUM<=:8)
- 5 - filter("SU"."BADGENUMBER"=SYS_OP_C2C(:5) OR "SU"."NAME" LIKE SYS_OP_C2C(:6) OR "SL"."SN"=SYS_OP_C2C(:7))
- 6 - access("SL"."SN"(+)="SC"."SN")
- 13 - filter(TO_DATE(:3,'yyyy-MM-dd')+1>=TO_DATE(:2,'yyyy-MM-dd'))
- 14 - access("T2"."SUPDEPTID"=PRIOR NULL)
- 16 - access("T2"."DEPTID"=SYS_OP_C2C(:4))
- 20 - access("connect$_by$_pump$_019"."prior T2.DEPTID "="T2"."SUPDEPTID")
- 22 - access("SD"."DEPTID"="DEPTID")
- 25 - access("DEPTID"="SD"."DEPTID")
- 26 - access("SD"."DEPTID"="DEPTID")
- 27 - filter("DEPTID"="SD"."DEPTID")
- 29 - access("T2"."SUPDEPTID"=PRIOR NULL)
- 31 - access("T2"."DEPTID"=SYS_OP_C2C(:1))
- 35 - access("connect$_by$_pump$_008"."prior T2.DEPTID "="T2"."SUPDEPTID")
- 36 - access("DEPTID"="SD"."DEPTID")
- 37 - access("SU"."BADGENUMBER"="SC"."BADGENUMBER")
- 38 - filter("SC"."CHECKTIME">=TO_DATE(:2,'yyyy-MM-dd') AND "SC"."CHECKTIME"<=TO_DATE(:3,'yyyy-MM-dd')+1)
- -------------------------------------------------------------------------------
- ALTERNATIVE PLANS SECTION
- -------------------------------------------------------------------------------
- Plan 1
- ------
- Plan Origin :Cursor Cache
- Plan Hash Value :3018912096
- Executions :39
- Elapsed Time :0.203 sec
- CPU Time :0.127 sec
- Buffer Gets :27754
- Disk Reads :38
- Disk Writes :0
- Notes:
- 1. Statistics shown are averaged over multiple executions.
- 2. The plan matches the original plan.
- ------------------------------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
- ------------------------------------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 10 | 4030 | 1604 (1)| 00:00:20 | | |
- |* 1 | VIEW | | 10 | 4030 | 1604 (1)| 00:00:20 | | |
- |* 2 | COUNT STOPKEY | | | | | | | |
- | 3 | VIEW | | 55 | 21450 | 1604 (1)| 00:00:20 | | |
- |* 4 | SORT ORDER BY STOPKEY | | 55 | 14740 | 1604 (1)| 00:00:20 | | |
- |* 5 | FILTER | | | | | | | |
- |* 6 | HASH JOIN OUTER | | 55 | 14740 | 1603 (1)| 00:00:20 | | |
- | 7 | NESTED LOOPS | | 1088 | 233K| 1520 (1)| 00:00:19 | | |
- | 8 | NESTED LOOPS | | 1767 | 233K| 1520 (1)| 00:00:19 | | |
- | 9 | NESTED LOOPS | | 93 | 14043 | 104 (1)| 00:00:02 | | |
- | 10 | NESTED LOOPS | | 3 | 270 | 10 (10)| 00:00:01 | | |
- | 11 | VIEW | VW_NSO_2 | 3 | 66 | 7 (15)| 00:00:01 | | |
- | 12 | HASH UNIQUE | | 3 | 192 | 7 (15)| 00:00:01 | | |
- |* 13 | FILTER | | | | | | | |
- |* 14 | CONNECT BY WITH FILTERING (UNIQUE) | | | | | | | |
- | 15 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 38 | 2 (0)| 00:00:01 | | |
- |* 16 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | 1 (0)| 00:00:01 | | |
- | 17 | NESTED LOOPS | | 2 | 160 | 4 (0)| 00:00:01 | | |
- | 18 | CONNECT BY PUMP | | | | | | | |
- | 19 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 2 | 76 | 2 (0)| 00:00:01 | | |
- |* 20 | INDEX RANGE SCAN | SUPDEPTID_IDX | 2 | | 1 (0)| 00:00:01 | | |
- | 21 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 68 | 1 (0)| 00:00:01 | | |
- |* 22 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | 0 (0)| 00:00:01 | | |
- | 23 | VIEW | | 31 | 1891 | 60 (0)| 00:00:01 | | |
- | 24 | UNION-ALL PARTITION | | | | | | | |
- |* 25 | INDEX RANGE SCAN | IDX1_SAMS_USERINFO | 69 | 4209 | 4 (0)| 00:00:01 | | |
- |* 26 | HASH JOIN | | 3 | 309 | 10 (10)| 00:00:01 | | |
- |* 27 | VIEW | VW_NSO_1 | 3 | 126 | 7 (15)| 00:00:01 | | |
- | 28 | SORT UNIQUE | | 3 | 192 | 7 (15)| 00:00:01 | | |
- |* 29 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | | | |
- | 30 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 38 | 2 (0)| 00:00:01 | | |
- |* 31 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | 1 (0)| 00:00:01 | | |
- | 32 | NESTED LOOPS | | 2 | 160 | 4 (0)| 00:00:01 | | |
- | 33 | CONNECT BY PUMP | | | | | | | |
- | 34 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 2 | 76 | 2 (0)| 00:00:01 | | |
- |* 35 | INDEX RANGE SCAN | SUPDEPTID_IDX | 2 | | 1 (0)| 00:00:01 | | |
- |* 36 | INDEX RANGE SCAN | IDX_SAMS_USERINFO_DIMISSION | 46 | 2806 | 3 (0)| 00:00:01 | | |
- |* 37 | INDEX RANGE SCAN | IDX_SAMS_CHECKINOUT | 19 | | 3 (0)| 00:00:01 | | |
- |* 38 | TABLE ACCESS BY GLOBAL INDEX ROWID | SAMS_CHECKINOUT | 12 | 828 | 23 (0)| 00:00:01 | ROWID | ROWID |
- | 39 | TABLE ACCESS FULL | SAMS_ICLOCK | 6337 | 297K| 83 (0)| 00:00:01 | | |
- ------------------------------------------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("RN">:9)
- 2 - filter(ROWNUM<=:8)
- 4 - filter(ROWNUM<=:8)
- 5 - filter("SU"."BADGENUMBER"=SYS_OP_C2C(:5) OR "SU"."NAME" LIKE SYS_OP_C2C(:6) OR "SL"."SN"=SYS_OP_C2C(:7))
- 6 - access("SL"."SN"(+)="SC"."SN")
- 13 - filter(TO_DATE(:3,'yyyy-MM-dd')+1>=TO_DATE(:2,'yyyy-MM-dd'))
- 14 - access("T2"."SUPDEPTID"=PRIOR NULL)
- 16 - access("T2"."DEPTID"=SYS_OP_C2C(:4))
- 20 - access("connect$_by$_pump$_019"."prior T2.DEPTID "="T2"."SUPDEPTID")
- 22 - access("SD"."DEPTID"="DEPTID")
- 25 - access("DEPTID"="SD"."DEPTID")
- 26 - access("SD"."DEPTID"="DEPTID")
- 27 - filter("DEPTID"="SD"."DEPTID")
- 29 - access("T2"."SUPDEPTID"=PRIOR NULL)
- 31 - access("T2"."DEPTID"=SYS_OP_C2C(:1))
- 35 - access("connect$_by$_pump$_008"."prior T2.DEPTID "="T2"."SUPDEPTID")
- 36 - access("DEPTID"="SD"."DEPTID")
- 37 - access("SU"."BADGENUMBER"="SC"."BADGENUMBER")
- 38 - filter("SC"."CHECKTIME">=TO_DATE(:2,'yyyy-MM-dd') AND "SC"."CHECKTIME"<=TO_DATE(:3,'yyyy-MM-dd')+1)
- Plan 2
- ------
- Plan Origin :AWR
- Plan Hash Value :205839464
- Executions :86
- Elapsed Time :1.492 sec
- CPU Time :0.100 sec
- Buffer Gets :31273
- Disk Reads :109
- Disk Writes :0
- Notes:
- 1. Statistics shown are averaged over multiple executions.
- -----------------------------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
- -----------------------------------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 10 | 4030 | 1998 (1)| 00:00:24 | | |
- |* 1 | VIEW | | 10 | 4030 | 1998 (1)| 00:00:24 | | |
- |* 2 | COUNT STOPKEY | | | | | | | |
- | 3 | VIEW | | 55 | 21450 | 1998 (1)| 00:00:24 | | |
- |* 4 | SORT ORDER BY STOPKEY | | 55 | 14740 | 1998 (1)| 00:00:24 | | |
- |* 5 | FILTER | | | | | | | |
- | 6 | NESTED LOOPS OUTER | | 55 | 14740 | 1997 (1)| 00:00:24 | | |
- | 7 | NESTED LOOPS | | 1088 | 233K| 1520 (1)| 00:00:19 | | |
- | 8 | NESTED LOOPS | | 93 | 14043 | 104 (1)| 00:00:02 | | |
- | 9 | NESTED LOOPS | | 3 | 270 | 10 (10)| 00:00:01 | | |
- | 10 | VIEW | VW_NSO_2 | 3 | 66 | 7 (15)| 00:00:01 | | |
- | 11 | HASH UNIQUE | | 3 | 192 | 7 (15)| 00:00:01 | | |
- |* 12 | FILTER | | | | | | | |
- |* 13 | CONNECT BY WITH FILTERING (UNIQUE) | | | | | | | |
- | 14 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 38 | 2 (0)| 00:00:01 | | |
- |* 15 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | 1 (0)| 00:00:01 | | |
- | 16 | NESTED LOOPS | | 2 | 160 | 4 (0)| 00:00:01 | | |
- | 17 | CONNECT BY PUMP | | | | | | | |
- | 18 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 2 | 76 | 2 (0)| 00:00:01 | | |
- |* 19 | INDEX RANGE SCAN | SUPDEPTID_IDX | 2 | | 1 (0)| 00:00:01 | | |
- | 20 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 68 | 1 (0)| 00:00:01 | | |
- |* 21 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | 0 (0)| 00:00:01 | | |
- | 22 | VIEW | | 31 | 1891 | 60 (0)| 00:00:01 | | |
- | 23 | UNION-ALL PARTITION | | | | | | | |
- |* 24 | INDEX RANGE SCAN | IDX1_SAMS_USERINFO | 69 | 4209 | 4 (0)| 00:00:01 | | |
- |* 25 | HASH JOIN | | 3 | 309 | 10 (10)| 00:00:01 | | |
- |* 26 | VIEW | VW_NSO_1 | 3 | 126 | 7 (15)| 00:00:01 | | |
- | 27 | SORT UNIQUE | | 3 | 192 | 7 (15)| 00:00:01 | | |
- |* 28 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | | | |
- | 29 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 38 | 2 (0)| 00:00:01 | | |
- |* 30 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | 1 (0)| 00:00:01 | | |
- | 31 | NESTED LOOPS | | 2 | 160 | 4 (0)| 00:00:01 | | |
- | 32 | CONNECT BY PUMP | | | | | | | |
- | 33 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 2 | 76 | 2 (0)| 00:00:01 | | |
- |* 34 | INDEX RANGE SCAN | SUPDEPTID_IDX | 2 | | 1 (0)| 00:00:01 | | |
- |* 35 | INDEX RANGE SCAN | IDX_SAMS_USERINFO_DIMISSION | 46 | 2806 | 3 (0)| 00:00:01 | | |
- |* 36 | TABLE ACCESS BY GLOBAL INDEX ROWID | SAMS_CHECKINOUT | 12 | 828 | 23 (0)| 00:00:01 | ROWID | ROWID |
- |* 37 | INDEX RANGE SCAN | IDX_SAMS_CHECKINOUT | 19 | | 3 (0)| 00:00:01 | | |
- | 38 | TABLE ACCESS BY INDEX ROWID | SAMS_ICLOCK | 1 | 48 | 1 (0)| 00:00:01 | | |
- |* 39 | INDEX UNIQUE SCAN | PK_SAMS_ICLOCK | 1 | | 0 (0)| 00:00:01 | | |
- -----------------------------------------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("RN">:9)
- 2 - filter(ROWNUM<=:8)
- 4 - filter(ROWNUM<=:8)
- 5 - filter("SU"."BADGENUMBER"=SYS_OP_C2C(:5) OR "SU"."NAME" LIKE SYS_OP_C2C(:6) OR "SL"."SN"=SYS_OP_C2C(:7))
- 12 - filter(TO_DATE(:3,'yyyy-MM-dd')+1>=TO_DATE(:2,'yyyy-MM-dd'))
- 13 - access("T2"."SUPDEPTID"=PRIOR NULL)
- 15 - access("T2"."DEPTID"=SYS_OP_C2C(:4))
- 19 - access("connect$_by$_pump$_019"."prior T2.DEPTID "="T2"."SUPDEPTID")
- 21 - access("SD"."DEPTID"="DEPTID")
- 24 - access("DEPTID"="SD"."DEPTID")
- 25 - access("SD"."DEPTID"="DEPTID")
- 26 - filter("DEPTID"="SD"."DEPTID")
- 28 - access("T2"."SUPDEPTID"=PRIOR NULL)
- 30 - access("T2"."DEPTID"=SYS_OP_C2C(:1))
- 34 - access("connect$_by$_pump$_008"."prior T2.DEPTID "="T2"."SUPDEPTID")
- 35 - access("DEPTID"="SD"."DEPTID")
- 36 - filter("SC"."CHECKTIME">=TO_DATE(:2,'yyyy-MM-dd') AND "SC"."CHECKTIME"<=TO_DATE(:3,'yyyy-MM-dd')+1)
- 37 - access("SU"."BADGENUMBER"="SC"."BADGENUMBER")
- 39 - access("SL"."SN"(+)="SC"."SN")
- Plan 3
- ------
- Plan Origin :AWR
- Plan Hash Value :4243346097
- Executions :130
- Elapsed Time :33323.697 sec
- CPU Time :5608.075 sec
- Buffer Gets :168638697
- Disk Reads :156922
- Disk Writes :0
- Notes:
- 1. Statistics shown are averaged over multiple executions.
- ------------------------------------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
- ------------------------------------------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 10 | 4030 | | 59M (1)|199:40:41 | | |
- |* 1 | VIEW | | 10 | 4030 | | 59M (1)|199:40:41 | | |
- |* 2 | COUNT STOPKEY | | | | | | | | |
- | 3 | VIEW | | 55 | 21450 | | 59M (1)|199:40:41 | | |
- |* 4 | SORT ORDER BY STOPKEY | | 55 | 14740 | | 59M (1)|199:40:41 | | |
- |* 5 | HASH JOIN | | 55 | 14740 | 228M| 59M (1)|199:40:41 | | |
- | 6 | NESTED LOOPS | | 928K| 217M| | 59M (1)|199:38:25 | | |
- | 7 | NESTED LOOPS | | 928K| 217M| | 59M (1)|199:38:25 | | |
- | 8 | NESTED LOOPS | | 928K| 157M| | 58M (1)|196:32:36 | | |
- | 9 | NESTED LOOPS OUTER | | 18M| 2066M| | 21M (1)| 73:02:45 | | |
- | 10 | TABLE ACCESS BY GLOBAL INDEX ROWID | SAMS_CHECKINOUT | 18M| 1218M| | 13M (1)| 45:58:50 | ROWID | ROWID |
- |* 11 | INDEX RANGE SCAN | IDX1_SAMS_CHECKINOUT | 18M| | | 242K (1)| 00:48:26 | | |
- | 12 | TABLE ACCESS BY INDEX ROWID | SAMS_ICLOCK | 1 | 48 | | 1 (0)| 00:00:01 | | |
- |* 13 | INDEX UNIQUE SCAN | PK_SAMS_ICLOCK | 1 | | | 0 (0)| 00:00:01 | | |
- |* 14 | VIEW | | 1 | 61 | | 2 (0)| 00:00:01 | | |
- | 15 | UNION-ALL PARTITION | | | | | | | | |
- | 16 | TABLE ACCESS BY INDEX ROWID | SAMS_USERINFO | 1 | 61 | | 3 (0)| 00:00:01 | | |
- |* 17 | INDEX UNIQUE SCAN | PK_SAMS_USERINFO | 1 | | | 2 (0)| 00:00:01 | | |
- | 18 | NESTED LOOPS | | 1 | 103 | | 10 (10)| 00:00:01 | | |
- | 19 | TABLE ACCESS BY INDEX ROWID | SAMS_USERINFO_DIMISSION | 1 | 61 | | 3 (0)| 00:00:01 | | |
- |* 20 | INDEX UNIQUE SCAN | PK_SAMS_USERINFO_DIMISSION | 1 | | | 2 (0)| 00:00:01 | | |
- |* 21 | VIEW | VW_NSO_1 | 1 | 42 | | 7 (15)| 00:00:01 | | |
- | 22 | SORT UNIQUE | | 3 | 192 | | 7 (15)| 00:00:01 | | |
- |* 23 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | | | | |
- | 24 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 38 | | 2 (0)| 00:00:01 | | |
- |* 25 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | | 1 (0)| 00:00:01 | | |
- | 26 | NESTED LOOPS | | 2 | 160 | | 4 (0)| 00:00:01 | | |
- | 27 | CONNECT BY PUMP | | | | | | | | |
- | 28 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 2 | 76 | | 2 (0)| 00:00:01 | | |
- |* 29 | INDEX RANGE SCAN | SUPDEPTID_IDX | 2 | | | 1 (0)| 00:00:01 | | |
- |* 30 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | | 0 (0)| 00:00:01 | | |
- | 31 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 68 | | 1 (0)| 00:00:01 | | |
- | 32 | VIEW | VW_NSO_2 | 3 | 66 | | 7 (15)| 00:00:01 | | |
- | 33 | HASH UNIQUE | | 3 | 192 | | 7 (15)| 00:00:01 | | |
- |* 34 | FILTER | | | | | | | | |
- |* 35 | CONNECT BY WITH FILTERING (UNIQUE) | | | | | | | | |
- | 36 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 38 | | 2 (0)| 00:00:01 | | |
- |* 37 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | | 1 (0)| 00:00:01 | | |
- | 38 | NESTED LOOPS | | 2 | 160 | | 4 (0)| 00:00:01 | | |
- | 39 | CONNECT BY PUMP | | | | | | | | |
- | 40 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 2 | 76 | | 2 (0)| 00:00:01 | | |
- |* 41 | INDEX RANGE SCAN | SUPDEPTID_IDX | 2 | | | 1 (0)| 00:00:01 | | |
- ------------------------------------------------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("RN">:9)
- 2 - filter(ROWNUM<=:8)
- 4 - filter(ROWNUM<=:8)
- 5 - access("SD"."DEPTID"="DEPTID")
- 11 - access("SC"."CHECKTIME">=TO_DATE(:2,'yyyy-MM-dd') AND "SC"."CHECKTIME"<=TO_DATE(:3,'yyyy-MM-dd')+1)
- 13 - access("SL"."SN"(+)="SC"."SN")
- 14 - filter("SU"."BADGENUMBER"=SYS_OP_C2C(:5) OR "SU"."NAME" LIKE SYS_OP_C2C(:6) OR "SL"."SN"=SYS_OP_C2C(:7))
- 17 - access("BADGENUMBER"="SC"."BADGENUMBER")
- 20 - access("BADGENUMBER"="SC"."BADGENUMBER")
- 21 - filter("SD"."DEPTID"="DEPTID")
- 23 - access("T2"."SUPDEPTID"=PRIOR NULL)
- 25 - access("T2"."DEPTID"=SYS_OP_C2C(:1))
- 29 - access("connect$_by$_pump$_008"."prior T2.DEPTID "="T2"."SUPDEPTID")
- 30 - access("SD"."DEPTID"="SU"."DEPTID")
- 34 - filter(TO_DATE(:3,'yyyy-MM-dd')+1>=TO_DATE(:2,'yyyy-MM-dd'))
- 35 - access("T2"."SUPDEPTID"=PRIOR NULL)
- 37 - access("T2"."DEPTID"=SYS_OP_C2C(:4))
- 41 - access("connect$_by$_pump$_019"."prior T2.DEPTID "="T2"."SUPDEPTID")
- -------------------------------------------------------------------------------
- SQL>
- SQL>
- SQL>
- SQL> !ora ddl sams index IDX1_SAMS_CHECKINOUT
- Session altered.
- DBMS_METADATA.GET_DDL(UPPER('INDEX'),UPPER('IDX1_SAMS_CHECKINOUT'),UPPER('SAMS')
- --------------------------------------------------------------------------------
- CREATE INDEX "SAMS"."IDX1_SAMS_CHECKINOUT" ON "SAMS"."SAMS_CHECKINOUT" ("CHECK
- TIME", "BADGENUMBER", "ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATIS
- TICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
- PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
- BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
- TABLESPACE "SAMSDATA"
通过自动优化功能，我们看到了跟awr报告上一样的执行计划。
- ------------------------------------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
- ------------------------------------------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 10 | 4030 | | 59M (1)|199:40:41 | | |
- |* 1 | VIEW | | 10 | 4030 | | 59M (1)|199:40:41 | | |
- |* 2 | COUNT STOPKEY | | | | | | | | |
- | 3 | VIEW | | 55 | 21450 | | 59M (1)|199:40:41 | | |
- |* 4 | SORT ORDER BY STOPKEY | | 55 | 14740 | | 59M (1)|199:40:41 | | |
- |* 5 | HASH JOIN | | 55 | 14740 | 228M| 59M (1)|199:40:41 | | |
- | 6 | NESTED LOOPS | | 928K| 217M| | 59M (1)|199:38:25 | | |
- | 7 | NESTED LOOPS | | 928K| 217M| | 59M (1)|199:38:25 | | |
- | 8 | NESTED LOOPS | | 928K| 157M| | 58M (1)|196:32:36 | | |
- | 9 | NESTED LOOPS OUTER | | 18M| 2066M| | 21M (1)| 73:02:45 | | |
- | 10 | TABLE ACCESS BY GLOBAL INDEX ROWID | SAMS_CHECKINOUT | 18M| 1218M| | 13M (1)| 45:58:50 | ROWID | ROWID |
- |* 11 | INDEX RANGE SCAN | IDX1_SAMS_CHECKINOUT | 18M| | | 242K (1)| 00:48:26 | | |
- | 12 | TABLE ACCESS BY INDEX ROWID | SAMS_ICLOCK | 1 | 48 | | 1 (0)| 00:00:01 | | |
- |* 13 | INDEX UNIQUE SCAN | PK_SAMS_ICLOCK | 1 | | | 0 (0)| 00:00:01 | | |
- |* 14 | VIEW | | 1 | 61 | | 2 (0)| 00:00:01 | | |
- | 15 | UNION-ALL PARTITION | | | | | | | | |
- | 16 | TABLE ACCESS BY INDEX ROWID | SAMS_USERINFO | 1 | 61 | | 3 (0)| 00:00:01 | | |
- |* 17 | INDEX UNIQUE SCAN | PK_SAMS_USERINFO | 1 | | | 2 (0)| 00:00:01 | | |
- | 18 | NESTED LOOPS | | 1 | 103 | | 10 (10)| 00:00:01 | | |
- | 19 | TABLE ACCESS BY INDEX ROWID | SAMS_USERINFO_DIMISSION | 1 | 61 | | 3 (0)| 00:00:01 | | |
- |* 20 | INDEX UNIQUE SCAN | PK_SAMS_USERINFO_DIMISSION | 1 | | | 2 (0)| 00:00:01 | | |
- |* 21 | VIEW | VW_NSO_1 | 1 | 42 | | 7 (15)| 00:00:01 | | |
- | 22 | SORT UNIQUE | | 3 | 192 | | 7 (15)| 00:00:01 | | |
- |* 23 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | | | | |
- | 24 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 38 | | 2 (0)| 00:00:01 | | |
- |* 25 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | | 1 (0)| 00:00:01 | | |
- | 26 | NESTED LOOPS | | 2 | 160 | | 4 (0)| 00:00:01 | | |
- | 27 | CONNECT BY PUMP | | | | | | | | |
- | 28 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 2 | 76 | | 2 (0)| 00:00:01 | | |
- |* 29 | INDEX RANGE SCAN | SUPDEPTID_IDX | 2 | | | 1 (0)| 00:00:01 | | |
- |* 30 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | | 0 (0)| 00:00:01 | | |
- | 31 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 68 | | 1 (0)| 00:00:01 | | |
- | 32 | VIEW | VW_NSO_2 | 3 | 66 | | 7 (15)| 00:00:01 | | |
- | 33 | HASH UNIQUE | | 3 | 192 | | 7 (15)| 00:00:01 | | |
- |* 34 | FILTER | | | | | | | | |
- |* 35 | CONNECT BY WITH FILTERING (UNIQUE) | | | | | | | | |
- | 36 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 38 | | 2 (0)| 00:00:01 | | |
- |* 37 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | | 1 (0)| 00:00:01 | | |
- | 38 | NESTED LOOPS | | 2 | 160 | | 4 (0)| 00:00:01 | | |
- | 39 | CONNECT BY PUMP | | | | | | | | |
- | 40 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 2 | 76 | | 2 (0)| 00:00:01 | | |
- |* 41 | INDEX RANGE SCAN | SUPDEPTID_IDX | 2 | | | 1 (0)| 00:00:01 | | |
- ------------------------------------------------------------------------------------------------------------------------------------------------------
从执行计划上看sql执行效率非常的不好，开销是从id为10的一步开始骤降：TABLE ACCESS BY GLOBAL INDEX ROWID， 其操作是对IDX1_SAMS_CHECKINOUT这个索引的范围扫描回表拿数据
删除这个索引，然后重新创建ID列的索引
- drop index "SAMS"."IDX1_SAMS_CHECKINOUT";
- CREATE INDEX "SAMS"."IDX1_SAMS_CHECKINOUT" ON "SAMS"."SAMS_CHECKINOUT" ("ID")
针对其他性能较差的sql进行优化
- Schema Name: SAMS
- SQL ID : bfv69ds34p99n
- SQL Text : SELECT ID,SN_ID,CMDCONTENT,CMDCOMMITTIME,CMDTRANSTIME,CMDOVERTIME
- ,CMDRETURN,USERID FROM SAMS_DEVCMDS WHERE CMDCONTENT IS NOT NULL
- AND CMDTRANSTIME IS NULL AND SN_ID=:1 AND ROWNUM<100 ORDER BY
- TO_NUMBER(ID)
- Bind Variables :
- 1 - (VARCHAR2(128)):6538992526356
- -------------------------------------------------------------------------------
- FINDINGS SECTION (1 finding)
- -------------------------------------------------------------------------------
- 1- Index Finding (see explain plans section below)
- --------------------------------------------------
- The execution plan of this statement can be improved by creating one or more
- indices.
- Recommendation (estimated benefit: 92.69%)
- ------------------------------------------
- - Consider running the Access Advisor to improve the physical schema design
- or creating the recommended index.
- create index SAMS.IDX$$_15B1A0001 on SAMS.SAMS_DEVCMDS("SN_ID","CMDTRANSTIME"); -------------------根据建议创建这个索引
- Rationale
- ---------
- Creating the recommended indices significantly improves the execution plan
- of this statement. However, it might be preferable to run "Access Advisor"
- using a representative SQL workload as opposed to a single statement. This
- will allow to get comprehensive index recommendations which takes into
- account index maintenance overhead and additional space consumption.
- -------------------------------------------------------------------------------
- EXPLAIN PLANS SECTION
- -------------------------------------------------------------------------------
- 1- Original
- -----------
- Plan hash value: 2086948762
- --------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 87 | 172K| 630 (1)| 00:00:08 |
- | 1 | SORT ORDER BY | | 87 | 172K| 630 (1)| 00:00:08 |
- |* 2 | COUNT STOPKEY | | | | | |
- |* 3 | TABLE ACCESS BY INDEX ROWID| SAMS_DEVCMDS | 87 | 172K| 629 (0)| 00:00:08 |
- |* 4 | INDEX RANGE SCAN | IDX_SAMS_DEVCMDS | 908 | | 16 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter(ROWNUM<100)
- 3 - filter("CMDTRANSTIME" IS NULL AND "CMDCONTENT" IS NOT NULL)
- 4 - access("SN_ID"=SYS_OP_C2C(:1))
- 2- Using New Indices
- --------------------
- Plan hash value: 3352898769
- -------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 87 | 172K| 46 (3)| 00:00:01 |
- | 1 | SORT ORDER BY | | 87 | 172K| 46 (3)| 00:00:01 |
- |* 2 | COUNT STOPKEY | | | | | |
- |* 3 | TABLE ACCESS BY INDEX ROWID| SAMS_DEVCMDS | 87 | 172K| 45 (0)| 00:00:01 |
- |* 4 | INDEX RANGE SCAN | IDX$$_15B1A0001 | 95 | | 3 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter(ROWNUM<100)
- 3 - filter("CMDCONTENT" IS NOT NULL)
- 4 - access("SN_ID"=SYS_OP_C2C(:1) AND "CMDTRANSTIME" IS NULL)
创建索引如下，都是根据oracle建议做的。：
- create index SAMS.IDX$$_15B1D0001 on SAMS.SAMS_USERINFO_DIMISSION("DEPTID") TABLESPACE "SAMSDATA" ;
- create index SAMS.IDX$$_15B1A0001 on SAMS.SAMS_DEVCMDS("SN_ID","CMDTRANSTIME");
- create index SAMS.IDX$$_15B3A0001 on SAMS.SAMS_CHECKINOUT_BAK(SYS_OP_C2C("BADGENUMBER")) TABLESPACE "SAMSDATA" parallel 12;
- Tune the sql
- ~~~~~~~~~~~~
- GENERAL INFORMATION SECTION
- -------------------------------------------------------------------------------
- Tuning Task Name : TASK_88888
- Tuning Task Owner : SYS
- Workload Type : Single SQL Statement
- Scope : COMPREHENSIVE
- Time Limit(seconds): 1800
- Completion Status : COMPLETED
- Started at : 03/30/2017 11:31:00
- Completed at : 03/30/2017 11:46:57
- -------------------------------------------------------------------------------
- Schema Name: SAMS
- SQL ID : cpbt6x15q273d
- SQL Text : select count(1) from(select aa.badgenumber,case when valids is
- not null then '是' else '否' end as valids from(select
- distinct(su.badgenumber),(select distinct('是') from
- sams_template st where st.badgenumber=su.badgenumber and
- st.template is not null and st.deltag = '0') as valids from
- SAMS_USERINFO su inner join SAMS_DEPARTMENTS sd on
- su.deptid=sd.deptid where 1=1 and su.SYSTAG='1' and sd.deptid
- in ( select T2.Deptid from SAMS_DEPARTMENTS T2 start with
- T2.Deptid = '000000' connect by prior T2.DEPTID = T2.SUPDEPTID)
- ) aa where 1=1 )
- -------------------------------------------------------------------------------
- FINDINGS SECTION (2 findings)
- -------------------------------------------------------------------------------
- 1- SQL Profile Finding (see explain plans section below)
- --------------------------------------------------------
- 2 potentially better execution plans were found for this statement. Choose
- one of the following SQL profiles to implement.
- Recommendation (estimated benefit: 98.35%)
- ------------------------------------------
- - Consider accepting the recommended SQL profile.
- execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_88888',
- task_owner => 'SYS', replace => TRUE);
- Recommendation (estimated benefit: 99.98%)
- ------------------------------------------
- - Consider accepting the recommended SQL profile to use parallel execution
- for this statement.
- execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_88888',
- task_owner => 'SYS', replace => TRUE, profile_type =>
- DBMS_SQLTUNE.PX_PROFILE);
- Executing this query parallel with DOP 96 will improve its response time
- 99.37% over the SQL profile plan. However, there is some cost in enabling
- parallel execution. It will increase the statement's resource consumption by
- an estimated 39.06% which may result in a reduction of system throughput.
- Also, because these resources are consumed over a much smaller duration, the
- response time of concurrent statements might be negatively impacted if
- sufficient hardware capacity is not available.
- The following data shows some sampled statistics for this SQL from the past
- week and projected weekly values when parallel execution is enabled.
- Past week sampled statistics for this SQL
- -----------------------------------------
- Number of executions 1241
- Percent of total activity .75
- Percent of samples with #Active Sessions > 2*CPU 42.16
- Weekly DB time (in sec) 462921.68
- Projected statistics with Parallel Execution
- --------------------------------------------
- Weekly DB time (in sec) 643740.64
- 2- Alternative Plan Finding
- ---------------------------
- Some alternative execution plans for this statement were found by searching
- the system's real-time and historical performance data.
- The following table lists these plans ranked by their average elapsed time.
- See section "ALTERNATIVE PLANS SECTION" for detailed information on each
- plan.
- id plan hash last seen elapsed (s) origin note
- -- ---------- -------------------- ------------ --------------- ----------------
- 1 1614405204 2017-03-30/09:32:49 7132.651 Cursor Cache original plan
- 2 2181297630 2017-03-29/19:00:16 19363.989 AWR
- Information
- -----------
- - The Original Plan appears to have the best performance, based on the
- elapsed time per execution. However, if you know that one alternative
- plan is better than the Original Plan, you can create a SQL plan baseline
- for it. This will instruct the Oracle optimizer to pick it over any other
- choices in the future.
- execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_88888',
- owner_name => 'SYS', plan_hash_value => xxxxxxxx);
- -------------------------------------------------------------------------------
- EXPLAIN PLANS SECTION
- -------------------------------------------------------------------------------
- 1- Original With Adjusted Cost
- ------------------------------
- Plan hash value: 1614405204
- --------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 2 | | 1683K (1)| 05:36:42 |
- | 1 | SORT AGGREGATE | | 1 | 2 | | | |
- | 2 | VIEW | | 18606 | 37212 | | 1683K (1)| 05:36:42 |
- | 3 | SORT UNIQUE NOSORT | | 3 | 7833 | | 7 (15)| 00:00:01 |
- |* 4 | TABLE ACCESS BY INDEX ROWID | SAMS_TEMPLATE | 3 | 7833 | | 6 (0)| 00:00:01 |
- |* 5 | INDEX RANGE SCAN | IDX$$_CBE40001 | 3 | | | 3 (0)| 00:00:01 |
- | 6 | HASH UNIQUE | | 18606 | 1835K| 170M| 1683K (1)| 05:36:42 |
- | 7 | NESTED LOOPS | | 1571K| 151M| | 1670K (1)| 05:34:05 |
- | 8 | NESTED LOOPS | | 3513K| 151M| | 1670K (1)| 05:34:05 |
- | 9 | NESTED LOOPS | | 50926 | 2188K| | 7 (15)| 00:00:01 |
- | 10 | VIEW | VW_NSO_1 | 3 | 66 | | 7 (15)| 00:00:01 |
- | 11 | HASH UNIQUE | | 3 | 252 | | 7 (15)| 00:00:01 |
- |* 12 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | | |
- | 13 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 38 | | 2 (0)| 00:00:01 |
- |* 14 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | | 1 (0)| 00:00:01 |
- | 15 | NESTED LOOPS | | 2 | 160 | | 4 (0)| 00:00:01 |
- | 16 | CONNECT BY PUMP | | | | | | |
- | 17 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 2 | 76 | | 2 (0)| 00:00:01 |
- |* 18 | INDEX RANGE SCAN | SUPDEPTID_IDX | 2 | | | 1 (0)| 00:00:01 |
- |* 19 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 16975 | 364K| | 0 (0)| 00:00:01 |
- |* 20 | INDEX RANGE SCAN | IDX_SAMS_USERINFO | 69 | | | 3 (0)| 00:00:01 |
- |* 21 | TABLE ACCESS BY INDEX ROWID | SAMS_USERINFO | 31 | 1767 | | 69 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 4 - filter("ST"."TEMPLATE" IS NOT NULL)
- 5 - access("ST"."DELTAG"='0' AND "ST"."BADGENUMBER"=:B1)
- 12 - access("T2"."SUPDEPTID"=PRIOR NULL)
- 14 - access("T2"."DEPTID"=U'000000')
- 18 - access("connect$_by$_pump$_008"."prior T2.DEPTID "="T2"."SUPDEPTID")
- 19 - access("SD"."DEPTID"="DEPTID")
- 20 - access("SU"."DEPTID"="SD"."DEPTID")
- 21 - filter("SU"."SYSTAG"=U'1')
- 2- Using SQL Profile
- --------------------
- Plan hash value: 1215825753
- -------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 2 | | 27702 (1)| 00:05:33 |
- | 1 | SORT AGGREGATE | | 1 | 2 | | | |
- | 2 | VIEW | | 18606 | 37212 | | 27702 (1)| 00:05:33 |
- | 3 | SORT UNIQUE NOSORT | | 3 | 7833 | | 7 (15)| 00:00:01 |
- |* 4 | TABLE ACCESS BY INDEX ROWID | SAMS_TEMPLATE | 3 | 7833 | | 6 (0)| 00:00:01 |
- |* 5 | INDEX RANGE SCAN | IDX$$_CBE40001 | 3 | | | 3 (0)| 00:00:01 |
- | 6 | HASH UNIQUE | | 18606 | 1835K| 170M| 27702 (1)| 00:05:33 |
- |* 7 | HASH JOIN | | 1571K| 151M| | 14640 (1)| 00:02:56 |
- |* 8 | TABLE ACCESS FULL | SAMS_USERINFO | 18606 | 1035K| | 14627 (1)| 00:02:56 |
- | 9 | NESTED LOOPS | | 50926 | 2188K| | 7 (15)| 00:00:01 |
- | 10 | VIEW | VW_NSO_1 | 3 | 66 | | 7 (15)| 00:00:01 |
- | 11 | HASH UNIQUE | | 3 | 252 | | 7 (15)| 00:00:01 |
- |* 12 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | | |
- | 13 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 1 | 38 | | 2 (0)| 00:00:01 |
- |* 14 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 1 | | | 1 (0)| 00:00:01 |
- | 15 | NESTED LOOPS | | 2 | 160 | | 4 (0)| 00:00:01 |
- | 16 | CONNECT BY PUMP | | | | | | |
- | 17 | TABLE ACCESS BY INDEX ROWID | SAMS_DEPARTMENTS | 2 | 76 | | 2 (0)| 00:00:01 |
- |* 18 | INDEX RANGE SCAN | SUPDEPTID_IDX | 2 | | | 1 (0)| 00:00:01 |
- |* 19 | INDEX UNIQUE SCAN | PK_SAMS_DEPARTMENTS | 16975 | 364K| | 0 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 4 - filter("ST"."TEMPLATE" IS NOT NULL)
- 5 - access("ST"."DELTAG"='0' AND "ST"."BADGENUMBER"=:B1)
- 7 - access("SU"."DEPTID"="SD"."DEPTID")
- 8 - filter("SU"."SYSTAG"=U'1')
- 12 - access("T2"."SUPDEPTID"=PRIOR NULL)
- 14 - access("T2"."DEPTID"=U'000000')
- 18 - access("connect$_by$_pump$_008"."prior T2.DEPTID "="T2"."SUPDEPTID")
- 19 - access("SD"."DEPTID"="DEPTID")
对以上sql进行了执行计划绑定修改
- execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_88888',task_owner => 'SYS', replace => TRUE);
- SQL> select NAME from dba_sql_profiles;
- NAME
- ------------------------------
- coe_8yshc4jbu0qc1_1759800418
- SYS_SQLPROF_015b1d66e6010001
- SYS_SQLPROF_015b1af651db0000
- coe_bw0b67268pva8_561269195
- coe_fq1q2q6h7kqf0_561269195
- 5 rows selected.
- SQL> select SQL_TEXT from dba_sql_profiles where name ='SYS_SQLPROF_015b1d66e6010001';
- SQL_TEXT
- --------------------------------------------------
- select count(1) from(select aa.badgenumber,case wh
- en valids is not null then '是' else '否' end a
- s valids from(select distinct(su.badgenumber),(sel
- ect distinct('是') from sams_template st where st
- .badgenumber=su.badgenumber and st.template is not
- null and st.deltag = '0') as valids from SAMS_USE
- RINFO su inner join SAMS_DEPARTMENTS sd on su.dept
- id=sd.deptid where 1=1 and su.SYSTAG='1' and sd.
- deptid in ( select T2.Deptid from SAMS_DEPARTMENTS
- T2 start with T2.Deptid = '000000' connect by pri
- or T2.DEPTID = T2.SUPDEPTID) ) aa where 1=1 )
- 1 row selected.
- SQL> select SQL_TEXT from dba_sql_profiles where name ='SYS_SQLPROF_015b1af651db0000';
- SQL_TEXT
- --------------------------------------------------
- SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (select
- sd.deptnumber,sd.deptname,su.badgenumber,su.name,
- to_char(sc.checktime,'YYYY-MM-DD HH24:MI:SS') as C
- HECKTIME,case when rtrim(sc.verifycode)='0' then '
- 密码' when rtrim(sc.verifycode)='1' then '指纹' wh
- en rtrim(sc.verifycode)='2' then '补签' when rtrim
- (sc.verifycode)='15' then '面部' when rtrim(sc.ver
- ifycode)='ZW' then '指纹' when rtrim(sc.verifycode
- )='RL' then '面部' when rtrim(sc.verifycode)='YD'
- then '移动打卡(GPS)' when rtrim(sc.verifycode)='EJ
- ' then '国寿E家' end as verifycode,sl.sn||'('||sl
- .alias||')' as devicename, to_char(sc.insystime,'Y
- YYY-MM-DD HH24:MI:SS') as INSYSTIME from SAMS_CHEC
- KINOUT sc inner join (select badgenumber,name,dept
- id from SAMS_USERINFO union all select badgenumber
- ,name,deptid from SAMS_USERINFO_DIMISSION sd where
- 1=1 and sd.DEPTID=:1 ) su on su.badgenumber = s
- c.badgenumber inner join SAMS_DEPARTMENTS sd on sd
- .deptid = su.deptid left join SAMS_ICLOCK sl on sl
- .sn=sc.sn where 1=1 and sc.checktime>= to_date(:2
- ,'yyyy-MM-dd') and sc.checktime<= to_date(:3 ,'yy
- yy-MM-dd')+1 and sd.DEPTID=:4 order by sc.check
- time,su.name desc ) A WHERE ROWNUM <= :5 ) WHERE
- RN > :6
一共做了2个profile如上，针对2个sql，先不动了。
六个变量的sql是前台没有做关联查询的，执行情况跟9个变量的差不多
手工测试也是无法模拟awr报告上面的执行计划，在sql自动优化里面可以看到，这个sql的执行计划已经做了profile，对应的name是SYS_SQLPROF_015b1af651db0000
2017年3月31日 星期五 10:45:22
今天测试一下上面两个索引的效果
create index sams.ind1_sams_iclock on sams.sams_iclock(sn,alias) TABLESPACE "SAMSDATA" parallel 12;
alter index sams.ind1_sams_iclock noparallel;
CREATE INDEX "SAMS"."IDX5_SAMS_CHECKINOUT" ON "SAMS"."SAMS_CHECKINOUT" (BADGENUMBER,CHECKTIME,SN,VERIFYCODE,INSYSTIME) TABLESPACE "SAMSDATA" parallel 12;
效果不明显，而且影响了已经存在的sql执行，故先删除,其实感觉走了IDX5_SAMS_CHECKINOUT性能能稍微好一点,但是从测试结果上看,可能对原来的执行有影响,所以先不动了