some value index is fast, and for some value it is slow
Steve Robin
is fast.
Now what can I do here to full index when it is useful, who to
maintain it according to query.
It is using CBO in 9.2.0.8 on Windows XP.
SQL> SELECT TC.PRODUCTID, TC.COUNTRYCODE FROM TC WHERE
TC.PRODUCTID IS NOT NULL AND TC.ISNODE = 0 ORDER BY TC.PRODUCTID;
679449 rows selected.
Elapsed: 00:00:43.54
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5268 Card=340335 Byt
es=6126030)
1 0 SORT (ORDER BY) (Cost=5268 Card=340335 Bytes=6126030)
2 1 TABLE ACCESS (FULL) OF 'TC' (Cost=3944 Card=340335 B
ytes=6126030)
Statistics
----------------------------------------------------------
0 recursive calls
6 db block gets
41008 consistent gets
26442 physical reads
0 redo size
11424430 bytes sent via SQL*Net to client
498763 bytes received via SQL*Net from client
45298 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
679449 rows processed
SQL> SELECT /*+ INDEX(TC IND_ISNODE) */ TC.PRODUCTID, TC.COUNTRYCODE
FROM TC WHERE TC.PRODUCTID IS NOT NULL AND TC.ISNODE = 0 ORDER BY
TC
679449 rows selected.
Elapsed: 00:01:02.25
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22619 Card=340335 By
tes=6126030)
1 0 SORT (ORDER BY) (Cost=22619 Card=340335 Bytes=6126030)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TC' (Cost=21295 Ca
rd=340335 Bytes=6126030)
3 2 INDEX (RANGE SCAN) OF 'IND_ISNODE' (NON-UNIQUE) (Cost=
619 Card=340358)
Statistics
----------------------------------------------------------
0 recursive calls
6 db block gets
42212 consistent gets
39530 physical reads
0 redo size
11424430 bytes sent via SQL*Net to client
498763 bytes received via SQL*Net from client
45298 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
679449 rows processed
SQL> SELECT TC.PRODUCTID, TC.COUNTRYCODE FROM TC WHERE
TC.PRODUCTID IS NOT NULL AND TC.ISNODE = 1 ORDER BY TC.PRODUCTID;
1221 rows selected.
Elapsed: 00:00:08.64
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5268 Card=340335 Byt
es=6126030)
1 0 SORT (ORDER BY) (Cost=5268 Card=340335 Bytes=6126030)
2 1 TABLE ACCESS (FULL) OF 'TC' (Cost=3944 Card=340335 B
ytes=6126030)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
41008 consistent gets
24347 physical reads
0 redo size
20483 bytes sent via SQL*Net to client
1398 bytes received via SQL*Net from client
83 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1221 rows processed
SQL> SELECT /*+ INDEX(TC IND_ISNODE) */ TC.PRODUCTID, TC.COUNTRYCODE
FROM TC WHERE TC.PRODUCTID IS NOT NULL AND TC.ISNODE = 1 ORDER BY
TCPAR
1221 rows selected.
Elapsed: 00:00:00.28
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22619 Card=340335 By
tes=6126030)
1 0 SORT (ORDER BY) (Cost=22619 Card=340335 Bytes=6126030)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TC' (Cost=21295 Ca
rd=340335 Bytes=6126030)
3 2 INDEX (RANGE SCAN) OF 'IND_ISNODE' (NON-UNIQUE) (Cost=
619 Card=340358)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
378 consistent gets
50 physical reads
0 redo size
20483 bytes sent via SQL*Net to client
1398 bytes received via SQL*Net from client
83 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1221 rows processed
SQL> select * from dba_tables where owner='MCC' AND TABLE_NAME='TC';
OWNER TABLE_NAME
TABLESPACE_NAME CLUSTER_NAME
IOT_NAME PCT_FREE PCT_USED INI_TRANS
MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B
------------------------------ ------------------------------
------------------------------ ------------------------------
------------------------------ ---------- ---------- ----------
---------- -------------- ----------- ----------- -----------
------------ ---------- --------------- --- -
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES
CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE T S NES BUFFER_
ROW_MOVE GLO USE DURATION SKIP_COR MON
CLUSTER_OWNER DEPENDEN
---------- ---------- ------------ ---------- ---------- -----------
------------------------- ------------------- ---------- ----------
----- -------- ----------- --------- --- ------------ - - --- -------
-------- --- --- --------------- -------- ---
------------------------------ --------
COMPRESS
--------
MCC TC
MCC_DATA
10 40 1 255
65536 1 2147483645
1 1 YES N
680716 40994 0 0 0
423 0 0 1 1 N
ENABLED 680716 08-MAR-07 NO N N NO DEFAULT
DISABLED YES NO DISABLED
NO DISABLED
DISABLED
Elapsed: 00:00:00.03
SQL> select * from dba_INDEXES where owner='MCC' AND
INDEX_NAME='IND_ISNODE';
OWNER INDEX_NAME
INDEX_TYPE TABLE_OWNER
TABLE_NAME TABLE_TYPE UNIQUENES COMPRESS
PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS
INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
------------------------------ ------------------------------
--------------------------- ------------------------------
------------------------------ ----------- --------- --------
------------- ------------------------------ ---------- ----------
-------------- ----------- ----------- -----------
PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS
PCT_FREE LOG BLEVEL LEAF_BLOCKS DISTINCT_KEYS
AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
STATUS NUM_ROWS SAMPLE_SIZE LAST_ANAL DEGREE
------------ ------------- -------------- ---------- ---------------
---------- --- ---------- ----------- -------------
----------------------- ----------------------- -----------------
-------- ---------- ----------- ---------
----------------------------------------
INSTANCES PAR T G S BUFFER_ USE
DURATION PCT_DIRECT_ACCESS ITYP_OWNER
ITYP_NAME
---------------------------------------- --- - - - ------- ---
--------------- ----------------- ------------------------------
------------------------------
PARAMETERS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI
--- ------------ ------ -------- ---
MCC IND_ISNODE
NORMAL MCC
TC TABLE NONUNIQUE
DISABLED MCC_INDEX 2
255 27770880 1 2147483645
1
1 10 YES 2 1234 2
617 20675 41351 VALID 680716
680716 08-MAR-07 1
1 NO N N N DEFAULT NO
OWNER INDEX_NAME
INDEX_TYPE TABLE_OWNER
TABLE_NAME TABLE_TYPE UNIQUENES COMPRESS
PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS
INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
------------------------------ ------------------------------
--------------------------- ------------------------------
------------------------------ ----------- --------- --------
------------- ------------------------------ ---------- ----------
-------------- ----------- ----------- -----------
PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS
PCT_FREE LOG BLEVEL LEAF_BLOCKS DISTINCT_KEYS
AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
STATUS NUM_ROWS SAMPLE_SIZE LAST_ANAL DEGREE
------------ ------------- -------------- ---------- ---------------
---------- --- ---------- ----------- -------------
----------------------- ----------------------- -----------------
-------- ---------- ----------- ---------
----------------------------------------
INSTANCES PAR T G S BUFFER_ USE
DURATION PCT_DIRECT_ACCESS ITYP_OWNER
ITYP_NAME
---------------------------------------- --- - - - ------- ---
--------------- ----------------- ------------------------------
------------------------------
PARAMETERS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI
--- ------------ ------ -------- ---
YES NO
fitzj...@cox.net
> GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI
> --- ------------ ------ -------- ---
>
> YES NO
An index scan will usually result in two I/O calls (one to read the
index and one to access the table) so the 20 seconds difference in
your hinted query and unhinted query may be just that --- time for the
additional I/O, especially with over 675000 records to process. Why
you'd want to use an index when returning 99+% of your data is a
mystery to me as a table scan would be much faster (as you've already
reported). I would want to be using the index for the 1221 records
where ISNODE = 1 as that total comprises less than 0.2% of your data
(and Oracle *is* using that index as evidenced by your explain plan
output). Your unhinted queries appear to be working correctly which
indicates, to me, anyway, that you have generated the proper
statistics and histograms.
Again, I'm lost as to why you think you need to use the index to
return essentially your entire table.
David Fitzjarrell
Steve Robin
> GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI
> --- ------------ ------ -------- ---
>
> YES NO
it is require. Why ?
Please give me any solution IN ORACLE 9i.
SQL> EXPLAIN PLAN FOR SELECT TCPART.PRODUCTID, TCPART.COUNTRYCODE
FROM SCOTT.TCPART WHERE TCPART.PRODUCTID IS NOT NULL AND
TCPART.ISNODE = 1 ORDER BY TCPART.PRODUCTID;
Explained.
Elapsed: 00:00:00.01
SQL> @?\RDBMS\ADMIN\UTLXPLP.SQL
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3598284462
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 338 | 9464 |
33 (4)| 00:00:01 |
| 1 | SORT ORDER BY | | 338 | 9464 |
33 (4)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| TCPART | 338 | 9464 |
32 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_ISNODE | 456 |
| 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TCPART"."PRODUCTID" IS NOT NULL)
3 - access("TCPART"."ISNODE"=1)
Note
-----
- dynamic sampling used for this statement
20 rows selected.
Elapsed: 00:00:00.12
SQL> EXPLAIN PLAN FOR SELECT TCPART.PRODUCTID, TCPART.COUNTRYCODE
FROM SCOTT.TCPART WHERE TCPART.PRODUCTID IS NOT NULL AND
TCPART.ISNODE =0 ORDER BY TCPART.PRODUCTID;
Explained.
Elapsed: 00:00:00.03
SQL> @?\RDBMS\ADMIN\UTLXPLP.SQL
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3792352330
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 676K| 18M| | 16358
(2)| 00:03:17 |
| 1 | SORT ORDER BY | | 676K| 18M| 51M| 16358
(2)| 00:03:17 |
|* 2 | TABLE ACCESS FULL| TCPART | 676K| 18M| | 9211
(2)| 00:01:51 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - filter("TCPART"."PRODUCTID" IS NOT NULL AND
"TCPART"."ISNODE"=0)
Note
-----
- dynamic sampling used for this statement
18 rows selected.
Elapsed: 00:00:00.25
In oracle 9i
SQL> show parameter optimizer
NAME TYPE
VALUE
------------------------------------ --------------------------------
------------------------------
optimizer_dynamic_sampling integer
1
optimizer_features_enable string
9.2.0
optimizer_index_caching integer
0
optimizer_index_cost_adj integer
100
optimizer_max_permutations integer
2000
optimizer_mode string
CHOOSE
In Oracle 10g
SQL> show parameter optimizer
NAME TYPE
VALUE
------------------------------------ --------------------------------
------------------------------
optimizer_dynamic_sampling integer
2
optimizer_features_enable string
10.1.0
optimizer_index_caching integer
0
optimizer_index_cost_adj integer
100
optimizer_mode string
ALL_ROWS
Steve Robin
> > GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI
> > --- ------------ ------ -------- ---
>
> > YES NO
>
> An index scan will usually result in two I/O calls (one to read the
> index and one to access the table) so the 20 seconds difference in
> your hinted query and unhinted query may be just that --- time for the
> additional I/O, especially with over 675000 records to process. Why
> you'd want to use an index when returning 99+% of your data is a
> mystery to me as a table scan would be much faster (as you've already
> reported). I would want to be using the index for the 1221 records
> where ISNODE = 1 as that total comprises less than 0.2% of your data
> (and Oracle *is* using that index as evidenced by your explain plan
> output). Your unhinted queries appear to be working correctly which
> indicates, to me, anyway, that you have generated the proper
> statistics and histograms.
>
> Again, I'm lost as to why you think you need to use the index to
> return essentially your entire table.
>
> David Fitzjarrell
Thanks for reply David.
Actually the problem is time and front end query. As you can see if I
use index hint output comes in less then 1 sec, but when I don't use
it I takes more than 8 sec. And I cann't apply hint on the front end.
Because this product has been released and Front end has been taken by
another company. So I can just make back-end changes.
I apologise for late 10g reply. But you can see in 10g , it is working
like I want it to do in oracle 9i.
peter
>
> read more ยป- Hide quoted text -
>
> - Show quoted text -
Well steve when I faced this problem, I get stats with all columns.
Why don't you try it.
fitzj...@cox.net
> > --- ------------ ------ -------- ---
>
> > YES NO
>
> I like to share one more think. In oracle 10g it is using index when
> it is require. Why ?
> Please give me any solution IN ORACLE 9i.
>
> SQL> EXPLAIN PLAN FOR SELECT TCPART.PRODUCTID, TCPART.COUNTRYCODE
> FROM SCOTT.TCPART WHERE TCPART.PRODUCTID IS NOT NULL AND
> TCPART.ISNODE = 1 ORDER BY TCPART.PRODUCTID;
>
> Explained.
>
> Elapsed: 00:00:00.01
> SQL> @?\RDBMS\ADMIN\UTLXPLP.SQL
>
> PLAN_TABLE_OUTPUT
> Plan hash value: 3598284462
>
> ---------------------------------------------------------------------------ยญ--------------------------------------------
> | Id | Operation | Name | Rows | Bytes |
> Cost (%CPU)| Time |
> | 0 | SELECT STATEMENT | | 338 | 9464 |
> 33 (4)| 00:00:01 |
> | 1 | SORT ORDER BY | | 338 | 9464 |
> 33 (4)| 00:00:01 |
> |* 2 | TABLE ACCESS BY INDEX ROWID| TCPART | 338 | 9464 |
> 32 (0)| 00:00:01 |
> |* 3 | INDEX RANGE SCAN | IND_ISNODE | 456 |
> | 3 (0)| 00:00:01 |
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------ยญ---------------------------------------------------------------------------ยญ--------------------------------------------------
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter("TCPART"."PRODUCTID" IS NOT NULL)
> 3 - access("TCPART"."ISNODE"=1)
>
> Note
> -----
> - dynamic sampling used for this statement
>
> 20 rows selected.
>
> Elapsed: 00:00:00.12
> SQL> EXPLAIN PLAN FOR SELECT TCPART.PRODUCTID, TCPART.COUNTRYCODE
> FROM SCOTT.TCPART WHERE TCPART.PRODUCTID IS NOT NULL AND
> TCPART.ISNODE =0 ORDER BY TCPART.PRODUCTID;
>
> Explained.
>
> Elapsed: 00:00:00.03
> SQL> @?\RDBMS\ADMIN\UTLXPLP.SQL
>
> PLAN_TABLE_OUTPUT
> Plan hash value: 3792352330
>
> ---------------------------------------------------------------------------ยญ----------
> | Id | Operation | Name | Rows | Bytes |TempSpc| Cost
> (%CPU)| Time |
> | 0 | SELECT STATEMENT | | 676K| 18M| | 16358
> (2)| 00:03:17 |
> | 1 | SORT ORDER BY | | 676K| 18M| 51M| 16358
> (2)| 00:03:17 |
> |* 2 | TABLE ACCESS FULL| TCPART | 676K| 18M| | 9211
> (2)| 00:01:51 |
>
> Predicate Information (identified by operation id):
>
> PLAN_TABLE_OUTPUT
I believe in 10g the optimizer has improved and you may have
historgrams computed. Computing histograms for this table would make
your 9i performance equivalent to the 10g performance you see. Using
dbms_stats.gather_table_stats() you can provide a method_opt to
compute histograms by setting that parameter to the following value:
'for all indexed columns size auto'
Oracle should then compute how many 'buckets' you need and create
them. Once histograms are in place you should see an improvement.
Jonathan Lewis has written much on this subject; I would buy a copy of
his 'Cost Based Oracle' text (volume one is out now, and volume two is
in the 'works'). Or go out to his website at www.jlcomp.demon.co.uk
and search there. You will find a wealth of information to help you.
David Fitzjarrell
Steve Robin
> > > GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI
> > > --- ------------ ------ -------- ---
>
> > > YES NO
>
> > I like to share one more think. In oracle 10g it is using index when
> > it is require. Why ?
> > Please give me any solution IN ORACLE 9i.
>
> > SQL> EXPLAIN PLAN FOR SELECT TCPART.PRODUCTID, TCPART.COUNTRYCODE
> > FROM SCOTT.TCPART WHERE TCPART.PRODUCTID IS NOT NULL AND
> > TCPART.ISNODE = 1 ORDER BY TCPART.PRODUCTID;
>
> > Explained.
>
> > Elapsed: 00:00:00.01
> > SQL> @?\RDBMS\ADMIN\UTLXPLP.SQL
>
> > PLAN_TABLE_OUTPUT
> > Plan hash value: 3598284462
>
> > ---------------------------------------------------------------------------ยญยญ--------------------------------------------
> > | Id | Operation | Name | Rows | Bytes |
> > Cost (%CPU)| Time |
> > ---------------------------------------------------------------------------ยญยญ---------------------------------------------
> > | 0 | SELECT STATEMENT | | 338 | 9464 |
> > 33 (4)| 00:00:01 |
> > | 1 | SORT ORDER BY | | 338 | 9464 |
> > 33 (4)| 00:00:01 |
> > |* 2 | TABLE ACCESS BY INDEX ROWID| TCPART | 338 | 9464 |
> > 32 (0)| 00:00:01 |
> > |* 3 | INDEX RANGE SCAN | IND_ISNODE | 456 |
> > | 3 (0)| 00:00:01 |
> > ---------------------------------------------------------------------------ยญยญ---------------------------------------------
>
> > PLAN_TABLE_OUTPUT
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
>
> > 2 - filter("TCPART"."PRODUCTID" IS NOT NULL)
> > 3 - access("TCPART"."ISNODE"=1)
>
> > Note
> > -----
> > - dynamic sampling used for this statement
>
> > 20 rows selected.
>
> > Elapsed: 00:00:00.12
> > SQL> EXPLAIN PLAN FOR SELECT TCPART.PRODUCTID, TCPART.COUNTRYCODE
> > FROM SCOTT.TCPART WHERE TCPART.PRODUCTID IS NOT NULL AND
> > TCPART.ISNODE =0 ORDER BY TCPART.PRODUCTID;
>
> > Explained.
>
> > Elapsed: 00:00:00.03
> > SQL> @?\RDBMS\ADMIN\UTLXPLP.SQL
>
> > PLAN_TABLE_OUTPUT
> > Plan hash value: 3792352330
>
> > ---------------------------------------------------------------------------ยญยญ----------
> > | Id | Operation | Name | Rows | Bytes |TempSpc| Cost
> > (%CPU)| Time |
> > ---------------------------------------------------------------------------ยญยญ----------
> > | 0 | SELECT STATEMENT | | 676K| 18M| | 16358
> > (2)| 00:03:17 |
> > | 1 | SORT ORDER BY | | 676K| 18M| 51M| 16358
> > (2)| 00:03:17 |
> > |* 2 | TABLE ACCESS FULL| TCPART | 676K| 18M| | 9211
> > (2)| 00:01:51 |
> > ---------------------------------------------------------------------------ยญยญ----------
>
> > Predicate Information (identified by operation id):
>
> > PLAN_TABLE_OUTPUT
Thanks David and Peter both.
Now it is ok. I use for all columns.
But is it all the time ? means do I have to use 'for all columns size
auto' or 'for all indexed columns size auto' with dbms_Stats all the
times for this particular table. Because it is changing very fast. I
do this on saturday night only. Is it OK or I have to get stats for it
more frequently?