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
这篇文章对你有帮助吗,投个票吧?
page revision: 0, last edited: 22 Jun 2014 15:30
留下你的评论