最近，客户的一套数据库系统服务器CPU及内存消耗持续高涨，后采集AWR及ASH报告分析，发现由于存在性能问题的SQL，导致Top User Event中出现大量的CPU等待，优化存在性能问题的SQL后，CPU使用率大幅降低。下面几条SQL语句是优化前后其执行计划及统计信息的对比情况： — 原执行计划及统计信息 — 优化后执行计划及统计信息
— 原执行计划统计信息 — 优化后执行计划统计信息
— 原执行计划及统计信息 — 优化后执行计划及统计信息
— 原执行计划及统计信息 — 优化后新执行计划及统计信息
--- SQL 1:
select distinct r.kpiname, r.deptid, r.recordtime, r.kpivalue value
from sde.rs_record r
where upper(r.kpiname) = upper('BusinessSystemRunningTime')
and r.recordtime =
(select max(recordtime)
from sde.rs_record
where deptid = '437247bafb6746ea8f29925ac03258a102120100000'
and upper(kpiname) = upper('BusinessSystemRunningTime'))
and r.deptid = '437247bafb6746ea8f29925ac03258a102120100000'
order by r.recordtime asc
SQL> set lines 200
SQL> select distinct r.kpiname, r.deptid, r.recordtime, r.kpivalue value
2 from sde.rs_record r
3 where upper(r.kpiname) = upper('BusinessSystemRunningTime')
4 and r.recordtime =
5 (select max(recordtime)
6 from sde.rs_record
7 where deptid = '437247bafb6746ea8f29925ac03258a102120100000'
8 and upper(kpiname) = upper('BusinessSystemRunningTime'))
9 and r.deptid = '437247bafb6746ea8f29925ac03258a102120100000'
10 order by r.recordtime asc
11 ;
Elapsed: 00:00:00.45
Execution Plan
----------------------------------------------------------
Plan hash value: 2705261340
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75 | 19237 (1)| 00:03:51 |
| 1 | SORT ORDER BY | | 1 | 75 | | |
| 2 | HASH UNIQUE | | 1 | 75 | 19237 (1)| 00:03:51 |
|* 3 | TABLE ACCESS BY INDEX ROWID | RS_RECORD | 1 | 75 | 19 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | RS_RECORDTIME | 154 | | 3 (0)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 73 | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| RS_RECORD | 1517 | 108K| 19217 (1)| 00:03:51 |
|* 7 | INDEX RANGE SCAN | RS_DEPTID | 151K| | 1173 (1)| 00:00:15 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("R"."DEPTID"='437247bafb6746ea8f29925ac03258a102120100000' AND
UPPER("R"."KPINAME")='BUSINESSSYSTEMRUNNINGTIME')
4 - access("R"."RECORDTIME"= (SELECT MAX("RECORDTIME") FROM "SDE"."RS_RECORD"
"RS_RECORD" WHERE "DEPTID"='437247bafb6746ea8f29925ac03258a102120100000' AND
UPPER("KPINAME")='BUSINESSSYSTEMRUNNINGTIME'))
6 - filter(UPPER("KPINAME")='BUSINESSSYSTEMRUNNINGTIME')
7 - access("DEPTID"='437247bafb6746ea8f29925ac03258a102120100000')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
20385 consistent gets
0 physical reads
0 redo size
789 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select distinct r.kpiname, r.deptid, r.recordtime, r.kpivalue value
2 from sde.rs_record r
3 where upper(r.kpiname) = upper('BusinessSystemRunningTime')
4 and r.recordtime =
5 (select max(recordtime)
6 from sde.rs_record
7 where deptid = '437247bafb6746ea8f29925ac03258a102120100000'
8 and upper(kpiname) = upper('BusinessSystemRunningTime'))
9 and r.deptid = '437247bafb6746ea8f29925ac03258a102120100000'
10 order by r.recordtime asc;
Elapsed: 00:00:00.43
Execution Plan
----------------------------------------------------------
Plan hash value: 4202252648
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75 | 2174 (1)| 00:00:27 |
| 1 | SORT ORDER BY | | 1 | 75 | | |
| 2 | HASH UNIQUE | | 1 | 75 | 2174 (1)| 00:00:27 |
|* 3 | TABLE ACCESS BY INDEX ROWID | RS_RECORD | 1 | 75 | 19 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | RS_RECORDTIME | 154 | | 3 (0)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 73 | | |
| 6 | TABLE ACCESS BY INDEX ROWID| RS_RECORD | 1517 | 108K| 2154 (1)| 00:00:26 |
|* 7 | INDEX RANGE SCAN | RS_RECORD_DEPTKPI | 3392 | | 41 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("R"."DEPTID"='437247bafb6746ea8f29925ac03258a102120100000' AND
UPPER("R"."KPINAME")='BUSINESSSYSTEMRUNNINGTIME')
4 - access("R"."RECORDTIME"= (SELECT MAX("RECORDTIME") FROM "SDE"."RS_RECORD" "RS_RECORD"
WHERE UPPER("KPINAME")='BUSINESSSYSTEMRUNNINGTIME' AND
"DEPTID"='437247bafb6746ea8f29925ac03258a102120100000'))
7 - access("DEPTID"='437247bafb6746ea8f29925ac03258a102120100000' AND
UPPER("KPINAME")='BUSINESSSYSTEMRUNNINGTIME')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
13972 consistent gets
0 physical reads
0 redo size
789 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
--- SQL 2:
select distinct u.username, u.userid, p.depid
from sde.sys_user u, sde.conf_runlog run, sde.sys_person p
where u.userid = run.operatorid
and u.personid = p.personid
and run.operatetype = 'CS????'
and to_char(run.operatetime, 'yyyy-mm-dd') = '2013-09-04'
SQL> select distinct u.username, u.userid, p.depid
2 from sde.sys_user u, sde.conf_runlog run, sde.sys_person p
3 where u.userid = run.operatorid
4 and u.personid = p.personid
5 and run.operatetype = 'CS????'
6 and to_char(run.operatetime, 'yyyy-mm-dd') = '2013-09-04';
no rows selected
Elapsed: 00:00:00.53
Execution Plan
----------------------------------------------------------
Plan hash value: 1967854316
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 236 | 11242 (1)| 00:02:15 |
| 1 | HASH UNIQUE | | 1 | 236 | 11242 (1)| 00:02:15 |
| 2 | NESTED LOOPS | | 1 | 236 | 11241 (1)| 00:02:15 |
| 3 | NESTED LOOPS | | 1 | 148 | 11240 (1)| 00:02:15 |
|* 4 | TABLE ACCESS FULL | CONF_RUNLOG | 1 | 51 | 11239 (1)| 00:02:15 |
| 5 | TABLE ACCESS BY INDEX ROWID| SYS_USER | 1 | 97 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_003 | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | SYS_PERSON | 1 | 88 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_002 | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("RUN"."OPERATETYPE"='CS????' AND
TO_CHAR(INTERNAL_FUNCTION("RUN"."OPERATETIME"),'yyyy-mm-dd')='2013-09-04')
6 - access("U"."USERID"="RUN"."OPERATORID")
8 - access("U"."PERSONID"="P"."PERSONID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
51048 consistent gets
0 physical reads
0 redo size
452 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select distinct u.username, u.userid, p.depid
2 from sde.sys_user u, sde.conf_runlog run, sde.sys_person p
3 where u.userid = run.operatorid
4 and u.personid = p.personid
5 and run.operatetype = 'CS????'
6 and to_char(run.operatetime, 'yyyy-mm-dd') = '2013-09-04';
no rows selected
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2603909728
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 236 | 7 (15)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 236 | 7 (15)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 236 | 6 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 148 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| CONF_RUNLOG | 1 | 51 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | CONF_RUNLOG_TYPETIME | 1 | | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| SYS_USER | 1 | 97 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_003 | 1 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | SYS_PERSON | 1 | 88 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_002 | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("RUN"."OPERATETYPE"='CS????' AND TO_CHAR(INTERNAL_FUNCTION("OPERATETIME"),'yyyy-m
m-dd')='2013-09-04')
7 - access("U"."USERID"="RUN"."OPERATORID")
9 - access("U"."PERSONID"="P"."PERSONID")
Statistics
----------------------------------------------------------
31 recursive calls
0 db block gets
7 consistent gets
2 physical reads
0 redo size
452 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
--- SQL 3:
select (a.conn - b.close) num
from (select count(*) conn
from sde.conf_runlog d
where to_CHAR(d.operatetime, 'yyyy-MM-dd') = '2013-09-04'
and d.operatetype = 'CS????') a,
(select count(*) close
from sde.conf_runlog d
where to_CHAR(d.operatetime, 'yyyy-MM-dd') = '2013-09-04'
and d.operatetype = 'CS????') b
SQL> select (a.conn - b.close) num
2 from (select count(*) conn
3 from sde.conf_runlog d
4 where to_CHAR(d.operatetime, 'yyyy-MM-dd') = '2013-09-04'
5 and d.operatetype = 'CS????') a,
6 (select count(*) close
7 from sde.conf_runlog d
8 where to_CHAR(d.operatetime, 'yyyy-MM-dd') = '2013-09-04'
9 and d.operatetype = 'CS????') b;
Elapsed: 00:00:00.92
Execution Plan
----------------------------------------------------------
Plan hash value: 4028634323
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 22477 (1)| 00:04:30 |
| 1 | NESTED LOOPS | | 1 | 26 | 22477 (1)| 00:04:30 |
| 2 | VIEW | | 1 | 13 | 11239 (1)| 00:02:15 |
| 3 | SORT AGGREGATE | | 1 | 17 | | |
|* 4 | TABLE ACCESS FULL| CONF_RUNLOG | 1 | 17 | 11239 (1)| 00:02:15 |
| 5 | VIEW | | 1 | 13 | 11239 (1)| 00:02:15 |
| 6 | SORT AGGREGATE | | 1 | 17 | | |
|* 7 | TABLE ACCESS FULL| CONF_RUNLOG | 1 | 17 | 11239 (1)| 00:02:15 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("D"."OPERATETYPE"='CS????' AND
TO_CHAR(INTERNAL_FUNCTION("D"."OPERATETIME"),'yyyy-MM-dd')='2013-09-04')
7 - filter("D"."OPERATETYPE"='CS????' AND
TO_CHAR(INTERNAL_FUNCTION("D"."OPERATETIME"),'yyyy-MM-dd')='2013-09-04')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
102096 consistent gets
0 physical reads
0 redo size
509 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select (a.conn - b.close) num
2 from (select count(*) conn
3 from sde.conf_runlog d
4 where to_CHAR(d.operatetime, 'yyyy-MM-dd') = '2013-09-04'
5 and d.operatetype = 'CS????') a,
6 (select count(*) close
7 from sde.conf_runlog d
8 where to_CHAR(d.operatetime, 'yyyy-MM-dd') = '2013-09-04'
9 and d.operatetype = 'CS????') b ;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2459318296
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 8 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 8 (0)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 4 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 17 | | |
|* 4 | TABLE ACCESS BY INDEX ROWID| CONF_RUNLOG | 1 | 17 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | CONF_RUNLOG_TYPETIME | 1 | | 3 (0)| 00:00:01 |
| 6 | VIEW | | 1 | 13 | 4 (0)| 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | 17 | | |
|* 8 | TABLE ACCESS BY INDEX ROWID| CONF_RUNLOG | 1 | 17 | 4 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | CONF_RUNLOG_TYPETIME | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(TO_CHAR(INTERNAL_FUNCTION("D"."OPERATETIME"),'yyyy-MM-dd')='2013-09-04')
5 - access("D"."OPERATETYPE"='CS????')
8 - filter(TO_CHAR(INTERNAL_FUNCTION("D"."OPERATETIME"),'yyyy-MM-dd')='2013-09-04')
9 - access("D"."OPERATETYPE"='CS????')
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
509 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--- SQL 4:
select s.userid, d.depid
from sde.sys_user s,
(select run.operatorid
from sde.conf_runlog run, sde.conf_rundetaillog de
where run.loggerid = de.loggerid
and ((de.operatename = '????' and de.description = '????'))) aa,
sde.sys_department d,
sde.sys_person p
where s.userid = aa.operatorid
and s.personid = p.personid
and p.depid = d.depid
union all
select u.userid, d.depid
from sde.sys_user u, sde.conf_runlog r, sde.sys_department d, sde.sys_person p
where u.userid = r.operatorid
and u.personid = p.personid
and p.depid = d.depid
and r.operatetype = 'CS????'
SQL> select s.userid, d.depid
2 from sde.sys_user s,
3 (select run.operatorid
4 from sde.conf_runlog run, sde.conf_rundetaillog de
5 where run.loggerid = de.loggerid
6 and ((de.operatename = '????' and de.description = '????'))) aa,
7 sde.sys_department d,
8 sde.sys_person p
9 where s.userid = aa.operatorid
10 and s.personid = p.personid
11 and p.depid = d.depid
12 union all
13 select u.userid, d.depid
14 from sde.sys_user u, sde.conf_runlog r, sde.sys_department d, sde.sys_person p
15 where u.userid = r.operatorid
16 and u.personid = p.personid
17 and p.depid = d.depid
18 and r.operatetype = 'CS????';
no rows selected
Elapsed: 00:00:00.46
Execution Plan
----------------------------------------------------------
Plan hash value: 3274435553
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 684 | 11266 (100)| 00:02:16 |
| 1 | UNION-ALL | | | | | |
| 2 | NESTED LOOPS | | 1 | 421 | 32 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 377 | 32 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 289 | 31 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 201 | 30 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | CONF_RUNDETAILLOG | 1 | 134 | 27 (0)| 00:00:01 |
| 7 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 8 | BITMAP AND | | | | | |
| 9 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 10 | INDEX RANGE SCAN | IND_RDL_OPE | 1452 | | 8 (0)| 00:00:01 |
| 11 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 12 | INDEX RANGE SCAN | IND_RDL_DES | 1452 | | 19 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | CONF_RUNLOG | 1 | 67 | 3 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IND_RL_LID | 1 | | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | SYS_USER | 1 | 88 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_003 | 1 | | 0 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | SYS_PERSON | 1 | 88 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_002 | 1 | | 0 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_001 | 1 | 44 | 0 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 263 | 11234 (1)| 00:02:15 |
| 21 | NESTED LOOPS | | 1 | 219 | 11234 (1)| 00:02:15 |
| 22 | NESTED LOOPS | | 1 | 131 | 11233 (1)| 00:02:15 |
|* 23 | TABLE ACCESS FULL | CONF_RUNLOG | 1 | 43 | 11232 (1)| 00:02:15 |
| 24 | TABLE ACCESS BY INDEX ROWID | SYS_USER | 1 | 88 | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_003 | 1 | | 0 (0)| 00:00:01 |
| 26 | TABLE ACCESS BY INDEX ROWID | SYS_PERSON | 1 | 88 | 1 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_002 | 1 | | 0 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_001 | 1 | 44 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("DE"."OPERATENAME"='????')
12 - access("DE"."DESCRIPTION"='????')
14 - access("RUN"."LOGGERID"="DE"."LOGGERID")
16 - access("S"."USERID"="RUN"."OPERATORID")
18 - access("S"."PERSONID"="P"."PERSONID")
19 - access("P"."DEPID"="D"."DEPID")
23 - filter("R"."OPERATETYPE"='CS????')
25 - access("U"."USERID"="R"."OPERATORID")
27 - access("U"."PERSONID"="P"."PERSONID")
28 - access("P"."DEPID"="D"."DEPID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
51051 consistent gets
0 physical reads
0 redo size
386 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select s.userid, d.depid
2 from sde.sys_user s,
3 (select run.operatorid
4 from sde.conf_runlog run, sde.conf_rundetaillog de
5 where run.loggerid = de.loggerid
6 and ((de.operatename = '????' and de.description = '????'))) aa,
7 sde.sys_department d,
8 sde.sys_person p
9 where s.userid = aa.operatorid
10 and s.personid = p.personid
11 and p.depid = d.depid
12 union all
13 select u.userid, d.depid
14 from sde.sys_user u, sde.conf_runlog r, sde.sys_department d, sde.sys_person p
15 where u.userid = r.operatorid
16 and u.personid = p.personid
17 and p.depid = d.depid
18 and r.operatetype = 'CS????';
no rows selected
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 43471384
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 686 | 40 (15)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | NESTED LOOPS | | 1 | 423 | 34 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 379 | 34 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 291 | 33 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 203 | 32 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | CONF_RUNDETAILLOG | 1 | 136 | 28 (0)| 00:00:01 |
| 7 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 8 | BITMAP AND | | | | | |
| 9 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 10 | INDEX RANGE SCAN | IND_RDL_OPE | 2085 | | 10 (0)| 00:00:01 |
| 11 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 12 | INDEX RANGE SCAN | IND_RDL_DES | 2085 | | 18 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | CONF_RUNLOG | 1 | 67 | 4 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IND_RL_LID | 1 | | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | SYS_USER | 1 | 88 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_003 | 1 | | 0 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | SYS_PERSON | 1 | 88 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_002 | 1 | | 0 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_001 | 1 | 44 | 0 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 263 | 6 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 219 | 6 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 131 | 5 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | CONF_RUNLOG | 1 | 43 | 4 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | CONF_RUNLOG_TYPETIME | 1 | | 3 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | SYS_USER | 1 | 88 | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_003 | 1 | | 0 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | SYS_PERSON | 1 | 88 | 1 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_002 | 1 | | 0 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | SYS_INDEX_SYS_001 | 1 | 44 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("DE"."OPERATENAME"='????')
12 - access("DE"."DESCRIPTION"='????')
14 - access("RUN"."LOGGERID"="DE"."LOGGERID")
16 - access("S"."USERID"="RUN"."OPERATORID")
18 - access("S"."PERSONID"="P"."PERSONID")
19 - access("P"."DEPID"="D"."DEPID")
24 - access("R"."OPERATETYPE"='CS????')
26 - access("U"."USERID"="R"."OPERATORID")
28 - access("U"."PERSONID"="P"."PERSONID")
29 - access("P"."DEPID"="D"."DEPID")
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
386 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL优化对CPU Cost的意义
2条回复