USA EAST
Do It Yourself: Implement Performance Monitoring Tool
By Jay Mehta
Scripts available at the END of the article
John 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 percent, a low and not acceptable rate. He knew that database buffer hit ratio was 90 percent 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 every day 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, US, 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.
Mark 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 more than 50 Oracle databases in six US offices. Mark found it very time consuming to log into every system at each of the US offices, and examine the health of all Oracle databases. 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 John, Rupa or Mark, 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 did not 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 chores 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. The 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 four weeks ago, three weeks ago, two weeks ago, one week ago, six days ago, five 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 four 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 stores 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 clean-up, 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.(We should provide all scripts on CompuServe or Internet for downloading.)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 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 <DB_LINK_NAME> CONNECT TO SYS IDENTIFIED BY <PASSWORD> USING 'T:<node_name>:<database_name>
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 ('<YOUR_DB_ID>','<YOUR DB NAME>','<DB_LINK_NAME_TO_DATABASE>'); Activating Watches: Use the following statement to add watches:INSERT INTO DB_WATCH(DB_ID, WATCH_ID, ACTIVE_YN) VALUES('<YOUR_DB_ID>','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 every day. 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 are 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 write 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 may not meet all of your requirements, but you should be able to customize Eagle to meet your needs. Eagle may not 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.About the Author
Jay Mehta works as an Oracle consultant for Mobil Oil Corporation in Fairfax, VA. Although the contents of this article have been tested thoroughly in production environment on Unix platform, author doesn't claim that this article is error-free. Your comments are welcome.Jay Mehta
Work Phone: +1.703.846.5169
Home Phone: +1.703.790.5732
CompuServe: 71034,261
email: [email protected]
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 ; 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_ID>'',''DB_BUF_HR'',SYSDATE,100*ROUND(((A.VALUE + B.VALUE - C.VALUE)/(A.VALUE + B.VALUE)),3) FROM SYS.V$SYSSTAT@<DB_LINK> A,SYS.V$SYSSTAT@<DB_LINK> B,SYS.V$SYSSTAT@<DB_LINK> 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 ''<DB_ID>'',''SORT_HR'',SYSDATE,100*ROUND(((A.VALUE - B.VALUE)/(NVL(A.VALUE,1))),3) FROM SYS.V$SYSSTAT@<DB_LINK> A,SYS.V$SYSSTAT@<DB_LINK> 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 ''<DB_ID>'',''DC_HR'',SYSDATE,100*ROUND(((SUM(GETS) - SUM(GETMISSES))/(SUM(GETS))),3) FROM SYS.V$ROWCACHE@<DB_LINK> ', ' '); 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 ''<DB_ID>'',''LIB_HR'',SYSDATE,100*ROUND(((SUM(PINS) - SUM(RELOADS))/(SUM(PINS))),3) FROM SYS.V$LIBRARYCACHE@<DB_LINK> ', ' '); 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 ''<DB_ID>'',''EXTENTS'',SYSDATE,OWNER||''.''||SEGMENT_NAME,EXTENTS FROM SYS.DBA_SEGMENTS@<DB_LINK> ', '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 ''<DB_ID>'',''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@<DB_LINK> GROUP BY TABLESPACE_NAME', 'CREATE VIEW DATA_FILE_VIEW(TABLESPACE_NAME,BYTES) AS SELECT TABLESPACE_NAME,SUM(BYTES) FROM SYS.DBA_DATA_FILES@<DB_LINK> 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, '<DB_ID>',database_rec.db_id); watch_rec.select_clause := REPLACE(watch_rec.select_clause, '<DB_LINK>',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, '<DB_LINK>',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, '<DB_LINK>',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 <Output Report File Name> SPOOL &1 COLUMN NAME FORMAT A26 HEADING 'Watch|Name' COLUMN PARAMETER FORMAT A28 HEADING 'Parameter Name' COLUMN WEEK4 FORMAT 999 HEADING '4wk|Ago' COLUMN WEEK3 FORMAT 999 HEADING '3wk|Ago' COLUMN WEEK2 FORMAT 999 HEADING '2wk|Ago' COLUMN WEEK1 FORMAT 999 HEADING '1wk|Ago' COLUMN DAYS6 FORMAT 999 HEADING '6dy|Ago' COLUMN DAYS5 FORMAT 999 HEADING '5dy|Ago' COLUMN DAYS4 FORMAT 999 HEADING '4dy|Ago' COLUMN DAYS3 FORMAT 999 HEADING '3dy|Ago' COLUMN DAYS2 FORMAT 999 HEADING '2dy|Ago' COLUMN DAYS1 FORMAT 999 HEADING '1dy|Ago' 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 <<EOF execute execute_db_watch start $HOME/eoutput $REPORTFILE exit EOF Figure 1 : Output Report Watch 4wk 3wk 2wk 1wk 6dy 5dy 4dy 3dy 2dy 1dy DB_Id Name Parameter Name Ago Ago Ago Ago Ago Ago Ago Ago Ago Ago Tdy Chg ------------ -------------------------- ---------------------------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- Sales % Free Space SYSTEM 50 50 50 50 50 50 50 50 50 50 50 0 RBS 28 36 36 36 28 20 20 12 12 12 12 -16 TEMP 70 100 100 100 100 100 100 100 100 100 100 30 USERS_DATA 40 36 36 30 30 29 29 28 27 27 26 -14 USERS_IDX 30 28 28 25 25 24 24 24 23 23 22 -8 DB Buffer Hit Ratio 98 90 93 90 84 89 88 90 92 91 65 -33 Dic. Cache Hit Ratio 97 96 94 93 96 94 92 91 93 92 87 -10 Lib. Cache Hit Ratio 92 93 93 92 93 92 91 90 91 91 90 -2 Object Extention JVM.CUSTOMER 6 7 7 7 7 7 7 7 7 7 7 1 JVM.PRODUCT 10 11 11 11 11 11 11 11 11 11 11 1 JVM.ORDER 7 7 7 7 7 7 7 7 7 7 7 0 JVM.ORDER_LINE 9 9 9 10 10 10 10 10 10 10 10 1 SYS.R01 20 20 20 20 20 20 20 20 20 20 20 0 SYS.R02 20 20 20 20 20 20 20 20 20 20 20 0 SYS.SYSTEM 4 4 4 4 4 4 4 4 4 4 4 0 Sort Area Hit Ratio 80 80 70 70 84 82 90 90 80 80 40 -40 HR % Free Space SYSTEM 30 30 30 30 30 30 30 30 30 30 30 0 RBS 49 49 49 49 49 49 49 49 49 49 49 0 TEMP 100 100 40 69 82 100 90 90 91 100 93 -7 USERS_DATA 10 9 8 8 7 7 6 6 6 6 5 -5 USERS_IDX 19 19 19 18 17 16 16 16 16 15 15 -4 DB Buffer Hit Ratio 92 90 93 90 84 89 88 90 92 91 90 -2 Dic. Cache Hit Ratio 97 96 94 93 96 94 92 91 93 92 94 -3 Lib. Cache Hit Ratio 92 93 93 92 93 92 91 90 91 91 90 -2 Object Extention JVM.DEPT 8 9 9 9 9 9 9 9 9 9 9 1 JVM.EMPLOYEE 40 41 43 44 44 44 44 45 45 45 46 6 SYS.R01 20 20 20 20 20 20 20 20 20 20 20 0 SYS.R02 20 20 20 20 20 20 20 20 20 20 20 0 SYS.SYSTEM 4 4 4 4 4 4 4 4 4 4 4 0 Sort Area Hit Ratio 80 80 70 70 84 82 90 90 80 80 80 0
This is a copy of an article published @ http://www.ioug.org/