Dynamic Sampling
640772Jun 30 2010 — edited Jun 30 2010Dear Experts,
I have a senario(Oracle 10gR2 Windows XP):
-------> I have created two tables with around 35000 records each, bigtable and smalltable
(1) -------> SQL> explain plan for select a.object_id, a.owner, b.object_name from bigtable a
, smalltable b where a.object_id=b.object_id and a.object_id=15778;
It go for full table scan its Ok as it should be
SQL> exec dbms_stats.gather_table_stAts('NEWUSER','BIGTABLE');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stAts('NEWUSER','smallTABLE');
PL/SQL procedure successfully completed.
(2) SQL> create index bigtable_oi on bigtable(object_id);
Index created.
SQL> create index smalltable_oi on smalltable(object_id);
Index created.
SQL> truncate table plan_table;
Table truncated.
SQL> explain plan for select a.object_id, a.owner, b.object_name from bigtable a
, smalltable b where a.object_id=b.object_id and a.object_id=15778;
-----> It use both of indexes its again OK
(3) SQL> declare
2 nn number:=0;
3 begin
4
5 while nn<30000
6 loop
7 insert into bigtable(object_id) values(15778);
8 nn:=nn+1;
9 end loop;
10 commit;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> explain plan for select a.object_id, a.owner, b.object_name from bigtable a
, smalltable b where a.object_id=b.object_id and a.object_id=15778;
Explained.
SQL> select plan_table_output from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1879317162
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56509 | 3311K| 145 (
2)| 00:00:02 |
|* 1 | HASH JOIN | | 56509 | 3311K| 145 (
2)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| SMALLTABLE | 1 | 30 | 2 (
0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SMALLTABLE_OI | 1 | | 1 (
0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 4 | TABLE ACCESS FULL | BIGTABLE | 56509 | 1655K| 142 ( ------------------> It is full table Scan Why ?
1)| 00:00:02 |
--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
3 - access("B"."OBJECT_ID"=15778)
4 - filter("A"."OBJECT_ID"=15778)
Note
-----
- dynamic sampling used for this statement
My question is that HOW OPTIMIZER came to know about that there is a huge amount of data insertion happend in BIGTABLE, So that it choose full table scan, instead of using index as previous.
Will not it degrade performance if it does like this every time when a query fire ?