Re: Subject: Re: Varying plans on different nodes
- From: fairlie rego <fairlie_r@xxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 8 Jun 2007 15:14:01 -0700 (PDT)
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@xxxxxxxxxxxxxxxxxx> 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
>
> --
> //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.
Other related posts:
