Re: Why Statistics doesn't match reponse time?

From: Bin <wangbinlxx_at_gmail.com>
Date: 11 Sep 2005 23:21:55 -0700
Message-ID: <1126506115.193863.81950@g43g2000cwa.googlegroups.com>

Absolutely genius. Thank you Jonathan.

> you will probably see 1.8million 'buffer is pinned count'.
I use runstats_pkg to check stats.
Run1 ran in 59 hsecs
Run2 ran in 284 hsecs
run 1 ran in 20.77% of the time

Name                                Run1      Run2      Diff
......
STAT...CPU used by this sessio        68       214       146
STAT...CPU used when call star        68       214       146
.....
STAT...buffer is pinned count    163,431 1,867,578 1,704,147
STAT...index fetch by key              0 1,867,580 1,867,580
STAT...table scan rows gotten          0 1,867,580 1,867,580

......

> If you enable cpu costing (there is an article of mine
> on OTN somewhere about this) then you will
> probably find that the plan you had to hint
> suddenly gets taken automatically - because
> the other plan becomes more expensive.
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

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)

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

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

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.

Thanks,
Bin Received on Mon Sep 12 2005 - 01:21:55 CDT