Automatic Statistics Gathering Job
Automation usually is a good thing, but sometimes it can make thing goes strange.
I came across this situation just now, even I thought maybe it should be an bug in CBO, before I discovered that the cause is the automatic statistics gathering.
Below is the output of this case.
SQL> delete from A a
2 WHERE exists
3 (SELECT 1 FROM B b
4 WHERE a.ID=b.ID)
5 ;
9888 rows deleted.
SQL> select * from table(dbms_xplan.display_cursor(null,null,’allstats last’));
PLAN_TABLE_OUTPUT
—————————————————————————————————————–
SQL_ID 0ztxv101z6bfu, child number 1
————————————-
delete from A a WHERE exists (SELECT 1 FROM
B b WHERE a.ID=b.ID)
Plan hash value: 106077456
—————————————————————————————————————-
| Id | Operation | Name| Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
—————————————————————————————————————-
| 1 | DELETE | A | 1 | | 0 |00:00:01.47 | 14808 | | | |
|* 2 | HASH JOIN SEMI | | 1 | 1 | 9888 |00:00:01.26 | 2728 | 3178K| 1130K| 5197K (0)|
| 3 | TABLE ACCESS FULL| A | 1 | 1 | 76611 |00:00:00.23 | 2712 | | | |
| 4 | TABLE ACCESS FULL| B | 1 | 74535 | 3906 |00:00:00.01 | 16 | | | |
—————————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
2 - access(”A”.”ID”=”B”.”ID”)
21 rows selected.
Firstly, dynamic sampling is not happened here, this is unusual as I haven’t gather statistics on these temporary tables before. secondly, the cardinality of A is 1, or to say, it’s empty!
One only explanation is that the statistics gathering job happened to gather statistics at the moment that I truncating the table A. As at that moment, there are more than 10% changes on that table. So when doing query next, the CBO assumes that the table table is almost empty, and use this table as the inner table when joining.
So please keep an eye on the bad side of the automation, the cause of stranger behaviors.
There are one way to disable the automatic statistics gathering job to gather the user tables, use the statement below:
exec DBMS_STATS.SET_PARAM('AUTOSTATS_TARGET','ORACLE');
Why we don’t completely disable automatic statistics gathering job is that it’s necessary for us to gather the system dictionary tables. And automatic statistics gathering job can do it well.