connect sys/oracle@nleduc1_angel1 as sysdba
rem demo sequences under rac
pause
rem clean up
pause
drop sequence rac_nocache_noorder;
drop sequence rac_nocache_order;
drop sequence rac_cache_noorder;
drop sequence rac_cache_order;
drop sequence rac_gap_order;
rem see description of "Sequence" type locks
pause
col type for a2
col name for a20
col id1_tag for a10
col id2_tag for a10
col description for a40
select type,name,id1_tag,
id2_tag,description
from v$lock_type
where name like '%Sequence%';
rem to check for symptoms of Sequence Cache replenishment
rem check how many SQ enqueues were used
pause
select *
from gv$enqueue_stat
where eq_type = 'SQ'
order by inst_id;
rem or check here for more descriptive detail
pause
col eq_name for a16
col req_description for a20
col req_reason for a20
select inst_id,eq_type,eq_name,req_reason,
req_description,total_req#,total_wait#
from gv$enqueue_statistics 
where eq_type = 'SQ'
order by 1,2,4;
rem look at cumulative waits for SQ enqueues 
rem from last AWR snap for both instances
pause
col req_reason for a12
select instance_number,eq_type,event#,
req_reason,total_req#,total_wait#
from dba_hist_enqueue_stat
where eq_type = 'SQ'
and snap_id = (select max(snap_id) 
               from dba_hist_enqueue_stat)
order by 1;
rem to check for symptoms of Sequence ordering contention
pause
col event for a20
select inst_id,event,total_waits,average_wait
from gv$system_event
where event like '%row cache lock%'
or    event like '%DFS lock handle%'
or    event like '%enq: SQ%';
rem look at cumulative waits for "Sequence" contention events 
rem from last AWR snap for both instances
pause
col event_name for a20
break on INSTANCE_NUMBER skip 1
select INSTANCE_NUMBER, EVENT_NAME,TOTAL_WAITS,TOTAL_TIMEOUTS
from dba_hist_system_event
where (event_name like '%DFS lock%'
or event_name like '%enq: SQ%'
or event_name like '%row cache lock%')
and snap_id = (select max(snap_id)
               from dba_hist_system_event)
order by 1;
PAUSE CHECK FOR CURRENT SV LOCK WAITS
SELECT INST_ID, SID, CHR(BITAND(P1,-16777216)/16777215)||
       CHR(BITAND(P1, 16711680)/65535) "LOCK",
       TO_CHAR(BITAND(P1, 65536)) "MODE",
       P2, P3,SECONDS_IN_WAIT
FROM GV$SESSION
WHERE EVENT = 'DFS lock handle'
AND CHR(BITAND(P1,-16777216)/16777215)||
    CHR(BITAND(P1, 16711680)/65535) = 'SV'
ORDER BY 1,3;
PAUSE CHECK ASH FOR SV LOCK WAITS
BREAK ON INST_ID SKIP 1
SELECT INST_ID,SESSION_ID, CHR(BITAND(P1,-16777216)/16777215)||
       CHR(BITAND(P1, 16711680)/65535) "LOCK",
       TO_CHAR(BITAND(P1, 65536)) "MODE",
       P2, P3,TIME_WAITED
FROM GV$ACTIVE_SESSION_HISTORY
WHERE EVENT = 'DFS lock handle'
AND CHR(BITAND(P1,-16777216)/16777215)||
    CHR(BITAND(P1, 16711680)/65535) = 'SV'
ORDER BY 1,3;
PAUSE CHECK AWR FOR HISTORICAL SV LOCL WAITS
BREAK on INSTANCE_NUMBER SKIP 1
SELECT INSTANCE_NUMBER,SESSION_ID, CHR(BITAND(P1,-16777216)/16777215)||
       CHR(BITAND(P1, 16711680)/65535) "LOCK",
       TO_CHAR(BITAND(P1, 65536)) "MODE",
       P2, P3,TIME_WAITED
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE EVENT = 'DFS lock handle'
AND CHR(BITAND(P1,-16777216)/16777215)||
    CHR(BITAND(P1, 16711680)/65535) = 'SV'
