DBMS_STATS mistake

In 10g I know, by default, Oracle will decide if the histogram is needed for a column. If we don't want to collect histogram, we should specify: method_opt=>'FOR ALL COLUMNS SIZE 1'.

I find I have maken a mistake in a pre-production 10g database. I omitted the 'ALL' keyword in the job for gathering table stats. So what could happen?

Here is the demonstration by a test:

SQL>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>
SQL>drop table t;
drop table t
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>create table t as select * from dba_objects;

Table created.

SQL>
SQL>begin
2 dbms_stats.gather_table_stats(user,'T',
3 estimate_percent =>30,
4 cascade => TRUE,degree => 8,
5 method_opt => 'FOR COLUMNS SIZE 1');
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>
SQL>select table_name, column_name, density,num_buckets, last_analyzed
2 from dba_tab_columns where owner=user and table_name='T';

TABLE_NAME COLUMN_NAME DENSITY NUM_BUCKETS LAST_ANAL
------------------------------ ------------------------------ ---------- ----------- ---------
T OWNER
T OBJECT_NAME
T SUBOBJECT_NAME
T OBJECT_ID
T DATA_OBJECT_ID
T OBJECT_TYPE
T CREATED
T LAST_DDL_TIME
T TIMESTAMP
T STATUS
T TEMPORARY
T GENERATED
T SECONDARY

13 rows selected.

SQL>
SQL>
SQL>begin
2 dbms_stats.gather_table_stats(user,'T',
3 estimate_percent =>30,
4 cascade => TRUE,degree => 8,
5 method_opt => 'FOR ALL COLUMNS SIZE 1');
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>select table_name, column_name, density,num_buckets, last_analyzed
2 from dba_tab_columns where owner=user and table_name='T';

TABLE_NAME COLUMN_NAME DENSITY NUM_BUCKETS LAST_ANAL
------------------------------ ------------------------------ ---------- ----------- ---------
T OWNER .03125 1 30-OCT-09
T OBJECT_NAME .000035967 1 30-OCT-09
T SUBOBJECT_NAME .005847953 1 30-OCT-09
T OBJECT_ID .000019563 1 30-OCT-09
T DATA_OBJECT_ID .000208203 1 30-OCT-09
T OBJECT_TYPE .027777778 1 30-OCT-09
T CREATED .000393236 1 30-OCT-09
T LAST_DDL_TIME .000394789 1 30-OCT-09
T TIMESTAMP .000388651 1 30-OCT-09
T STATUS .5 1 30-OCT-09
T TEMPORARY .5 1 30-OCT-09
T GENERATED .5 1 30-OCT-09
T SECONDARY .5 1 30-OCT-09

13 rows selected.


We can see that no column stats will be gathered if that 'ALL' is missing, though no errors thrown out. This will cause bad execution plan in some situations.

DBA makes mistake. The question is how to reduce and avoid serious one. Some are due to carelessness and some are due to lack of experinece or knowledge. I should say the above mentioned mistake belongs to the first category. ( Though, I did not realize that all column stats would be missing) . Another example, the other day, I forgot to lift the restricted session mode after I was done a maitenance job. I do have this step in my checklist, the situation was that I was distracted by a production issue and have not come back to verify every steps in my checklist. Chris Foot has written an execent blog: The Art of Being a Successful DBA – Poka-Yoke and Paranoid DBA Best Practices, which I may want to read from time to time.