Re: The 20% rule

On Jul 24, 8:29 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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_th...
>
> > 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Confession time - I just reviewed the top section of the log file that I generated to capture the output of the test run on Oracle 10.2.0.2 (for my first post in this thread). It seems that while I changed the first column name in the create table command to work with the rest of the SELECT statements, I did not change the column name in the create index command - thus the index was never built.

SQL> CREATE TABLE t (
  2 testcol NUMBER,
  3 bcol NUMBER);

Table created.

SQL> CREATE INDEX ix_t
  2 ON t(acol);
ON t(acol)

     *
ERROR at line 2:
ORA-00904: "ACOL": invalid identifier

Based on the examination of the 10053 trace (11.1.0.6) for my second post in this thread, I suspect that the index access would have appeared to be slightly less expensive (lower calculated cost) than a full tablescan when there were only a couple rows in the table, even though it likely would have required the hard drive head to be repositioned twice compared to just once with the full table scan, should the blocks need to be read from disk. It might have been interesting to flush the buffer cache and experiment with various data sizes using a 10046 trace to determine the tipping point...

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jul 25 2008 - 04:59:42 CDT