| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Nov 17 2006 - 09:48:56 CST
 |
 |