分区清理：
--范围分区示例
drop table range_part_tab purge;
--注意，此分区为范围分区
create table range_part_tab (id number,deal_date date,area_code number,contents varchar2(4000))
partition by range (deal_date)
(
partition p1 values less than (TO_DATE('2012-02-01', 'YYYY-MM-DD')),
partition p2 values less than (TO_DATE('2012-03-01', 'YYYY-MM-DD')),
partition p3 values less than (TO_DATE('2012-04-01', 'YYYY-MM-DD')),
partition p4 values less than (TO_DATE('2012-05-01', 'YYYY-MM-DD')),
partition p5 values less than (TO_DATE('2012-06-01', 'YYYY-MM-DD')),
partition p6 values less than (TO_DATE('2012-07-01', 'YYYY-MM-DD')),
partition p7 values less than (TO_DATE('2012-08-01', 'YYYY-MM-DD')),
partition p8 values less than (TO_DATE('2012-09-01', 'YYYY-MM-DD')),
partition p9 values less than (TO_DATE('2012-10-01', 'YYYY-MM-DD')),
partition p10 values less than (TO_DATE('2012-11-01', 'YYYY-MM-DD')),
partition p11 values less than (TO_DATE('2012-12-01', 'YYYY-MM-DD')),
partition p12 values less than (TO_DATE('2013-01-01', 'YYYY-MM-DD')),
partition p_max values less than (maxvalue)
)
;
--以下是插入2012年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into range_part_tab (id,deal_date,area_code,contents)
select rownum,
to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
ceil(dbms_random.value(590,599)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
--分区原理分析之普通表插入
drop table norm_tab purge;
create table norm_tab (id number,deal_date date,area_code number,contents varchar2(4000));
insert into norm_tab(id,deal_date,area_code,contents)
select rownum,
to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
ceil(dbms_random.value(590,599)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
--观察范围分区表的分区消除带来的性能优势
set linesize 1000
set autotrace traceonly
set timing on
select *
from range_part_tab
where deal_date >= TO_DATE('2012-09-04', 'YYYY-MM-DD')
and deal_date <= TO_DATE('2012-09-07', 'YYYY-MM-DD');
--比较相同语句，普通表无法用到DEAL_DATE条件进行分区消除的情况
select *
from norm_tab
where deal_date >= TO_DATE('2012-09-04', 'YYYY-MM-DD')
and deal_date <= TO_DATE('2012-09-07', 'YYYY-MM-DD');
--分区原理分析之普通表与分区表在段分配上的差异
SET LINESIZE 666
set pagesize 5000
column segment_name format a20
column partition_name format a20
column segment_type format a20
select segment_name,
partition_name,
segment_type,
bytes / 1024 / 1024 "字节数(M)",
tablespace_name
from user_segments
where segment_name IN('RANGE_PART_TAB','NORM_TAB');
分区表的性能：
普通表：
把一个表分成了多个段，提高了性能。
避免SQL中的函数调用：
drop table people purge;
drop table sex purge;
create table people (first_name varchar2(200),last_name varchar2(200),sex_id number);
create table sex (name varchar2(20), sex_id number);
insert into people (first_name,last_name,sex_id) select object_name,object_type,1 from dba_objects;
insert into sex (name,sex_id) values ('男',1);
insert into sex (name,sex_id) values ('女',2);
insert into sex (name,sex_id) values ('不详',3);
commit;
create or replace function get_sex_name(p_id sex.sex_id%type) return sex.name%type is
v_name sex.name%type;
begin
select name
into v_name
from sex
where sex_id=p_id;
return v_name;
end;
/
以下两种写法是等价的，都是为了查询people 表信息，同时通过sex 表，获取人员的性别 信息。
select sex_id,
first_name||' '||last_name full_name,
get_sex_name(sex_id) gender
from people;
select p.sex_id,
p.first_name||' '||p.last_name full_name,
sex.name
from people p, sex
where sex.sex_id=p.sex_id;
但是通过autotrace 比较观察发现两种写法性能上存在巨大差异
第一种：
第二种：
表连接的性能一定比函数调用的高。
减少SQL中的函数调用：
drop table t1 purge;
drop table t2 purge;
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
update t2 set object_id=rownum;
commit;
create or replace function f_deal1(p_name in varchar2)
return varchar2 deterministic
is
v_name varchar2(200);
begin
-- select substr(upper(p_name),1,4) into v_name from dual;
v_name:=substr(upper(p_name),1,4);
return v_name;
end;
/
create or replace function f_deal2(p_name in varchar2)
return varchar2 deterministic
is
v_name varchar2(200);
begin
select substr(upper(p_name),1,4) into v_name from dual;
-- v_name:=substr(upper(p_name),1,4);
return v_name;
end;
/
set autotrace traceonly statistics
set linesize 1000
select * from t1 where f_deal1(object_name)='FILE';
select * from t1 where f_deal2(object_name)='FILE' ;
CREATE INDEX IDX_OBJECT_NAME ON T1(f_deal2(object_name));
select * from t1 where f_deal2(object_name)='FILE' ;
select f_deal1(object_name) from t1 ;
select f_deal2(object_name) from t1 ;
select f_deal2(t1.object_name)
from t1, t2
where t1.object_id = t2.object_id
and t2.object_type LIKE '%PART%';
select *
from t2, (select f_deal2(t1.object_name), object_ID from t1) t
where t2.object_id = t.object_id
and t2.object_type LIKE '%PART%';
select name from (select rownum rn ,f_deal2(t1.object_name) name from t1) where rn>=10 and rn<=12;
select name from (select rownum rn ,f_deal2(t1.object_name) name from t1 where rownum<=12) where rn>=10 ;
select name from (select rownum rn ,f_deal2(t1.object_name) name from t1) where rn<=12;
select f_deal2(t1.object_name) name from t1 where rownum<=12;
select f_deal2(t1.object_name) name from t1 where object_id=9999999999999;
select * from t1 where f_deal2(t1.object_name)='AAAA'
select * from t1 where f_deal1(t1.object_name)='AAAA'
性能对比：
集合写法提高性能。
create or replace procedure proc_insert
as
begin
for i in 1 .. 100000
loop
insert into t values (i);
end loop;
commit;
end;
/
drop table t purge;
create table t ( x int );
set timing on
begin
for i in 1 .. 100000
loop
insert into t values (i);
end loop;
commit;
end;
/
--集合写法改造：
drop table t purge;
create table t ( x int );
insert into t select rownum from dual connect by level<=100000;
commit;
只取你需要的列，访问视图变的更快。
drop table t1 cascade constraints purge; drop table t2 cascade constraints purge; create table t1 as select * from dba_objects; create table t2 as select * from dba_objects where rownum<=10000; update t1 set object_id=rownum ; update t2 set object_id=rownum ; commit; create or replace view v_t1_join_t2 as select t2.object_id,t2.object_name,t1.object_type,t1.owner from t1,t2 where t1.object_id=t2.object_id; set autotrace traceonly set linesize 1000 select * from v_t1_join_t2; select object_id,object_name from v_t1_join_t2; alter table T1 add constraint pk_object_id primary key (OBJECT_ID); alter table T2 add constraint fk_objecdt_id foreign key (OBJECT_ID) references t1 (OBJECT_ID);
只取你所需的列，索引无需回表。
drop table t purge; create table t as select * from dba_objects; create index idx_object_id on t(object_id,object_type); set linesize 1000 set autotrace traceonly select object_id,object_type from t where object_id=28; / select * from t where object_id=28; /
只取所需要的列，表连接提速了。
--环境构造
--研究Nested Loops Join访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS PURGE;
CREATE TABLE t1 (
id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
)
;
CREATE TABLE t2 (
id NUMBER NOT NULL,
t1_id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
)
;
execute dbms_random.seed(0);
INSERT INTO t1
SELECT rownum, rownum, dbms_random.string('a', 50)
FROM dual
CONNECT BY level <= 100
ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
ORDER BY dbms_random.random;
COMMIT;
select count(*) from t1;
select count(*) from t2;
--Merge Sort Join取所有字段的情况
alter session set statistics_level=all ;
set linesize 1000
SELECT /*+ leading(t2) use_merge(t1)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---Merge Sort Join取部分字段的情况
SELECT /*+ leading(t2) use_merge(t1)*/ t1.id
FROM t1, t2
WHERE t1.id = t2.t1_id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
改写以下sql语句：
IDEP的SQL语句。
该语句存在性能问题，执行非常缓慢，极耗CPU，为了实现行列转换的需求，具体如下：
select distinct to_char(a.svcctx_id),
to_char(0),
to_char(nvl((select peer_id
from dcc_ne_config
where peer_name = a.peer),
0)),
to_char(a.priority),
to_char(nvl((select peer_id
from dcc_ne_config
where peer_name = b.peer),
0)),
to_char(b.priority),
to_char(nvl((select peer_id
from dcc_ne_config
where peer_name = c.peer),
0)),
to_char(c.priority)
from (select hopbyhop,
svcctx_id,
substr(cause,
instr(cause, 'Host = ') + 7,
instr(cause, 'Priority = ') - instr(cause, 'Host = ') - 11) peer,
substr(cause,
instr(cause, 'Priority = ') + 11,
instr(cause, 'reachable = ') -
instr(cause, 'Priority = ') - 13) priority
from dcc_sys_log
where cause like '%SC路由应答%'
and hopbyhop in (select distinct hopbyhop from dcc_sys_log)) a,
(select hopbyhop,
svcctx_id,
substr(cause,
instr(cause, 'Host = ') + 7,
instr(cause, 'Priority = ') - instr(cause, 'Host = ') - 11) peer,
substr(cause,
instr(cause, 'Priority = ') + 11,
instr(cause, 'reachable = ') -
instr(cause, 'Priority = ') - 13) priority
from dcc_sys_log
where cause like '%SC路由应答%'
and hopbyhop in (select distinct hopbyhop from dcc_sys_log)) b,
(select hopbyhop,
svcctx_id,
substr(cause,
instr(cause, 'Host = ') + 7,
instr(cause, 'Priority = ') - instr(cause, 'Host = ') - 11) peer,
substr(cause,
instr(cause, 'Priority = ') + 11,
instr(cause, 'reachable = ') -
instr(cause, 'Priority = ') - 13) priority
from dcc_sys_log
where cause like '%SC路由应答%'
and hopbyhop in (select distinct hopbyhop from dcc_sys_log)) c
where a.hopbyhop = b.hopbyhop
and a.hopbyhop = c.hopbyhop
and a.peer <> b.peer
and a.peer <> c.peer
and b.peer <> c.peer
and a.priority <> b.priority
and a.priority <> c.priority
and b.priority <> c.priority
执行计划：
Execution Plan
----------------------------------------------------------
Plan hash value: 408096778
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1941 | 159K| | 18E(100)|999:59:59 |
|* 1 | TABLE ACCESS FULL | DCC_NE_CONFIG | 1 | 28 | | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | DCC_NE_CONFIG | 1 | 28 | | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DCC_NE_CONFIG | 1 | 28 | | 3 (0)| 00:00:01 |
| 4 | HASH UNIQUE | | 1941 | 159K| | 18E(100)|999:59:59 |
| 5 | MERGE JOIN | | 18E| 15E| | 18E(100)|999:59:59 |
| 6 | MERGE JOIN | | 18E| 15E| | 32P(100)|999:59:59 |
| 7 | MERGE JOIN | | 1147T| 79P| | 1018T(100)|999:59:59 |
| 8 | SORT JOIN | | 746T| 36P| 85P| 101G (95)|999:59:59 |
|* 9 | HASH JOIN | | 746T| 36P| 70M| 4143M(100)|999:59:59 |
| 10 | TABLE ACCESS FULL | DCC_SYS_LOG | 4939K| 14M| | 14325 (1)| 00:02:52 |
|* 11 | HASH JOIN | | 151M| 7530M| 8448K| 366K (93)| 01:13:19 |
|* 12 | TABLE ACCESS FULL| DCC_SYS_LOG | 246K| 5547K| | 14352 (2)| 00:02:53 |
|* 13 | TABLE ACCESS FULL| DCC_SYS_LOG | 246K| 6994K| | 14352 (2)| 00:02:53 |
|* 14 | FILTER | | | | | | |
|* 15 | SORT JOIN | | 246K| 5547K| 15M| 16046 (2)| 00:03:13 |
|* 16 | TABLE ACCESS FULL | DCC_SYS_LOG | 246K| 5547K| | 14352 (2)| 00:02:53 |
|* 17 | SORT JOIN | | 4939K| 14M| 113M| 27667 (2)| 00:05:32 |
| 18 | TABLE ACCESS FULL | DCC_SYS_LOG | 4939K| 14M| | 14325 (1)| 00:02:52 |
|* 19 | SORT JOIN | | 4939K| 14M| 113M| 27667 (2)| 00:05:32 |
| 20 | TABLE ACCESS FULL | DCC_SYS_LOG | 4939K| 14M| | 14325 (1)| 00:02:52 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PEER_NAME"=SUBSTR(:B1,INSTR(:B2,'Host = ')+7,INSTR(:B3,'Priority =
')-INSTR(:B4,'Host = ')-11))
2 - filter("PEER_NAME"=SUBSTR(:B1,INSTR(:B2,'Host = ')+7,INSTR(:B3,'Priority =
')-INSTR(:B4,'Host = ')-11))
3 - filter("PEER_NAME"=SUBSTR(:B1,INSTR(:B2,'Host = ')+7,INSTR(:B3,'Priority =
')-INSTR(:B4,'Host = ')-11))
9 - access("HOPBYHOP"="HOPBYHOP")
11 - access("HOPBYHOP"="HOPBYHOP")
filter(SUBSTR("CAUSE",INSTR("CAUSE",'Host = ')+7,INSTR("CAUSE",'Priority =
')-INSTR("CAUSE",'Host = ')-11)<>SUBSTR("CAUSE",INSTR("CAUSE",'Host =
')+7,INSTR("CAUSE",'Priority = ')-INSTR("CAUSE",'Host = ')-11) AND
SUBSTR("CAUSE",INSTR("CAUSE",'Priority = ')+11,INSTR("CAUSE",'reachable =
')-INSTR("CAUSE",'Priority = ')-13)<>SUBSTR("CAUSE",INSTR("CAUSE",'Priority =
')+11,INSTR("CAUSE",'reachable = ')-INSTR("CAUSE",'Priority = ')-13))
12 - filter("CAUSE" LIKE '%SC路由应答%')
13 - filter("CAUSE" LIKE '%SC路由应答%')
14 - filter(SUBSTR("CAUSE",INSTR("CAUSE",'Host = ')+7,INSTR("CAUSE",'Priority =
')-INSTR("CAUSE",'Host = ')-11)<>SUBSTR("CAUSE",INSTR("CAUSE",'Host =
')+7,INSTR("CAUSE",'Priority = ')-INSTR("CAUSE",'Host = ')-11) AND
SUBSTR("CAUSE",INSTR("CAUSE",'Host = ')+7,INSTR("CAUSE",'Priority =
')-INSTR("CAUSE",'Host = ')-11)<>SUBSTR("CAUSE",INSTR("CAUSE",'Host =
')+7,INSTR("CAUSE",'Priority = ')-INSTR("CAUSE",'Host = ')-11) AND
SUBSTR("CAUSE",INSTR("CAUSE",'Priority = ')+11,INSTR("CAUSE",'reachable =
')-INSTR("CAUSE",'Priority = ')-13)<>SUBSTR("CAUSE",INSTR("CAUSE",'Priority =
')+11,INSTR("CAUSE",'reachable = ')-INSTR("CAUSE",'Priority = ')-13) AND
SUBSTR("CAUSE",INSTR("CAUSE",'Priority = ')+11,INSTR("CAUSE",'reachable =
')-INSTR("CAUSE",'Priority = ')-13)<>SUBSTR("CAUSE",INSTR("CAUSE",'Priority =
')+11,INSTR("CAUSE",'reachable = ')-INSTR("CAUSE",'Priority = ')-13))
15 - access("HOPBYHOP"="HOPBYHOP")
filter("HOPBYHOP"="HOPBYHOP")
16 - filter("CAUSE" LIKE '%SC路由应答%')
17 - access("HOPBYHOP"="HOPBYHOP")
filter("HOPBYHOP"="HOPBYHOP")
19 - access("HOPBYHOP"="HOPBYHOP")
filter("HOPBYHOP"="HOPBYHOP")
Statistics
----------------------------------------------------------
54 recursive calls
0 db block gets
128904 consistent gets
5549 physical reads
0 redo size
1017 bytes sent via SQL*Net to client
1422 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
改写方法：
----第1次改造----
动作：将NE_STATE单独成WITH子句，并用ROWNUM>=0来告知ORACLE中间记录数的情况
不过我觉的有点奇怪，以前用ROWNUM是为了让视图不要拆开与其他表关联，做
为一个整体，和今天的效果不一样，今天感觉是起到改变表驱动顺序的作用。
效果：改进了表驱动的顺序，WITH部分的视图结果集小，在前面驱动才是正确的，原先没有ROWNUM时变成在后面驱动。
具体可以通过UE的比较工具来比较老脚本的执行计划可清晰看出，改变驱动顺序后速度从3000秒提升到50秒。
第1次改造后的SQL语句如下：
with ne_state as
( select distinct dsl1.peer_id peer_id,
nvl(ne_disconnect_info.ne_state, 1) ne_state
from dcc_sys_log dsl1,
(select distinct dnl.peer_id peer_id,
decode(action,
'disconnect',
0,
'connect',
0,
1) ne_state
from dcc_sys_log dsl, dcc_ne_log dnl
where dsl.peer_id = dnl.peer_id
and ((dsl.action = 'disconnect' and
dsl.cause = '关闭对端') or
(dsl.action = 'connect' and
dsl.cause = '连接主机失败'))
and dsl.log_time =
(select max(log_time)
from dcc_sys_log
where peer_id = dnl.peer_id
and log_type = '对端交互')) ne_disconnect_info
where dsl1.peer_id = ne_disconnect_info.peer_id(+) and rownum>=0)
select distinct ne_state.peer_id peer_name,
to_char(ne_state.ne_state) peer_state,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(select distinct to_char(nvl(ne_active.active, 0))
from dcc_sys_log,
(select peer_id,
decode(action,
'active',
1,
'de-active',
0,
0) active,
max(log_time)
from dcc_sys_log
where action = 'active'
or action = 'de-active'
group by (peer_id, action)) ne_active
where dcc_sys_log.peer_id = ne_active.peer_id(+)
and dcc_sys_log.peer_id = ne_state.peer_id)
end) peer_active,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(to_char(nvl((select count(*)
from dcc_ne_log
where dcc_ne_log.result <> 1
and peer_id = ne_state.peer_id
and log_time between
trunc(sysdate) and sysdate
group by (peer_id)),
0)))
end) err_cnt,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(to_char(nvl((select count(*)
from dcc_ne_log in_dnl
where in_dnl.direction = 'recv'
and in_dnl.peer_id =
ne_state.peer_id
and log_time between
trunc(sysdate) and sysdate),
0)))
end) recv_cnt,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(to_char(nvl((select sum(length)
from dcc_ne_log in_dnl
where in_dnl.direction = 'recv'
and in_dnl.peer_id =
ne_state.peer_id
and log_time between
trunc(sysdate) and sysdate),
0)))
end) recv_byte,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(to_char(nvl((select count(*)
from dcc_ne_log in_dnl
where in_dnl.direction = 'send'
and in_dnl.peer_id =
ne_state.peer_id
and log_time between
trunc(sysdate) and sysdate),
0)))
end) send_cnt,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(to_char(nvl((select sum(length)
from dcc_ne_log in_dnl
where in_dnl.direction = 'send'
and in_dnl.peer_id =
ne_state.peer_id
and log_time between
trunc(sysdate) and sysdate),
0)))
end) send_byte
from dcc_ne_log,ne_state
where ne_state.peer_id = dcc_ne_log.peer_id(+);
第1次改造后的SQL执行计划如下：
SELECT STATEMENT, GOAL = ALL_ROWS 34310 7 336
HASH UNIQUE 6001 119421 6448734
MERGE JOIN OUTER 4414 119421 6448734
INDEX RANGE SCAN IDEPTEST IDX_DCC_SYS_LOG_PEER 596 119421 2866104
SORT JOIN 3818 2 60
VIEW IDEPTEST 3817 2 60
SORT GROUP BY 3817 2 84
TABLE ACCESS BY INDEX ROWID IDEPTEST DCC_SYS_LOG 3816 30 1260
INDEX RANGE SCAN IDEPTEST IDX_DCC_SYS_LOG_PEER 596 119421
SORT GROUP BY NOSORT 302 1 31
FILTER
TABLE ACCESS BY INDEX ROWID IDEPTEST DCC_NE_LOG 302 1 31
INDEX RANGE SCAN IDEPTEST IDX_DCC_NE_LOG_TIME 21 7302
SORT AGGREGATE 1 33
FILTER
TABLE ACCESS BY INDEX ROWID IDEPTEST DCC_NE_LOG 302 540 17820
INDEX RANGE SCAN IDEPTEST IDX_DCC_NE_LOG_TIME 21 7302
SORT AGGREGATE 1 37
FILTER
TABLE ACCESS BY INDEX ROWID IDEPTEST DCC_NE_LOG 302 540 19980
INDEX RANGE SCAN IDEPTEST IDX_DCC_NE_LOG_TIME 21 7302
SORT AGGREGATE 1 33
FILTER
TABLE ACCESS BY INDEX ROWID IDEPTEST DCC_NE_LOG 302 503 16599
INDEX RANGE SCAN IDEPTEST IDX_DCC_NE_LOG_TIME 21 7302
SORT AGGREGATE 1 37
FILTER
TABLE ACCESS BY INDEX ROWID IDEPTEST DCC_NE_LOG 302 503 18611
INDEX RANGE SCAN IDEPTEST IDX_DCC_NE_LOG_TIME 21 7302
HASH UNIQUE 34310 7 336
HASH JOIN OUTER 34309 1299 62352
VIEW IDEPTEST 34222 7 189
HASH UNIQUE 34222 7 336
COUNT
FILTER
HASH JOIN RIGHT OUTER 33949 4896255 235020240
VIEW IDEPTEST 28574 10 240
HASH UNIQUE 28574 10 1160
HASH JOIN 28573 1561 181076
HASH JOIN 28486 3874 368030
VIEW SYS VW_SQ_1 14365 41 1353
HASH GROUP BY 14365 41 2624
TABLE ACCESS FULL IDEPTEST DCC_SYS_LOG 14092 4895849 313334336
TABLE ACCESS FULL IDEPTEST DCC_SYS_LOG 14107 2462339 152665018
INDEX FAST FULL SCAN IDEPTEST IDX_DCC_NE_LOG_PEER 86 85428 1793988
INDEX FAST FULL SCAN IDEPTEST IDX_DCC_SYS_LOG_PEER 5347 4896255 117510120
INDEX FAST FULL SCAN IDEPTEST IDX_DCC_NE_LOG_PEER 86 85428 1793988
---第2次改造----
动作：构造dcc_ne_log_time的WITH子句.
效果：多次调用这个后，系统内部优化，产生自带临时表SYS_TEMP_0FD9D661A_2F9A0F1，
多次调用SYS_TEMP_0FD9D661A_2F9A0F1而非调用dcc_ne_log是有差别的，调用SYS_TEMP_0FD9D661A_2F9A0F1
极大的提升了性能，从50秒缩短为14秒！
第2次改造后SQL语句如下：
with ne_state as
(select distinct dsl1.peer_id peer_id,
nvl(ne_disconnect_info.ne_state, 1) ne_state
from dcc_sys_log dsl1,
(select distinct dnl.peer_id peer_id,
decode(action,
'disconnect',
0,
'connect',
0,
1) ne_state
from dcc_sys_log dsl, dcc_ne_log dnl
where dsl.peer_id = dnl.peer_id
and ((dsl.action = 'disconnect' and
dsl.cause = '关闭对端') or
(dsl.action = 'connect' and
dsl.cause = '连接主机失败'))
and dsl.log_time =
(select max(log_time)
from dcc_sys_log
where peer_id = dnl.peer_id
and log_type = '对端交互')) ne_disconnect_info
where dsl1.peer_id = ne_disconnect_info.peer_id(+) and rownum>=0),
dcc_ne_log_time as (select * from dcc_ne_log where log_time between trunc(sysdate) and sysdate )
select distinct ne_state.peer_id peer_name,
to_char(ne_state.ne_state) peer_state,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(select distinct to_char(nvl(ne_active.active, 0))
from dcc_sys_log,
(select peer_id,
decode(action,
'active',
1,
'de-active',
0,
0) active,
max(log_time)
from dcc_sys_log
where action = 'active'
or action = 'de-active'
group by (peer_id, action)) ne_active
where dcc_sys_log.peer_id = ne_active.peer_id(+)
and dcc_sys_log.peer_id = ne_state.peer_id)
end) peer_active,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(to_char(nvl((select count(*)
from dcc_ne_log_time
where dcc_ne_log_time.result <> 1
and peer_id = ne_state.peer_id
group by (peer_id)),
0)))
end) err_cnt,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(to_char(nvl((select count(*)
from dcc_ne_log_time in_dnl
where in_dnl.direction = 'recv'
and in_dnl.peer_id =
ne_state.peer_id),
0)))
end) recv_cnt,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(to_char(nvl((select sum(length)
from dcc_ne_log_time in_dnl
where in_dnl.direction = 'recv'
and in_dnl.peer_id =
ne_state.peer_id),
0)))
end) recv_byte,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(to_char(nvl((select count(*)
from dcc_ne_log_time in_dnl
where in_dnl.direction = 'send'
and in_dnl.peer_id =
ne_state.peer_id),
0)))
end) send_cnt,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(to_char(nvl((select sum(length)
from dcc_ne_log_time in_dnl
where in_dnl.direction = 'send'
and in_dnl.peer_id =
ne_state.peer_id),
0)))
end) send_byte
from dcc_ne_log_time,ne_state
where ne_state.peer_id = dcc_ne_log_time.peer_id(+);
第2次改造后的SQL执行计划如下：
SELECT STATEMENT, GOAL = ALL_ROWS 34584 7 336
HASH UNIQUE 6001 119421 6448734
MERGE JOIN OUTER 4414 119421 6448734
INDEX RANGE SCAN IDEPTEST IDX_DCC_SYS_LOG_PEER 596 119421 2866104
SORT JOIN 3818 2 60
VIEW IDEPTEST 3817 2 60
SORT GROUP BY 3817 2 84
TABLE ACCESS BY INDEX ROWID IDEPTEST DCC_SYS_LOG 3816 30 1260
INDEX RANGE SCAN IDEPTEST IDX_DCC_SYS_LOG_PEER 596 119421
SORT GROUP BY NOSORT 58 7 238
VIEW IDEPTEST 58 7302 248268
TABLE ACCESS FULL SYS SYS_TEMP_0FD9D661A_2F9A0F1 58 7302 1891218
SORT AGGREGATE 1 53
VIEW IDEPTEST 58 7302 387006
TABLE ACCESS FULL SYS SYS_TEMP_0FD9D661A_2F9A0F1 58 7302 1891218
SORT AGGREGATE 1 57
VIEW IDEPTEST 58 7302 416214
TABLE ACCESS FULL SYS SYS_TEMP_0FD9D661A_2F9A0F1 58 7302 1891218
SORT AGGREGATE 1 53
VIEW IDEPTEST 58 7302 387006
TABLE ACCESS FULL SYS SYS_TEMP_0FD9D661A_2F9A0F1 58 7302 1891218
SORT AGGREGATE 1 57
VIEW IDEPTEST 58 7302 416214
TABLE ACCESS FULL SYS SYS_TEMP_0FD9D661A_2F9A0F1 58 7302 1891218
TEMP TABLE TRANSFORMATION
LOAD AS SELECT
COUNT
FILTER
FILTER
TABLE ACCESS BY INDEX ROWID IDEPTEST DCC_NE_LOG 302 7302 1891218
INDEX RANGE SCAN IDEPTEST IDX_DCC_NE_LOG_TIME 21 7302
HASH UNIQUE 34282 7 336
HASH JOIN OUTER 34281 7 336
VIEW IDEPTEST 34222 7 189
HASH UNIQUE 34222 7 336
COUNT
FILTER
HASH JOIN RIGHT OUTER 33949 4896255 235020240
VIEW IDEPTEST 28574 10 240
HASH UNIQUE 28574 10 1160
HASH JOIN 28573 1561 181076
HASH JOIN 28486 3874 368030
VIEW SYS VW_SQ_1 14365 41 1353
HASH GROUP BY 14365 41 2624
TABLE ACCESS FULL IDEPTEST DCC_SYS_LOG 14092 4895849 313334336
TABLE ACCESS FULL IDEPTEST DCC_SYS_LOG 14107 2462339 152665018
INDEX FAST FULL SCAN IDEPTEST IDX_DCC_NE_LOG_PEER 86 85428 1793988
INDEX FAST FULL SCAN IDEPTEST IDX_DCC_SYS_LOG_PEER 5347 4896255 117510120
VIEW IDEPTEST 58 7302 153342
TABLE ACCESS FULL SYS SYS_TEMP_0FD9D661A_2F9A0F1 58 7302 1891218
----第3次改进-----
动作：部分SQL进行等价改写
将 select distinct to_char(nvl(ne_active.active, 0))
from dcc_sys_log,
(select peer_id,
decode(action,
'active',
1,
'de-active',
0,
0) active,
max(log_time)
from dcc_sys_log
where action = 'active'
or action = 'de-active'
group by (peer_id, action)) ne_active
where dcc_sys_log.peer_id = ne_active.peer_id(+)
and dcc_sys_log.peer_id = ne_state.peer_id
修改为：
NVL((select '1' from dcc_sys_log where peer_id = ne_state.peer_id and action = 'active' and rownum=1),'0')
效果：改写的写法dcc_sys_log表的扫描1次数，而未改写时是扫描dcc_sys_log表2次
性能提升，执行速度由15秒变为10秒
第3次改造后的SQL语句如下：
with ne_state as
(select distinct dsl1.peer_id peer_id,
nvl(ne_disconnect_info.ne_state, 1) ne_state
from dcc_sys_log dsl1,
(select distinct dnl.peer_id peer_id,
decode(action,
'disconnect',
0,
'connect',
0,
1) ne_state
from dcc_sys_log dsl, dcc_ne_log dnl
where dsl.peer_id = dnl.peer_id
and ((dsl.action = 'disconnect' and
dsl.cause = '关闭对端') or
(dsl.action = 'connect' and
dsl.cause = '连接主机失败'))
and dsl.log_time =
(select max(log_time)
from dcc_sys_log
where peer_id = dnl.peer_id
and log_type = '对端交互')) ne_disconnect_info
where dsl1.peer_id = ne_disconnect_info.peer_id(+) and rownum>=0),
dcc_ne_log_time as (select * from dcc_ne_log where log_time between trunc(sysdate) and sysdate )
select distinct ne_state.peer_id peer_name,
to_char(ne_state.ne_state) peer_state,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
NVL((select '1' from dcc_sys_log where peer_id = ne_state.peer_id and action = 'active' and rownum=1),'0')
end) peer_active,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(to_char(nvl((select count(*)
from dcc_ne_log_time
where dcc_ne_log_time.result <> 1
and peer_id = ne_state.peer_id
group by (peer_id)),
0)))
end) err_cnt,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(to_char(nvl((select count(*)
from dcc_ne_log_time in_dnl
where in_dnl.direction = 'recv'
and in_dnl.peer_id =
ne_state.peer_id),
0)))
end) recv_cnt,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(to_char(nvl((select sum(length)
from dcc_ne_log_time in_dnl
where in_dnl.direction = 'recv'
and in_dnl.peer_id =
ne_state.peer_id),
0)))
end) recv_byte,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(to_char(nvl((select count(*)
from dcc_ne_log_time in_dnl
where in_dnl.direction = 'send'
and in_dnl.peer_id =
ne_state.peer_id),
0)))
end) send_cnt,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
(to_char(nvl((select sum(length)
from dcc_ne_log_time in_dnl
where in_dnl.direction = 'send'
and in_dnl.peer_id =
ne_state.peer_id),
0)))
end) send_byte
from dcc_ne_log_time,ne_state
where ne_state.peer_id = dcc_ne_log_time.peer_id(+);
第3次改造后的SQL执行计划如下：
SELECT STATEMENT, GOAL = ALL_ROWS 34584 7 336
COUNT STOPKEY
TABLE ACCESS BY INDEX ROWID IDEPTEST DCC_SYS_LOG 3816 1 35
INDEX RANGE SCAN IDEPTEST IDX_DCC_SYS_LOG_PEER 596 119421
SORT GROUP BY NOSORT 58 7 238
VIEW IDEPTEST 58 7302 248268
TABLE ACCESS FULL SYS SYS_TEMP_0FD9D662A_2F9A0F1 58 7302 1891218
SORT AGGREGATE 1 44
VIEW IDEPTEST 58 7302 321288
TABLE ACCESS FULL SYS SYS_TEMP_0FD9D662A_2F9A0F1 58 7302 1891218
SORT AGGREGATE 1 57
VIEW IDEPTEST 58 7302 416214
TABLE ACCESS FULL SYS SYS_TEMP_0FD9D662A_2F9A0F1 58 7302 1891218
SORT AGGREGATE 1 44
VIEW IDEPTEST 58 7302 321288
TABLE ACCESS FULL SYS SYS_TEMP_0FD9D662A_2F9A0F1 58 7302 1891218
SORT AGGREGATE 1 57
VIEW IDEPTEST 58 7302 416214
TABLE ACCESS FULL SYS SYS_TEMP_0FD9D662A_2F9A0F1 58 7302 1891218
TEMP TABLE TRANSFORMATION
LOAD AS SELECT
FILTER
TABLE ACCESS BY INDEX ROWID IDEPTEST DCC_NE_LOG 302 7302 1891218
INDEX RANGE SCAN IDEPTEST IDX_DCC_NE_LOG_TIME 21 7302
HASH UNIQUE 34282 7 336
HASH JOIN OUTER 34281 7 336
VIEW IDEPTEST 34222 7 189
HASH UNIQUE 34222 7 336
COUNT
FILTER
HASH JOIN RIGHT OUTER 33949 4896255 235020240
VIEW IDEPTEST 28574 10 240
HASH UNIQUE 28574 10 1160
HASH JOIN 28573 1561 181076
HASH JOIN 28486 3874 368030
VIEW SYS VW_SQ_1 14365 41 1353
HASH GROUP BY 14365 41 2624
TABLE ACCESS FULL IDEPTEST DCC_SYS_LOG 14092 4895849 313334336
TABLE ACCESS FULL IDEPTEST DCC_SYS_LOG 14107 2462339 152665018
INDEX FAST FULL SCAN IDEPTEST IDX_DCC_NE_LOG_PEER 86 85428 1793988
INDEX FAST FULL SCAN IDEPTEST IDX_DCC_SYS_LOG_PEER 5347 4896255 117510120
VIEW IDEPTEST 58 7302 153342
TABLE ACCESS FULL SYS SYS_TEMP_0FD9D662A_2F9A0F1 58 7302 1891218
---第4次改造----
动作：将所有标量子查询改造为单独一个表关联写法
效果：将标量子查询的多次扫描，降低为将表扫描次仅1次，执行时间从10秒缩短为7秒
with ne_state as
(select distinct dsl1.peer_id peer_id,
nvl(ne_disconnect_info.ne_state, 1) ne_state
from dcc_sys_log dsl1,
(select distinct dnl.peer_id peer_id,
decode(action,
'disconnect',
0,
'connect',
0,
1) ne_state
from dcc_sys_log dsl, dcc_ne_log dnl
where dsl.peer_id = dnl.peer_id
and ((dsl.action = 'disconnect' and
dsl.cause = '关闭对端') or
(dsl.action = 'connect' and
dsl.cause = '连接主机失败'))
and dsl.log_time =
(select max(log_time)
from dcc_sys_log
where peer_id = dnl.peer_id
and log_type = '对端交互')) ne_disconnect_info
where dsl1.peer_id = ne_disconnect_info.peer_id(+) and rownum>=0),
dcc_ne_log_time as (select peer_id
,COUNT(CASE WHEN RESULT <> 1 THEN 1 END) err_cnt
,COUNT(CASE WHEN direction = 'recv' THEN 1 END) recv_cnt
,SUM(CASE WHEN direction = 'recv' THEN length END) recv_byte
,COUNT(CASE WHEN direction = 'send' THEN 1 END) send_cnt
,SUM(CASE WHEN direction = 'send' THEN length END) send_byte
from dcc_ne_log
where log_time >=trunc(sysdate) ---- between trunc(sysdate) and sysdate
GROUP BY peer_id)
select distinct ne_state.peer_id peer_name,
to_char(ne_state.ne_state) peer_state,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
NVL((select '1' from dcc_sys_log where peer_id = ne_state.peer_id and action = 'active' and rownum=1),'0')
end) peer_active,
decode(ne_state.ne_state,0,'0',nvl(dnlt.ERR_CNT,0)) ERR_CNT, ---注意NVL改造
decode(ne_state.ne_state,0,'0',nvl(dnlt.recv_cnt,0)) recv_cnt,
decode(ne_state.ne_state,0,'0',nvl(dnlt.recv_byte,0)) recv_byte,
decode(ne_state.ne_state,0,'0',nvl(dnlt.send_cnt,0)) send_cnt,
decode(ne_state.ne_state,0,'0',nvl(dnlt.send_byte,0)) send_byte
from ne_state ,dcc_ne_log_time dnlt
where ne_state.peer_id=dnlt.peer_id(+)
第4次改造后的SQL执行计划
SELECT STATEMENT, GOAL = ALL_ROWS 34231 7 791
COUNT STOPKEY
TABLE ACCESS BY INDEX ROWID IDEPTEST DCC_SYS_LOG 3816 1 35
INDEX RANGE SCAN IDEPTEST IDX_DCC_SYS_LOG_PEER 596 119421
HASH UNIQUE 34231 7 791
HASH JOIN OUTER 34230 7 791
VIEW IDEPTEST 34222 7 189
HASH UNIQUE 34222 7 336
COUNT
FILTER
HASH JOIN RIGHT OUTER 33949 4896255 235020240
VIEW IDEPTEST 28574 10 240
HASH UNIQUE 28574 10 1160
HASH JOIN 28573 1561 181076
HASH JOIN 28486 3874 368030
VIEW SYS VW_SQ_1 14365 41 1353
HASH GROUP BY 14365 41 2624
TABLE ACCESS FULL IDEPTEST DCC_SYS_LOG 14092 4895849 313334336
TABLE ACCESS FULL IDEPTEST DCC_SYS_LOG 14107 2462339 152665018
INDEX FAST FULL SCAN IDEPTEST IDX_DCC_NE_LOG_PEER 86 85428 1793988
INDEX FAST FULL SCAN IDEPTEST IDX_DCC_SYS_LOG_PEER 5347 4896255 117510120
VIEW IDEPTEST 8 7 602
HASH GROUP BY 8 7 280
TABLE ACCESS BY INDEX ROWID IDEPTEST DCC_NE_LOG 7 108 4320
INDEX RANGE SCAN IDEPTEST IDX_DCC_NE_LOG_TIME 2 108
---第5次改造
如下部分（即我改造后语句的第一个WITH的地方），觉的非常怪异
外面用 action和cause，里面在查最大时间用的是log_type = '对端交互'
根据我的经验常识来看，需求应该是log_type = '对端交互'同时作用于内外，即找出log_type = '对端交互'的最近一条记录，
然后看它的action和cause是否满足要求，难道不是这样吗，这样才顺畅嘛。
select distinct dsl1.peer_id peer_id,
nvl(ne_disconnect_info.ne_state, 1) ne_state
from dcc_sys_log dsl1,
(select distinct dnl.peer_id peer_id,
decode(action,
'disconnect',
0,
'connect',
0,
1) ne_state
from dcc_sys_log dsl, dcc_ne_log dnl
where dsl.peer_id = dnl.peer_id
and ((dsl.action = 'disconnect' and
dsl.cause = '关闭对端') or
(dsl.action = 'connect' and
dsl.cause = '连接主机失败'))
and dsl.log_time =
(select max(log_time)
from dcc_sys_log
where peer_id = dnl.peer_id
and log_type = '对端交互')) ne_disconnect_info
where dsl1.peer_id = ne_disconnect_info.peer_id(+) and rownum>=0
如果我猜测的是对的，代码就应该改写为如下(增加and log_type = '对端交互' ）：
把数据限于log_type = '对端交互'的那些。如果有哪个peer_id不存在 log_type = '对端交互'，
那么这个peer_id不在新写法中出现。之前的旧写法包括所有。
select distinct dsl1.peer_id peer_id,
nvl(ne_disconnect_info.ne_state, 1) ne_state
from dcc_sys_log dsl1,
(select distinct dnl.peer_id peer_id,
decode(action,
'disconnect',
0,
'connect',
0,
1) ne_state
from dcc_sys_log dsl, dcc_ne_log dnl
where dsl.peer_id = dnl.peer_id
and ((dsl.action = 'disconnect' and
dsl.cause = '关闭对端') or
(dsl.action = 'connect' and
dsl.cause = '连接主机失败'))
and log_type = '对端交互'
and dsl.log_time =
(select max(log_time)
from dcc_sys_log
where peer_id = dnl.peer_id
and log_type = '对端交互')) ne_disconnect_info
where dsl1.peer_id = ne_disconnect_info.peer_id(+) and rownum>=0
以下改写可以完善取最大日期的记录的方法,可进一步减少扫描次数
SELECT a.peer_id,
CASE WHEN dnl.peer_id IS NOT NULL AND str IN ('disconnect关闭对端','connect连接主机失败') THEN '0' ELSE '1' END ne_state
FROM (SELECT peer_id,MIN(action||cause) KEEP(DENSE_RANK LAST ORDER BY log_time) str
FROM dcc_sys_log dsl
WHERE log_type = '对端交互'
GROUP BY peer_id
) a,(SELECT DISTINCT peer_id FROM dcc_ne_log) dnl
WHERE a.peer_id = dnl.peer_id(+)
经过5次改造后，最终完善优化版代码如下：
with ne_state as
(SELECT a.peer_id,
CASE WHEN dnl.peer_id IS NOT NULL AND str IN ('disconnect关闭对端','connect连接主机失败') THEN '0' ELSE '1' END ne_state
FROM (SELECT peer_id,MIN(action||cause) KEEP(DENSE_RANK LAST ORDER BY log_time) str
FROM dcc_sys_log dsl
WHERE log_type = '对端交互'
GROUP BY peer_id
) a,(SELECT DISTINCT peer_id FROM dcc_ne_log) dnl
WHERE a.peer_id = dnl.peer_id(+)),
dcc_ne_log_time as (select peer_id
,COUNT(CASE WHEN RESULT <> 1 THEN 1 END) err_cnt
,COUNT(CASE WHEN direction = 'recv' THEN 1 END) recv_cnt
,SUM(CASE WHEN direction = 'recv' THEN length END) recv_byte
,COUNT(CASE WHEN direction = 'send' THEN 1 END) send_cnt
,SUM(CASE WHEN direction = 'send' THEN length END) send_byte
from dcc_ne_log
where log_time >=trunc(sysdate) ---- between trunc(sysdate) and sysdate
GROUP BY peer_id)
select distinct ne_state.peer_id peer_name,
to_char(ne_state.ne_state) peer_state,
(case
when ne_state.ne_state = 0 then
to_char(0)
else
NVL((select '1' from dcc_sys_log where peer_id = ne_state.peer_id and action = 'active' and rownum=1),'0')
end) peer_active,
decode(ne_state.ne_state,0,'0',nvl(dnlt.ERR_CNT,0)) ERR_CNT, ---注意NVL改造
decode(ne_state.ne_state,0,'0',nvl(dnlt.recv_cnt,0)) recv_cnt,
decode(ne_state.ne_state,0,'0',nvl(dnlt.recv_byte,0)) recv_byte,
decode(ne_state.ne_state,0,'0',nvl(dnlt.send_cnt,0)) send_cnt,
decode(ne_state.ne_state,0,'0',nvl(dnlt.send_byte,0)) send_byte
from ne_state ,dcc_ne_log_time dnlt
where ne_state.peer_id=dnlt.peer_id(+)
执行计划：
SELECT STATEMENT, GOAL = ALL_ROWS 14880 71 19667
COUNT STOPKEY
TABLE ACCESS BY INDEX ROWID IDEPTEST DCC_SYS_LOG 3235 1 35
INDEX RANGE SCAN IDEPTEST IDX_DCC_SYS_LOG_PEER 505 100809
HASH UNIQUE 14880 71 19667
HASH JOIN OUTER 14878 19100 5290700
HASH JOIN RIGHT OUTER 14657 49 12446
VIEW IDEPTEST 65 10 880
HASH GROUP BY 65 10 430
TABLE ACCESS BY INDEX ROWID IDEPTEST DCC_NE_LOG 64 1388 59684
INDEX RANGE SCAN IDEPTEST IDX_DCC_NE_LOG_TIME 7 1388
VIEW IDEPTEST 14592 49 8134
SORT GROUP BY 14592 49 3479
TABLE ACCESS FULL IDEPTEST DCC_SYS_LOG 14316 4939225 350684975
INDEX FAST FULL SCAN IDEPTEST IDX_DCC_NE_LOG_PEER 220 176424 4057752
group by的合并sql：
select decode(so.sFileName, 'SNP_20', 'SNP', 'HNIC_2', 'HNIC', 'IBRC_2', 'IBRC', 'IISMP_', 'IISMP', 'NIC_20', 'NIC', 'NIG_20', 'NIG', 'IIC_20', 'IIC', 'HIIC_2', 'HIIC', 'CA.D.A', 'CA.D.ATSR', 'ULH_20', 'ULH', 'IBRST_', 'IBRST', so.sFileName) 业务名称,
so.sFileCount 合并前文件个数,
so.sRecordNum 合并前总记录数,
ta.tFileCount 合并后文件个数,
ta.tRecordNum 合并后总记录数,
NVL(so1.sFileCount, 0) 合并前当天文件个数,
NVL(so1.sRecordNum, 0) 合并前当天文件总记录数,
NVL(so2.sFileCount, 0) 合并前昨天文件个数,
NVL(so2.sRecordNum, 0) 合并前昨天文件总记录数
from (select substr(a.file_name, 1, 6) sfileName,
count(*) sFileCount,
sum(sRecordNum) sRecordNum
from (select distinct bsf.file_name,
bsf.record_num sRecordNum,
bsf.create_time
from BUSINESS_SEND_FILELIST bsf, IDEP_PLUGIN_AUTO_RELATION ipar
where bsf.file_id = ipar.bus_file_id
and trunc(ipar.relation_time) = to_date('2010-08-05', 'yyyy-mm-dd')) a
group by substr(a.file_name, 1, 6)
order by substr(a.file_name, 1, 6)) so
LEFT JOIN
(select substr(a.file_name, 1, 6) sfileName,
count(*) sFileCount,
sum(sRecordNum) sRecordNum
from (select distinct bsf.file_name,
bsf.record_num sRecordNum,
bsf.create_time
from BUSINESS_SEND_FILELIST bsf, IDEP_PLUGIN_AUTO_RELATION ipar
where bsf.file_id = ipar.bus_file_id
and trunc(ipar.relation_time) = to_date('2010-08-05', 'yyyy-mm-dd')
AND FILE_NAME LIKE '%20100805%') a
group by substr(a.file_name, 1, 6)
order by substr(a.file_name, 1, 6)) so1
ON (so.sFileName = so1.sFileName)
LEFT JOIN
(select substr(a.file_name, 1, 6) sfileName,
count(*) sFileCount,
sum(sRecordNum) sRecordNum
from (select distinct bsf.file_name,
bsf.record_num sRecordNum,
bsf.create_time
from BUSINESS_SEND_FILELIST bsf, IDEP_PLUGIN_AUTO_RELATION ipar
where bsf.file_id = ipar.bus_file_id
and trunc(ipar.relation_time) = to_date('2010-08-05', 'yyyy-mm-dd')
AND FILE_NAME not LIKE '%20100805%') a
group by substr(a.file_name, 1, 6)
order by substr(a.file_name, 1, 6)) so2
ON (so.sFileName = so2.sFileName)
LEFT JOIN
(select substr(a.file_name, 1, 6) tFileName,
count(*) tFileCount,
sum(record_num) tRecordNum
from (select distinct ipsf.file_name,
ipsf.record_num
from idep_plugin_send_filelist ipsf
where trunc(ipsf.create_time) = to_date('2010-08-05', 'yyyy-mm-dd')
and remark = '处理成功') a
group by substr(a.file_name, 1, 6)
order by substr(a.file_name, 1, 6)) ta
ON (so.sFileName = ta.tFileName)
where so.sFileName not like 'MVI%'
union
select so.sFileName,
so.sFileCount,
(so.sRecordNum - (so.sFileCount * 2)) sRecordNum,
ta.tFileCount,
ta.tRecordNum,
NVL(so1.sFileCount, 0),
(nvl(so1.sRecordNum, 0) - (nvl(so1.sFileCount, 0) * 2)),
NVL(so2.sFileCount, 0),
(nvl(so2.sRecordNum, 0) - (nvl(so2.sFileCount, 0) * 2))
from (select substr(a.file_name, 1, 3) sfileName,
count(*) sFileCount,
sum(sRecordNum) sRecordNum
from (select distinct bsf.file_name,
bsf.record_num sRecordNum,
bsf.create_time
from BUSINESS_SEND_FILELIST bsf, IDEP_PLUGIN_AUTO_RELATION ipar
where bsf.file_id = ipar.bus_file_id
and trunc(ipar.relation_time) = to_date('2010-08-05', 'yyyy-mm-dd')) a
group by substr(a.file_name, 1, 3)
order by substr(a.file_name, 1, 3)) so
LEFT JOIN
(select substr(a.file_name, 1, 3) sfileName,
count(*) sFileCount,
sum(sRecordNum) sRecordNum
from (select distinct bsf.file_name,
bsf.record_num sRecordNum,
bsf.create_time
from BUSINESS_SEND_FILELIST bsf, IDEP_PLUGIN_AUTO_RELATION ipar
where bsf.file_id = ipar.bus_file_id
and trunc(ipar.relation_time) = to_date('2010-08-05', 'yyyy-mm-dd')
AND FILE_NAME LIKE 'MVI100805%') a
group by substr(a.file_name, 1, 3)
order by substr(a.file_name, 1, 3)) so1
ON (so.sFileName = so1.sFileName)
LEFT JOIN
(select substr(a.file_name, 1, 3) sfileName,
count(*) sFileCount,
sum(sRecordNum) sRecordNum
from (select distinct bsf.file_name,
bsf.record_num sRecordNum,
bsf.create_time
from BUSINESS_SEND_FILELIST bsf, IDEP_PLUGIN_AUTO_RELATION ipar
where bsf.file_id = ipar.bus_file_id
and trunc(ipar.relation_time) = to_date('2010-08-05', 'yyyy-mm-dd')
AND FILE_NAME not LIKE 'MVI100805%') a
group by substr(a.file_name, 1, 3)
order by substr(a.file_name, 1, 3)) so2
ON (so.sFileName = so2.sFileName)
LEFT JOIN
(select substr(a.file_name, 1, 3) tFileName,
count(*) tFileCount,
sum(record_num) tRecordNum
from (select distinct ipsf.file_name,
ipsf.record_num
from idep_plugin_send_filelist ipsf
where trunc(ipsf.create_time) = to_date('2010-08-05', 'yyyy-mm-dd')
and remark = '处理成功') a
group by substr(a.file_name, 1, 3)
order by substr(a.file_name, 1, 3)) ta
ON (so.sFileName = ta.tFileName)
WHERE so.sFileName = 'MVI' ---这里有错吧，感觉应该是 WHERE so.sFileName like 'MVI%'，确实没错，因为这里是截取过的了
注意点：
1.将trunc(ipar.relation_time) = to_date('2010-08-05', 'yyyy-mm-dd') 等等类似之处改写为如下，要避免对列进行运算，这样会
导致用不上索引，除非是建立了函数索引。
ipsf.create_time >= to_date('2010-08-05', 'yyyy-mm-dd') and ipsf.create_time < to_date('2010-08-05', 'yyyy-mm-dd')+1
2. 确保IDEP_PLUGIN_AUTO_RELATION的relation_time有索引
确保idep_plugin_send_filelist的create_time列有索引
3.可通过CASE WHEN 语句进一步减少表扫描次数，如（count(CASE WHEN FILE_NAME LIKE '%20100805%' THEN 1 END) sFileCount1），
类似如上的修改，可以等价改写，将本应用的表扫描从8次减少为4次。
代码改写如下：
select decode(so.sFileName, 'SNP_20', 'SNP', 'HNIC_2', 'HNIC', 'IBRC_2', 'IBRC', 'IISMP_', 'IISMP', 'NIC_20', 'NIC', 'NIG_20', 'NIG', 'IIC_20', 'IIC', 'HIIC_2', 'HIIC', 'CA.D.A', 'CA.D.ATSR', 'ULH_20', 'ULH', 'IBRST_', 'IBRST', so.sFileName) 业务名称,
so.sFileCount 合并前文件个数,
so.sRecordNum 合并前总记录数,
ta.tFileCount 合并后文件个数,
ta.tRecordNum 合并后总记录数,
NVL(so.sFileCount1, 0) 合并前当天文件个数,
NVL(so.sRecordNum1, 0) 合并前当天文件总记录数,
NVL(so.sFileCount2, 0) 合并前昨天文件个数,
NVL(so.sRecordNum2, 0) 合并前昨天文件总记录数
from (select substr(a.file_name, 1, 6) sfileName,
count(*) sFileCount,
sum(sRecordNum) sRecordNum,
count(CASE WHEN FILE_NAME LIKE '%20100805%' THEN 1 END) sFileCount1,
sum(CASE WHEN FILE_NAME LIKE '%20100805%' THEN sRecordNum END) sRecordNum1,
count(CASE WHEN FILE_NAME NOT LIKE '%20100805%' THEN 1 END) sFileCount2,
sum(CASE WHEN FILE_NAME NOT LIKE '%20100805%' THEN sRecordNum END) sRecordNum2
from (select distinct bsf.file_name,
bsf.record_num sRecordNum,
bsf.create_time
from BUSINESS_SEND_FILELIST bsf, IDEP_PLUGIN_AUTO_RELATION ipar
where bsf.file_id = ipar.bus_file_id
and ipar.relation_time >= to_date('2010-08-05', 'yyyy-mm-dd')
and ipar.relation_time < to_date('2010-08-05', 'yyyy-mm-dd')+1) a
group by substr(a.file_name, 1, 6)
order by substr(a.file_name, 1, 6)) so
LEFT JOIN
(select substr(a.file_name, 1, 6) tFileName,
count(*) tFileCount,
sum(record_num) tRecordNum
from (select distinct ipsf.file_name,
ipsf.record_num
from idep_plugin_send_filelist ipsf
where ipsf.create_time >= to_date('2010-08-05', 'yyyy-mm-dd')
and ipsf.create_time < to_date('2010-08-05', 'yyyy-mm-dd')+1
and remark = '处理成功') a
group by substr(a.file_name, 1, 6)
order by substr(a.file_name, 1, 6)) ta
ON (so.sFileName = ta.tFileName)
where so.sFileName not like 'MVI%'
union
select so.sFileName,
so.sFileCount,
(so.sRecordNum - (so.sFileCount * 2)) sRecordNum,
ta.tFileCount,
ta.tRecordNum,
NVL(so.sFileCount1, 0),
(nvl(so.sRecordNum1, 0) - (nvl(so.sFileCount1, 0) * 2)),
NVL(so.sFileCount2, 0),
(nvl(so.sRecordNum2, 0) - (nvl(so.sFileCount2, 0) * 2))
from (select substr(a.file_name, 1, 3) sfileName,
count(*) sFileCount,
sum(sRecordNum) sRecordNum,
count(CASE WHEN FILE_NAME LIKE 'MVI100805%' THEN 1 END) sFileCount1,
sum(CASE WHEN FILE_NAME LIKE 'MVI100805%' THEN sRecordNum END) sRecordNum1,
count(CASE WHEN FILE_NAME NOT LIKE 'MVI100805%' THEN 1 END) sFileCount2,
sum(CASE WHEN FILE_NAME NOT LIKE 'MVI100805%' THEN sRecordNum END) sRecordNum2
from (select distinct bsf.file_name,
bsf.record_num sRecordNum,
bsf.create_time
from BUSINESS_SEND_FILELIST bsf, IDEP_PLUGIN_AUTO_RELATION ipar
where bsf.file_id = ipar.bus_file_id
and ipar.relation_time >= to_date('2010-08-05', 'yyyy-mm-dd')
and ipar.relation_time < to_date('2010-08-05', 'yyyy-mm-dd')+1) a
group by substr(a.file_name, 1, 3)
order by substr(a.file_name, 1, 3)) so
LEFT JOIN
(select substr(a.file_name, 1, 3) tFileName,
count(*) tFileCount,
sum(record_num) tRecordNum
from (select distinct ipsf.file_name,
ipsf.record_num
from idep_plugin_send_filelist ipsf
where ipsf.create_time >= to_date('2010-08-05', 'yyyy-mm-dd')
and ipsf.create_time < to_date('2010-08-05', 'yyyy-mm-dd')+1
and remark = '处理成功') a
group by substr(a.file_name, 1, 3)
order by substr(a.file_name, 1, 3)) ta
ON (so.sFileName = ta.tFileName)
WHERE so.sFileName = 'MVI'
最终版
select decode(so.sFileName, 'SNP_20', 'SNP', 'HNIC_2', 'HNIC', 'IBRC_2', 'IBRC', 'IISMP_', 'IISMP', 'NIC_20', 'NIC', 'NIG_20', 'NIG', 'IIC_20', 'IIC', 'HIIC_2', 'HIIC', 'CA.D.A', 'CA.D.ATSR', 'ULH_20', 'ULH', 'IBRST_', 'IBRST', so.sFileName) 业务名称,
so.sFileCount 合并前文件个数,
case when so.sfilename like 'MVI%' then (so.sRecordNum - (so.sFileCount * 2)) else so.sRecordNum end 合并前总记录数,
ta.tFileCount 合并后文件个数,
ta.tRecordNum 合并后总记录数,
NVL(so.sFileCount1, 0) 合并前当天文件个数,
case when so.sfilename like 'MVI%'
then (nvl(so.sRecordNum1, 0) - (nvl(so.sFileCount1, 0) * 2))
else NVL(so.sRecordNum1, 0) end 合并前当天文件总记录数,
NVL(so.sFileCount2, 0) 合并前昨天文件个数,
case when so.sfilename like 'MVI%'
then (nvl(so.sRecordNum2, 0) - (nvl(so.sFileCount2, 0) * 2))
else NVL(so.sRecordNum2, 0) end 合并前昨天文件总记录数
from (select substr(a.file_name, 1, CASE WHEN a.file_name like 'MVI%' THEN 3 ELSE 6 END) sfileName,
count(*) sFileCount,
sum(sRecordNum) sRecordNum,
count(CASE WHEN (FILE_NAME LIKE '%20100805%' AND FILE_NAME not like 'MVI%') OR (FILE_NAME LIKE 'MVI100805%' AND FILE_NAME like 'MVI%') THEN 1 END) sFileCount1,
sum (CASE WHEN (FILE_NAME LIKE '%20100805%' AND FILE_NAME not like 'MVI%') OR (FILE_NAME LIKE 'MVI100805%' AND FILE_NAME like 'MVI%') THEN sRecordNum END) sRecordNum1,
count(CASE WHEN (FILE_NAME NOT LIKE '%20100805%' AND FILE_NAME not like 'MVI%') OR (FILE_NAME NOT LIKE 'MVI100805%' AND FILE_NAME like 'MVI%') THEN 1 END) sFileCount2,
sum (CASE WHEN (FILE_NAME NOT LIKE '%20100805%' AND FILE_NAME not like 'MVI%') OR (FILE_NAME NOT LIKE 'MVI100805%' AND FILE_NAME like 'MVI%') THEN sRecordNum END) sRecordNum2
from (select distinct bsf.file_name,
bsf.record_num sRecordNum,
bsf.create_time
from BUSINESS_SEND_FILELIST bsf, IDEP_PLUGIN_AUTO_RELATION ipar
where bsf.file_id = ipar.bus_file_id
and ipar.relation_time >= to_date('2010-08-05', 'yyyy-mm-dd')
and ipar.relation_time < to_date('2010-08-05', 'yyyy-mm-dd')+1) a
group by substr(a.file_name, 1, CASE WHEN a.file_name like 'MVI%' THEN 3 ELSE 6 END)
order by substr(a.file_name, 1, CASE WHEN a.file_name like 'MVI%' THEN 3 ELSE 6 END)) so
LEFT JOIN
(select substr(a.file_name, 1, CASE WHEN a.file_name like 'MVI%' THEN 3 ELSE 6 END) tFileName,
count(*) tFileCount,
sum(record_num) tRecordNum
from (select distinct ipsf.file_name,
ipsf.record_num
from idep_plugin_send_filelist ipsf
where ipsf.create_time >= to_date('2010-08-05', 'yyyy-mm-dd')
and ipsf.create_time < to_date('2010-08-05', 'yyyy-mm-dd')+1
and remark = '处理成功') a
group by substr(a.file_name, 1, CASE WHEN a.file_name like 'MVI%' THEN 3 ELSE 6 END)
order by substr(a.file_name, 1, CASE WHEN a.file_name like 'MVI%' THEN 3 ELSE 6 END)) ta
ON (so.sFileName = ta.tFileName)
with as
该语句存在性能问题，执行非常缓慢，极耗CPU，为了实现行列转换的需求，具体如下：
select distinct to_char(a.svcctx_id),
to_char(0),
to_char(nvl((select peer_id
from dcc_ne_config
where peer_name = a.peer),
0)),
to_char(a.priority),
to_char(nvl((select peer_id
from dcc_ne_config
where peer_name = b.peer),
0)),
to_char(b.priority),
to_char(nvl((select peer_id
from dcc_ne_config
where peer_name = c.peer),
0)),
to_char(c.priority)
from (select hopbyhop,
svcctx_id,
substr(cause,
instr(cause, 'Host = ') + 7,
instr(cause, 'Priority = ') - instr(cause, 'Host = ') - 11) peer,
substr(cause,
instr(cause, 'Priority = ') + 11,
instr(cause, 'reachable = ') -
instr(cause, 'Priority = ') - 13) priority
from dcc_sys_log
where cause like '%SC路由应答%'
and hopbyhop in (select distinct hopbyhop from dcc_sys_log)) a,
(select hopbyhop,
svcctx_id,
substr(cause,
instr(cause, 'Host = ') + 7,
instr(cause, 'Priority = ') - instr(cause, 'Host = ') - 11) peer,
substr(cause,
instr(cause, 'Priority = ') + 11,
instr(cause, 'reachable = ') -
instr(cause, 'Priority = ') - 13) priority
from dcc_sys_log
where cause like '%SC路由应答%'
and hopbyhop in (select distinct hopbyhop from dcc_sys_log)) b,
(select hopbyhop,
svcctx_id,
substr(cause,
instr(cause, 'Host = ') + 7,
instr(cause, 'Priority = ') - instr(cause, 'Host = ') - 11) peer,
substr(cause,
instr(cause, 'Priority = ') + 11,
instr(cause, 'reachable = ') -
instr(cause, 'Priority = ') - 13) priority
from dcc_sys_log
where cause like '%SC路由应答%'
and hopbyhop in (select distinct hopbyhop from dcc_sys_log)) c
where a.hopbyhop = b.hopbyhop
and a.hopbyhop = c.hopbyhop
and a.peer <> b.peer
and a.peer <> c.peer
and b.peer <> c.peer
and a.priority <> b.priority
and a.priority <> c.priority
and b.priority <> c.priority
执行计划：
Execution Plan
----------------------------------------------------------
Plan hash value: 408096778
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1941 | 159K| | 18E(100)|999:59:59 |
|* 1 | TABLE ACCESS FULL | DCC_NE_CONFIG | 1 | 28 | | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | DCC_NE_CONFIG | 1 | 28 | | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DCC_NE_CONFIG | 1 | 28 | | 3 (0)| 00:00:01 |
| 4 | HASH UNIQUE | | 1941 | 159K| | 18E(100)|999:59:59 |
| 5 | MERGE JOIN | | 18E| 15E| | 18E(100)|999:59:59 |
| 6 | MERGE JOIN | | 18E| 15E| | 32P(100)|999:59:59 |
| 7 | MERGE JOIN | | 1147T| 79P| | 1018T(100)|999:59:59 |
| 8 | SORT JOIN | | 746T| 36P| 85P| 101G (95)|999:59:59 |
|* 9 | HASH JOIN | | 746T| 36P| 70M| 4143M(100)|999:59:59 |
| 10 | TABLE ACCESS FULL | DCC_SYS_LOG | 4939K| 14M| | 14325 (1)| 00:02:52 |
|* 11 | HASH JOIN | | 151M| 7530M| 8448K| 366K (93)| 01:13:19 |
|* 12 | TABLE ACCESS FULL| DCC_SYS_LOG | 246K| 5547K| | 14352 (2)| 00:02:53 |
|* 13 | TABLE ACCESS FULL| DCC_SYS_LOG | 246K| 6994K| | 14352 (2)| 00:02:53 |
|* 14 | FILTER | | | | | | |
|* 15 | SORT JOIN | | 246K| 5547K| 15M| 16046 (2)| 00:03:13 |
|* 16 | TABLE ACCESS FULL | DCC_SYS_LOG | 246K| 5547K| | 14352 (2)| 00:02:53 |
|* 17 | SORT JOIN | | 4939K| 14M| 113M| 27667 (2)| 00:05:32 |
| 18 | TABLE ACCESS FULL | DCC_SYS_LOG | 4939K| 14M| | 14325 (1)| 00:02:52 |
|* 19 | SORT JOIN | | 4939K| 14M| 113M| 27667 (2)| 00:05:32 |
| 20 | TABLE ACCESS FULL | DCC_SYS_LOG | 4939K| 14M| | 14325 (1)| 00:02:52 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PEER_NAME"=SUBSTR(:B1,INSTR(:B2,'Host = ')+7,INSTR(:B3,'Priority =
')-INSTR(:B4,'Host = ')-11))
2 - filter("PEER_NAME"=SUBSTR(:B1,INSTR(:B2,'Host = ')+7,INSTR(:B3,'Priority =
')-INSTR(:B4,'Host = ')-11))
3 - filter("PEER_NAME"=SUBSTR(:B1,INSTR(:B2,'Host = ')+7,INSTR(:B3,'Priority =
')-INSTR(:B4,'Host = ')-11))
9 - access("HOPBYHOP"="HOPBYHOP")
11 - access("HOPBYHOP"="HOPBYHOP")
filter(SUBSTR("CAUSE",INSTR("CAUSE",'Host = ')+7,INSTR("CAUSE",'Priority =
')-INSTR("CAUSE",'Host = ')-11)<>SUBSTR("CAUSE",INSTR("CAUSE",'Host =
')+7,INSTR("CAUSE",'Priority = ')-INSTR("CAUSE",'Host = ')-11) AND
SUBSTR("CAUSE",INSTR("CAUSE",'Priority = ')+11,INSTR("CAUSE",'reachable =
')-INSTR("CAUSE",'Priority = ')-13)<>SUBSTR("CAUSE",INSTR("CAUSE",'Priority =
')+11,INSTR("CAUSE",'reachable = ')-INSTR("CAUSE",'Priority = ')-13))
12 - filter("CAUSE" LIKE '%SC路由应答%')
13 - filter("CAUSE" LIKE '%SC路由应答%')
14 - filter(SUBSTR("CAUSE",INSTR("CAUSE",'Host = ')+7,INSTR("CAUSE",'Priority =
')-INSTR("CAUSE",'Host = ')-11)<>SUBSTR("CAUSE",INSTR("CAUSE",'Host =
')+7,INSTR("CAUSE",'Priority = ')-INSTR("CAUSE",'Host = ')-11) AND
SUBSTR("CAUSE",INSTR("CAUSE",'Host = ')+7,INSTR("CAUSE",'Priority =
')-INSTR("CAUSE",'Host = ')-11)<>SUBSTR("CAUSE",INSTR("CAUSE",'Host =
')+7,INSTR("CAUSE",'Priority = ')-INSTR("CAUSE",'Host = ')-11) AND
SUBSTR("CAUSE",INSTR("CAUSE",'Priority = ')+11,INSTR("CAUSE",'reachable =
')-INSTR("CAUSE",'Priority = ')-13)<>SUBSTR("CAUSE",INSTR("CAUSE",'Priority =
')+11,INSTR("CAUSE",'reachable = ')-INSTR("CAUSE",'Priority = ')-13) AND
SUBSTR("CAUSE",INSTR("CAUSE",'Priority = ')+11,INSTR("CAUSE",'reachable =
')-INSTR("CAUSE",'Priority = ')-13)<>SUBSTR("CAUSE",INSTR("CAUSE",'Priority =
')+11,INSTR("CAUSE",'reachable = ')-INSTR("CAUSE",'Priority = ')-13))
15 - access("HOPBYHOP"="HOPBYHOP")
filter("HOPBYHOP"="HOPBYHOP")
16 - filter("CAUSE" LIKE '%SC路由应答%')
17 - access("HOPBYHOP"="HOPBYHOP")
filter("HOPBYHOP"="HOPBYHOP")
19 - access("HOPBYHOP"="HOPBYHOP")
filter("HOPBYHOP"="HOPBYHOP")
Statistics
----------------------------------------------------------
54 recursive calls
0 db block gets
128904 consistent gets
5549 physical reads
0 redo size
1017 bytes sent via SQL*Net to client
1422 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
------
with t as
(select hopbyhop,
svcctx_id,
substr(cause,
instr(cause, 'Host = ') + 7,
instr(cause, 'Priority = ') - instr(cause, 'Host = ') - 11) peer,
substr(cause,
instr(cause, 'Priority = ') + 11,
instr(cause, 'reachable = ') -
instr(cause, 'Priority = ') - 13)
selet * from t1,t2, t
where t....