Explain Plan/AutoTrace/tkprof using Toad

Using Toad you can easily view the explain plan feature of oracle. To run this feature you have to execute either TOADPREP.SQL script orUTLXPLAN.SQL (If you choose to use oracle plan table) then you have to run UTLXPLAN.SQL in each schema from the location <ORACLE_HOME>/RDBMS/ADMIN and also you need to change the TOAD_PLAN_TABLE name to “PLAN_TABLE” from the Toad option button under Oracle –>General option. The below screen shot will help to change the explain plan table for your schema.

Now simply write your query on Toad editor and press CLT+E to check or display explain plan. You can change the optimizer mode just right click on the statement and choosing the optimizer mode. More about the optimizer mode, you can search the separate topics on this blog.

Toad stores previously generated explain plan for review and comparison. You can access these by check marking on the “save previous explain plan results” option of the above screen shot. Thus you can compare the generated plan for the variation of same queries or different queries. You can clear the previous generated explain plan results that are no longer needed or obsolete.

Auto Trace:

Through this feature you can review the resource usage for the particular query in SQL Editor. Unlike toad explain plan for current statement, which can be generated without executing the statement, auto trace requires statement must be executed in order to generated its result. You can review the result of auto trace in below figure.

If the auto trace is not enabled when you click the auto trace button in results panel, TOAD prompts you to enable it. Otherwise you can enable or disable it with the right mouse click on the SQL editor and selecting auto trace. It will remain enabled until you disable or the Toad session is terminated.

SQL Trace (TKPROF)

SQL Trace (TKPROF) is a server-side Oracle trace utility that captures CPU, I/O, and resource usage during statement execution. The output file is created on your Oracle server in the directory USER_DUMP_DEST. You can view this file from Database –> diagnose –>TKPROF Interface. 

To enable the TKPROF interface, select View –> Toad Options –>Executables option. Check the below screen shot for more details.

SGA Trace Optimization:

Instead of using Auto Trace and TKPROF, you can use SGA Trace Optimization from Database –> Monitor to displays statistics from multiple SQL statements currently present in Oracle's SGA.

You can set several options from this screen in order to search for SQL statements. You can pass a SQL statement into the SQL Editor from the SGA Trace window. Highlight the desired statement then click on the “Load selected statement in a SQL Editor” button on the SGA Trace toolbar.

Setting Schema Browser in Toad

Through the use of Toad Schema browser you can not only quickly and easily monitor the complex structure of database also manage and control all those structure.

You can open Schema Browser from the menu Database –> Schema Browser

Toad Tab Style Setting:

Suppose your Toad tabs style setting is horizontally scrollable and if you need to change it multi-line span.

Select schema browser option from the menu View –> Toad Options to change the setting or browser display style.

Then from the option Schema Browser –> Left hand side check the browser style multi-line tabs

Finally the Toad tab style setting looks like as above. In this way you can change different required setting from toad option screen such as:

If you select “only show user that own object” from the toad option screen then you are able to access the only those schema having object in database in your schema browser. Same like above you can check and uncheck the option “only show users that own object excluding synonym” and “only show users that own object excluding synonyms and temporary tables”. Through this way you can change the different setting and style of the schema browser. It is not possible for me to describe all those setting. Here I am trying to explain only the most common and required setting.

Enabling/Disabling Schema Browser Tabs:

Suppose you do not want to developer using DBA related information. You can disable that tab from the toad schema browser. Just Right click on the schema browser Tabs as shown in below figure.

Then click on the Configure and uncheck the tab, you want to disable.

From the above figure you can see that, I already uncheck the Java, DB links, Jobs and Types option for the schema browser tab.

You can see from the above figure the uncheck tab is not available on the schema browser.

How to Find Database Optimal and Needed Undo Size

