Index   Search   Add FAQ   Ask Question  

Oracle Parallel Server Option FAQ

$Date: 01-Dec-1998 $
$Revision: 2.01 $
$Author: Frank Naudé $

Topics

  • What is the Oracle Parallel Server Option?
  • What are the benefits for running in Parallel Server mode?
  • Are all applications suited for the Parallel Server?
  • How does one setup an Oracle Parallel Server?
  • How does one start an Oracle Parallel Server?
  • How can I test if a database is running is shared (parallel) mode?
  • How can I keep track of active instances?
  • How can one see how many PCM locks are allocated per instance?
  • How can one see how many PCM locks are allocated per data file?
  • What is pinging and why is it so bad?
  • How does one monitor PCM lock activity?
  • How does one setup a common SQL*Net connect string for all OPS instances?
  • How does one trace DLM activity?
  • Any other tips for using the Parallel Server?

  • 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:
  • 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?

    1. Shut Down your Database:
          SVRMGR> CONNECT INTERNAL
          SVRMGR> SHUTDOWN NORMAL
      
    2. Enable the Parallel Server Option. On Unix this is done by relinking the Oracle software.

    3. 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.

    4. 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;
    5. Each instance requires its own set of rollback segments. Add Rollback Segments for New Nodes:
          SVRMGR> CREATE ROLLBACK SEGMENT RB2 TABLESPACE RBS;
      
    6. 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....
      
    7. Create the dictionary views needed for the PSO:
          SVRMGR> START ?/RDBMS/ADMIN/CATPARR.SQL
      
    8. 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:

    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?

  • Back to top of file

  • General: Home | Index | Preamble | Glossary | OraCorp | Papers | Fun | News | Events | Y2000 | Books | Links | Forums
    Products: SQL | Plus | Loader | PL/SQL | PreComp | OPO | OMO | OO4OLE | DBA | PQO | PSO | OCO | Net | ODBC | WebServer | Des2k | Dev2k
    Systems: MVS | Unix | Windows | WindowsNT | NetWare | VMS