Re: Subject: Re: Varying plans on different nodes

I don't think we can prove what happened after the event, especially on a RAC system - but I think the statement was optimised on one node with one set of system statistics, and optimised on the other with a different set of system statistics. Changing the system statistics does not invalidate current execution plans - so the plan could persist long after the initial optimisation.

Corroborative observation (from the 10053 trace files you sent me). Here's the index only access path from the A2 object

Access Path: index (index-only)
Index: ADVANCED_FILTER_PK
rsc_cpu: 105519992 rsc_io: 2411
ix_sel: 2.2936e-01 ix_sel_with_filters: 2.2936e-01 BEST_CST: 970.90 PATH: 4 Degree: 1

Access Path: index (index-only)
Index: ADVANCED_FILTER_PK
rsc_cpu: 105519992 rsc_io: 2411
ix_sel: 2.2936e-01 ix_sel_with_filters: 2.2936e-01 BEST_CST: 966.63 PATH: 4 Degree: 1

Note that the rsc_io and rsc_cpu are the same for both, but the final costs differ. This suggests most strongly that the CPUSPEED statistic used for optimisation was different - and if that was different, the read time stats would probably differ as well.

Because of the optimizer_index_cost_adj we can say:

    966.63 = 0.4 * 2411 + 105519992 / (cpuspeed1 * 1000 * sreadtim1)     970.90 = 0.4 * 2411 + 10551992 / (cpuspeed2 * 1000 * sreadtim2)

Unfortunately we don't have enough examples with enough precision in the entire file to produce another pair of equations that would allow us to solve for the four unknowns.

Odd little anomaly - your system statistics have an MBRC that is larger than the db_file_multiblock_read_count. That shouldn't be possible.

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" <fairlie_r_at_yahoo.com> To: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org> Sent: Friday, June 08, 2007 11:14 PM Subject: Re: Subject: Re: Varying plans on different nodes

> 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
>

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