Since oracle 9i, rollback segments are re-named undo logs, traditionally it were stored in rollback segments until a commit or rollback segments were issued.
Automatic undo management allows the DBA how long information should be retained after commit. The larger your undo tablespace the more you can hold for long running DML operation (Preventing "snapshot to old error on long running queries).
You can choose to allocate a specific size for the UNDO tablespace and then set the optimal UNDO_RETENTION according to UNDO size. This is specially useful when you disk space is limited and you do not want to allocate more space than required UNDO size.

OPTIMAL UNDO RETENTION = ACTUAL UNDO SIZE /(DB_BLOCK_SIZE *       UNDO_BLOCK_PER_SEC)

Find Actual Undo Size:
SELECT SUM(a.bytes) "UNDO_SIZE"
  FROM v$datafile a, v$tablespace b, dba_tablespaces c
 WHERE c.contents = 'UNDO' AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name AND a.ts# = b.ts#;

UNDO_SIZE
-----------
  7948206080

Find Undo Blocks per Second:
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
      "UNDO_BLOCK_PER_SEC"
  FROM v$undostat;

Read more »

How to Find Database Optimal and Needed Undo Size

Since oracle 9i, rollback segments are re-named undo logs, traditionally it were stored in rollback segments until a commit or rollback segments were issued.
Automatic undo management allows the DBA how long information should be retained after commit. The larger your undo tablespace the more you can hold for long running DML operation (Preventing "snapshot to old error on long running queries).
You can choose to allocate a specific size for the UNDO tablespace and then set the optimal UNDO_RETENTION according to UNDO size. This is specially useful when you disk space is limited and you do not want to allocate more space than required UNDO size.

OPTIMAL UNDO RETENTION = ACTUAL UNDO SIZE /(DB_BLOCK_SIZE *       UNDO_BLOCK_PER_SEC)

Find Actual Undo Size:
SELECT SUM(a.bytes) "UNDO_SIZE"
  FROM v$datafile a, v$tablespace b, dba_tablespaces c
 WHERE c.contents = 'UNDO' AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name AND a.ts# = b.ts#;

UNDO_SIZE
-----------
  7948206080

Find Undo Blocks per Second:
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
      "UNDO_BLOCK_PER_SEC"
  FROM v$undostat;

UNDO_BLOCK_PER_SEC
------------------
        6.47
Find Undo Block Size:
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
 FROM v$parameter
WHERE name = 'db_block_size';

DB_BLOCK_SIZE [Byte]
--------------------
                8192

OPTIMAL UNDO RETENTION = 7948206080 /(8192 * 6.47)
149959.8145 [sec]
Using below query you can find all those information collectively:

Script: To Find calculated Optimal Undo retention:
Using below query you can find all those above information collectively.
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       ROUND((d.undo_size / (to_number(f.value) *
       g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a, v$tablespace b, dba_tablespaces c
         WHERE c.contents = 'UNDO' AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name AND a.ts# = b.ts#
       ) d,
       v$parameter e, v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
              undo_block_per_sec
         FROM v$undostat
       ) g
WHERE e.name = 'undo_retention' AND f.name = 'db_block_size';

ACTUAL UNDO SIZE [MByte]   UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
----------------------    ---------------- -----------------------------
7580 10800 149960 
Script: Planning to Calculate Needed Undo size for database:
In Oracle, UNDO size can be controlled with the undo_retention parameter and the size of the UNDO tablespace, thus the setting for these are determined by the level of DML activity in the database:
1. If you are using heavy DML operation make sure there is enough sized rollback segments.
2. If you expect heavy DML load then must have multiple undo tablespace.
3. Try to limit the number of simultaneous users per UNDO to four.
4. For large batch transactions create special large extent rollback segments in a separate tablespace from the other rollback segments,
only bring them online when needed and use SET TRANSACTION command to assign them to specific transactions.
5. Try to avoid running large batch transactions simultaneously with OLTP or smaller transactions.
UNDO_SIZE = UNDO_RETENTION * DB_BLOCK_SIZE * UNDO_BLOCK_PER_SEC

  SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a, v$tablespace b, dba_tablespaces c
        WHERE c.contents = 'UNDO' AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name AND a.ts# = b.ts#
       ) d,
      v$parameter e, v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention' AND f.name = 'db_block_size';

ACTUAL UNDO SIZE [MByte]    UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
-----------------------     ----------------         -----------------------
7580 10800 545.90

Tuning PGA_AGGREGATE_TARGET

The oracle 9i introduces a new parameter PGA_AGGREGATE_TARGET to fix the issue of multiple parameters in oracle 9i such as SOR_AREA_SIZE, HASH_AREA_SIZE of earlier version.
The PGA is private memory region that contains the data and control information for a server process. Oracle Database reads and writes information in the PGA on behalf of the server process. The RAM allocated to the PGA_AGGREGATE_TARGET is used by Oracle connections to maintain connection-specific information (e.g., cursor states) and to sort Oracle SQL result sets.

PGA_AGGREGATE_TARGET allows Oracle9i perform far faster than earlier version because the memory is only allocated in used for the duration of the session upon which is immediately freed up to become available for use by other connected Oracle tasks.
Initial or Default Setting of PGA_AGGREGATE_TARGET
By default oracle database uses PGA_AGGREGATE_TARGET 20% of SGA Size. However the initial or default setting may be too low for database. In that case you need to run a representative workload on the instance and monitor performance of PGA statistics. Finally after monitoring (if required)
tune PGA_AGGREGATE_TARGET using oracle PGA advice setting.
If you have total memory of system = 4GB then memory required for OS = 20% of total Memory 0.8GB resulting memory 3.2GB available for use of SGA+PGA.
For OLTP system the PGA_AGG_TARGET = 20% of resulting memory i.e 3.2 * 0.2 = 0.64GB or 655MB. For DSS system the PGA_AGG_TARGET = 70% of resulting memory i.e 3.2 * 0.7 = 2.24GB or 1600MB
How to Tune PGA_AGG_TARGET
If the amount of data processed fits the size of optimal work area (Optimal size is when the size of a work area is large enough that it can accommodate the input data) then all the operation is performed in memory and cache is almost 100%.
If the amount of data processed larger than the optimal size then the input is divided into smaller pieces and in that case some piece of data are processed in memory while rest are spilled to temporary tablespace, thus an extra parse is performed on all or some input data, that corresponding size of available work area is called "one-pass". when the available work area size even less than "one-pass" then multiple passes over the input data are required causing dramatic increase in response time hence decreasing the cache hit %.
Generally in OLTP system, size of input data is small hence mostly run in optimal mode where as in DSS system, input data is very large thus require more PGA for good or healthy performance rate.
Before tuning PGA you need to monitor performace of automatic PGA memory. For that several dynamic performance views are available. These views show the total amount of RAM memory utilization for every RAM memory region within the database and new statistics to V$SYSTAT and the new V$PGASTAT and V$PGA_TARGET_ADVICE views assist the DBA in determining if this parameter is not set correctly and the best setting for the PGA_AGGREGATE_TARGET parameter.
Oracle allows a single process to use up to 5 %of the PGA_AGGREGATE_TARGET, and parallel operations are allowed to consume up to 30 percent of the PGA RAM pool.

SQL> Select * from v$SYSSTAT;

SQL> select  name, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage

       from (select name, value cnt, (sum(value) over()) total

        from v$sysstat where name like 'workarea exec%'

       );

Profile                                    Count Percentage

----------------------------------- ------------ ----------

workarea executions - optimal              2,258      100

workarea executions - onepass              0            0

workarea executions - multipass            0            0

Select name, value/1024/1024 VALUE_MB
from   V$PGASTAT
where name in ('aggregate PGA target parameter',
'total PGA allocated',
'total PGA inuse')
union
select name, value
from   V$PGASTAT
where name in ('over allocation count');

NAME VALUE_MB
--------------- ----------------
aggregate PGA target parameter 471
over allocation count 0
total PGA allocated 33.8583984375
total PGA inuse 26.32421875

The optimal executions are performed entirely within the allocated memory areas. This is the most favorable type of execution. Sometimes, the operation is too big to perform within allocated memory area, and then some part of the operation spills on to disk. If only one-pass was needed on disk, then this execution is noted on one-pass statistics. If more than onepass was needed on disk then this execution is noted on multi-passstatistics. Ideally all execution should be in optimal statistics and the statistics for one-pass and multi-pass should be zero.

PGA_AGGREGATE_TARGET multipass executions indicate a RAM shortage, and you should always allocate enough RAM to ensure that at least 95% of connected tasks can acquire their RAM memory optimally. Thus DBA needs to increase this Parameter when "multipass" value is greater than ZERO and Reduce whenever the optimal executions are 100 percent.

SQL> select name, value from v$pgastat;

The following script provides excellent overall usage statistics for all Oracle9i connections.

NAME                                                   VALUE    

------------------------------------------------------ ----------

aggregate PGA target parameter                        284164096

aggregate PGA auto target                             235938816

global memory bound                                   14208000

total PGA inuse                                       25638912

total PGA allocated                                   35466240

maximum PGA allocated   1                             84498176

total freeable PGA memory                             0

PGA memory freed back to OS                           0

total PGA used for auto workareas                     3637248

maximum PGA used for auto workareas                   15818752

total PGA used for manual workareas                   0

maximum PGA used for manual workareas                 0

over allocation count                                 0

bytes processed                                       18302224384

extra bytes read/written                              4149905408

cache hit percentage                                  81.51

In the above display from v$pgastat we see the following statistics.

·         Aggregate PGA auto target - This column gives the total amount of available memory for Oracle9i connections. This value is derived from the value on the INIT.ORA parameter PGA_AGGREGATE_TARGET. 

·         Global memory bound - This statistic measures the max size of a work area, and Oracle recommends that whenever this statistics drops below 1MB, you should increase the value of the PGA_AGGREGATE_TARGET parameter.
·         Total PGA allocated - This statistic display the high-water mark of all PGA memory usage on the database. You should see this value approach the value of PGA_AGGREGATE_TARGET as usage increases.
·         Over allocation Count - If over allocation > 0 indicating that PGA_Target is too small to even meet the minimum PGA memory needs then you must increase the PGA_AGG_TARGET.
·         extra bytes read/write - Ideally it should be small if it is having large value you should increase the PGA_TARGET.

·         Cache hit percentage - A value of 100% means that all work are executed by the system since instance startup time have used an optimal amount of PGA memory. When it is not running optimally one or more extra parse is performed on input data which reduce the cache hit percentage.

1> If available memory >= optimal memory for 100% PGA cache hit %, set PGA_AGGREGATE_TARGET = optimal memory as in PGA_TARGET_ADVICE.

2> If available memory < optimal memory for 100% PGA cache hit %, set PGA_AGGREGATE_TARGET = available memory and settle for a lower PGA cache hit %.

·         Total PGA used for auto workareas - This statistic monitors RAM consumption or all connections that are running in automatic memory mode. Remember, not all internal processes may use the automatic memory feature. For example, Java and PL/SQL will allocate RAM memory, and this will not be counted in this statistic. Hence, we can subtract value to the total PGA allocated to see the amount of memory used by connections and the RAM memory consumed by Java and PL/SQL.

Estimated PGA memory for optimal/one-pass
This statistic estimates how much memory is required to execute all task connections RAM demands in optimal mode. Remember, when Oracle9i experienced a memory shortage, it invoked the multi-pass operation. This statistics is critical for monitoring RAM consumption in Oracle9i, and most Oracle DBA's will increase PGA_AGGREGATE_TARGET to this value.
SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,

     ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,

     ESTD_OVERALLOC_COUNT

     FROM   v$pga_target_advice;

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT

---------- -------------- --------------------

        34             95                      0

        68            100                     0

       136            100                    0

       203            100                    0

       271            100                    0

       325            100                    0

       379            100                    0

       434            100                    0

       488            100                    0

       542            100                    0

       813            100                    0

Set the value of PGA_AGG_TARGET to a value where we avoid any over allocation, so lower target value we can set 942 where allocation count is 0 as well as cache 100%.

SQL> SELECT LOW_OPTIMAL_SIZE/1024 low_kb, (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,

     estd_total_executions estd_tot_exe,

     estd_optimal_executions estd_opt_cnt,

     estd_onepass_executions estd_onepass_cnt,

     estd_multipasses_executions estd_mpass_cnt

     FROM v$pga_target_advice_histogram

     WHERE pga_target_factor = 2

     AND estd_total_executions != 0

     ORDER BY 1;

    LOW_KB    HIGH_KB ESTD_TOT_EXE ESTD_OPT_CNT ESTD_ONEPASS_CNT ESTD_MPASS_CNT

---------- ---------- ------------ ------------ ---------------- --------------

         8         16         1721         1721                0              0

        16         32           61           61                0              0

        64        128            6            6                0              0

       128        256           22           22                0              0

       512       1024           19           19                0              0

      1024       2048           61           61                0              0

      2048       4096            2            2                0              0

You can use the content of above views to determine how key PGA statistics will be impacted if you change the value of PGA_AGGREGATE_TARGET.
To change the PGA_AGG_TARGET value:
Alter system set pga_aggregate_target=987758592;
After increasing the PGA check PGA_AGG_target again.
select * from v$pgastat;
Some mor dynamic views to check the PGA perfromace:
v$process, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM and PGA_MAX_MEM
V$SQL_WORKAREA_HISTOGRAM;
V$SQL_WORKAREA_ACTIVE;
V$SQL_WORKAREA;
V$PGA_TARGET_ADVICE;
V$PGA_TARGET_ADVICE_HISTOGRAM
V$SYSSTAT
V$SESSTAT

FAQs on Sequence issues in RAC database with Test Case

1) What will be best DDL of sequence in RAC database and what will be impact on performance?

Ans:

You are basing conclusions on wrong assumptions. Documentation states: "A sequence is a schema object that can generate unique sequential values". I wish it would explain it in more detail since many non-math oriented (no offense to anyone) people get confused: "A sequence is a schema object that can generate unique sequential but not necessarily consecutive values". All you can do is change sequence attribute NOORDER to ORDER (keep in mind ORDER/NOORDER is only meaningful if you have RAC - which you do). However expect performance degradation - sequence ordering comes at a price. But even then you will get gaps - transaction can roll back and sequence value is lost or you shutdown database and unused portion of cached sequence values is lost (you can avoid that by changing sequence to NOCACHE but it will again come at a cost of performace degradation).

2) We migrated our database to 3 node RAC cluster and the application will generate sequences on each instance.  What are the most common ways to reduce contention on busy RAC database where there is contention for sequence generation?

Ans:

Sequences in Oracle are the same as a single instance, and the RAC internals will manage the uniqueness of the sequence.  First, see these Oracle best practices for Oracle RAC sequence usage.

The "contention that we see on busy RAC systems (with high insert DML are because each application of each node is competing for resources that exist on the database.  Fortunately, Oracle has several tools for relieving Oracle RAC sequence contention:

a) Use the sequence “cache” option: Caching sequences is especially important for high-DML applications with lots on insert and update activity.  You can easily cache as sequence with the "add/alter sequence xxx cache" command.  The "cache" clause caches the specified number of sequence values into the buffers in the SGA, speeding-up sequence access speed. 
b)  Increase sequence index freelist groups (or use ASSM):
Note: But beware that ASSM performance may degrade under "massive" insert DML. (over 100 inserts per second) 
c) Use sequence staggering:  A staggered sequence insert trigger is a specific constant added to the sequence value based on the instance number. This isolates each set of inserts and prevents inadvertent attempts to use the same sequence number.