ORDER BY 1,3;
rem now see the SV resources owned by LCK0 currently
rem in both instances
pause
col program for a20
col state for a10
col resource_name2 for a14
select l.inst_id,l.pid,p.program,l.resource_name2,
l.owner_node,l.state,l.blocked,l.blocker
from gv$ges_enqueue l, gv$process p
where resource_name2 like '%SV%'
and p.spid = l.pid
order by 1;
rem create a sequence using noorder and nocache
pause
create sequence rac_nocache_noorder
start with 1 increment by 1
nocache noorder;
rem create a sequence using order but nocache
pause
create sequence rac_nocache_order
start with 1 increment by 1
nocache order;
rem create a sequence using cache but no order
pause
create sequence rac_cache_noorder
start with 1 increment by 1 
cache 50000 noorder;
rem now create one with cache and order
pause
create sequence rac_cache_order
start with 1 increment by 1 
cache 50000 order;
rem create a second cache one with cache and order
pause
create sequence rac_gap_order
start with 1 increment by 1 
cache 50000 order;
rem see them in the dictionary
pause
desc dba_sequences
pause
col sequence_owner for a14
col sequence_name for a20
select * 
from dba_sequences
where sequence_name like '%RAC_%ORDER';
rem see them in row cache metadata
pause
select inst_id,sequence_owner,sequence_name,
active_flag,replenish_flag,wrap_flag,cycle_flag,order_flag,
min_value,max_value,nextvalue,increment_by,cache_size
from gv$_sequences
where sequence_name like '%RAC_%ORDER'
order by inst_id,sequence_name;
rem now see the SV resources owned by LCK0 currently
rem in both instances
pause
select l.inst_id,l.pid,p.program,l.resource_name2,
l.owner_node,l.state,l.blocked,l.blocker
from gv$ges_enqueue l, gv$process p
where resource_name2 like '%SV%'
and p.spid = l.pid
order by 1;
rem show gaps occurring
pause
select rac_gap_order.nextval
from dual;
rem do it again
pause
select rac_gap_order.nextval
from dual;
rem now flush the shared pool as this is one of 
rem several ways to "lose" cached sequence numbers
pause
alter system flush shared_pool;
rem now increment the second sequence with cache and order again
pause
select rac_gap_order.nextval
from dual;
rem now keep the sequence to prevent ageouts
pause
exec dbms_shared_pool.keep('RAC_GAP_ORDER','Q');
rem now increment it
pause
select rac_gap_order.nextval
from dual;
rem now flush the shared pool again
pause
alter system flush shared_pool;
rem now increment again
pause
select rac_gap_order.nextval
from dual;
rem sequence has been kept for this instance
pause
rem remove the gap demo sequence
pause
drop sequence rac_gap_order;
rem now keep remaining cached sequences in row cache of first intance
pause
exec dbms_shared_pool.keep ('RAC_CACHE_NOORDER','Q');
pause
exec dbms_shared_pool.keep ('RAC_CACHE_ORDER','Q');
rem see them in the object cache of the first instance
pause
col name for a20
col type for a16
select inst_id,name,type,kept 
from gv$db_object_cache
where name like '%RAC_CACHE%' 
and type = 'SEQUENCE'
order by 1,2;
rem one must keep them separately in each instance
pause
rem  now get first value from the nocache 
rem noorder sequence in the first instance
pause
select rac_nocache_noorder.nextval
from dual;
rem now check the SQ enqueues
pause
select *
from gv$enqueue_stat
where eq_type = 'SQ'
order by inst_id;
rem now see the SV resources owned by LCK0 currently
rem in both instances
pause
select l.inst_id,l.pid,p.program,l.resource_name2,
l.owner_node,l.state,l.blocked,l.blocker
from gv$ges_enqueue l, gv$process p
where resource_name2 like '%SV%'
and p.spid = l.pid
order by 1;
rem now get first value from the nocache 
rem ordered sequence in the first instance
pause
select rac_nocache_order.nextval
from dual;
rem now check the SQ enqueues
pause
select *
from gv$enqueue_stat
where eq_type = 'SQ'
order by inst_id;
rem now see the SV resources owned by LCK0 currently
rem in both instances
pause
select l.inst_id,l.pid,p.program,l.resource_name2,
l.owner_node,l.state,l.blocked,l.blocker
from gv$ges_enqueue l, gv$process p
where resource_name2 like '%SV%'
and p.spid = l.pid
order by 1;
rem now get first value from the cached 
rem noorder sequence in the first instance
pause
select rac_cache_noorder.nextval
from dual;
rem now check the SQ enqueues
pause
select *
from gv$enqueue_stat
where eq_type = 'SQ'
order by inst_id;
rem now see the SV resources owned by LCK0 currently
rem in both instances
pause
select l.inst_id,l.pid,p.program,l.resource_name2,
l.owner_node,l.state,l.blocked,l.blocker
from gv$ges_enqueue l, gv$process p
where resource_name2 like '%SV%'
and p.spid = l.pid
order by 1;
rem now get first value from the cached 
rem ordered sequence in the first instance
pause
select rac_cache_order.nextval
from dual;
rem now check the SQ enqueues
pause
select *
from gv$enqueue_stat
where eq_type = 'SQ'
order by inst_id;
rem now see the SV resources owned by LCK0 currently
rem in both instances
pause
select l.inst_id,l.pid,p.program,l.resource_name2,
l.owner_node,l.state,l.blocked,l.blocker
from gv$ges_enqueue l, gv$process p
where resource_name2 like '%SV%'
and p.spid = l.pid
order by 1;
rem chech which object the GES reource relates to
pause
col object_name for a20
select o.object_name,o.object_type
from dba_objects o, v$ges_enqueue e
where e.resource_name2 like '%SV%'
and o.object_id = substr(e.resource_name2,1,
                 (instr (e.resource_name2,',') -1 ));
