RE: gather stats

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Mon, 9 Nov 2015 18:32:18 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED1FE4D379_at_USA7109MB012.na.xerox.net>

So with the introduction of Adaptive plans and Adaptive Statistics in 12c where Oracle can (in theory) optimize plans based on the feedback, would histograms still pose issues? I mean, wouldn’t the optimizer be able to handle any bad plans through its Adaptive features if it indeed makes a bad choice in the beginning?

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chris Taylor Sent: Monday, November 09, 2015 1:14 PM To: MAdams_at_troversolutions.com
Cc: oracle-l
Subject: Re: gather stats

Matt,

Histograms can cause the optimizer to make some really bad or really good decisions. Histograms are good for SQL statements that use literals but can be detrimental for systems that use binds. (They can also be good for systems that use binds if the data is evenly distributed between stored values).

So, histograms are not always bad and are not always good either. You have to really be aware of them when performance tuning as they could be helping you or killing you depending on the specific situation.

Chris

On Mon, Nov 9, 2015 at 12:06 PM, Matt Adams <MAdams_at_troversolutions.com<mailto:MAdams_at_troversolutions.com>> wrote: I can understand not gathering histograms across all columns due to the potential time it would take to do so. Is there any other technical reason not to..(perhaps some issue with the optimizer I'm not currently aware of)?

I routinely gather histogram information for all INDEXED columns.

Matt Adams

-----Original Message-----
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Wolfgang Breitling Sent: Monday, November 09, 2015 12:13 PM To: kp0773_at_gmail.com<mailto:kp0773_at_gmail.com> Cc: Oracle-L_at_freelists.org<mailto:Oracle-L_at_freelists.org> Subject: Re: gather stats

Use auto_sample_size and refrain from gathering histograms on all columns ( method_opt 'for all columns size 1')

Sent from my iPhone. Typing errors may have occurred.

> On Nov 9, 2015, at 10:01, K R <kp0773_at_gmail.com<mailto:kp0773_at_gmail.com>> wrote:
>
> All,
>
> What is the recommended way of gathering stats on 11gR2 database ( 2.5 TB) . If i keep estimate percent 10,15 then some of the table are taking hours and hours .
> Please let me know .
>
> Thanks in advance.
>
> Kart.

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

• This communication may contain privileged and/or confidential information. If you are not the intended recipient, you are hereby notified that disclosing, copying, or distributing of the contents is strictly prohibited. If you have received this message in error, please contact the sender immediately and destroy any copies of this document. ****

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

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

Received on Mon Nov 09 2015 - 19:32:18 CET