3) What will be Proper Sequence Usage in RAC database?

Ans:
 If sequence DDLs are not proper, then sequences can be a major headache in RAC. Non-cached sequences can be the cause of major performance issues on RAC.  Another major performance issue can occur if the cached sequence’s cache_value is set too low.  Tom Kyte wrote on his website, asktom.oracle.com, the following about proper sequence usage.

Note, however, that cache values are lost during shutdown.  Generally speaking, sequences should be either CACHED or ORDERED, but not both. The preferred sequence is a CACHED, non-ordered sequence. If the ordering of sequences is forced, performance in a RAC environment will suffer unless ordering the sequence to a single node in the RAC 
cluster isolates insert activity. 

Oracle 11g RAC Sequence Example:

Create the sequence.

SQL> create sequence seq_rac_test cache 50;

Sequence created.

Select the sequence from node 1.

SQL> select seq_rac_test.nextval from dual;

NEXTVAL
----------
1

Again.

SQL> /

NEXTVAL
----------
2

Again.

SQL> /

NEXTVAL
----------
3

Again.

SQL> /

NEXTVAL
----------
4

Now select the sequence from node 2.

SQL> select seq_rac_test.nextval from dual;

NEXTVAL
----------
51

Again.

SQL> /

NEXTVAL
----------
52

