Wednesday, October 28, 2015

Using DBMS_STATS to Gather Statistics

Collection of statistics on the database objects is very important for the performance of the database. DBMS_STATS package is used to collect these statistics.
To collect system statistics, use following procedure
SQL> exec dbms_stats.gather_system_stats

PL/SQL procedure successfully completed.

To collect Dictionary statistics, use following procedure
SQL> exec dbms_stats.gather_dictionary_stats

PL/SQL procedure successfully completed.

To collect statistics on full database (all schemas tables/indexes etc.)
SQL>  exec dbms_stats.gather_database_stats

PL/SQL procedure successfully completed

To collect stats on a single schema (all tables/indexes). Suppose schema name is SCOTT.
SQL> exec dbms_stats.gather_schema_stats('SCOTT')

PL/SQL procedure successfully completed.

To collect stats on a single table.
First parameter of the procedure is table owner name, and second is the table name. Third parameter “cascade” can have value TRUE or FALSE, to mention whether stats on the index(es) of this table need to be collected or not. TRUE means collect index stats along with table stats.
SQL> exec dbms_stats.gather_table_stats('SCOTT','test_table', cascade=>true);

PL/SQL procedure successfully completed.

To collect stats on a single index.
First parameter is index owner and second is index name.
SQL>  exec dbms_stats.gather_index_stats('SCOTT',’test_table_idx')

PL/SQL procedure successfully completed.

For the full detail of DBMS_STATS package and its procedures (and parameters), see bellow document.



Monday, October 26, 2015

ORA-20005: object statistics are locked

Error “ORA-20005: object statistics are locked” means that table statistics are locked and hence stats can’t be gathered on this table. Stats are locked if we don’t want stats to be gathered on some or all of our tables.

Following example explains stats locking and unlocking process.

SQL> create table test_table (id number);

Table created.

SQL> insert into test_table values(1);

1 row created.

SQL> insert into test_table values(1);

1 row created.

SQL> commit;

Commit complete.

-- Check if stats are current locked or unlocked. NULL returned in column STATTYPE_LOCKED column means stats are not locked

SQL>  select STATTYPE_LOCKED from user_tab_statistics;

STATT
-----

-- Now we gather fresh stats on the table. We will be able to gather stats since stats are not locked
SQL> exec dbms_stats.gather_table_stats('test','test_table');

PL/SQL procedure successfully completed.

-- Now we lock the stats and try gathering stats

SQL> exec dbms_stats.lock_table_stats('test','test_table');

PL/SQL procedure successfully completed.

SQL>  select STATTYPE_LOCKED from user_tab_statistics;

STATT
-----
ALL

SQL> exec dbms_stats.gather_table_stats('test','test_table');
BEGIN dbms_stats.gather_table_stats('test','test_table'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1

-- Now we unlock the stats again
SQL> exec dbms_stats.unlock_table_stats('test','test_table');

PL/SQL procedure successfully completed.

SQL>  select STATTYPE_LOCKED from user_tab_statistics;

STATT
-----


SQL> exec dbms_stats.gather_table_stats('test','test_table');

PL/SQL procedure successfully completed.

SQL>

Tuesday, October 20, 2015

ORA-15410: Disks in disk group DISKGROUP_NAME do not have equal size

If you are trying to create a diskgroup (suppose diskgroup name is DATA) and you see errors as bellow
ORA-15018: diskgroup cannot be created
ORA-15410: Disks in disk group DATA do not have equal size
following would be the reason of it.

Starting Grid Infrastructure 12.1.0.2, we need to have all disks with same size in a disk group, unlike previous versions where we could have varying sized disks in a diskgroup. So if we face ORA-15410 while creating a diskgroup, it would mean that disk size(s) is/are different in the diskgroup; which you are creating. Make sure that all disks being used in this disk group have same size and try again.