菜鸟我手上有一段SQL代码,效率相比其他同组代码较低,我想找到这段代码的瓶颈并提出解决方案。于是,我用Oracle SQL Developer得到代码的执行计划,如下图:等待高手指点迷津!
我的问题:
1. 这张图怎么看,瓶颈在哪里?
2. 可以从哪里着手优化?
我的问题:
1. 这张图怎么看,瓶颈在哪里?
2. 可以从哪里着手优化?
解决方案 »
- 物化视图复制,当主站点与物化视图站点断开连接的情况下主站点不可操作的问题~
- 逻辑备份不支持BINARY_DOUBLE,数据不能导出
- 求ORACLE 数据库导到其他数据库的具体方法和步骤
- 用户权限中的adm一栏对应为no,为什么还能赋予权限给其他的用户?
- 查询问题
- a.cust_no=b.cust_no(+)和a.cust_no=b.cust_no什么区别?
- 请问可不可以把ACCESS数据库转换成ORACLE
- 有没有人用过oracle portal,一起交流交流
- 能否给点有关用JOB定期执行ANASYLE的源码,还希望各位指教呀。
- 如何统计一个表实际占用了多少空间?
- 关于oracle视图查询的问题
- 在Windowxp上安装ORACLE时提示错误
只能上csdn滴说
贴你的sql看看
set termout off;
var start_time varchar2(40);
var stop_time varchar2(40);
var time_diff varchar2(40);col spoolname new_value splname nopriselect 'REPBSS_PET_RB_'||to_char(sysdate,'YYYYMMDD_HH24MISS') spoolname from dual;SPOOL &SPLNAME..log
set timing on;
set heading off;
set linesize 132;------------------Start------------------------
exec :start_time := to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss.ff3');
select rbs_BSC_objects.co_gid as "XXXXX BSC0_0",NVL(rbs_BSC_objects.co_name, NVL(rbs_BSC_objects.co_object_instance, rbs_BSC_objects.co_gid)) as "XXXXX BSC0",trunc(rbs_ps_mchdelay_bcf_raw.period_start_time, 'HH24') as "Hour0",
'JB_FILL_LEVEL_SUM_BTS (Mchdelay)' as fact, SUM(rbs_ps_mchdelay_bcf_raw.JB_FILL_LEVEL_SUM_BTS) as result,
'ABIS_ROUND_TRIP_TIME_MIN (Mchdelay)' as fact_1, MIN(rbs_ps_mchdelay_bcf_raw.ABIS_ROUND_TRIP_TIME_MIN) as result_1,
'ABIS_ROUND_TRIP_TIME_SUCC (Mchdelay)' as fact_2, SUM(rbs_ps_mchdelay_bcf_raw.ABIS_ROUND_TRIP_TIME_SUCC) as result_2,
'JB_FILL_LEVEL_MIN_SUM_BSC (Mchdelay)' as fact_3, SUM(rbs_ps_mchdelay_bcf_raw.JB_FILL_LEVEL_MIN_SUM_BSC) as result_3,
'ABIS_ROUND_TRIP_TIME_MAX (Mchdelay)' as fact_4, MAX(rbs_ps_mchdelay_bcf_raw.ABIS_ROUND_TRIP_TIME_MAX) as result_4,
'JB_FILL_LEVEL_SAMPLES_BSC (Mchdelay)' as fact_5, SUM(rbs_ps_mchdelay_bcf_raw.JB_FILL_LEVEL_SAMPLES_BSC) as result_5,
'JB_FILL_LEVEL_MAX_SUM_BSC (Mchdelay)' as fact_6, SUM(rbs_ps_mchdelay_bcf_raw.JB_FILL_LEVEL_MAX_SUM_BSC) as result_6,
'ABIS_ROUND_TRIP_TIME_SAMPLES (Mchdelay)' as fact_7, SUM(rbs_ps_mchdelay_bcf_raw.ABIS_ROUND_TRIP_TIME_SAMPLES) as result_7
from
rbs_ps_mchdelay_bcf_raw,
rbs_pd_etip_objects,
roh_ds_utp_common_objects_dim rbs_BSC_objects,
rbs_pd_pw1_objects,
rbs_pd_exsubtsl_objects,
roh_ds_utp_common_objects_dim rbs_BCF_objects
where
rbs_ps_mchdelay_bcf_raw.etip_unit_index_in_bsc = rbs_pd_etip_objects.etip_id and
rbs_BSC_objects.co_oc_id = 3 and
rbs_ps_mchdelay_bcf_raw.bsc_gid = rbs_BSC_objects.co_gid and
rbs_ps_mchdelay_bcf_raw.pw_id = rbs_pd_pw1_objects.pw_id and
rbs_ps_mchdelay_bcf_raw.bsc_gid = rbs_pd_exsubtsl_objects.bsc_gid and
rbs_ps_mchdelay_bcf_raw.pcm_id = rbs_pd_exsubtsl_objects.expcm_id and
rbs_ps_mchdelay_bcf_raw.tsl = rbs_pd_exsubtsl_objects.extsl_id and
rbs_ps_mchdelay_bcf_raw.sub_tsl = rbs_pd_exsubtsl_objects.exsubtsl_id and
rbs_BCF_objects.co_oc_id = 27 and
rbs_ps_mchdelay_bcf_raw.bcf_gid = rbs_BCF_objects.co_gid
and
( rbs_ps_mchdelay_bcf_raw.period_start_time >= (trunc(( SYSDATE+0.20833333333333334 ), 'dd')-(2*7)) and rbs_ps_mchdelay_bcf_raw.period_start_time < trunc(( SYSDATE+0.20833333333333334 ), 'dd') )
group by
rbs_BSC_objects.co_gid,NVL(rbs_BSC_objects.co_name, NVL(rbs_BSC_objects.co_object_instance, rbs_BSC_objects.co_gid)), trunc(rbs_ps_mchdelay_bcf_raw.period_start_time, 'HH24')
union (
select
"XXXXX BSC0_0",
"XXXXX BSC0",
"Hour0",
'period_duration (Mchdelay)' as fact, AVG(result) as result,
to_char(NULL) as fact_1, to_number(NULL) as result_1,
to_char(NULL) as fact_2, to_number(NULL) as result_2,
to_char(NULL) as fact_3, to_number(NULL) as result_3,
to_char(NULL) as fact_4, to_number(NULL) as result_4,
to_char(NULL) as fact_5, to_number(NULL) as result_5,
to_char(NULL) as fact_6, to_number(NULL) as result_6,
to_char(NULL) as fact_7, to_number(NULL) as result_7
from
(
select
trunc(rbs_ps_mchdelay_bcf_raw.period_start_time, 'HH24') as "Hour0",
rbs_BSC_objects.co_gid as "XXXXX BSC0_0",
NVL(rbs_BSC_objects.co_name, NVL(rbs_BSC_objects.co_object_instance, rbs_BSC_objects.co_gid)) as "XXXXX BSC0",
rbs_BCF_objects.co_gid as "XXXXX BCF0_0",
NVL(rbs_BCF_objects.co_name, NVL(rbs_BCF_objects.co_object_instance, rbs_BCF_objects.co_gid)) as "XXXXX BCF0",
rbs_pd_etip_objects.etip_id as "ETIP Unit0_0",
rbs_pd_etip_objects.etip_id as "ETIP Unit0",
rbs_pd_exsubtsl_objects.bsc_gid as "External SUB TSL0_0",
rbs_pd_exsubtsl_objects.expcm_id as "External SUB TSL0_1",
rbs_pd_exsubtsl_objects.extsl_id as "External SUB TSL0_2",
rbs_pd_exsubtsl_objects.exsubtsl_id as "External SUB TSL0_3",
rbs_pd_exsubtsl_objects.exsubtsl_id as "External SUB TSL0",
rbs_pd_pw1_objects.pw_id as "pw10_0",
rbs_pd_pw1_objects.pw_id as "pw10",
'period_duration (Mchdelay)' as fact, SUM(rbs_ps_mchdelay_bcf_raw.period_duration) as result
from
rbs_ps_mchdelay_bcf_raw,
rbs_pd_etip_objects,
roh_ds_utp_common_objects_dim rbs_BSC_objects,
rbs_pd_pw1_objects,
rbs_pd_exsubtsl_objects,
roh_ds_utp_common_objects_dim rbs_BCF_objects
where
rbs_ps_mchdelay_bcf_raw.etip_unit_index_in_bsc = rbs_pd_etip_objects.etip_id and
rbs_BSC_objects.co_oc_id = 3 and
rbs_ps_mchdelay_bcf_raw.bsc_gid = rbs_BSC_objects.co_gid and
rbs_ps_mchdelay_bcf_raw.pw_id = rbs_pd_pw1_objects.pw_id and
rbs_ps_mchdelay_bcf_raw.bsc_gid = rbs_pd_exsubtsl_objects.bsc_gid and
rbs_ps_mchdelay_bcf_raw.pcm_id = rbs_pd_exsubtsl_objects.expcm_id and
rbs_ps_mchdelay_bcf_raw.tsl = rbs_pd_exsubtsl_objects.extsl_id and
rbs_ps_mchdelay_bcf_raw.sub_tsl = rbs_pd_exsubtsl_objects.exsubtsl_id and
rbs_BCF_objects.co_oc_id = 27 and
rbs_ps_mchdelay_bcf_raw.bcf_gid = rbs_BCF_objects.co_gid
and
( rbs_ps_mchdelay_bcf_raw.period_start_time >= (trunc(( SYSDATE+0.20833333333333334 ), 'dd')-(2*7)) and rbs_ps_mchdelay_bcf_raw.period_start_time < trunc(( SYSDATE+0.20833333333333334 ), 'dd') )
group by
trunc(rbs_ps_mchdelay_bcf_raw.period_start_time, 'HH24'),
rbs_BSC_objects.co_gid,
NVL(rbs_BSC_objects.co_name, NVL(rbs_BSC_objects.co_object_instance, rbs_BSC_objects.co_gid)),
rbs_BCF_objects.co_gid,
NVL(rbs_BCF_objects.co_name, NVL(rbs_BCF_objects.co_object_instance, rbs_BCF_objects.co_gid)),
rbs_pd_etip_objects.etip_id,
rbs_pd_etip_objects.etip_id,
rbs_pd_exsubtsl_objects.bsc_gid,
rbs_pd_exsubtsl_objects.expcm_id,
rbs_pd_exsubtsl_objects.extsl_id,
rbs_pd_exsubtsl_objects.exsubtsl_id,
rbs_pd_exsubtsl_objects.exsubtsl_id,
rbs_pd_pw1_objects.pw_id,
rbs_pd_pw1_objects.pw_id
)
group by
"XXXXX BSC0_0",
"XXXXX BSC0",
"Hour0"
);
exec :stop_time := to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss.ff3');exec :time_diff := substr(to_char(to_timestamp(:stop_time, 'yyyy-mm-dd hh24:mi:ss.ff3') - to_timestamp(:start_time, 'yyyy-mm-dd hh24:mi:ss.ff3')), 11, 13);
exec DBMS_OUTPUT.PUT_LINE('query execution took (HH24:MI:SS.xxx) : '||:time_diff);----------------End----------------set timing off;
SPOOL OFF;
exit;
还有null为啥还要to_char,to_number
怎么走的全是全表扫描?你用HINT提示并行执行看看效果,
感觉是没有索引,对比另外一组SQL,第一个选取大致ROW数量在45000,第二个是400000,两者耗时几乎相等。
以下是第二个计划解释,我看到大量才有了INDEX或者LOCAL INDEX,用RANGE SCAN代替了FULL,是这样么?
minitoy给些提示吧!
你按java3344520说的查查,如果有索引可以加hints。格式是
/*+index(表名 索引名)*/
select /*+index(test test1)*/ * from test
where a=1;
这个脚本是机器生成的,所以看起来有些奇怪(虽然也不是准备给人看的) :)
1. Too many tables use full scanning.
2. Some tables have no index or can not use it.
Way Out:
1. Check that if all tables need full scanning really.
If answer is yes, maybe could improve efficiency by parallel querying;
If not, create index to avoid it.
2. Find out why selections do not go index way. Have all files in where clause created concatenated indexes?
3. Using HINTS to optimize SQL script.
4. Lookup which resource system is waiting for?
Using command: v$session_wait.sid = your session sid
5. Other optimization solutions.
Relation materials:
1. Comprehensive material about Oracle execution plan and SQL trace,
http://report.retailsolution.cn:81/wp-content/uploads/2009/01/sql-tracee4b88ee689a7e8a18ce8aea1e58892e69cafe8afade8a7a3e8afbb-v1.pdf
2. HINTS specification, http://www.javaeye.com/topic/193649
3. More discussion about SQL optimize efficiency,
http://wenku.baidu.com/view/7ae1d20f76c66137ee061917.html
http://hi.baidu.com/mumu1108/blog/item/200035f5df1fa82dbd310976.html