关于histograms 与 hard parse 的关系,一些迷惑!!

Yong Huang

最初由 SONGHEFEI 发布
[B]Jonathan Lewis's "Cost-Based Oracle" 这本书我们没有看过，
On p.159 of his book, he says "two things will trigger this reoptimization: first, if any of the predicates involves a range scan, and second, even on a simple equality, if there is histogram on a column that appears in a predicate, the query will be reoptimized
但是我想oracle 总不会因为列上存在histograms 而每次都要create new cursor  吧？如果在高并发的情况下，对数据库系统也是一个不小的负担啊！！ [/B]

I quoted that text in the context of setting cursor_sharing to similar. If cursor_sharing=similar, the behavior is the same as cursor_sharing left as the default, exact. That is, a new cursor is created for each SQL. Many developers don't know they need to use bind variables and cursor_sharing is left as default. That *is* causing performance and scalability problems.

Yong Huang

clar

ding

clar

顶

SONGHEFEI

前两天,数据库进行了restart ,我又做了一次测试,测试的结果与biti , Yong Huang 的结果完全相同,但是重新启动之前竟然是那个结果!真是搞不懂!而且这一次我的索引是唯一索引:
create unique index idid on test_cursor(tcid) ;
并且指定了histograms 的bucket 的数量为75,而不是auto
但是如果根据这个测试结果,如果该表存在100万行,在进行
select  * from test_cursor tc where tc.tcid=':sys_1' 的时候,每次付值不同的话,难道就要创建100万个cursor ?

Yong Huang

最初由 SONGHEFEI 发布
[B]...
如果该表存在100万行,在进行
select  * from test_cursor tc where tc.tcid=':sys_1' 的时候,每次付值不同的话,难道就要创建100万个cursor ? [/B]

According to Jonathan Lewis's and biti's research, yes, if you provide 1 million different values to tc.tcid and there's a histogram on it and cursor_sharing is similar, 1 million cursors will be created.

Yong Huang

SONGHEFEI

最初由 Yong Huang 发布
[B]

According to Jonathan Lewis's and biti's research, yes, if you provide 1 million different values to tc.tcid and there's a histogram on it and cursor_sharing is similar, 1 million cursors will be created.

Yong Huang [/B]

所以,histograms 并不是为分布均匀的列所用的,同样在distinct value
很高的情况下,也没有必要分析histograms ,否则严重影响性能: 除了大量的hard parse 外,对shared pool 同样产生更多的小的chunk,并且在同一个bucket 下!