A
OPS Performance Monitoring for Oracle Parallel Server

Oracle Performance Manager is a powerful tool for monitoring database performance in real-time. It is integrated with Oracle's Enterprise Manager to display charts with tabular and graphical performance statistics for instances running in a parallel server. These charts are presented in Chapter 3, "Monitoring Oracle Parallel Server Performance".

Note:

This appendix provides information for performance monitoring on both the Oracle7 database described and the Oracle8 database described. Refer to the appropriate section.

Oracle8: Performance Monitoring

If you are using Enterprise Manager's Performance Manager to monitor an Oracle8 Parallel Server, you must run the ops_8mon.sql script from either the Enterprise Manager Console or from the Oracle8 Server.

Enterprise Manager Console

Follow these steps to run the ops_8mon.sql script from the Enterprise Manager Console:

From the ORACLE_HOME\sysman\admin directory, execute Server Manager:
```
C:\ORANT\SYSMAN\ADMIN> SVRMGR30
```

Connect to the database to be monitored, using the account that will be used to monitor the database with Performance Manager:
```
SVRMGR> connect system/system_password@ops_db
```

where ops_db is the alias for the monitored database to which you are connecting.

Run the ops_8mon.sql script as follows:
```
SVRMGR> @ops_8mon.sql
```

Oracle8 Server

Follow these steps to run the ops_8mon.sql script from the Oracle8 Server:

Connect to the database to be monitored as the "internal" user by typing the following:
```
connect internal
```

Run the ops_8mon.sqlscript from the ORACLE_HOME/opsm/admin directory as follows:
```
@?/opsm/admin/ops_8mon.sql
```

Oracle7: Performance Monitoring

If you are using Enterprise Manager Performance Manager to monitor an Oracle7 Parallel Server, you must first install the PL/SQL package on your parallel server.

PL/SQL Package Contents

The Enterprise Manager Performance Manager PL/SQL package consists of the following three components:

A set of PL/SQL scripts (procedures) that are executed by Performance Manager to aggregate results from all parallel server instances.
A set of tables used to store the aggregated results.
A database link for each parallel server instance.

The PL/SQL procedures are contained in ops_pack.sql, the tables are created by ops_ctab.sql, and the database links are created by ops_dbl.sql, which must be configured to match your parallel server configuration.

Once the above components have been installed, you can start Performance Manager against any parallel server instance. Performance Manager executes a
PL/SQL procedure on the instance to query for performance data from all parallel server instances via database links, aggregating the results into a set of database tables, which are then displayed in graphical charts by Performance Manager.

Performance Manager Requirements

The following is a list of requirements to properly run Performance Manager on Oracle7:

Since the PL/SQL package uses database links to get results from all parallel server instances, the init.ora parameter, open_links, should be increased by the number of instances in the parallel server.
Each instance must have a TNS service name defined for it, which is used to define the database links for the instances. If Oracle Names is not being used, the tnsnames.ora containing these service names must be installed on each node or in a single tnsnames.ora file shared among all nodes.
The SIDs (instance identifiers) of the instances in the parallel server must be different, otherwise it will not be possible to distinguish among them in the Performance Manager charts.

Note:

The Enterprise Manager's Intelligent Agent is not required by the Performance Manager, as all of its functions are performed using a database connection to the parallel server.

Installing Oracle7 Performance Monitoring Scripts

You can install Oracle7 monitoring scripts from either of these locations:

(Recommended) Enterprise Manager Console's ORACLE_HOME\sysman\admin
Oracle7 Server

Enterprise Manager Console

The SQL*Plus scripts are located in the following location from the Enterprise Manager Console:

ORACLE_HOME\sysman\admin

Follow these steps to install the PL/SQL package on an Oracle7 database:

From the ORACLE_HOME\sysman\admin directory, ensure that all parallel server instances are running.

Connect to the database to be monitored using SQL*Plus as the user, "sys". Run the SQL*Plus script, ops_gdl.sql, against the parallel server. This creates the database links creation script named ops_dbl.sql.
```
C:\ORANT\SYSMAN\ADMIN> sqlplus sys/sys_password@ops_db
SQL> set linesize 250 
SQL> @ops_gdl.sql 
```

where ops_db is the alias for the monitored database to which you are connecting.

Run the ops_mon.sql script against the parallel server while still connected as the user "sys":
```
SQL> @ops_mon.sql
```

Oracle7 Server

If you are installing from the Oracle7 Server, the pathname is ORACLE_HOME/rdbms/admin. Create the ops_dbl.sql script file using this format for each instance's entry as follows:

create public database link db_name@node_name_SID using 'connect_string';

