Category Archives: Architecture

Monitor_undo_tablespace

select sum(decode(status,’EXPIRED’,bytes,0))/sum(bytes) EXPIRED_PERCENT,
sum(decode(status,’UNEXPIRED’,bytes,0))/sum(bytes) UNEXPIRED_PERCENT,
sum(decode(status,’ACTIVE’,bytes,0))/sum(bytes) ACTIVE_PERCENT
from dba_undo_extents;

 

select t.start_time, t.used_ublk, s.username, r.segment_name
from v$transaction t,v$session s, dba_rollback_segs r
where t.ses_addr= s.saddr and t.xidusn=r.segment_id;

calculate optimum undo retention:

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’

–Undo advisor

DECLARE
tid NUMBER;
tname VARCHAR2(30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK(‘Undo Advisor’, tid, tname, ‘Undo Advisor Task’);
DBMS_ADVISOR.CREATE_OBJECT(tname, ‘UNDO_TBS’, null, null, null, ‘null’, oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, ‘TARGET_OBJECTS’, oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, ‘START_SNAPSHOT’, 1);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, ‘END_SNAPSHOT’, 2);
DBMS_ADVISOR.execute_task(tname);
end;

–Monitor Undo of a user session–

select ses.username
, substr(ses.program, 1, 19) command
, tra.used_ublk
, from v$session ses
, v$transaction tra
where ses.saddr = tra.ses_addr;

Parallel Execution on the same node where it started in RAC

Parallel Execution on the same node where it started in RAC


By default, in an Oracle RAC environment, a SQL statement executed in parallel can run across all of the nodes in the cluster. For this cross-node or inter-node parallel execution to perform, the interconnection in the Oracle RAC environment must be size appropriately because inter-node parallel execution may result in a lot of interconnect traffic. If the interconnection has a considerably lower bandwidth in comparison to the I/O bandwidth from the server to the storage subsystem, it may be better to restrict the parallel execution to a single node or to a limited number of nodes. Inter-node parallel execution does not scale with an undersized interconnection.

10g:
Utilize the parameters instance_group and parallel_instance_group to limit this execution to particular node.
ex.,
Instance#1 parameter:
instance_groups=’pqgrp1′,’pqallnodes’;
Instance#2 parameter:
instance_groups=’pqgrp2′,’pqallnodes’;
Now, While running the batch process or any program that needs to be executed in only one instance then:
alter session set parallel_instance_group = ‘pqgrp1‘; — This will make the following program executions to be used only Instance#1.
11g on-wards:
To limit inter-node parallel execution, you can control parallel execution in an Oracle RAC environment using the PARALLEL_FORCE_LOCAL initialization parameter. By setting this parameter to TRUE, the parallel server processes can only execute on the same Oracle RAC node where the SQL statement was started.