SQL> select* from idep_alarm_list a; --这个是视图，视图语句见附录
112767 rows selected.
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=3702 Card=96580 Byte
s=420895640)
10 VIEW OF 'IDEP_ALARM_LIST' (Cost=3702 Card=96580 Bytes=4208
95640)
21 SORT (UNIQUE) (Cost=3702 Card=96580 Bytes=20441872)
32 UNION-ALL
43 HASH JOIN (Cost=565 Card=89619 Bytes=19536942)
54 TABLE ACCESS (FULL) OF 'IDEP_ALERT_LOG_CONFIG' (Co
st=2 Card=31 Bytes=2201)
64 HASH JOIN (Cost=561 Card=89619 Bytes=13173993)
76
TABLE ACCESS (FULL) OF 'IDEP_SCHEDULE' (Cost=2 C
ard=117 Bytes=1755)
86
HASH JOIN (Cost=557 Card=89626 Bytes=11830632)
98
TABLE ACCESS (FULL) OF 'IDEP_WORKITEM' (Cost=2
Card=18 Bytes=126)
108
HASH JOIN (Cost=553 Card=89626 Bytes=11203250)
11 10
HASH JOIN (Cost=258 Card=78782 Bytes=5199612
)
12 11
HASH JOIN (Cost=131 Card=67080 Bytes=18111
60)
13 12
TABLE ACCESS (FULL) OF 'IDEP_TASK_LOG' (
Cost=42 Card=67084 Bytes=670840)
14 12
TABLE ACCESS (FULL) OF 'IDEP_FLOW' (Cost
=38 Card=64224 Bytes=1091808)
15 11
VIEW (Cost=26 Card=78787 Bytes=3072693)
16 15
SORT (GROUP BY) (Cost=26 Card=78787 Byte
s=945444)
17 16
INDEX (FULL SCAN) OF 'UNION_IDX' (NON-
UNIQUE) (Cost=26 Card=79689 Bytes=956268)
18 10
TABLE ACCESS (FULL) OF 'IDEP_ALERT_LOG' (Cos
t=96 Card=97454 Bytes=5749786)
193 NESTED LOOPS (Cost=97 Card=6961 Bytes=904930)
20 19 TABLE ACCESS (BY INDEX ROWID) OF 'IDEP_ALERT_LOG_C
ONFIG' (Cost=1 Card=1 Bytes=71)
21 20
INDEX (UNIQUE SCAN) OF 'PK_IDEP_ALERT_LOG_CONFIG
' (UNIQUE)
22 19 TABLE ACCESS (FULL) OF 'IDEP_ALERT_LOG' (Cost=96 C
ard=6961 Bytes=410699)
Statistics
----------------------------------------------------------
225613recursive calls
10db block gets
1583117consistent gets
4778physical reads
0redo size
5874248bytes sent via SQL*Net to client
83342bytes received via SQL*Net from client
7519SQL*Net roundtrips to/from client
0sorts (memory)
2sorts (disk)
112767rows processed
由于语句太复杂了，我做了全分析
analyze table idep_alert_logcompute statistics for table for all indexes for all indexed columns;
analyze table idep_workitem_log compute statistics for table for all indexes for all indexed columns;
analyze table IDEP_TASK_LOG compute statistics for table for all indexes for all indexed columns;
analyze table idep_flow
compute statistics for table for all indexes for all indexed columns;
analyze table idep_schedule compute statistics for table for all indexes for all indexed columns;
analyze table IDEP_ALERT_LOG_CONFIG compute statistics for table for all indexes for all indexed columns;
analyze table idep_workitem compute statistics for table for all indexes for all indexed columns;
也做了全部CACHE进内存的动作
alter indexUNION_IDX storage(buffer_pool keep);
alter tableidep_alert_log storage(buffer_pool keep);
alter tableidep_workitem_log storage(buffer_pool keep);
alter tableIDEP_TASK_LOGstorage(buffer_pool keep);
alter tableidep_flow storage(buffer_pool keep);
alter tableidep_schedule storage(buffer_pool keep);
alter tableIDEP_ALERT_LOG_CONFIG storage(buffer_pool keep);
alter tableidep_workitem storage(buffer_pool keep);
alter indexPK_IDEP_ALERT_LOG_CONFIG storage(buffer_pool keep);
此外记录数也都不大
SQL> select count(*) from idep_alert_log;
COUNT(*)
----------
134997
SQL> select count(*) from idep_workitem_log;
COUNT(*)
----------
114817
SQL> select count(*) fromIDEP_TASK_LOG ;
COUNT(*)
----------
105473
SQL> select count(*) fromidep_flow
;
COUNT(*)
----------
103009
SQL> select count(*) from idep_schedule;
COUNT(*)
----------
163
SQL> select count(*) from IDEP_ALERT_LOG_CONFIG ;
COUNT(*)
----------
32
SQL> select count(*) fromidep_workitem ;
COUNT(*)
----------
18
附：
试图写法
select e.sched_name,
e.sched_id,
d.flow_inst_id,
d.start_time,
g.workitem_mod_id,
a.log_code alarm_type,
f.message alarm_type_name,
f.alert_level,
get_domainlabel('DOMAIN_ALARM_LEVEL', f.alert_level, '无') alert_level_name,
f.action,
a.generate_time,
a.message
from idep_alert_log a,
(select max(workitem_inst_id) workitem_inst_id,
max(TASK_INST_ID) TASK_INST_ID,
max(WORKITEM_ID) WORKITEM_ID
from idep_workitem_log
group by workitem_inst_id) b,
IDEP_TASK_LOG c,
idep_flow d,
idep_schedule e,
IDEP_ALERT_LOG_CONFIG f,
idep_workitem g
where a.workitem_inst_id = b.workitem_inst_id
and a.log_code = f.log_code
and b.TASK_INST_ID = c.TASK_INST_ID
and c.flow_inst_id = d.flow_inst_id
and d.sched_id = e.sched_id
and b.workitem_id = g.workitem_id
and a.log_code0
union
select '生命周期检查' sched_name,a.log_id sched_id,'999'+a.log_id flow_inst_id,a.generate_time start_time,
9 workitem_mod_id,a.log_code alarm_type,f.message alarm_type_name,
f.alert_level,get_domainlabel('DOMAIN_ALARM_LEVEL', f.alert_level, '无') alert_level_name,f.action,
a.generate_time,
a.message from idep_alert_log a,IDEP_ALERT_LOG_CONFIG f
where a.log_code=f.log_code anda.log_code=37
[ 本帖最后由 wabjtam123 于 2009-9-17 18:46 编辑 ]
|
