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.

Truncate a parent table

I overlooked the operations required to truncate a parent table. I thought I only need to
truncate all of its child tables first, then truncate itself. The following test demonstrated
that constraints have to be taken care of in addition:


SQL> @fk_child
Enter value for 1: z9xxx94
Enter value for 2: p
----- Children of z9xxx94 p -------

Owner Table Name Constraint Name Ref Constraint Name Status
-------------- ----------------- ------------------ ------------------------ --------
Z9xxx94 C C_FK P_PK ENABLED



SQL> truncate table c;

Table truncated.

SQL> truncate table p;
truncate table p
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


We have to disable child table FK constraint first:


SQL> alter table c modify constraint C_FK disable;

Table altered.

SQL> truncate table p;

Table truncated.

SQL> alter table c modify constraint C_FK enable;

Table altered.

SQL> select count(*) from c;

COUNT(*)
----------
0

SQL> select count(*) from p;

COUNT(*)
----------
0

A SQL with a hidden error

This test case is built from a real life DBA task.

SQL> create table t as select rownum id, object_name from dba_objects where rownum <=10;

Table created.

SQL> create table t2 as select rownum id1, object_name from dba_objects where rownum <=5;

Table created.


Our intention was to do the following SQL with a subqeury:

SQL> select * from t where id in ( select id1 from t2);

ID OBJECT_NAME
---------- ------------------------------
1 TAB$
2 I_IND1
3 I_COBJ#
4 USER$
5 I_OBJ1


Instead, we executed the following SQL, which has a typo . However, it got executed without error:

SQL> select * from t where id in ( select id from t2);

ID OBJECT_NAME
---------- ------------------------------
1 TAB$
2 I_IND1
3 I_COBJ#
4 USER$
5 I_OBJ1
6 I_PROXY_ROLE_DATA$_2
7 C_FILE#_BLOCK#
8 C_OBJ#
9 BOOTSTRAP$
10 I_ICOL1

10 rows selected.

Isn't it better Oracle can throw error for this SQL?