Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Optimizer Statistics,Histograms and 10.2.0.2

Re: Optimizer Statistics,Histograms and 10.2.0.2

From: Fuad Arshad <fuadar_at_yahoo.com>
Date: Fri, 17 Nov 2006 08:18:37 -0800 (PST)
Message-ID: <20061117161837.87137.qmail@web82104.mail.mud.yahoo.com>

i used this query to get the result set below 
select * from DBA_OPTSTAT_OPERATIONS 
where operation like '%database%';

yes the 2 entries are part of the mytery.
i've looked at all parts of the applications and no one has even authority to execute gather_database_stats.
so its something oracle is doing but i cant figure why and how.
the entries are 2 at  a time and are random sometimes 1 week and sometimes 15 days. so i cant come up with a pattern.
can you tell me how i can change the method_opt parameter so it  defaults to size 1 




----- Original Message ----
From: Wolfgang Breitling <breitliw@centrexcc.com>
To: fuadar@yahoo.com
Cc: oracle-l@freelists.org
Sent: Friday, November 17, 2006 9:48:56 AM
Subject: Re: Optimizer Statistics,Histograms and 10.2.0.2


Fuad,

Two things:

(a) In addition to disabling the auto-gather job I would also (and 
have on "my" Oracle 10 systems) change the method_opt parameter 
default from 'for all columns size auto' back to 'for all columns 
size 1' so that if anyone or anything submits a dbms_stats.gather 
request without specifying method_opt you don't get any unwanted histograms.

(b) Where did you get that list from? From optstat_tab_history? How 
come there are two gather_database_stats entries for each day shortly 
after each other? Or is that part of the mystery?

(c) I noticed that the time jumped by one hour when daylight savings 
time ended. That leads me to believe that the origin is not within 
Oracle since that would keep the same time-of-day even after the 
DT-ST and vice versa switch. I have noticed that shift by 1 hour in 
weblogic appservers that I had set up to start a new log at midnight. 
After daylight savings kicked in the logs switched an hour earlier 
(or later, I forgot which).

At 08:21 AM 11/17/2006, Fuad Arshad wrote:
>We're facing a weird issue with 10.2.0.2 which did not exist in 10.1 
>and before.
>We've  disabled the standard stats gatherign job since it is 
>generating histograms which doesnt work well with the application.
>this is what my  job looks like
>[snip]
>but every so often  i see histograms back on the tables and the 
>peformance starts sinking.
>operation target start_time end_time
>gather_database_stats   10/17/2006 1:35:13.261525 AM -05:00 
>10/17/2006 1:35:13.853932 AM -05:00
>gather_database_stats   10/17/2006 1:35:14.157003 AM -05:00 
>10/17/2006 1:35:14.319034 AM -05:00
>gather_database_stats   10/19/2006 1:20:17.302342 AM -05:00 
>10/19/2006 1:20:18.074960 AM -05:00
>gather_database_stats   10/19/2006 1:20:18.560200 AM -05:00 
>10/19/2006 1:20:18.737658 AM -05:00
>gather_database_stats   10/20/2006 1:35:27.848579 AM -05:00 
>10/20/2006 1:35:28.617191 AM -05:00
>gather_database_stats   10/20/2006 1:35:28.953961 AM -05:00 
>10/20/2006 1:35:29.153783 AM -05:00
>gather_database_stats   10/27/2006 1:15:12.106323 AM -05:00 
>10/27/2006 1:15:12.790376 AM -05:00
>gather_database_stats   10/27/2006 1:15:13.117510 AM -05:00 
>10/27/2006 1:15:13.286650 AM -05:00
>gather_database_stats   10/31/2006 1:20:13.260779 AM -06:00 
>10/31/2006 1:20:13.976838 AM -06:00
>gather_database_stats   10/31/2006 1:20:14.305219 AM -06:00 
>10/31/2006 1:20:14.457594 AM -06:00
>gather_database_stats   11/1/2006 1:45:09.231076 AM -06:00 11/1/2006 
>1:45:09.906855 AM -06:00
>gather_database_stats   11/1/2006 1:45:10.211063 AM -06:00 11/1/2006 
>1:45:10.376020 AM -06:00
>gather_database_stats   11/15/2006 1:15:07.822728 AM -06:00 
>11/15/2006 1:15:08.403452 AM -06:00
>gather_database_stats   11/15/2006 1:15:08.746648 AM -06:00 
>11/15/2006 1:15:08.899510 AM -06:00
>

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com 



______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 17 2006 - 10:18:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US