Re: The 20% rule

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 24 Jul 2008 17:29:22 -0700
Message-ID: <1216945758.943133@bubbleator.drizzle.com>

Charles Hooper wrote:
> On Jul 24, 2:02 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> Every explain plan looks like this:
>>
>> ------------------------------------------------------------------------
>> Plan hash value: 2619160949
>> ------------------------------------------------------------------------
>> | Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |
>> ------------------------------------------------------------------------
>> |   0 | SELECT STATEMENT |      |     1 |    13 |     1   (0)| 00:00:01|
>> |*  1 |  INDEX RANGE SCAN| IX_T |     1 |    13 |     1   (0)| 00:00:01|
>> ------------------------------------------------------------------------
>>
>> There is only one rule in this business ... it is the rule of testing.
>> --
>> Daniel A. Morgan
>> Oracle Ace Director & Instructor
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org

>
> My Oracle server is apparently defective, the final part with a couple
> small enhancements (Oracle 10.2.0.2):
> SQL> INSERT INTO t VALUES (9,91);
>
> 1 row created.
>
> SQL> COMMIT;
>
> Commit complete.
>
> SQL> EXEC
> DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T',CASCADE=>TRUE);
>
> PL/SQL procedure successfully completed.
>
> SQL> EXPLAIN PLAN FOR
> 2 SELECT bcol
> 3 FROM t
> 4 WHERE testcol = 1;
>
> Explained.
>
> SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------
> Plan hash value:
> 1601196873
>
> --------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time |
> --------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)|
> 00:00:01 |
> |* 1 | TABLE ACCESS FULL| T | 1 | 6 | 3 (0)|
> 00:00:01 |
> --------------------------------------------------------------------------
>
> Predicate Information (identified by operation
> id):
> ---------------------------------------------------
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------
> 1 -
> filter("TESTCOL"=1)
>
> Every one of my plans looked like the above. There are a couple very
> good reasons why a full table scan was selected for the above SQL
> statement. Just a few:
> * DB_FILE_MULTIBLOCK_READ_COUNT is set to 128 (and MBRC, used by the
> cost based optimizer, is set to 100), so Oracle believes that it is
> able to read 100 (or 128) blocks in a single IO call.
> * The rows are very narrow, so a very large number of rows will fit in
> each block.
> * I had one row in the table when the SQL statement was hard parsed.
> * Gathering statistics on tables and indexes does not immediately
> invalidate SQL statements accessing those objects on Oracle 10.2.0.2,
> so I would expect to see exactly the same *predicted* plan from
> dbms_xplan unless the SQL statement was hard parsed every time it was
> executed.
>
> This topic came up at least once before, and I had unfortunately
> poorly paraphrased one of Tom Kyte's books in the thread.
> Fortunately, Tom saw the thread and offered some clarification:
> http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/e676da84569d696a
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Here are my parameters
db_file_multiblock_read_count = 128
optimizer_index_caching = 0
optimizer_index_cost_adj = 100

I ran several versions of it before posting what I did and could reproduce the behavior at will.

Both my result, and yours, demonstrate the 20% rule is not a rule any more than any other fabricated Oracle rule is a rule. The only rule that means anything is to test:

1. With your data
2. With your hardware
3. With your version
4. With your workload

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Jul 24 2008 - 19:29:22 CDT