Oracle Parallel Server Option FAQ
$Date: 01-Dec-1998 $
$Revision: 2.01 $
$Author: Frank Naudé $
Topics
Back to Oracle FAQ Index
What is the Oracle Parallel Server Option?
A normal Oracle installation consists of a single Oracle instance that
access a database on the same computer system.
With the parallel server option (PSO), multiple instances on different
computer systems (nodes) can access the same database files simultaneously.
The OPS option is mainly used for high availability reasons.
The parallel server can provide improved performance by using CPU resources
across multiple computer systems.
However, performance can be worse if your data is not partitioned correctly.
In Oracle Parallel Server installations, multiple
instances mount the same database files. Communication
between instances are managed by the Distributed Lock
Manager (DLM). Note that the DLM is hardware and operating system dependent.
To address the possibility of two or more instances attempting to
modify the same information simultaneously, Oracle uses up to ten additional
background processes, named LCK0 through LCK9, to lock a resource in use by
an instance.
The PSO is normally used on DEC VAX Clusters, UNIX Clusters, IBM's MVS
SYSPLEX etc.
Back to top of file
What are the benefits for running in Parallel Server mode?
Running Oracle in parallel mode can benefit you in the following ways:
- High availability and limited failover support from Oracle8.
- Speedup (increase transaction response time)
- can be achieved for DSS applications.
- Scaleup (increase the transaction volume)
- can be achieved for OLTP applications.
Back to top of file
Are all applications suited for the Parallel Server?
Applications that can be partitioned based on function or data
are perfect candidates for the Oracle Parallel Server.
Applications with 'hot' rows (the same row being accessed by processes
on different nodes) will not work well. This is because data will
constantly be moved (pinged) from one Oracle Instance to another.
Back to top of file
How does one setup an Oracle Parallel Server?
- Shut Down your Database:
SVRMGR> CONNECT INTERNAL
SVRMGR> SHUTDOWN NORMAL
- Enable the Parallel Server Option. On Unix this is done by relinking the Oracle software.
- Make the software available on all computer systems the Parallel Server will run on.
This can be done by copying the software to all systems or to a shared disk.
- Each instance requires its own set of Redo Log Files. Create additional log files:
SVRMGR> CONNECT INTERNAL
SVRMGR> STARTUP EXCLUSIVE
SVRMGR> ALTER DATABASE ADD LOGFILE THREAD 2
SVRMGR> GROUP G4 ('RAW_FILE1') SIZE 500k,
SVRMGR> GROUP G5 ('RAW_FILE2') SIZE 500k,
SVRMGR> GROUP G6 ('RAW_FILE3') SIZE 500k;
SVRMGR> ALTER DATABASE ENABLE PUBLIC THREAD 2;
- Each instance requires its own set of rollback segments. Add Rollback Segments for New Nodes:
SVRMGR> CREATE ROLLBACK SEGMENT RB2 TABLESPACE RBS;
- Edit the INIT.ORA files and number the instances 1, 2,...:
INSTANCE_NUMBER = 1
THREAD = 1
ROLLBACK_SEGMENTS = (r01, r02, r03, r04)
PARALLEL_SERVER = TRUE
# Include %T for the thread in the LOG_ARCHIVE_FORMAT string.
# Set LM_PROCS to the number of nodes * PROCESSES
# etc....
- Create the dictionary views needed for the PSO:
SVRMGR> START ?/RDBMS/ADMIN/CATPARR.SQL
- On all the computer systems, startup the instances:
SVRMGR> CONNECT INTERNAL;
SVRMGR> STARTUP PARALLEL; or STARTUP SHARED;
Back to top of file
How does one start an Oracle Parallel Server?
Do the following from each node:
SVRMGR> connect internal
SVRMGR> set retries 5
SVRMGR> startup parallel retry
You can also use the SET INSTANCE instanceN command to switch between instances (if defined in TNSNAMES.ORA).
Back to top of file
How can I test if a database is running is shared (parallel) mode?
begin
if dbms_utility.is_parallel_server then
dbms_output.put_line('Running in SHARED/PARALLEL mode.');
else
dbms_output.put_line('Running in EXCLUSIVE mode.');
end if;
end;
/
Back to top of file
How can I keep track of active instances?
You can keep track of active parallel server instances by executing one of the following queries:
SELECT * FROM SYS.V_$ACTIVE_INSTANCES;
SELECT * FROM SYS.V_$THREAD;
Back to top of file
How can one see how many PCM locks are allocated per instance?
select count(*) "Number of hashed PCM locks"
from v$lock_element where bitand(flags, 4) != 0
/
select count(*) "Number of fine grain PCM locks"
from v$lock_element where bitand(flags, 4) = 0
/
Back to top of file
How can one see how many PCM locks are allocated per data file?
The following statement shows how many PCM locks are allocated per file and what the ping rate on those files are:
col file_name format a29
col tablespace format a12
col blocking format 9999999
col nlocks format 99999
col start_lk format 9999999
select l.file_id || ' ' || l.file_name file_name,
l.ts_name "TABLESPACE",
start_lk, nlocks, blocking, frequency "PING COUNT"
from sys.file_ping p, sys.file_lock l
where l.file_id = p.file_id
order by l.file_id
/
Back to top of file
What is pinging and why is it so bad?
Pinging is the process whereby one Oracle Instance
requests another to write a set of blocks from its
SGA to disk so it can obtain it in exclusive mode.
To move a data block from one instance's SGA to another is a slow process.
The challenge of tuning an Oracle Parallel Server is to minimize pinging.
Back to top of file
How does one monitor PCM lock activity?
Summary of PCM lock activity for the current instance:
select * from sys.v$lock_activity;
/
PCM lock activity per database object:
col table format a40
select file#, kind||' '||username||'.'||name "TABLE", sum(xnc) pings
from sys.v$false_ping p, sys.dba_users u
where u.user_id = p.owner#
group by file#, kind||' '||username||'.'||name, xnc
order by xnc desc
/
Back to top of file
How does one setup a common SQL*Net connect string for all OPS instances?
Define all Oracle System Identifiers (SIDs) to be the same on all nodes of the system.
If you already have your SIDs defined, you can rename them by:
- Shutting down all instances of the database;
- Change the ORACLE_SID environment variable to the common name you have chosen;
- Copy the current initOLDSID.ora file to initCOMMON.ora;
- Restart the Oracle instances.
This is a sample TNSNAMES entry to connect to one of two instances in a parallel server:
rprd = (DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=node_1)
(PORT=1526)
)
(ADDRESS=
(PROTOCOL=tcp)
(HOST=node_2)
(PORT=1526)
)
)
(CONNECT_DATA=(SID=rprd))
)
Back to top of file
How does one trace DLM activity?
Look at this example:
SVRMGR> REM Dump Parallel Server DLM locks
SVRMGR> oradebug lkdebug -a convlock
SVRMGR> oradebug lkdebug -a convres
SBRMGR> oradebug lkdebug -r <resource handle> (i.e 0x8066d338 from convres dump)
Execute the "ORADEBUG HELP" command from svrmgrl for a list of valid commands.
See the Internals FAQ for more tracing events.
Back to top of file
Any other tips for using the Parallel Server?
- Run the ?/rdbms/admin/catparr.sql script regularly,
it creates a table called EXT_TO_OBJ which needs to be current.
- Re-create your tables with the FREELISTS and FREELIST GROUPS storage parameters.
- Know your DLM (Distributed Lock Manager) and its capacity limits.
- Use a blocking factor (!n) when setting up the GC_FILES_TO_LOCKS parameter.
This will allocate n contiguous new blocks to a specific instance (and FREELIST GROUP) when table blocks are allocated.
- Choose the right mix of HASH LOCKS (gc_db_locks) and FINE GRAIN LOCKS (gc_releasable_locks).
Normally HASH locking is faster but FINE GRAIN locking scales better with multiple instances.
- On some systems you can only create raw devices on the master node. For example, on Sequent you need to issue "vxdctl -c mode" to get the master from where to allocate database files from.
Back to top of file