Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dynamic Sampling

640772Jun 30 2010 — edited Jun 30 2010
Dear 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 ?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 28 2010
Added on Jun 30 2010
4 comments
854 views