Re: The 20% rule
- From: Charles Hooper <hooperc2000@xxxxxxxxx>
- Date: Fri, 25 Jul 2008 02:59:42 -0700 (PDT)
On Jul 24, 8:29 pm, DA Morgan <damor...@xxxxxxxxx> wrote:
Charles Hooper wrote:
On Jul 24, 2:02 pm, DA Morgan <damor...@xxxxxxxxx> 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...@xxxxxxxxxxxxxxxx (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...@xxxxxxxxxxxxxxxx (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.
.
- References:
- The 20% rule
- From: aarklon
- Re: The 20% rule
- From: DA Morgan
- Re: The 20% rule
- From: Charles Hooper
- Re: The 20% rule
- From: DA Morgan
- The 20% rule
- Prev by Date: Re: NLS_LANG
- Next by Date: free data base soft ware get go to website view
- Previous by thread: Re: The 20% rule
- Next by thread: Re: The 20% rule
- Index(es):
Relevant Pages
|