Again.

SQL> /

NEXTVAL
----------
53

Again.

SQL> /

NEXTVAL
----------
54

Select again from node 1 when NEXTVAL is near the cache maximum of 50.

SQL> /

NEXTVAL
----------
48

Again.

SQL> /

NEXTVAL
----------
49

Again.

SQL> /

NEXTVAL
----------
50

Again.

SQL> /

NEXTVAL
----------
101

As can be seen, since node 2 has already cached values 51-100, it is logical that node 1 will then cache 101-150.  Using the order clause when creating the sequence will guarantee sequence order across all RAC instances, but will likely cause performance problems.

Another method to optimize the use of sequences is to use a staggered sequence insert trigger. A staggered sequence insert trigger is a specific constant added to the sequence value based on the instance number. This isolates each set of inserts and prevents inadvertent attempts to use the same sequence number. An example of a staggered sequence insert trigger is shown in the following script:

CREATE TRIGGER insert_EMP_PK
 BEFORE insert ON EMP
 FOR EACH ROW
DECLARE
 INST_ID NUMBER;
 SEQ_NUM NUMBER;
 INST_SEQ_ID NUMBER;
BEGIN
 select
    INSTANCE_NUMBER INTO INST_ID
  FROM
    V$INSTANCE;
  select
    EMP_ID_SEQ.NEXTVAL INTO SEQ_NUM
  FROM
    DUAL;
  INST_SEQ_ID:=(INST_ID-1)*100000 + SEQ_NUM;
  :NEW.EMP_ID:=INST_SEQ_ID;
