Re: Why Statistics doesn't match reponse time?

Jonathan Lewis wrote:
> "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

Hi Jonathan,

Below is a quote from you above

"Your index unique scan (unique) on pool is probably doing one consistent get on the one and only block, in the index (with 9 rows, I am assuming the index is just one block) and pinning it for the duration of the query. Consequently, assuming the cardinality in the execution plan is correct, you will probably see 1.8million 'buffer is pinned count'. That, plus the continuous comparison for the join, amounts to a lot of CPU."

So oracle is pinning the index block for the entire duration of the query. My question is if this block is pinned for entire duration i.e. about 3 minutes then will the other queries trying to access the same block would wait on buffer busy waits through out the entire duration ? Received on Mon Sep 12 2005 - 14:00:18 CDT