Re: Subject: Re: Varying plans on different nodes

From: fairlie rego <fairlie_r_at_yahoo.com>
Date: Fri, 8 Jun 2007 15:14:01 -0700 (PDT)
Message-ID: <227884.6404.qm@web31910.mail.mud.yahoo.com>

Sorry There is a weekly job on this system which does gather_system_stats and since it belongs to SYS I thought it was a default job . My bad...

SYS.DBMS_SCHEDULER.CREATE_JOB
(

       job_name        => 'GATHER_SYSTEM_STATS'


      ,start_date      => TO_TIMESTAMP_TZ('2006/04/01 00:00:00.000000 +10:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')

      ,repeat_interval => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=10'

      ,end_date        => NULL

      ,job_class       => 'DEFAULT_JOB_CLASS'

      ,job_type        => 'PLSQL_BLOCK'

      ,job_action      => 'begin dbms_stats.gather_system_stats(gathering_mode=>''interval'',interval=>120); end;'

      ,comments        => 'Gathers system statistics - dbms_stats.gather_system_stats'

    );
    
   
select * from sys.aux_stats$
  
SNAME PNAME PVAL1 PVAL2
  SYSSTATS_INFO STATUS  COMPLETED
SYSSTATS_INFO DSTART  06-04-2007 10:00
SYSSTATS_INFO DSTOP  06-04-2007 12:00
SYSSTATS_INFO FLAGS 0 
SYSSTATS_MAIN CPUSPEEDNW 555.40508245041 
SYSSTATS_MAIN IOSEEKTIM 10 
SYSSTATS_MAIN IOTFRSPEED 4096 
SYSSTATS_MAIN SREADTIM 29.043 
SYSSTATS_MAIN MREADTIM 11.17 
SYSSTATS_MAIN CPUSPEED 651 
SYSSTATS_MAIN MBRC 17

SYSSTATS_MAIN MAXTHR 21626880
SYSSTATS_MAIN SLAVETHR 3072 SQL> show parameter db_file

  NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16

On instance 1

processes                            integer     5000
__db_cache_size                      big integer 40576M

On Instance 3

processes                            integer     5000
__db_cache_size                      big integer 39184M

The above differing values are because we use sga_target

The table stats gathering job had been turned off for the last few weeks because of instability with plans. This will be turned on back again tonight...

Thanks
Fairlie

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

I don't have 10.1.0.5 available, but my 10.2 instances don't gather system stats in the gather_stats_job.

What do the contents of sys.aux_stats$ look like, and what are the instance values for the three parameters I listed.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message ----- From: "fairlie rego" To: Cc: Sent: Friday, June 08, 2007 10:24 PM Subject: Re: Subject: Re: Varying plans on different nodes

> Jonathan
>
> DB version : 10.1.0.5
>
> System stats are gathered by Oracle's default job which runs in the default 
> window.
>
> Thanks
> Jonathan Lewis wrote:
>
> Are you running 10.1 or 10.2
> Have you gathered system stats, or do you let
> Oracle set the noworkload values on startup.
>
> If the latter,
> Have you set the db_file_mulitblock_read_count
> If not,
> What is the value of "processes"
> What was the size of the db_cache at the time the
> queries were optimised
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>>
>> ------------------------------
>>
>> Date: Thu, 7 Jun 2007 10:22:41 -0700 (PDT)
>> From: fairlie rego
>> Subject: Re: Varying plans on different nodes
>>
>> Thanks for your emails
>>
>> Its 3:00 am in windy and rainy Sydney and not sure why Fairlie is actually
>> awake.
>>
>> The plans are indeed same with workarea_size_policy MANUAL (i.e no MJC)
>>
>> Thanks
>> Fairlie
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
> Fairlie Rego
> Senior Oracle Consultant
> http://el-caro.blogspot.com/
> M: +61 402 792 405
>




          Fairlie Rego

Senior Oracle Consultant
http://el-caro.blogspot.com/
M: +61 402 792 405

Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games.

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

Received on Fri Jun 08 2007 - 17:14:01 CDT