Oracle 在dbms_stat中使用的SQL及HINT

博客首页 » Oracle 在dbms_stat中使用的SQL及HINT

发布于 22 Jun 2014 15:30
标签 blog
dbms_stat中使用的SQL

select substrb(dump(val,16,0,32),1,120) ep, cnt
from
(select /*+ no_parallel(t) no_parallel_index(t) 
dbms_stats cursor_sharing_exact 
use_weak_name_resl dynamic_sampling(0) 
no_monitoring */
max(substrb("COL1",1,32)) val,count(*) cnt
from "OWNER1"."TAB1" t
where substrb("COL1",1,32) is not null 
group by nlssort(substrb("COL11",1,32), 'NLS_SORT = binary')) 
order by nlssort(val,'NLS_SORT = binary');
select substrb(dump(val,16,0,32),1,120) ep, cnt
from
(select /*+ no_parallel(t) no_parallel_index(t) dbms_stats 
cursor_sharing_exact use_weak_name_resl 
dynamic_sampling(0) no_monitoring */
max("C") val,count(*) cnt
from "USER"."T" t
where "C" is not null group by nlssort("C", 'NLS_SORT = binary')) 
order by nlssort(val,'NLS_SORT = binary');

select  min(minbkt),maxbkt,
substrb(dump(min(val),16,0,32),1,120) minval,
substrb(dump(max(val),16,0,32),1,120) maxval,
sum(rep) sumrep, sum(repsq) sumrepsq, 
max(rep) maxrep, count(*) bktndv, 
sum(case when rep=1 then 1 else 0 end) unqrep
from
(select val,min(bkt) minbkt, max(bkt) maxbkt, 
count(val) rep, count(val)*count(val) repsq
from
(select /*+ no_parallel(t) no_parallel_index(t) 
dbms_stats cursor_sharing_exact use_weak_name_resl 
dynamic_sampling(0) no_monitoring */
"C" val, ntile(254) 
over (order by nlssort("C",'NLS_SORT = binary')) bkt
from "USER"."T" t
where TBL$OR$IDX$PART$NUM("USER"."T",0,4,0,"ROWID") 
= :objn and "C" is not null) 
group by val) 
group by maxbkt 
order by maxbkt

本页面的文字允许在知识共享 署名-相同方式共享 3.0协议和GNU自由文档许可证下修改和再使用,仅有一个特殊要求,请用链接方式注明文章引用出处及作者。请协助维护作者合法权益。


系列文章

文章列表

  • Oracle 在dbms_stat中使用的SQL及HINT

这篇文章对你有帮助吗,投个票吧?

rating: 0+x

留下你的评论

Add a New Comment
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License