Re: Query Performance with params

On 05/01/2017 10:36 AM, Jonathan Lewis wrote:
> Mladen,
>
> Larger histograms -- uses the same 254 columns by default
> New calculation for clustering_factor -- uses the old mechanism (table_cached_blocks = 1) by default
>
> New type of histograms: you still get Top-N and Hybrid histograms with OFE= 11.2.0.4 if you have preference approximate_ndv = true (though you can disable them). But Frequency and Top-N give you much better accuracy virtually free of ANY cost (in 12.1.0.2) and Hybrid give you better accuracy for no increase over the 11g cost - the main problem is that frequency and top-N histograms may be based on a small sample size if you set OFE=11.2.0.4
>
> Regards
> Jonathan Lewis
Hi Jonathan,
I tend to agree with you with respect to the optimizer, although I've had my fair share of performance problems after upgrade. However the instance crash described in the thread named "strange problem" was caused by asynchronous IO. Oracle support asked me to set disk_asynch_io to FALSE. The same thing worked well in 11.2.0.4 instance. If only Oracle could talk to the manufacturer of Solaris and ZFS, maybe things would be better then.
That is the reason why I advise clients not to upgrade to 12.1.0.2. Basically, each upgrade is a painful process which takes 6 months of testing and planning. Upgrading to 12.1 means that at some point it will become necessary to upgrade to 12.2 and here go another 6 months of hard labour on the chain gang.
on the other hand, 11.2.0.4 will be supported until 2018, which gives me enough time to wait for the 1st major patchset. And my experience tells me that 12.1 is not very stable yet.
Regards

-- 
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217

--
http://www.freelists.org/webpage/oracle-l