Category: Performance Tunning

How to Configure HugePages for Oracle Database on Linux

Oracle strongly recommends to configure HugePages for servers with more than 8GB memory used by Oracle database(s).

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Active Session History (ASH) Performed an Emergency Flush

Set proper ASH size to avoid emergency flushes.

The alert log with following message now and then :

"Active Session History (ASH) performed an emergency flush. This
 may mean that ASH is undersized. If emergency flushes are a 
recurring issue, you may consider increasing ASH size by setting 
the value of _ASH_SIZE to a sufficiently large value. Currently, 
ASH size is 134217728 bytes. Both ASH size and the total number 
of emergency flushes since instance startup can be monitored by 
running the following query:  

select total_size, awr_flush_emergency_count from v$ash_info;"

Query the current ASH size :

SQL> select total_size from v$ash_info;

TOTAL_SIZE
----------
 134217728

Current size is 134M according both the query and the messages in alert.log. We can add another 50% by running the following sql. The size should be appropriate if without any more similar messages in alert.log. Otherwise increase it up to maximum 254M.

SQL>alter system set "_ash_size"=200M;

ORA-20005: object statistics are locked (stattype = ALL)

datapump might lock table stats

ISSUES

When trying to collect a table statistics, we get “ORA-20005: object statistics are locked (stattype = ALL)” as below:

SQL> exec dbms_stats.gather_table_stats(ownname=>'TESTUSER',tabname=>'TEST_TABLE');
BEGIN dbms_stats.gather_table_stats(ownname=>'TESTUSER',tabname=>'TEST_TABLE'); END;

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

HOW IS the STATS LOCKED ?

1) DBMS_STATS.LOCK_[SCHEMA|TABLE]_STATS has been used to lock statistics on the table.

SQL> exec dbms_stats.unlock_table_stats('TESTUSER','TEST_TABLE');
PL/SQL procedure successfully completed.

SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED 
       from dba_tab_statistics 
      where OWNER='TESTUSER' 
        and TABLE_NAME='TEST_TABLE';

OWNER    TABLE_NAME  STATT
-------- ----------- --------
TESTUSER TEST_TABLE

SQL> exec dbms_stats.lock_table_stats('TESTUSER','TEST_TABLE');

PL/SQL procedure successfully completed.

SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED 
       from dba_tab_statistics 
      where OWNER='TESTUSER' and TABLE_NAME='TEST_TABLE';

OWNER     TABLE_NAME  STATT
--------- ----------- -----
TESTUSER  TEST_TABLE   ALL

2) Using data pump import (impdp) to import a table without data results in the table’s statistics being locked in 10gR2 and 11gR2.

a. Unlock statistics on the previous table:

SQL> exec dbms_stats.unlock_table_stats('TESTUSER','TEST_TABLE');
PL/SQL procedure successfully completed.

SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED 
       from dba_tab_statistics 
      where OWNER='TESTUSER' and TABLE_NAME='TEST_TABLE';

OWNER     TABLE_NAME  STATTYPE_LOCKED
--------- ----------- ---------------
TESTUSER  TEST_TABLE

b. Export table TESTUSER.TEST_TABLE.

 $ expdp / directory=EXPP_DIR dumpfile=testuser.TEST_TABLE.dmp tables=testuser.TEST_TABLE

Export: Release 11.2.0.4.0 -Production on Mon Nov 10 13:54:50 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options
Starting "OPS$ORACLE"."SYS_EXPORT_TABLE_01": /******** directory=EXPDP_DIR dumpfile=testuser.TEST_TABLE.dmp tables=testuser.TEST_TABLE

Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TESTUSER"."TEST_TABLE" 11.46 KB 1 rows
Master table "OPS$ORACLE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************
Dump file set for OPS$ORACLE.SYS_EXPORT_TABLE_01 is: /EXPDP_DIR/testuser.TEST_TABLE.dmp
Job "OPS$ORACLE"."SYS_EXPORT_TABLE_01" successfully completed at Mon Nov 10 13:55:13 2014 elapsed 0 00:00:16

c. Drop tablete TESTUSER.TEST_TABLE, and import with CONTENT=METADATA_ONLY, so the table will have zero records.

SQL> drop table TESTUSER.TEST_TABLE;
Table dropped.

$ impdp / directory=EXPDP_DIR dumpfile=TESTUSER.TEST_TABLE.dmp tables=TESTUSER.TEST_TABLE content=METADATA_ONLY

Import: Release 11.2.0.4.0 -Production on Mon Nov 10 13:59:07 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters and Automatic Storage Management options

Master table "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "OPS$ORACLE"."SYS_IMPORT_TABLE_01": /******** directory=EXPDP_DIR dumpfile=TESTUSER.TEST_TABLE.dmp tables=TESTUSER.TEST_TABLE content=METADATA_ONLY

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully completed at Mon Nov 10 13:59:13 2014 elapsed 0 00:00:04

d. Check table stats locked.

SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED 
       from dba_tab_statistics 
      where OWNER='TESTUSER' and TABLE_NAME='TEST_TABLE';

OWNER    TABLE_NAME  STATT
-------- ----------- -----
TESTUSER  TEST_TABLE ALL

3) If the table is a queue table then the statistics are intended to be empty and locked so that dynamic sampling will be used due to the table’s volatility.

SQL> select OWNER,QUEUE_TABLE,TYPE 
       from dba_queue_tables 
      where owner ='TESTUSER';

OWNER     QUEUE_TABLE 
--------- --------------- 
TESTUSER  TEST_TABLE_QTAB

SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED 
       from dba_tab_statistics 
      where OWNER='TESTUSER' and TABLE_NAME='TEST_TABLE_QTAB';

OWNER    TABLE_NAME        STATT
-------- ----------------- -----
TESTUSER TEST_TABLE_QTAB   ALL

SOLUTION

Here are a couple of solutions to prevent table stats from being locked:

1)If the table is a queue table then the statistics should remain empty and locked so that dynamic sampling is used due to the volatility of queue tables.

2)If the table is not a queue table, unlock the statistics using DBMS_STATS.UNLOCK_[SCHEMA|TABLE]_STATS or gather statistics on the table using DBMS_STATS.GATHER_[SCHEMA|TABLE|INDEX]_STATS and the force=>true parameter.

3)To prevent import (imp) from locking the table’s statistics when importing a table without the rows (rows=n), use statistics=none instead.

4)To prevent data pump import (impdp) from locking the table’s statistics when importing a table without the rows (content=metadata_only), use exclude=(table_statistics,index_statistics).

SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED 
       from dba_tab_statistics 
      where OWNER='TESTUSER' and TABLE_NAME='TEST_TABLE';

no rows selected

$ impdp / directory=EXPDP_DIR dumpfile=TESTUSER.TEST_TABLE.dmp tables=TESTUSER.TEST_TABLE content=METADATA_ONLY exclude=table_statistics,index_statistics

Import: Release 11.2.0.4.0 -Production on Mon Nov 10 14:12:14 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters and Automatic Storage Management options 
Master table "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "OPS$ORACLE"."SYS_IMPORT_TABLE_01": /******** directory=EXPDP_DIR dumpfile=TESTUSER.TEST_TABLE.dmp tables=TESTUSER.TEST_TABLE content=METADATA_ONLY exclude=table_statistics,index_statistics
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully completed at Mon Nov 10 14:12:17 2014 elapsed 0 00:00:02

SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED 
       from dba_tab_statistics 
       where OWNER='TESTUSER' and TABLE_NAME='TEST_TABLE';

OWNER     TABLE_NAME  STATT
--------- ----------- ------
TESTUSER  TEST_TABLE