rem now check the dictionary again 
pause
select * 
from dba_sequences
where sequence_name like '%RAC_%ORDER';
rem see them in the row caches metadata again
pause
select inst_id,sequence_name,order_flag,
nextvalue,cache_size
from gv$_sequences
where sequence_name like '%RAC_%ORDER'
order by inst_id,sequence_name;
rem now check for any increase in SV LOCK waits
pause
BREAK ON INST_ID SKIP 1
SELECT INST_ID,SESSION_ID, CHR(BITAND(P1,-16777216)/16777215)||
       CHR(BITAND(P1, 16711680)/65535) "LOCK",
       TO_CHAR(BITAND(P1, 65536)) "MODE",
       P2, P3,TIME_WAITED
FROM GV$ACTIVE_SESSION_HISTORY
WHERE EVENT = 'DFS lock handle'
AND CHR(BITAND(P1,-16777216)/16777215)||
    CHR(BITAND(P1, 16711680)/65535) = 'SV'
ORDER BY 1,3;
PAUSE CHECK AWR FOR HISTORICAL SV LOCL WAITS
BREAK on INSTANCE_NUMBER SKIP 1
SELECT INSTANCE_NUMBER,SESSION_ID, CHR(BITAND(P1,-16777216)/16777215)||
       CHR(BITAND(P1, 16711680)/65535) "LOCK",
       TO_CHAR(BITAND(P1, 65536)) "MODE",
       P2, P3,TIME_WAITED
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE EVENT = 'DFS lock handle'
AND CHR(BITAND(P1,-16777216)/16777215)||
    CHR(BITAND(P1, 16711680)/65535) = 'SV'
