3 node Oracle RAC - Cluster wait due to remastering of objects

In  our production  environment,  we are using a three Node oracle RAC  with around 1+ TB of database and handles a medium OLTP transactions in database.  This application is being used by a financial company in India that has around 4500 branches across the country,  The version of oracle is 11.2.0.2.0 (11GR2) . 

Recently we started observing high cluster wait during the peak  load hours and also  experience node eviction few times .  We could not find any  misbehavior on the network  side specially  on interconnect . None of the monitoring tools helped us to  find any  issues in  the inter connect. Then we started analyzing the OEM  results in depth  and found, the cluster event occurs while GC  Remastering happens. By  default DRM (Dynamic Remastering) is enabled and the spikes are occurring at 10 min intervals after a node rejoin/start.  On further analysis we found the the _gc_policy_time  parameter (It controls how often the queue is checked to see if the remastering must be triggered )   has the default value 10 .  We debated whether to  disable the DRM  by  setting the parameter value to 0. But since this require a complete restart of oracle,  we were bit hesitant to do  the restart of the server. This was essentially  due to  the cluster wait it shows after the restart.

On checking further we found another parameter that controls the remastering behavior _gc_policy_minimum. This parameter is defined as “minimum amount of dynamic affinity activity per minute” to be a candidate for remastering. Defaults to 1500 and we thought it is lower in a busy environment like ours.  The best part of this is we do  not require a restart after setting this value and we decided to  increase the value to 20000 .  After setting the value we found the cluster wait events has almost disappeared and system is back to  the sate it was earlier.  
Since it was not a fully  documented parameter, we couldn't get much  expert opinion on this. Since we got a definite advantage by  setting this parameter,  i  thought i  will  share this in my  blog hoping some one may get advantage of this ...

Below is OEM display  after and before setting the parameter. 

Note:  This is an undocumented parameter by  oracle,  you  may  contact oracle support before setting the parameter value. 

How to pass array parameters into oracle stored procedure

There are various ways in which you could handle passing multiple records to oracle stored procedures. The old style of handling this was passing the values as xml and process the xml by the procedure. This adds lots of overhead to the oracle processors. An alternate to this is to pass the array in to the procedure and iterate and insert the values to the table. this approach may be more meaningful for the web applications with UI and middle tier separated out from database layer. This approach will significantly reduce the number of calls made to the database layer from middle tier. from the .Net development environment, ODP.Net gives good options to bind your parameter array to stored procedures . You could leverage that for better efficiency.

Steps i have scripted below are
1. Creating a table to store the values
2. Create a package with the type and the procedure to insert the values to table using the array.
3. PL SQL script to try executing the procedure from the sqlplus.

-- Drop the existing test table
Drop table CPM_TESTTABLE;
-- Create the new test table
Create table CPM_TESTTABLE
(COL_NAME varchar2(20) ,
COLN_NAME1 varchar2(10));

--Create the package with the type and the procedure
CREATE OR REPLACE PACKAGE ARRAY_PKG AS
TYPE char_array1 IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;

PROCEDURE SPM_INS_TESTTABLE (
pColName IN char_array1,
pColName1 IN char_array1);
END ARRAY_PKG;

CREATE or REPLACE package body ARRAY_PKG as
PROCEDURE SPM_INS_TESTTABLE
(
pColName IN char_array1,
pColName1 IN char_array1
)
IS

BEGIN

FOR I IN 1..PCOLNAME.COUNT
LOOP
INSERT INTO CPM_TESTTABLE
(COL_NAME,COLN_NAME1)
VALUES
(pColName(i),pColName1(i));

END LOOP;

EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END SPM_INS_TESTTABLE;

END ARRAY_PKG;

-- PL sql script to execute the procedure with array inout parameter
Declare
Customer_name ARRAY_PKG.char_array1;
Begin
-- Adding 10 rows to the array
for i in 1..10 Loop
customer_name(i) := 'Ciju '|| to_char(i);
End Loop;
delete from CPM_TESTTABLE ; -- Useful while re re-running the test
-- Call the procedure using array input
ARRAY_PKG.SPM_INS_TESTTABLE (customer_name , customer_name);
End;
commit;
SELECT * FROM CPM_TESTTABLE
-- End of script

Oracle - SYS_REFCURSOR Variable value listing

First step is to create a procedure with out parameter datatype SYS_REFCURSOR
-- Procedure creation
CREATE OR REPLACE procedure cp_test1 (pgnum int ,pgrecords int ,pResultSet OUT SYS_REFCURSOR) is
Begin
Open pResultSet for
select *
from ( select a.*, rownum r
from ( select *
from test1
order by id ) a
where rownum < (pgnum * pgrecords)+1)
where r > ((pgnum-1) * pgrecords);

