Re: The 20% rule
Date: Thu, 24 Jul 2008 13:02:46 -0700 (PDT)
Message-ID: <efcfdfb2-d4e8-44ee-9aa2-7c157284ecd4@y22g2000prd.googlegroups.com>
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.
Received on Thu Jul 24 2008 - 15:02:46 CDT