ORDER BY 1,3;
rem now connect to second instance 
pause
connect sys/oracle@nleduc2_angel2 as sysdba
rem now keep cached sequences in row cache of second intance
pause
exec dbms_shared_pool.keep ('RAC_CACHE_NOORDER','Q');
pause
exec dbms_shared_pool.keep ('RAC_CACHE_ORDER','Q');
rem see them in the object cache of the second instance
pause
select inst_id,name,type,kept 
from gv$db_object_cache
where name like '%RAC_CACHE%' 
and type = 'SEQUENCE'
order by 1,2;
rem now get next value from the nocache 
rem noorder sequence in the second instance
pause
select rac_nocache_noorder.nextval
from dual;
rem now check the SQ enqueues
pause
select *
from gv$enqueue_stat
where eq_type = 'SQ'
order by inst_id;
rem now see the SV resources owned by LCK0 currently
rem in both instances
pause
select l.inst_id,l.pid,p.program,l.resource_name2,
l.owner_node,l.state,l.blocked,l.blocker
from gv$ges_enqueue l, gv$process p
where resource_name2 like '%SV%'
and p.spid = l.pid
order by 1;
rem now get next value from the nocache 
rem ordered sequence in the second instance
pause
select rac_nocache_order.nextval
from dual;
rem now check the SQ enqueues
pause
select *
from gv$enqueue_stat
where eq_type = 'SQ'
order by inst_id;
rem now see the SV resources owned by LCK0 currently
rem in both instances
pause
select l.inst_id,l.pid,p.program,l.resource_name2,
l.owner_node,l.state,l.blocked,l.blocker
from gv$ges_enqueue l, gv$process p
where resource_name2 like '%SV%'
and p.spid = l.pid
order by 1;
rem now get next value from the cached 
rem noorder sequence in the second instance
pause
select rac_cache_noorder.nextval
from dual;
rem now check the SQ enqueues
pause
select *
from gv$enqueue_stat
where eq_type = 'SQ'
order by inst_id;
rem now see the SV resources owned by LCK0 currently
rem in both instances
pause
select l.inst_id,l.pid,p.program,l.resource_name2,
l.owner_node,l.state,l.blocked,l.blocker
from gv$ges_enqueue l, gv$process p
where resource_name2 like '%SV%'
and p.spid = l.pid
order by 1;
rem now get next value from the cached 
rem ordered sequence in the second instance
pause
select rac_cache_order.nextval
from dual;
rem now check the SQ enqueues
pause
select *
from gv$enqueue_stat
where eq_type = 'SQ'
order by inst_id;
rem now see the SV resources owned by LCK0 currently
rem in both instances
pause
select l.inst_id,l.pid,p.program,l.resource_name2,
l.owner_node,l.state,l.blocked,l.blocker
from gv$ges_enqueue l, gv$process p
where resource_name2 like '%SV%'
and p.spid = l.pid
order by 1;
rem now check the dictionary again 
pause
select * 
from dba_sequences
where sequence_name like '%RAC_%ORDER';
rem see them in the row caches metadata again
pause
select inst_id,sequence_name,order_flag,
nextvalue,cache_size
from gv$_sequences
where sequence_name like '%RAC_%ORDER'
order by inst_id,sequence_name;
rem now check for any increase in SV LOCK waits
pause
BREAK ON INST_ID SKIP 1
SELECT INST_ID,SESSION_ID, CHR(BITAND(P1,-16777216)/16777215)||
       CHR(BITAND(P1, 16711680)/65535) "LOCK",
       TO_CHAR(BITAND(P1, 65536)) "MODE",
       P2, P3,TIME_WAITED
FROM GV$ACTIVE_SESSION_HISTORY
WHERE EVENT = 'DFS lock handle'
AND CHR(BITAND(P1,-16777216)/16777215)||
    CHR(BITAND(P1, 16711680)/65535) = 'SV'
ORDER BY 1,3;
PAUSE CHECK AWR FOR HISTORICAL SV LOCL WAITS
BREAK on INSTANCE_NUMBER SKIP 1
SELECT INSTANCE_NUMBER,SESSION_ID, CHR(BITAND(P1,-16777216)/16777215)||
       CHR(BITAND(P1, 16711680)/65535) "LOCK",
       TO_CHAR(BITAND(P1, 65536)) "MODE",
       P2, P3,TIME_WAITED
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE EVENT = 'DFS lock handle'
AND CHR(BITAND(P1,-16777216)/16777215)||
    CHR(BITAND(P1, 16711680)/65535) = 'SV'
