Efficiently handle problematic Oracle Sequences!!

Author: Lewis Campano

Today we will discuss an interesting scenario that most of you must have encountered at one time or another. Sometime a tricky Oracle sequence can have tremendous impact on the performance of your database.

AWR Reports:

STATSPACK reports were used in Oracle releases prior to 10g so as to analyze the performance of your database. Oracle 10g brought with it automatic workload repository (AWR) report to analyze your Oracle database health.

Problematic Oracle Statement:

Our organization runs a busy On Line Transaction Processing (OLTP) database. Yesterday I found a suspicious dictionary related UPDATE statement in the AWR report. You can locate AWR report in $ORACLE_HOME/rdbms/admin The problematic SQL statement is as under

UPDATE MYSEQ$ SET MY_INCREMENT$=:2, MINIMUM_VAL=:3, MAXIMUM_VAL=:4, CYCLE_NUM#=:5, MYORDER$=:6, MYCACHE=:7, MYHIGHWATER=:8, MYAUDIT$=:9, MYFLAGS=:10 WHERE MYOBJECT#=:1

Top SQL:

The SQL section of the AWR report shows the top SQL ordered by important SQL execution metrics. In my case the above mentioned SQL statement was coming in top ten SQL statements under SQL statistics ordered by Executions and SQL ordered by Parse Calls sections of the AWR report. In just half hour, this statement got parsed and executed 863 times. What an overhead!!

Query Oracle Data Dictionary:

In order to troubleshoot this tricky statement I queried Oracle data dictionary for a list of sequences that belonged to important schema.

SELECT SEQUENCE_NAME, CACHE_SIZE, LAST_NUMBER FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER = 'MYSEQ_OWNER';

SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
--------------------------------- -------------------- ------------------------
MYSEQ_AUDIT_INFO 0 8193628
MYSEQ_TRANSACTION_LOG 0 1
MYSEQ_BATCH_LOG 20 991

Problematic Oracle Sequence:

Here we can see that our sequence MYSEQ_AUDIT_INFO is problematic as it was used for more than eight million times and did not get cached even once. Further analysis showed that the AUDIT_INFO table was using this sequence in order to generate unique serial.

Caching Oracle Sequence:

As we all know that if you cache an Oracle sequence then Oracle reallocates values in memory so that they can be accessed faster.

I ran below command to make sequence definition cache 1000 values. In my case after running below command the UPDATE SQL statement did not come in my AWR report and the time execution got reduced to five minutes instead of half hour. What a large difference!

ALTER SEQUENCE MYSEQ_OWNER.MYSEQ_AUDIT_INFO CACHE 1000;

Conclusion:

To conclude I would suggest you that whenever it is possible, try to cache your sequences. This will avoid the overhead of extra physical I/O and give tremendous performance boost to your system. Read Again!!

 More Database Articles
   Database Security: Step by step guideline
   Great Tips for Protecting Oracle Listener!!
   The power of Oracle Optimizer Hints!
   Handling Tricky Oracle Job Scheduling Problem!!
   Oracle 11g Enhanced Optimizer Statistics Maintenance!!