SQL> set autot traceonly
SQL> select * from (select row_number() over (partition by lc_id order by absent
_day asc) rn,lc_id,absent_day from kdlc_result_web where 2>1 and call_time>=(sys
date-40) and dslam_ip in (select dslam_ip from dslam_ip where exch_id=16)) p whe
re p.rn=1;已选择448行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 WINDOW (SORT PUSHED RANK)
3 2 MERGE JOIN
4 3 SORT (JOIN)
5 4 VIEW OF 'KDLC_RESULT_WEB'
6 5 UNION-ALL
7 6 TABLE ACCESS (FULL) OF 'KDLC_DEVICEJC_RESULT'
8 6 TABLE ACCESS (FULL) OF 'KDLC_DEVICEJC_RESULT_H
IS' 9 3 SORT (JOIN)
10 9 VIEW OF 'VW_NSO_1'
11 10 SORT (UNIQUE)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'DSLAM_IP'
13 12 INDEX (RANGE SCAN) OF 'DSLAM_IP_EXCH_ID' (NO
N-UNIQUE)Statistics
----------------------------------------------------------
347 recursive calls
26 db block gets
54262 consistent gets
66444 physical reads
0 redo size
9001 bytes sent via SQL*Net to client
822 bytes received via SQL*Net from client
31 SQL*Net roundtrips to/from client
9 sorts (memory)
1 sorts (disk)
448 rows processedSQL>
kdlc_result_web 是一个现在数据和历史数据结合起来的一个视图.帮我分析一下,我需要对数据库的哪个地方不合理?哪位做过 AWR分析(toad)的,简单说一下.怎么操作的或分析后主要看哪个数据 都可以,谢谢啦.....我没分了,哈
8 6 TABLE ACCESS (FULL) OF 'KDLC_DEVICEJC_RESULT_H
上面这个视是两个表直接union all,全表扫描(TABLE ACCESS FULL),如果上面两张表数据量大,未用上索引,建议在call_time上增加索引
另外,建议使用exists取代in 如:
select *
from (select row_number() over(partition by lc_id order by absent _day asc) rn,
lc_id,
absent_day
from kdlc_result_web
where 2 > 1
and call_time >= (sysdate - 40)
and dslam_ip exists
(select 1
from dslam_ip a
where a.dslam_ip = kdlc_result_web.dslam_ip
where exch_id = 16))) p
where p.rn = 1;
----------------------------------------------------------
347 recursive calls
26 db block gets
54262 consistent gets
66444 physical reads
0 redo size
9001 bytes sent via SQL*Net to client
822 bytes received via SQL*Net from client
31 SQL*Net roundtrips to/from client
9 sorts (memory)
1 sorts (disk)
448 rows processed
---------------------------------
主要看逻辑读和物理读,
你这个有几个明显的问题;
1,逻辑读consistents超过5万,这个值偏高,最好1万以下;
2,物理读比逻辑读还高,这说明排序中使用到了临时表;
3,使用了disk排序
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 WINDOW (SORT PUSHED RANK)
3 2 MERGE JOIN
4 3 SORT (JOIN)
5 4 VIEW OF 'KDLC_RESULT_WEB'
6 5 UNION-ALL
7 6 TABLE ACCESS (FULL) OF 'KDLC_DEVICEJC_RESULT'
8 6 TABLE ACCESS (FULL) OF 'KDLC_DEVICEJC_RESULT_H
IS' 9 3 SORT (JOIN)
10 9 VIEW OF 'VW_NSO_1'
11 10 SORT (UNIQUE)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'DSLAM_IP'
13 12 INDEX (RANGE SCAN) OF 'DSLAM_IP_EXCH_ID' (NO
N-UNIQUE)
-----------------------------------
从执行计划来看,该查询最大的问题在于两个表table full scan后merge join
1, table full scan不好;
2,merge join更不好;
3,两个加一块,不好加不好。
1, 在kdlc_result_web的源表的call_time字段上建立索引;
2,kdlc_result_web的脚本中如果存在union的话,尝试看看能否用union all代替
3,kdlc_result_web如果是两个表做关联的,尝试加hint阻止其使用merge join