关于oracle的CBO一些不可思议的事情-CSDN博客

1）今天运行了下面的脚本
insert into FS_DEVICE_CURRENT_COLD(
current_id,manufacturer,device_instance_id, MODEL, DEVICE_VERSION, LAST_MODIFIED_TIME, DEVICE_ID, NODE_MASK, NODE_MASK_ID
)
select /*+use_hash(b a)*/ b.CURRENT_ID, b.manufacturer,b.device_instance_id, b.MODEL, b.DEVICE_VERSION
, b.LAST_MODIFIED_TIME, a.DEVICE_ID, 0 NODE_MASK, 0 NODE_MASK_ID
from odomain19.FS_DEVICE_CURRENT b, mvp_device_instance_cold a
where b.device_instance_id= a.device_instance_id(+);
这是一条普通的插入语句，表a有5kw条记录，表b有200万条记录，强制使用了hash join
在a.device_instance_id和b.device_instance_id上面分别有一个索引
关键问题肯定出现在索引上面
我在测试的时候运行是在golden中运行的，查看了执行计划，肯定是用到了hash join
但在正式环境运行的时候，我放在了shell中运行，用的方式就是普通 shell调用一个sql文件，将insert语句放在sql文件中
我查看了正式环境中的解释计划，用到了嵌套循环连接，不知为何，强制写了/*+use_hash(b a)*/，还仍然使用嵌套循环连接，难以解释。
查看运行环境的解释计划脚本如下：
select * --distinct aaa,sql_id
from (select b.sql_text aaa,a.timestamp,a.sql_id,a.operation,a.options,a.object_owner,a.object_name
,a.object_type,a.optimizer,a.cost,a.cardinality,a.bytes,a.cpu_cost
,a.io_cost,b.sql_text,b.loads,b.first_load_time
from v$sql_plan a,v$sqlarea b
where a.sql_id=b.sql_id
--and (object_owner not in ('SYS' ,'SYSMAN','SYSTEM','XDB','MDSYS') or object_owner is null)
--and a.sql_id='68n5tg37fy6g2'
and b.sql_text like '%FS_DEVICE_CURRENT_COLD%'
)
2）然后我用DBMS_STATS收集了一些信息
之后再到正式环境中运行脚本，我发现居然用到了merge join

3）我于是想到了清空share pool
alter system flush shared_pool;
然后在正式环境中运行脚本，这次确实用到了hash join，但居然花费了1个小时才运行完毕
普通运行这个脚本需要花费5分钟左右，居然这次用到了1个小时的时间。

---系统10046监控的语句
--开始监控，odomain0是用户，12是级别，是最高级别
exec dbms_system.set_ev(493,196,10046,12,'ODOMAIN0')
--其中第一个和第二个参数通过下面语句得到
select sid,serial#,username from v$session where username is not null and status='ACTIVE'
--and sid=493
order by status
--结束监控
exec dbms_system.set_ev(493,196,10046,0,'ODOMAIN0')

---增加PGA
alter session set workarea_size_policy=MANUAL;
alter session set sort_area_size=524288000;--500M
alter session set hash_area_size=524288000;--500M

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/41451/viewspace-1015970/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/41451/viewspace-1015970/