Oracle Parallel Server (OPS)

Oracle Parallel Server (OPS)
--------------------------
Http://doc.linuxpk.com/49010.html
This article describes the concept of Oracle Parallel Server in question and answer mode.

1. What is Ops?

OPS (Oracle Parallel Server) allows multiple instances in different systems to access the same database at the same time. Parallel servers can effectively improve system availability and access performance to multiple systems. However, if your data is not well divided, the performance may decline.

When ops is installed, multiple instances mount the same database file. The communication between instances is managed by the distributed lock manager (DLM. Note that the distributed lock manager is closely related to the hardware you use and the * Operating System. To identify multiple instances that attempt to modify the same data at the same time, Oracle uses ten background processes: LCK0-LCK9 to lock the resources used by an instance.

Ops is mainly used in Unix/Linux cluster environments.

2. Ops advantages

1) High Availability

2) Faster Transaction Response Time-can be used in Decision Support Systems

3) increase the number of transaction connections-available for online transaction processing systems

3. Are all applications applicable to Ops?

Apps that can be split by function or data are the most suitable for OPS. Ops is not suitable for applications with "hot data" (data frequently accessed by multiple instances at the same time.

4. Does ops need special hardware?

OPS requires servers to interconnect and share disk subsystems. All systems that can be made into clusters can be used, commonly used Unix/Linux and NT.

5. How to Set ops?

1) shut down the database

2) Enable the ops option and reconnect to the Oracle software in UNIX.

3) make the Oracle software valid on all nodes, and you can copy the software to other nodes or share the disk.

4) each instance must have its own redo log file, so add the necessary log files:

Alter database add logfile thread 2

Group G4 ('raw _ file1') size 500 K,

Group G5 ('raw _ file2') size 500 K,

Group G6 ('raw _ file3') size 500 K;

Alter database enable public thread 2;

5) each instance must have its own rollback segment, so add the necessary rollback segments:

Create rollback segment Rb2 tablespace RBS;

6) edit the initsid. ora file of the initialization parameter file and add the following items:

Parallel_server = true

Instance_number = 1

Thread = 1

Rollback_segments = (R01, r02, r03, r04)

7) Create the data dictionary required by OPS, that is, run catparr. SQL.

8) Start the instance on all nodes.

6. How can I determine whether a database is running in parallel?

Show parameter parallel_server

7. How do I track activity instances?

Select * From SYS. V _ $ active_instances;

Select * From SYS. V _ $ thread;

8. How to determine how many PCM locks are used for each 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

/

9. How can I check the allocated PCM locks and Ping rates for each data file?

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 then comment ''then fill L. 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

/

10. What is pinging?

Pinging is a process used to coordinate multiple instances to read and write the same data block. One challenge for OPS performance optimization is to minimize pinging.

11. How can I monitor the activity of PCM locks?

View the total number of PCM locks of the current instance: Select * From SYS. V $ lock_activity;

View the PCM lock activity status of each database object:

Col table format A40

Select File #, kind partition comment '''partition & brvbarusername partition comment'. 'partition & brvbarname "table", sum (xnc) pings

From SYS. V $ false_ping P, SYS. dba_users u

Where U. user_id = P. Owner #

Group by file #, kind used comment '''sale & brvbarusername used comment '. 'sale & brvbarname, xnc

Order by xnc DESC

/

12. How to set an SQL * net connection string for all OPS instances?

1) First, it is required that the Sid on all nodes be the same. If they are different, you can change the SID as follows:

Close all instances of the database

Set oracle_sid environment variables to the same

Copy the original initialization file initoldsid. ora to initcommon. ora.

Restart all instances

2) edit the local tnsnames. ora, as shown in the following example:

Phoenix =

(Description =

(Address_list =

(Address = (Protocol = TCP) (host = 10.1.1.50) (Port = 1521 ))

(Address = (Protocol = TCP) (host = 10.1.1.51) (Port = 1521 ))

)

(CONNECT_DATA =

(SERVICE_NAME = ora8)

)

)

PS:

Set parallel queries to improve CPU utilization:

Http://www.oracle.com.cn/archiver? Tid-120936.html
-------------------------
Parallel query is the most commonly used of Oracle's parallel execution features. it was the first parallel execution feature to be developed by Oracle and was introduced in Oracle release 7.1 as the Oracle Parallel query option (pqo ). parallel Execution can significantly reduce the elapsed time for large queries, But it doesn' t apply to every query.

To parallelize a SELECT statement, the following conditions must be met:

1. At least one of the tables is accessed through a full table scan, or an index is accessed through a range scan involving multiple partitions.

2. If the execution involves a full table scan, the statement must contain a parallel hint specifying the corresponding table, or the corresponding table must have a parallel Declaration in its definition.

3. If the execution involves an index range scan spanning multiple partitions, the statement must contain a specified hint specifying the corresponding index, or the corresponding index must have a parallel Declaration in its definition.

The following two sections explain how the degree of parallelism is chosen for a select statement and discuss restrictions on the use of the parallel query feature.

Setting the degree of Parallelism

Once Oracle decides to execute a SELECT statement in parallel, the degree of parallelism is determined by following precedence rules:

Oracle retrieves the degree and instances specifications from the definition of all tables and indexes involved in the query and chooses the highest values found for those settings.

Oracle checks the statement for a parallel hint. If such a hint is found, the hint overrides the degree of parallelism obtained as a result of the previous step.

You can use the parallel and parallel_index hints to specify the degree of parallelism for a select statement. You can use the noparallel and noparallel_index hints to ensure that parallel execution is not completed MED.

Example

Alter table emp parallel (degree 4 );
Select degree from user_tables where table_name = 'emp ';
Select count (*) from EMP;
Alter table emp noparallel;

Select/* + parallel (EMP, 4) */count (*)
From EMP;