END;
/

A staggered sequence trigger will insert the values into indexes such that each instance’s values are staggered to prevent index node intra-node transfers. The formula to allow this is:

index key = (instance_number -1)* 100000+ Sequence number

One of the best ways to determine if sequences are a bottleneck on the system is to simply run the following query.

SELECT LAST_NUMBER FROM DBA_SEQUENCES WHERE SEQUENCE_NAME = ‘X’;

The above query will show the last sequence number that has been written to disk.  A safe rule to follow is to ensure the LAST_NUMBER value changes only a few times per day.  If the LAST_NUMBER is changing constantly, make sure the sequence is cached.  If the sequence is cached, keep increasing the cache value until the LAST_NUMBER stabilizes.

In some applications, the sequence numbers used must be sequential.  An example would be the line numbers for a purchase order or perhaps check numbers for an automatic check printer. In this case, a sequence table may have to be used to store the highest sequence number. The value is read from the sequence table, increased by one, and then 
updated. While all of this occurs, the row for the sequence being used is locked, thus no one else can use it. If this type of logic must be used, the table should be placed in a tablespace with a small 2048 block size.

Key Note:
Great care must be taken to select the fastest interface and network components to get optimal performance from the cluster interconnect.

Designing for true high availability starts with redundant hardware. If there are multiple single-points of failure, the finest RAC implementation in the known universe will do little to achieve high availability.