End;

After the procedure is created, please try below statements in SQL plus:
sql > set serveroutput on
sql > variable r1 refcursor
sql > exec cp_test1(1,20,:r1)
sql > print :r1

Oracle - Search nodes of tree structure using connect by

Finding --Bottom to top relation
select entityunitid, entityname
from table name
connect by prior parentunitid = entityunitid
start with entityunitid = bottom id

--Top to Bottom relation
select entityunitid, entityname
from table name
connect by prior entityunitid = parentunitid
start with entityunitid = top id

Change an existing primary key for table having records; with a new identity column

Steps to change an existing primary key for table having records with a new identity column :

-- Create a sample table with primary key

Create table test3 ( id int primary key, name char(2))

go

-- Insert some records to this table

Insert into test3 values (1,'cc')

Insert into test3 values (2,'cq')

Insert into test3 values (3,'cw')

go

-- Add a new column ( this will be set primary key in subsequent steps)

Alter table test3 add new_id bigint

go

-- Get and drop the primary key constraint (to drop already created primary key)

declare @cons_name varchar(300);

Select @cons_name = name from sysobjects where xtype = 'PK' and parent_obj in ( select id from sysobjects where xtype = 'U' and name = 'test3')

exec (' Alter table test3 Drop constraint ' + @cons_name) ;

-- Update the new column with the sequence number

go

Update test3 set test3.new_id = tt.a

From (

Select row_number() over (order by name) as a ,name from test3) tt

Where test3.name = tt.name

go

-- Set the column as not null

alter table test3 alter column new_id bigint not null

go

-- Set the new column as primary key

alter table test3 add primary key (new_id)

go

Select * from test3

-- You could set the identity key to next value using dbcc_checkindent

-- DBCC CHECKIDENT ("table name", RESEED, 300)

TSQL enhancements in SQL server 2005 # Part 1

1. TOP clause with TIES option : Select TOP(4) with TIES * from test order by idkey desc

2. Supports an OUTPUT clause so a single trip to the server performs the data updateand returns the results.

3. PIVOT operator provides the ability to quickly and easily generate cross tab queries. A cross tab query rotates rows data into columns data. : select VENDORID , [7],[8],[9] from MonthlyPurchaseOrders pivot (sum(subtotal) for Ordermonth in ( [7],[8],[9],[10])) as a

4. Exception Handling with TRY/CATCH

5. ROW_NUMBER() function creates a column that displays a number corresponding the row's position in the query result : select name, row_number() over (order by name) from test

6. RANK() function works much like the ROW_NUMBER() function in that it numbers records in order. They differ in the way they work when duplicate values are contained in the ORDER BY expression. : select name, row_number() over (order by name) , rank() over ( order by name) from test

7. DENSE_RANK() works the same way as RANK() does but eliminates the gaps in the numbering.

8. NTILE() breaks the result set into a specified number of groups and assigns the same number to each record in a group

9. Common Table Expressions (CTE) A Common Table Expression (CTE) is a temporary result set created from a simple query

Using NOLOCK and READPAST table hints in SQL Server

NOLOCK

This table hint, also known as READUNCOMMITTED, is applicable to SELECT statements only. NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.

The following example shows how NOLOCK works and how dirty reads can occur. In the script below, I begin a transaction and insert a record in the SalesHistory table.

SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)
 
The number of records returned is 301. Since the transaction that entered the  record into the SalesHistory table has not been committed, I can undo it. I'll  roll back the transaction by issuing the following statement:
ROLLBACK TRANSACTION
 
This statement removes the record from the SalesHistory table that I  previously inserted. Now I run the same SELECT statement that I ran earlier:
SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)
 
This time the record count returned is 300. My first query read a record that  was not yet committed -- this is a dirty read.

READPAST
 
This is a much less commonly used table hint than NOLOCK. This hint specifies  that the database engine not consider any locked rows or data pages when  returning results
The READPAST table hint example is very similar to the NOLOCK table hint  example. I'll begin a transaction and update one record in the SalesHistory  table.
BEGIN TRANSACTION
      UPDATE TOP(1) SalesHistory
      SET SalePrice = SalePrice + 1
 
Because I do not commit or roll back the transaction, the locks that were  placed on the record that I updated are still in effect. In a new query editor  window, run the following script, which uses READPAST on the SalesHistory table  to count the number of records in the table.
SELECT COUNT(*)
FROM SalesHistory WITH(READPAST)
 
My SalesHistory table originally had 300 records in it. The UPDATE statement  is currently locking one record in the table. The script above that uses  READPAST returns 299 records, which means that because the record I am updating  is locked, it is ignored by the READPAST hint.