Re: Why Statistics doesn't match reponse time?

"Bin" <wangbinlxx_at_gmail.com> wrote in message news:1126506115.193863.81950_at_g43g2000cwa.googlegroups.com...
> I think this time there is a bug in 9.2.0.3 . I try different load on
> the database. The system statistics looks like this.
>
> NAME PVAL1
> ------------------------------ ----------
> CPUSPEED 448
> MAXTHR -1
> MBRC 14
> MREADTIM 6.457
> SLAVETHR -1
> SREADTIM 1.945
>
> PNAME PVAL1
> ------------------------------ ----------
> CPUSPEED 451
> MAXTHR -1
> MBRC 15
> MREADTIM 11.519
> SLAVETHR -1
> SREADTIM 3.577
>
> PNAME PVAL1
> ------------------------------ ----------
> CPUSPEED 455
> MAXTHR 130048
> MBRC 14
> MREADTIM 10.523
> SLAVETHR -1
> SREADTIM 3.257
>

Whatever's going on, your mreadtim is about 3 x your sreadtim - and the MBRC is fairly constant, so the change in costs across these three sets of stats won't be dramatic.

> When I run two queries, both plans are the same.(Both are changed.)
> Hint :
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2477 Card=1789
> Bytes=48303)
> 1 0 SORT (GROUP BY) (Cost=2477 Card=1789 Bytes=48303)
> 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CONTRIBUTIONLOG'
> (Cost=309 Card=1044 Bytes=24012)
> 3 2 NESTED LOOPS (Cost=2473 Card=8351 Bytes=225477)
> 4 3 INDEX (FULL SCAN) OF 'PK_POOL' (UNIQUE) (Cost=2
> Card=8 Bytes=32)
> 5 3 INDEX (RANGE SCAN) OF 'TOTAL_CONTRIBUTIONLOG_IDX'
> (NON-UNIQUE) (Cost=2 Card=1044)
>
> No hint:
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2478 Card=1789
> Bytes=48303)
> 1 0 SORT (GROUP BY) (Cost=2478 Card=1789 Bytes=48303)
> 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CONTRIBUTIONLOG'
> (Cost=310 Card=1044 Bytes=24012)
> 3 2 NESTED LOOPS (Cost=2474 Card=8351 Bytes=225477)
> 4 3 INDEX (FULL SCAN) OF 'PK_POOL' (UNIQUE) (Cost=2
> Card=8 Bytes=32)
> 5 3 INDEX (RANGE SCAN) OF 'TOTAL_CONTRIBUTIONLOG_IDX'
> (NON-UNIQUE) (Cost=2 Card=1044)
>

When you run autotrace, you are actually doing an explain plan using "explain plan set statement_id = 'xxx' for ..." so you get a new plan and proper optimisation.

> However, response time and statistics shows the no-hint-query doesn't
> use the new plan.
> Run1 ran in 59 hsecs
> Run2 ran in 3948 hsecs
>

When you run the plan, if an existing version of the cursor is in the shared pool it can be re-used. Changing system statistics does NOT invalidate cursors.

> Then I enable SQL_TRACE, try to see what the query is doing. Strangely
> query 2 starts using the same plan as query 1.
> Run1 ran in 276 hsecs
> Run2 ran in 236 hsecs
>

When you enable sql_trace you enable '_rowsource_execution_statistics', which creates a new cursor with (hidden) rowsource operations that are collecting the statistics. So the statement is re-optimised against the new system statistics.

If you had done a 'flush shared_pool' you would probably have found that the 59/3948 split would not have happened.

> Anyway, apart from the bug it is exactly the same as what you perdict.
>
>>Cost Based Oracle - Volume 1: Fundamentals On-shelf date: Nov 2005
> Great news. Finally you decide to publish a second book instead of only
> reviewing others. Will it be any further delay? I cannot find it in any
> book store.

After a long delay from Addison-Wesley, it's been taken on by Apress - and they're moving really quickly with it, and it will probably be available to pre-order in about a week or so. The target for being in the shops is early Nov - and if you are at the UKOUG annual conference, there may be a few early copies there (31st Oct - 3rd Nov, Birmingham, UK).

>
> Thanks,
> Bin
>

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

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

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005