4) How I ll overcome sequence issues in RAC database?

The old 80/20 rule applies here; 80% or more of the overhead results from 20% or less of the workload. If the 20% is fixed by observing some simple guidelines, tangible benefits can be achieved with minimal effort. Workload problems can be corrected by implementing any or all of the following:
a) Use the sequence “cache” option: Oracle has a method for  caching frequently reference sequences, and you can also cache sequences with n-way Streams replication for fast access to sequence values.  Caching sequences is especially important for high-DML applications with lots on insert and update activity.  You can easily cache as sequence with the "add/alter sequence xxx cache" command.  The "cache" clause caches the specified number of sequence values into the buffers in the SGA, speeding-up sequence access speed. 
b) Use the noorder sequence clause:  When creating sequences for a RAC environment, DBAs should use the noorder keyword to avoid an additional cause of SQ enqueue contention that is forced ordering of queued sequence values.  In RAC, a best practice is to specify the “noordered” clause for a sequence.  With a non-ordered sequence, a global lock not required by a node whenever you access the sequence.
c) Increase sequence index freelist groups (or use ASSM):  Indexes with key values generated by sequences tend to be subject to leaf block contention when the insert rate is high. One remedy is to alter the index to use additional freelists groups.  You can often achieve the same relief from index block contention by using ASSM or 

using ht sequence “cache” option”. If possible, reduce concurrent changes to index blocks. However, if index key values are not modified by multiple instances, or if the modification rate is not excessive, the overhead may be acceptable. In extreme cases, techniques like physical table partitioning can be applied.