ORDER BY 1,3;
rem now connect back to first instance
pause
connect sys/oracle@nleduc1_angel1 as sysdba
rem now get next value from the nocache 
rem noorder sequence in the first instance
pause
select rac_nocache_noorder.nextval
from dual;
rem now check the SQ enqueues
pause
select *
from gv$enqueue_stat
where eq_type = 'SQ'
order by inst_id;
rem now see the SV resources owned by LCK0 currently
rem in both instances
pause
select l.inst_id,l.pid,p.program,l.resource_name2,
l.owner_node,l.state,l.blocked,l.blocker
from gv$ges_enqueue l, gv$process p
where resource_name2 like '%SV%'
and p.spid = l.pid
order by 1;
rem now get next value from the nocache 
rem ordered sequence in the first instance
pause
select rac_nocache_order.nextval
from dual;
rem now check the SQ enqueues
pause
select *
from gv$enqueue_stat
where eq_type = 'SQ'
order by inst_id;
rem now see the SV resources owned by LCK0 currently
rem in both instances
pause
select l.inst_id,l.pid,p.program,l.resource_name2,
l.owner_node,l.state,l.blocked,l.blocker
from gv$ges_enqueue l, gv$process p
where resource_name2 like '%SV%'
and p.spid = l.pid
order by 1;
rem now get next value from the cached 
rem noorder sequence in the first instance
pause
select rac_cache_noorder.nextval
from dual;
rem now check the SQ enqueues
pause
select *
from gv$enqueue_stat
where eq_type = 'SQ'
order by inst_id;
rem now see the SV resources owned by LCK0 currently
rem in both instances
pause
select l.inst_id,l.pid,p.program,l.resource_name2,
l.owner_node,l.state,l.blocked,l.blocker
from gv$ges_enqueue l, gv$process p
where resource_name2 like '%SV%'
and p.spid = l.pid
order by 1;
rem now get next value from the cached 
rem ordered sequence in the first instance
pause
select rac_cache_order.nextval
from dual;
rem now check the SQ enqueues
pause
select *
from gv$enqueue_stat
where eq_type = 'SQ'
order by inst_id;
rem now see the SV resources owned by LCK0 currently
rem in both instances
pause
select l.inst_id,l.pid,p.program,l.resource_name2,
l.owner_node,l.state,l.blocked,l.blocker
from gv$ges_enqueue l, gv$process p
where resource_name2 like '%SV%'
and p.spid = l.pid
order by 1;
rem now check the dictionary again 
pause
select * 
from dba_sequences
where sequence_name like '%RAC_%ORDER';
rem see them in the row caches metadata again
pause
select inst_id,sequence_name,order_flag,
nextvalue,cache_size
from gv$_sequences
where sequence_name like '%RAC_%ORDER'
order by inst_id,sequence_name;
rem now check for any increase in SV LOCK waits
pause
BREAK ON INST_ID SKIP 1
SELECT INST_ID,SESSION_ID, CHR(BITAND(P1,-16777216)/16777215)||
       CHR(BITAND(P1, 16711680)/65535) "LOCK",
       TO_CHAR(BITAND(P1, 65536)) "MODE",
       P2, P3,TIME_WAITED
FROM GV$ACTIVE_SESSION_HISTORY
WHERE EVENT = 'DFS lock handle'
AND CHR(BITAND(P1,-16777216)/16777215)||
    CHR(BITAND(P1, 16711680)/65535) = 'SV'
ORDER BY 1,3;
PAUSE CHECK AWR FOR HISTORICAL SV LOCL WAITS
BREAK on INSTANCE_NUMBER SKIP 1
SELECT INSTANCE_NUMBER,SESSION_ID, CHR(BITAND(P1,-16777216)/16777215)||
       CHR(BITAND(P1, 16711680)/65535) "LOCK",
       TO_CHAR(BITAND(P1, 65536)) "MODE",
       P2, P3,TIME_WAITED
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE EVENT = 'DFS lock handle'
AND CHR(BITAND(P1,-16777216)/16777215)||
    CHR(BITAND(P1, 16711680)/65535) = 'SV'
ORDER BY 1,3;
rem clean up
pause
drop sequence rac_nocache_noorder;
drop sequence rac_nocache_order;
drop sequence rac_cache_noorder;
drop sequence rac_cache_order;