Oracle执行计划解释，在线等

xiaozi1220 2010-07-27 02:47:27
菜鸟我手上有一段SQL代码，效率相比其他同组代码较低，我想找到这段代码的瓶颈并提出解决方案。

于是，我用Oracle SQL Developer得到代码的执行计划，如下图：



等待高手指点迷津！
我的问题：
1. 这张图怎么看，瓶颈在哪里？
2. 可以从哪里着手优化？
...全文
249 21 打赏 收藏 转发到动态 举报
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
xmanliming 2010-07-28
  • 打赏
  • 举报
 回复
学习下。
xiaozi1220 2010-07-28
  • 打赏
  • 举报
 回复
Here is my conclusion about SQL analyse task. Any comments and discussion are welcome.

Main Prob:
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
xiaozi1220 2010-07-27
  • 打赏
  • 举报
 回复
等问题解决了，我会就各位的意见以及我查到的东西来个总结的。
谢谢大家了。
xiaozi1220 2010-07-27
  • 打赏
  • 举报
 回复
接受tangren的建议。
这个脚本是机器生成的，所以看起来有些奇怪（虽然也不是准备给人看的） ：）
tangren 2010-07-27
  • 打赏
  • 举报
 回复
直接引表名来限定列，头晕~~~~
养成一个良好的习惯，为表指定一个简短的别名。
kingkingzhu 2010-07-27
  • 打赏
  • 举报
 回复
这100分花的值
xiaozi1220 2010-07-27
  • 打赏
  • 举报
 回复
谢谢 minitoy & java3344520 我先试一下
minitoy 2010-07-27
  • 打赏
  • 举报
 回复
比如有个表test含a和b两列，列a上有索引test1，可以指定sql走这个索引的。
select /*+index(test test1)*/ * from test
where a=1;
minitoy 2010-07-27
  • 打赏
  • 举报
 回复
上面不是写着table access full 了么。
你按java3344520说的查查，如果有索引可以加hints。格式是
/*+index(表名 索引名)*/
xiaozi1220 2010-07-27
  • 打赏
  • 举报
 回复
右手一抖，就按错了，这个才是第二组计划的图片：


它选取的数据是有问题那个的接近10倍，时间却几乎相同。
xiaozi1220 2010-07-27
  • 打赏
  • 举报
 回复
我不知道怎么查看表有没索引，minitoy能告诉我一下么？
感觉是没有索引，对比另外一组SQL，第一个选取大致ROW数量在45000，第二个是400000，两者耗时几乎相等。
以下是第二个计划解释，我看到大量才有了INDEX或者LOCAL INDEX，用RANGE SCAN代替了FULL，是这样么？
minitoy给些提示吧！

iqlife 2010-07-27
  • 打赏
  • 举报
 回复
表中没有主键和索引?
怎么走的全是全表扫描?

你用HINT提示并行执行看看效果,
minitoy 2010-07-27
  • 打赏
  • 举报
 回复
有索引么？
还有null为啥还要to_char,to_number
xiaozi1220 2010-07-27
  • 打赏
  • 举报
 回复
SET serveroutput ON size 12000;
set termout off;
var start_time varchar2(40);
var stop_time varchar2(40);
var time_diff varchar2(40);

col spoolname new_value splname nopri

select '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;
minitoy 2010-07-27
  • 打赏
  • 举报
 回复
全部全表扫描。。。。。。
贴你的sql看看
xiaozi1220 2010-07-27
  • 打赏
  • 举报
 回复
图片如下：


谢谢了
minitoy 2010-07-27
  • 打赏
  • 举报
 回复
上传到自己空间再发个链接看看。
只能上csdn滴说
gisinfo 2010-07-27
  • 打赏
  • 举报
 回复
图看不到呀
xiaozi1220 2010-07-27
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 java3344520 的回复:]
看不到图.....你看看哪里消耗的COST多...
[/Quote]

看不到图么？ 这组语句有些复杂
xiaozi1220 2010-07-27
  • 打赏
  • 举报
 回复
正在看相关材料，希望得到一些指点，先个谢了。
加载更多回复（1）
Oracle11g客户端精简版
Oracle 11g客户端是Oracle数据库的一个轻量级版本，主要供开发人员和系统管理员用于连接到Oracle数据库服务器，执行查询、管理和维护数据库任务。这个精简版在保持基本功能的同时，减少了安装体积，便于快速部署和...
oracle的解释计划,oracle解释&执行计划-Oracle
执行计划解释计划语句EXPLAIN PLAN用来显示优化器为SQL语句所选择的执行计划。需要说明的是当你得到了解释计划输出的时候，你其实是得到了当SQL语句执行的时候应该采用的预期执行计划。你并没有得到实际的执行计划...
Oracle 执行计划详解（预估 + 真实）
文章目录1 概述1.1 思维导图1.2 概念2 执行计划2.1 预估的2.2 真实的3 示例4 备选命令 1 概述 什么是 Oracle 的执行计划？ 执行计划是一条查询语句...oracle 的执行计划是很复杂的，一般我们看到的执行计划都是 oracle
oracle 执行计划详解
执行计划是指示 Oracle 如何获取和过滤数据、产生最终的结果集，是影响SQL 语句执行性能的关键因素。在深入了解执行计划之前，首先需要知道执行计划是在什么时候产生的，以及如何让 SQL 引擎为语句生成执行划。先...
Oracle 执行计划篇
1.执行计划的常用列字段解释： 基数（Rows）：Oracle估计的当前操作的返回结果集行数 字节（Bytes）：执行该步骤后返回的字节数 耗费（COST）、CPU耗费：Oracle估计的该步骤的执行成本，用于说明SQL执行的代价， ...
基础和管理

17,378

社区成员

95,113

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章