where db_name is the database name from V$DATABASE, node_name is the hostname of the node on which the instance runs, SID is the ORACLE_SID of the instance, and connect_string is the SQL*Net connect string for the instance.

The ops_dbl.sql file is a script that creates database links across instances. These links are necessary for Performance Manager to run. You must have one database link per parallel server instance. See "Example of ops_dbl.sql" below.

Choose from either of these two methods to create the ops_dbl.sql script on Oracle7:
1. If your platform includes the opsctl utility, use it to generate the database link file by running the following command:
```
opsctl config -l
```
  The ORACLE_HOME/ops/ops_dbl.sql is created. Move the ops_dbl.sql file to ORACLE_HOME/rdbms/admin.
2. Or, the SQL*Plus script, ops_gdl.sql, can be run against the parallel server to create these database links. Ensure that all parallel server instances are running, change the directory to ORACLE_HOME/rdbms/admin, and execute SQL*Plus as follows:
```
sqlplus sys/sys_password 
SQL> set linesize 250 

SQL> @ops_gdl.sql
```

Run the ops_mon.sql script while still connected as the user "sys":
```
SQL> @ops_mon.sql 
```

Example of ops_dbl.sql

Rem -- This is an example ops_dbl.sql file for a database named "fin" 
        with 4 instances
Rem --  "fin1" running on node "spdev13"
Rem --  "fin2" running on node "spdev14"
Rem --  "fin3" running on node "spdev15"
Rem --  "fin4" running on node "spdev16"

drop public database link fin@spdev13_fin1
/
create public database link fin@spdev13_fin1 using 'fin1'
/
drop public database link fin@spdev14_fin2
/
create public database link fin@spdev14_fin2 using 'fin2'
/
drop public database link fin@spdev15_fin3
/
create public database link fin@spdev15_fin3 using 'fin3'
/
drop public database link fin@spdev16_fin4
/
create public database link fin@spdev16_fin4 using 'fin4'

Troubleshooting Oracle7 Performance Manager

If the Performance Manager displays the "No Data Available" message, there are several items to check on the monitored parallel server. Invoke Server Manager and connect to the first instance of the parallel server. Log on with the same database username as Performance Manager. For example,

connect user/password@fin1

First, ensure that the database links can be used to connect to remote instances. Choose an instance other than the one to which you are connected (we will use instance "fin4" from the example ops_dbl.sql file, and run a simple query using its database link. For example,

select * from dual@fin@spdev16_fin4;

If this query fails, check that the service name used ("fin4") is valid on the node which runs the instance ("fin1") by logging into the node ("spdev13") and connecting via the service name ("fin4"). Check whether the listener serving the instance ("fin4") is up. Ensure that the init.ora parameters are set properly on instance "fin1" (see "Requirements" above).

Ensure that the database links as created use the proper database name, host names, and instance names. The database name is given by:

select name from v$database
/

The host names are given by:

select substr(inst_name, 0, instr(inst_name, ':') -1) from v$active_instances
/

The instance names are given by:

select substr(inst_name,instr(inst_name,':') + 1) from v$active_instances
/

If the database links work properly, try running the PL/SQL procedure used by Performance Manager to fill the aggregation table O$FILEIO:

connect user/password
truncate table sys.o$fileio
/
begin sys.ops_monitor.fileio; end;
/
select * from sys.o$fileio
/

If rows are retrieved by the final query, check that there are no o$* tables owned by the database account used by Performance Manager, which hide the synonyms:

connect user/password
truncate table o$fileio
/
begin ops_monitor.fileio; end;
/
select * from o$fileio
/

If the last query returns no rows but did return rows when the sys. prefix was appended, then the PL/SQL objects and o$ tables have been incorrectly installed under the database account used by Performance Manager, rather than under the "internal" user. Correct this by typing the following:

connect user/password
drop package ops_monitor
/
drop table o$fileio
/
drop table o$ping
/
drop table o$lockact
/
drop table o$session  
/
drop table o$license
/

Then, if necessary, reinstall the package under the "internal" account as follows:

connect internal
@?/rdbms/admin/ops_mon.sql

A OPS Performance Monitoring for Oracle Parallel Server

Oracle8: Performance Monitoring

Enterprise Manager Console

Oracle8 Server

Oracle7: Performance Monitoring

PL/SQL Package Contents

Performance Manager Requirements

Installing Oracle7 Performance Monitoring Scripts

Enterprise Manager Console

Oracle7 Server

Example of ops_dbl.sql

Troubleshooting Oracle7 Performance Manager

A
OPS Performance Monitoring for Oracle Parallel Server