d) Use sequence staggering:  A staggered sequence insert trigger is a specific constant added to the sequence value based on the instance number. This isolates each set of inserts and prevents inadvertent attempts to use the same sequence number.

5) How does RAC synchronize sequences?

In Oracle 10g/11g RAC, if you specify the “ordered” clause for a sequence, then a global lock is allocated by the node when you access the sequence.

This lock acquisition happens only at the first sequence access for the node (A), and subsequent uses of the sequence do not wait on this lock. If another node (B) selects from that sequence, it requests the same global lock and once acquired it returns the sequences next value.

The wait event associated with this activity is recorded as “events in waitclass Other” when looked in gv$system_event. So much for event groups, it couldn’t be more obscure. That view shows overall statistics for the session.

However if you look in the gv$session_wait_history it shows as “DFS lock handle” with the “p1” parameter been the object_id of the sequence. This second view has a sample of the last 10 wait events for a session.

In a SQL_TRACE with waitevents (10046 trace) it will be a “DFS lock handle” but in AWR or statspack reports it will be “events in waitclass Other”. So much for consistency.


Test Case:

This is a RAC database and the sequence was created with the default values.

Not only the sequences cache was the default of 20, but it was “noordered”. Being “noordered” Oracle will not guarantee the order in which numbers are generated.

Example of “noorder” sequence in 10g RAC:

Session 1 on node-A: nextval -> 101
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 121
Session 1 on node-B: nextval -> 122
Session 1 on node-A: nextval -> 103
Session 1 on node-A: nextval -> 104

The sequence cache is in the shared pool, therefore sessions on the same node can share the cached entry, but sessions on different nodes cannot. I wonder why Oracle doesn’t make “ordered” the default for sequences.

So I explained to the developer how sequences work in RAC and how each node has its own “cache”.

We changed the sequence to “ordered” and increased the cache to 1000. Now selecting on either node gets the next number as he expected. I warned him that there would be some performance implications due to cluster synchronization. Him been a responsive developer, asked me what would be the impact, so I tested it out.

How does that change our example?

Session 1 on node-A: nextval -> 101 (DFS Lock handle) (CR read)
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 103 (DFS Lock handle)
Session 1 on node-B: nextval -> 104
Session 1 on node-A: nextval -> 105 (DFS Lock handle)
Session 1 on node-A: nextval -> 106
(more selects)
Session 1 on node-A: nextval -> 998
Session 1 on node-B: nextval -> 999 (DFS Lock handle)
Session 1 on node-B: nextval -> 1000 (CR read)
The cache size also has some RAC synchronization implications. When the cached entries for the sequence are exhausted, the sequence object needs to be updated. This usually causes a remote CR (current read) over the interconnect for the block that has the specific sequence object. So a bit more activity here.

create sequence test_rac;
declare
  dummy number;
begin
for i in 1..50000 loop
  select test_rac.nextval into dummy from dual;
end loop;
end;
/
Results:

50 000 loops with cache = 20 (default)
1 node = 5 seconds
2 nodes at same time = 14 seconds
2 nodes at same time ordered = 30 seconds

50 000 loops with cache = 1000

1 node = 1.5 seconds
2 nodes at same time = 1.8 seconds
2 nodes at same time ordered = 20 seconds

With a smaller cache, the “noordered” still has as significant impact as every 10 fetches (cache 20 divided by 2 nodes fetching) it has to synchronize between the 2 nodes

conclusion:

By default sequences in 10g RAC are created without ordering. Beware of using applications that rely on sequences to be ordered and using it in a RAC environment.

Consider changing all user sequences to “ordered” as a precaution and increasing the cache size.

The default cache value is still very low and even not-ordered sequences will cause contention in a highly-active sequence even in non-RAC and causing an additional block exchange every 20 values in RAC.

For high volume insert operations where ordering is not performed on the value returned from the sequence, consider leaving the sequence “noordered” but increasing the cache size significantly.

Either way, the sequence parameters should be reviewed, as chances are, the defaults are not what you need.

I remember reading somewhere that in Oracle 9i the “ordered” clause in RAC was equivalent to “nochache”. I can’t imagine how bad that would be in concurrent selects from the same sequence.

It would be interesting if someone running 9i RAC performs the test case and I would appreciate if you post the results in the comments.