During the era of Oracle7 back in 1998, I wrote an article about how to build a performance monitoring tool. This article, titled ‘Do It Yourself: Implement Performance Monitoring Tool - Eagle’, was published in Select magazine. This article received several kudos from fellow DBAs.
Although this article may not be as relavent as it was 10 years ago, it still demonstrate a concept that can be applied to solve real-life business problems. Eagle introduces a monitoring framework that can be easily extended and fully customized. Some of the key features of Eagle are: Multiple database monitoring from control center database, trend analysis, implemented using PL/SQL and Unix shell, easy to implement and use, customizable and extensible to meet your needs. Source code and procedures to implement Eagle are included here.
-----------------------------------------------------------------------------------------------
Do It Yourself
Implement Performance Monitoring Tool
Keval is a database administrator for a financial company in New York. One day, John queried dynamic performance views to calculate database buffer hit ratio for a Sales database. John found that database buffer hit ratio was 65% which was low and not acceptable. He knew that database buffer hit ratio was 90% just a week ago. He thought this drop in the buffer hit ratio might be due to some spike activities. But he wasn’t sure. John decided to watch database buffer hit ratio everyday for at least a week before taking any corrective actions. John also decided to watch library cache hit ratio, dictionary cache hit ratio and sort area hit ratio to identify memory parameters tuning requirements, if any. John needed a performance monitoring tool that could collect database performance statistics in an automated fashion.
Rupa, a database administrator for a communication company in Fairfax, VA, was informed by her human resources department that they were going to make a heavy use of HR database as company was going through a merger. As she was running low on free space in HR database, she decided to watch % free space and object extensions more closely along with other performance parameters. Rupa needed an automated database monitoring procedures to do this.
Kunal is a database administrator for a consulting firm in Chicago. His company decided to form a centralized DBA service to support all six US offices from a single site. This re-engineering move placed a heavy burden on Mark and his team, as there were 50+ Oracle databases in US six offices. Mark found it very time consuming to log into every system at all six US offices, and examine the health of all Oracle databases he was responsible for. Mark needed an automated database monitoring tool that can reach out to all six sites from a central site, and collect information pertaining to the health of all databases.
Like Keval, Kunal or Rupa, if you are looking for a database monitoring tool, then Eagle may be of interest to you. In fact, I was facing the same challenge as many of us do: monitor multiple databases proactively and efficiently. I evaluated few commercially available DBA tools. Some of them were good but too expensive while some of them didn’t do what I wanted. Finally, I decided to design and implement a tool, Eagle, for database monitoring and administration. I started using Eagle for day-to-day dba chorus and found it very useful. I passed Eagle to couple of my DBA friends, and they also found it very useful. That’s when I decided to write an article about Eagle. I feel that Eagle will also be very useful to all of you. I will be using the name Eagle throughout this article to refer to this performance monitoring tool.
Eagle is a tool designed by the DBA for the DBA to monitor Oracle databases. It falls into do-it-yourself category. With the help of Eagle, you can monitor critical performance tuning parameters like database buffer hit ratio, library cache hit ratio, dictionary cache hit ratio or sort area hit ratio, and space management parameters like % free space and object extension. Eagle can also be customized and extended easily to meet ever-changing requirements.
Now let’s look at the results produced by Eagle. Eagle's output report is shown in Figure 1. Watch Name is the name of the watch for the database. Parameter column is used to provide an additional information for the watch. The rest of the columns represent values of the watch 4 weeks ago, 3 weeks ago, 2 weeks ago, 1 week ago, 6 days ago, 5 days ago, and so on. Today column shows today's value for the watch. Change column represents the change in value of the watch in last 4 weeks.
For example, Database buffer hit ratio in the Sales database was 98% 4 weeks ago, 95% 3 weeks age, 95% 2 weeks age, 90% 1 week ago, and 65% today. The drop of 33%, as shown in change column of Figure 1, is the difference between the values of database buffer hit ratio in 4 weeks. By the same token, you can also examine the trend in the values of library cache hit ratio, dictionary cache hit ratio and sort area hit ratio. % Free Space and Object Extension watches help you analyze database space usage pattern.
Overview
Eagle is based on the concept of watch. Watches are used extensively by programmers to debug code or to evaluate expressions at runtime. In Eagle’s terminology, a watch is used to monitor a parameter of importance. A parameter could be a database buffer hit ratio or library cache hit ratio or % free space. Watches are run against databases being monitored. Eagle lets you monitor watches over a period of time. Not only Eagle collects monitoring data, but also displays trend in the value of a parameter. Here are some of the features of Eagle:
Control Center Database: Eagle uses a control center database to store monitoring information and database statistics of all the databases that are being monitored. You can monitor multiple databases from a single site. Control center database can be an independent Oracle database, or it can be a part of any database. Advantage of having an independent control center database for Eagle is an isolation of monitoring information and database statistics from your production data. Control center database provides a centralized monitoring capability.
Multiple Databases: In today's world, most of the DBAs are called upon to support, monitor and tune more than one Oracle databases. Eagle can help you monitor multiple databases simultaneously. Databases being monitored by Eagle can be on different platforms (e.g. UNIX, NT, Open VMS) at different locations (New York, Washington, San Francisco). There is no limit on number of monitored databases in Eagle. It’s very easy to add new databases for monitoring in Eagle.
History of Watches: Eagle lets you monitor a watch over a period of time. It is very important to collect sufficient data before a conclusion can be drawn as to the behavior of a parameter. You need to distinguish between a drop in the value of a critical parameter due to an unusual activity and a drop in the value of a critical parameter due to a routine activity against your database. Eagle’s output report shows a watch for last 28 days, and you can determine a trend in the value of a watch.
Standard Watches: Eagle provides quite a few standard watches. Standard watches, described below, include categories like memory tuning to I/O tuning, auditing to space management. All you have to do is activate watches for the database that you wish to monitor. Note that the same watch can be used to monitor more than one databases.
Extensibility: Eagle is fully extensible. If you are not satisfied with standard watches available in Eagle, you can add watches of your own. If you want to add a watch to monitor, for example, rollback segment contention, you can do so easily. You define a watch, activate the watch, and you are ready to go. Watches in Eagle are defined in SQL language. This is one of the advantages of Eagle. We all are familiar with SQL, and have used SQL scripts extensively to manage our day-to-day chorus of database administration.
Customization of Watches: It’s likely that features available in a tool may not meet your tuning and monitoring requirements. You may need to customize performance monitoring tool to accommodate conflicting tuning needs of your databases. For example, OLTP database may have differing tuning requirements than OLAP database. It's very easy to customize standard and/or user defined watches in Eagle. Just modify the criteria of the watch.
Standard Watches
To gain more insight, let’s go through standard watches, and how you can use them to tune your database.
Database Buffer Hit Ratio: Database buffer hit ratio shows how effective your database buffers in memory are to satisfy database block access requests. If a database block is not in buffers, then Oracle must perform disk I/O to access this database block, and disk I/O is always more expensive than memory access. This ratio is calculated using the formulae (consistent gets + db block gets) / (consistent gets + db block gets + physical reads). Dynamic performance view v$sysstat stores information about these parameters. You should try to keep database buffer ratio as high as possible. Increase db_block_buffers parameter of init.ora to achieve high database buffer hit ratio.
Dictionary Cache Hit Ratio: Dictionary cache hit ratio represents the percentage of dictionary cache information requests satisfied by dictionary cache in memory. This ratio is calculated using the formulae sum(gets) / (sum(gets) + sum(misses)). Values of sum(gets) and sum(misses) are obtained from v$rowcache dynamic performance view. If you find this ratio, for example, to be 90 or lower over the period of time, consider increasing shared_pool_size parameter of init.ora.
Library Cache Hit Ratio: Library cache hit ratio represents the percentage of library objects requests satisfied by library cache area of SGA. This ratio is calculated using the formulae sum(pins) / (sum(pins) + sum(reloads) ). Values of sums(pins) and sum(reloads) can be obtained from v$library cache dynamic performance view table. Consider increasing shared_pool_size parameter if you find library cache hit ratio in the low range consistently.
Sort Area Hit Ratio: Sort area hit ratio shows the percentage of sorts performed in memory. Sort area hit ratio is calculated by using the formulae sorts(memory) / (sorts(memory) + sorts(disk)). Values of sorts(memory) and sorts(disk) are obtained from v$sysstat dynamic performance view. Sort area hit ratio can vary from 100 to 0 where value of 100 is the ideal situation. If you find sort area hit ratio consistently low, you should consider increasing sort_area_size parameter of init.ora.
% Free Space Alert: One of the daily DBA chores is to ensure that there is enough free space in your database for growth. % free space alert warns you whenever % free space in tablespace drops below 10% of the total space or if there is change of 5% in free space in last 28 days. % free space alert derives its information from data dictionary.
Object Extension Alert: One of the other DBA chores is to watch for object extension. If an object tries to grow beyond maximum extent, you will get “Maximum number of extents reached” error. Object extension alert will alert you about the rate at which object is growing. You can also identify “hot” objects in your database, and take appropriate actions. Even though the later version of Oracle will allow unlimited number of extents, it’s good to identify “hot” objects.
Design
Entity relationship diagram for Eagle is shown in Figure 2, and DDL for Eagle is shown in the script eddl.sql. There are four entities in Eagle: Database, Watch, Database Watch and Database Watch Result.
Information about databases that are going to be monitored is stored in database entity. This entity contains three attributes: database id, name and a database link name. Database id and name attributes of this entity are the id and the name of the database. Database link name is the name of the database link from control center database to the database we want to monitor. You need to add one row in this entity for each database you wish to monitor. As database that is being monitored is queried via database link, you need to create a database link via SQL command. At run time, value of the database link is substituted in the database link parameter of the watch criteria. This allows us to use the same watch for multiple databases.
Watch entity contains watch id, name and watch criteria. Watch criteria is written in SQL language. SQL statement is broken into three parts for simplicity and manageability: insert clause, select clause and where clause. As names imply, Insert clause contains an insert part of the SQL statement while select clause contains select part of the SQL statement and where clause contains where clause of SQL statement, as shown in the script estdwtch.sql.
To simplify SQL programming, you can use prepare1 clause, prepare2 clause, close1 clause and close2 clause attributes of watch entity. One example would be to store SQL statements that create views, and refer them in select clause. One of the standard watch that monitors % free space creates two views: free space view and data file view. Both of these views are referred in subsequent select clause. Since these views are created for temporary purposes, SQL statements to drop them are stored in close1 clause and close2 clause. Refer to estdwtch.sql script to see how watch entity and its components are used to build a watch criteria.
Database watch entity contains information about the watches that are used for a database. It allows you to mix and match databases and watches for monitoring. You can use the same watch for more than one database, and one database can have many watches. There are three attributes in database watch entity: watch id, database id and active yesno. You can turn the watch on by setting it to ‘Y’ and off by setting it to ‘N’.
Database watch result entity stores the monitoring information. There are five attributes in this entity: database id, watch id, run time, parameter and value. Database id and watch id attributes are self-explanatory. Run time attribute contains the time the watch was run. Parameter attribute is used to store optional information about monitoring data. For example, % free space watch uses parameter attribute to store tablespace name, and extent watch uses this attribute to store segment name. Value attribute stores the result or value of the watch.
Execution of Watches
A PL/SQL script, eplsql.sql, is at the heart of Eagle. This script is run at a pre-determined time everyday. First of all, it queries database watch entity for active database watches. A watch is active if active yesno attribute of database watch entity is set to ‘Y’. This script queries database entity to retrieve database link name for the database that is going to be watched. Then it queries watch entity to retrieve watch criteria. Database link name is substituted in the watch criteria. Before executing watch criteria, it executes watch prepare clauses if existent. For example, % free space watch has prepare clauses. Then, it executes watch criteria, and stores results in database watch result entity. Then Eagle performs cleanup, if any, by executing close clauses. The same process is repeated for all active watches.
Installation
If you like what you read so far, then let’s discuss a step-by-step procedure to install Eagle on your system. All scripts have been included in this article
Creating an Oracle Account: Eagle employs a control center database to store monitoring information. If you have a database exclusively for administration, you can use it for Eagle. If you plan to monitor quite a few databases, you might be better off creating a separate database just for Eagle. If you don't want to create a separate database for Eagle, then you can pick any of your database as an Eagle control center database. In any event, create an Oracle account for Eagle with dba privileges. You also need to grant two other privileges as shown in the script eaccount.sql.
Creating Objects, Standard Watches and PL/SQL procedure: Run DDL script, as shown in eddl.sql script, to create necessary tables and indexes. Run estdwtch.sql script to create Eagle’s standard watches. Create execute_db_watch PL/SQL stored procedure by executing a eplsql.sql script. All of these scripts should be run from Eagle Oracle account.
Creating Database Link: Create a database link from the control center database to the database that you wish to monitor, as shown below:
CREATE DATABASE LINK
CONNECT TO EAGLE IDENTIFIED BY
USING 'ConnectString
Note that you can use SQL*Net 1.X connect string or SQL*Net 2.X connect descriptor while creating a database link. If you want to use any Oracle account other than SYS in your database link create statement, then you need to grant appropriate privileges to that Oracle account so that database monitoring information can be selected. Eagle needs select privileges on V$ dynamic performance views and data dictionary views. If you want to monitor control center database itself, then you also need to create a database link to itself as database that is being monitored is queried via database link. Another scenario in which you need to create a database link to the same database is when you are using your production database for Eagle and you want to monitor your production database.
Adding Databases: The next step is to add rows in database table for the databases that are going to be monitored by Eagle. There are three columns in this table: database id, name and database link name. Use the following statement with appropriate values for your database.
INSERT INTO DATABASE (DB_ID, NAME, DB_LINK)
VALUES ('
','
','
');
Activating Watches: Use the following statement to add watches:
INSERT INTO DB_WATCH(DB_ID, WATCH_ID, ACTIVE_YN)
VALUES('
','WATCH_ID>','Y') ;
DB_ID and WATCH_ID columns in this table are foreign keys from database and watch tables, respectively. You can de-activate a watch for a database by setting active_yn column to ‘N’ for a corresponding row in db_watch table.
Running Watches: You should execute execute_db_watch stored procedure from SQL*Plus once everyday. You might want to run watches in the evening rather than in the morning so as to take into account daily activities. We have provided eagle.sh Unix shell script that executes EXECUTE_DB_WATCH stored procedure. You can submit this shell script via scheduling utilities like cron in Unix or you can execute this stored procedure via dbms_jobs package. Note that results of watches like Database Buffer Hit Ratio are based on statistics from V$ views, and hence reflect the values since the last database startup, not from the time you ran Eagle last time.
Generating Report: Run a SQL*Plus script, eoutput.sql, to generate an output report. The output produced by this script is shown in Figure 1. The script eagle.sh executes eoutput.sql script along with running execute_db_watch stored procedure. If you run execute_db_watch more than once a day, then you need to modify eoutput.sql script to take into account the script running frequency.
Customizing Watches: As shown in eddl.sql script, Watch criteria is divided into insert clause, select clause and where clause. If you need to modify watch criteria, just update the where clause of the watch. For example, as shown in estdwtch.sql script, the where clause of Extents watch contains extents > 1 clause. If you want to monitor objects with 10 or more extents, you just need to modify where clause to extents > 10. We have shown all standard watches in estdwtch.sql. Make sure that you use two single quotes for character string in the where clause.
Creating a Watch of Your Own: With some modifications, you may be able to import your SQL scripts into Eagle as watches, and use them over and over again to monitor databases. To wtire a watch of your own, you need to construct a SQL statement and store it in watch table. estdwtch.sql script gives you some examples on how to create a watch of your own. Note
in the watch criteria.
acts as a formal parameter, and is replaced by the database link name during runtime.
Summary
There are two aspects of performance monitoring process: in-depth knowledge of Oracle database and necessary statistics collected over the period of time. Eagle will help you do the later part. By using the sample scripts provided in this article, you should be able to automate performance statistics collection procedures. The purpose of this article is not to provide a shrink-wrapped performance monitoring tool, but to provide a frame-work upon which you can build a sound performance monitoring tool to meet your needs. Eagle maynot meet all of your requirements, but you should be able to customize Eagle to meet your needs. Eagle maynot have all the watches you need, but you should be able to add new watches to extend its functionality. Eagle is a do-it-yourself tool and you are encouraged to enhance and improve Eagle to meet your requirements.
Script 1: Oracle Account Script
REM File Name: eaccount.sql
REM Description: Script to create Eagle Oracle Account
REM Usage: Run from sys or privileged Oracle account
REM Assigns USERS as default and TEMP as temporary tablespace
REM
CREATE USER EAGLE
IDENTIFIED BY EAGLE
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP ;
GRANT DBA TO EAGLE ;
GRANT CREATE VIEW TO EAGLE ;
GRANT EXECUTE ON DBMS_SQL TO EAGLE ;
GRANT SELECT ON TO EAGLE;
Script 2 : Objects Creation Script
REM Name: eddl.sql
REM Description: Creates Objects(Tables/Indexes) in USERS and USERS_IDX tablespace
REM Usage: Run this script from SQL*Plus, Use Eagle Oracle account
REM
CREATE TABLE WATCH (
WATCH_ID VARCHAR2(12) NOT NULL,
NAME VARCHAR2(30) NOT NULL,
PREPARE1_CLAUSE VARCHAR2(256) NULL,
PREPARE2_CLAUSE VARCHAR2(256) NULL,
INSERT_CLAUSE VARCHAR2(256) NOT NULL,
SELECT_CLAUSE VARCHAR2(256) NOT NULL,
WHERE_CLAUSE VARCHAR2(256) NULL,
CLOSE1_CLAUSE VARCHAR2(256) NULL,
CLOSE2_CLAUSE VARCHAR2(256) NULL,
CONSTRAINT WATCK_PK
PRIMARY KEY (WATCH_ID)
USING INDEX TABLESPACE USERS_IDX
STORAGE(INITIAL 16K NEXT 16K PCTINCREASE 0)
) TABLESPACE USERS
STORAGE ( INITIAL 16K NEXT 16K PCTINCREASE 0) ;
CREATE TABLE DATABASE (
DB_ID VARCHAR2(12) NOT NULL,
NAME VARCHAR2(30) NOT NULL,
DB_LINK VARCHAR2(30) NOT NULL,
CONSTRAINT DATABASE_PK
PRIMARY KEY (DB_ID)
USING INDEX TABLESPACE USERS_IDX
STORAGE(INITIAL 16K NEXT 16K PCTINCREASE 0)
) TABLESPACE USERS
STORAGE ( INITIAL 16K NEXT 16K PCTINCREASE 0) ;
CREATE TABLE DB_WATCH (
DB_ID VARCHAR2(12) NOT NULL,
WATCH_ID VARCHAR2(12) NOT NULL,
ACTIVE_YN VARCHAR2(1),
CONSTRAINT DB_WATCH_PK
PRIMARY KEY (DB_ID, WATCH_ID)
USING INDEX TABLESPACE USERS_IDX
STORAGE(INITIAL 16K NEXT 16K PCTINCREASE 0),
CONSTRAINT DB_WATCH_FK1
FOREIGN KEY (DB_ID)
REFERENCES DATABASE (DB_ID),
CONSTRAINT DB_WATCH_FK2
FOREIGN KEY (WATCH_ID)
REFERENCES WATCH (WATCH_ID)
) TABLESPACE USERS
STORAGE ( INITIAL 16K NEXT 16K PCTINCREASE 0) ;
CREATE TABLE DB_WATCH_RESULT(
DB_ID VARCHAR2(12) NOT NULL,
WATCH_ID VARCHAR2(12) NOT NULL,
RUN_TIME DATE NOT NULL,
PARAMETER VARCHAR2(256) NULL,
VALUE NUMBER(12,2) NOT NULL,
CONSTRAINT DB_WATCH_RESULT_FK
FOREIGN KEY (DB_ID,WATCH_ID)
REFERENCES DB_WATCH(DB_ID, WATCH_ID)
) TABLESPACE USERS
STORAGE ( INITIAL 16K NEXT 16K PCTINCREASE 0) ;
Script 3 : Standard Watches Creation Script
REM Name: estdwtch.sql
REM Description: Script to create Standard Watches
REM Usage: Run from SQL*Plus, Use Eagle account
REM
rem
rem
rem Database Buffer Watch
rem
INSERT INTO WATCH(WATCH_ID,NAME,INSERT_CLAUSE,SELECT_CLAUSE,WHERE_CLAUSE)
VALUES('DB_BUF_HR','Database Buffer Hit Ratio',
'INSERT INTO DB_WATCH_RESULT(DB_ID,WATCH_ID,RUN_TIME,VALUE) ',
'SELECT ''
'',''DB_BUF_HR'',SYSDATE,100*ROUND(((A.VALUE + B.VALUE - C.VALUE)/(A.VALUE + B.VALUE)),3) FROM SYS.V$SYSSTAT@
A,SYS.V$SYSSTAT@
B,SYS.V$SYSSTAT@
C ',
'WHERE C.NAME = ''physical reads'' AND A.NAME = ''consistent gets'' AND B.NAME = ''db block gets'' ') ;
rem
rem Sort Area Hit Ratio
rem
INSERT INTO WATCH(WATCH_ID,NAME,INSERT_CLAUSE,SELECT_CLAUSE,WHERE_CLAUSE)
VALUES('SORT_HR','Sort Area Hit Ratio',
'INSERT INTO DB_WATCH_RESULT(DB_ID,WATCH_ID,RUN_TIME,VALUE) ',
'SELECT ''
'',''SORT_HR'',SYSDATE,100*ROUND(((A.VALUE - B.VALUE)/(NVL(A.VALUE,1))),3) FROM SYS.V$SYSSTAT@
A,SYS.V$SYSSTAT@
B ',
'WHERE A.NAME = ''sorts (memory)'' AND B.NAME = ''sorts (disk)'' ') ;
rem
rem Dictionary Cache Hit Ratio
rem
INSERT INTO WATCH(WATCH_ID,NAME,INSERT_CLAUSE,SELECT_CLAUSE,WHERE_CLAUSE)
VALUES('DC_HR','Dictionary Cache Hit Ratio',
'INSERT INTO DB_WATCH_RESULT(DB_ID,WATCH_ID,RUN_TIME,VALUE) ',
'SELECT ''
'',''DC_HR'',SYSDATE,100*ROUND(((SUM(GETS) - SUM(GETMISSES))/(SUM(GETS))),3) FROM SYS.V$ROWCACHE@
',
' ');
rem
rem Library Cache Hit Ratio
rem
INSERT INTO WATCH(WATCH_ID,NAME,INSERT_CLAUSE,SELECT_CLAUSE,WHERE_CLAUSE)
VALUES('LIB_HR','Library Cache Hit Ratio',
'INSERT INTO DB_WATCH_RESULT(DB_ID,WATCH_ID,RUN_TIME,VALUE) ',
'SELECT ''
'',''LIB_HR'',SYSDATE,100*ROUND(((SUM(PINS) - SUM(RELOADS))/(SUM(PINS))),3) FROM SYS.V$LIBRARYCACHE@
',
' ');
rem
rem Extents Watch
rem
INSERT INTO WATCH(WATCH_ID,NAME,INSERT_CLAUSE,SELECT_CLAUSE,WHERE_CLAUSE)
VALUES('EXTENTS','Object Extention',
'INSERT INTO DB_WATCH_RESULT(DB_ID,WATCH_ID,RUN_TIME,PARAMETER,VALUE) ',
'SELECT ''
'',''EXTENTS'',SYSDATE,OWNER''.''SEGMENT_NAME,EXTENTS FROM SYS.DBA_SEGMENTS@
',
'WHERE (EXTENTS > 10 AND OWNER NOT IN (''SYSTEM'',''SYS'')) OR SEGMENT_TYPE = ''ROLLBACK''') ;
rem
rem Free Space Watch
rem
INSERT INTO WATCH(WATCH_ID,NAME,INSERT_CLAUSE,SELECT_CLAUSE,WHERE_CLAUSE,PREPARE1_CLAUSE,PREPARE2_CLAUSE,CLOSE1_CLAUSE,CLOSE2_CLAUSE)
VALUES('FREESPC','% Free Space',
'INSERT INTO DB_WATCH_RESULT(DB_ID,WATCH_ID,RUN_TIME,PARAMETER,VALUE) ',
'SELECT ''
'',''FREESPC'',SYSDATE,B.TABLESPACE_NAME,((NVL(A.BYTES,0))/((B.BYTES)))*100 FROM FREE_SPACE_VIEW A,DATA_FILE_VIEW B ',
'WHERE A.TABLESPACE_NAME(+)=B.TABLESPACE_NAME ',
'CREATE VIEW FREE_SPACE_VIEW(TABLESPACE_NAME,BYTES) AS SELECT TABLESPACE_NAME,SUM(BYTES) FROM SYS.DBA_FREE_SPACE@
GROUP BY TABLESPACE_NAME',
'CREATE VIEW DATA_FILE_VIEW(TABLESPACE_NAME,BYTES) AS SELECT TABLESPACE_NAME,SUM(BYTES) FROM SYS.DBA_DATA_FILES@
GROUP BY TABLESPACE_NAME',
'DROP VIEW FREE_SPACE_VIEW',
'DROP VIEW DATA_FILE_VIEW');
rem
rem
commit;
Script 4 : PL/SQL Stored Procedure
REM
REM Name: eplsql.sql
REM Description: Create PL/SQL procudure that executes active watched
REM Usage: Run from SQL*Plus, Use Eagle account
REM
CREATE OR REPLACE PROCEDURE EXECUTE_DB_WATCH
AS
CURSOR C_DB_WATCH IS
SELECT DB_ID, WATCH_ID, ACTIVE_YN
FROM DB_WATCH ;
db_watch_rec DB_WATCH%ROWTYPE ;
watch_rec WATCH%ROWTYPE ;
database_rec DATABASE%ROWTYPE ;
sql_stmt VARCHAR2(1000) ;
ret_val INTEGER ;
cursor_id INTEGER ;
c_get_data INTEGER ;
temp_clause VARCHAR2(256) ;
BEGIN
OPEN C_DB_WATCH;
LOOP
FETCH C_DB_WATCH INTO db_watch_rec;
EXIT WHEN C_DB_WATCH%NOTFOUND ;
IF db_watch_rec.active_yn = 'Y' THEN
SELECT *
INTO watch_rec
FROM WATCH
WHERE WATCH_ID = db_watch_rec.watch_id ;
/* fetch database record */
SELECT *
INTO database_rec
FROM DATABASE
WHERE DB_ID = db_watch_rec.db_id ;
watch_rec.select_clause := REPLACE(watch_rec.select_clause,
'
',database_rec.db_id);
watch_rec.select_clause := REPLACE(watch_rec.select_clause,
'
',database_rec.db_link);
sql_stmt := watch_rec.insert_clause
watch_rec.select_clause
watch_rec.where_clause;
IF watch_rec.prepare1_clause IS NOT NULL THEN
watch_rec.prepare1_clause := REPLACE(watch_rec.prepare1_clause,
'
',database_rec.db_link);
cursor_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_id,watch_rec.prepare1_clause,DBMS_SQL.V7);
ret_val := DBMS_SQL.EXECUTE(cursor_id) ;
DBMS_SQL.CLOSE_CURSOR(cursor_id);
END IF;
IF watch_rec.prepare2_clause IS NOT NULL THEN
watch_rec.prepare2_clause := REPLACE(watch_rec.prepare2_clause,
'
',database_rec.db_link);
cursor_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_id,watch_rec.prepare2_clause,DBMS_SQL.V7);
ret_val := DBMS_SQL.EXECUTE(cursor_id) ;
DBMS_SQL.CLOSE_CURSOR(cursor_id);
END IF;
c_get_data := DBMS_SQL.OPEN_CURSOR ;
DBMS_SQL.PARSE(c_get_data,sql_stmt,DBMS_SQL.V7) ;
ret_val := DBMS_SQL.EXECUTE(c_get_data) ;
DBMS_SQL.CLOSE_CURSOR(c_get_data) ;
IF watch_rec.close1_clause IS NOT NULL THEN
cursor_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_id,watch_rec.close1_clause,DBMS_SQL.V7);
ret_val := DBMS_SQL.EXECUTE(cursor_id) ;
DBMS_SQL.CLOSE_CURSOR(cursor_id);
END IF;
IF watch_rec.close2_clause IS NOT NULL THEN
cursor_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_id,watch_rec.close2_clause,DBMS_SQL.V7);
ret_val := DBMS_SQL.EXECUTE(cursor_id) ;
DBMS_SQL.CLOSE_CURSOR(cursor_id);
END IF;
END IF;
END LOOP;
END ;
/
Script 5 : Output Report Script
REM
REM Name: eoutput.sql
REM Description: Script to generate output report
REM Usage: Run from SQL*Plus, Use Eagle Oracle account
REM sqlplus eagle/eagle
SPOOL &1
COLUMN NAME FORMAT A26 HEADING 'WatchName'
COLUMN PARAMETER FORMAT A28 HEADING 'Parameter Name'
COLUMN WEEK4 FORMAT 999 HEADING '4wkAgo'
COLUMN WEEK3 FORMAT 999 HEADING '3wkAgo'
COLUMN WEEK2 FORMAT 999 HEADING '2wkAgo'
COLUMN WEEK1 FORMAT 999 HEADING '1wkAgo'
COLUMN DAYS6 FORMAT 999 HEADING '6dyAgo'
COLUMN DAYS5 FORMAT 999 HEADING '5dyAgo'
COLUMN DAYS4 FORMAT 999 HEADING '4dyAgo'
COLUMN DAYS3 FORMAT 999 HEADING '3dyAgo'
COLUMN DAYS2 FORMAT 999 HEADING '2dyAgo'
COLUMN DAYS1 FORMAT 999 HEADING '1dyAgo'
COLUMN TODAY FORMAT 999 HEADING 'Tdy'
COLUMN CHANGE FORMAT 999 HEADING 'Chg'
rem
SET PAGESIZE 60 LINESIZE 130
SET TERMOUT OFF ECHO OFF FEEDBACK OFF
rem
rem
BREAK ON DB_ID NODUP SKIP 2 ON NAME NODUP SKIP 1
rem TTITLE CENTER 'Eagle Output' SKIP 2
SELECT DB_ID,
WATCH.NAME,
PARAMETER,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-28),VALUE,0)) WEEK4,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-21),VALUE,0)) WEEK3,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-14),VALUE,0)) WEEK2,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-7),VALUE,0)) WEEK1,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-6),VALUE,0)) DAYS6,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-5),VALUE,0)) DAYS5,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-4),VALUE,0)) DAYS4,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-3),VALUE,0)) DAYS3,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-2),VALUE,0)) DAYS2,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-1),VALUE,0)) DAYS1,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE),VALUE,0)) TODAY,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE),VALUE,0)) -
MAX(DECODE(TRUNC(RUN_TIME), TRUNC(SYSDATE-28),VALUE,0)) CHANGE
FROM DB_WATCH_RESULT ,WATCH
WHERE WATCH.WATCH_ID = DB_WATCH_RESULT.WATCH_ID
GROUP BY DB_ID,WATCH.NAME,PARAMETER
ORDER BY DB_ID, WATCH.NAME,PARAMETER;
REM
SET NEWPAGE 0
REM
EXIT
Script 6 : Unix Shell Script
# Name: eagle.sh
# Description: Unix shell script to run PL/SQL procudure and generate report
# Set ORACLE_HOME and ORACLE_SID in this script
# eoutput.sql is in home directory
# Output file also created in home directory
#
REPORTDIR=$HOME
REPORTFILE=$REPORTDIR/eagle`date "+%m%d"`.out
touch $REPORTFILE
sqlplus -s eagle/eagle <
