Hello,
I was trying to test the impact of extended statistics feature of 11g when I was puzzled by another observation.
I created a table (from ALL_OBJECTS view). The data in this table was such that it had lots of rows where OWNER = 'PUBLIC'
and lots of rows where OBJECT_TYPE = 'JAVA CLASS' but no rows where OWNER = 'PUBLIC' AND OBJECT_TYPE = 'JAVA CLASS'.
I also create an index on the combination of (OWNER, OBJECT_TYPE).
Now, after collecting statistics on table and index, I queried the table for above condition (OWNER = 'PUBLIC' AND OBJECT_TYPE = 'JAVA CLASS').
To my surprise (or not), the query used the index.
Then I recollected the statistics on the table and index and now the same query started to do a full table scan.
Only creation of extended statistics ensured that the plan changed to indexed access subsequently. While this proved the use of extended stats,
I am not sure how oracle was able to use indexed access path initially but not afterwards.
Is this due to column usage monitoring info? Can anybody help?
Here is my test case:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
5 rows selected.
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> create table t1 nologging as select * from all_objects ;
Table created.
SQL> exec dbms_stats.gather_table_stats(user, 'T1', no_invalidate=>false) ;
PL/SQL procedure successfully completed.
SQL> select * from t1 where owner = 'PUBLIC' and object_type = 'JAVA CLASS' ;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor) ;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID bnrj3cac3upfd, child number 0
-------------------------------------
select * from t1 where owner = 'PUBLIC' and object_type = 'JAVA CLASS'
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 226 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 155 | 15190 | 226 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("OBJECT_TYPE"='JAVA CLASS' AND "OWNER"='PUBLIC'))
18 rows selected.
SQL> create index t1_idx on t1(owner, object_type) nologging ;
Index created.
SQL> select * from t1 where owner = 'PUBLIC' and object_type = 'JAVA CLASS' ;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor) ;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID bnrj3cac3upfd, child number 0
-------------------------------------
select * from t1 where owner = 'PUBLIC' and object_type = 'JAVA CLASS'
Plan hash value: 546753835
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 23 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 633 | 62034 | 23 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_IDX | 633 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='JAVA CLASS')
19 rows selected.
SQL> REM This shows that CBO decided to use the index even when there are no extended statistics
SQL> REM Now, we will gather statistics on the table again and see what happens
SQL> exec dbms_stats.gather_table_stats(user, 'T1', no_invalidate=>false) ;
PL/SQL procedure successfully completed.
SQL> select * from t1 where owner = 'PUBLIC' and object_type = 'JAVA CLASS' ;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor) ;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID bnrj3cac3upfd, child number 0
-------------------------------------
select * from t1 where owner = 'PUBLIC' and object_type = 'JAVA CLASS'
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 226 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 11170 | 1069K| 226 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("OBJECT_TYPE"='JAVA CLASS' AND "OWNER"='PUBLIC'))
18 rows selected.
SQL> REM And the plan changes to Full Table scan. Why?