BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Monitoring Database Links Using V$ and DBA_ Views

Oracle Tips by Burleson Consulting

Database links provide connection paths to external databases. They specify user name, password, and connection string data. In earlier versions, a protocol had to be specified; now, protocol-specific data is placed in the TNSNAMES.ORA file and is hidden from the user. Database links can be either private, used by a single user, or public, and accessible by all users. Database links can be used on the fly in queries or can be made invisible to the common user by using synonyms. The DBA_DB_LINKS view is used to monitor them. Source 11.21 shows an example of a database link report, and Listing 11.18 shows an example of a listing from a DB link report script.

SOURCE 11.21 Example of database links report.

REM
REM NAME         : DBLINK_REP.SQL
REM FUNCTION     : GENERATE REPORT OF DATABASE LINKS
REM USE          : FROM SQLPLUS
REM Limitations  : None
REM  MRA 10/14/01 Verified for Oracle9i
REM
SET PAGES 58 LINES 80 VERIFY OFF TERM OFF
START title80 "Db Links Report"
SPOOL rep_out/&db/dblink_rep
COLUMN host             FORMAT a18      HEADING "Connect|String"
COLUMN owner            FORMAT a8      HEADING "Creator"
COLUMN db_link          FORMAT a19      HEADING "DB Link|Name"
COLUMN username         FORMAT a8      HEADING "Connect|User"
COLUMN created          FORMAT a15      HEADING "Date|Created"
SELECT
     host,owner,db_link,username,
     to_char(created,'dd-mon-yy hh24:mi') created
FROM
     DBA_db_links
ORDER BY
     owner,
     host;
SPOOL OFF
SET PAGES 22 LINES 80 VERIFY ON FEEDBACK ON TERM ON
CLEAR COLUMNS
TTITLE OFF
PAUSE Press Enter to continue

LISTING 11.18 Example of listing from DB links report script.

Date: 10/14/01                                                  Page:   1
Time: 02:21 PM                Db Links Report                   DBAUTIL
                              galinux1 databa

Connect                     DB Link             Connect  Date
String             Creator  Name                User     Created
------------------ -------- ------------------- -------- ---------------aultdb1.mramobile  DBAUTIL  AULTDB1.TUSCGALINUX DBAUTIL  14-oct-01 14:01

Monitoring Database Rollback Segments Using V$ and DBA_Views

Rollback segments and the new Oracle9i UNDO segments must be monitored. Though their tablespace area is monitored through the freespace and extents reports shown in previous sections, it would be helpful to have a report just for rollback segments to present rollback-related data in one convenient location. Even in Oracle9i, information on UNDO usage is helpful to the DBA in tuning the Oracle UNDO tablespace. The same views as with Oracle7, Oracle8, and Oracle8i are used to monitor UNDO segments in Oracle9i.

Monitoring Rollback Usage and Statistics

Unfortunately, the DBA_ROLLBACK_SEGS view is just too large to allow a single report to cover all of the parameters it shows. Therefore, two views and two reports are required to adequately cover the DBA_ROLLBACK_SEGS view and the monitoring of rollback segments. The scripts in Source 11.22 create two views, ROLLBACK1 and ROLLBACK2, both based on the V$ROLLSTAT and V$ROLLNAME views, which are very important for monitoring rollback activity. The DBA_ view, DBA_ROLLBACK_ SEGS, is based on these two tables. In Oracle9i, a new V$ view was added to allow monitoring of the UNDO segment usage statistics; this new view is called V$UNDOSTAT.

SOURCE 11.22 SQL scripts to generate ROLLBACK1 and ROLLBACK2 views.

REM
REM FUNCTION: create views required for rbk1 and rbk2 reports.
REM
REM
CREATE OR REPLACE VIEW rollback1 AS
SELECT
     d.segment_name, extents, optsize, shrinks,
     aveshrink, aveactive, d.status
FROM
     v$rollname n,
     v$rollstat s,
     DBA_rollback_segs d
WHERE
     d.segment_id=n.usn(+)
     AND d.segment_id=s.usn(+)
;


CREATE OR REPLACE VIEW rollback2 AS
SELECT
     d.segment_name,extents,xacts,hwmsize,
     rssize,waits,wraps,extends,d.status
FROM
     v$rollname n,
     v$rollstat s,
     DBA_rollback_segs d
WHERE
     d.segment_id=n.usn(+)
     AND d.segment_id=s.usn(+);

Once the ROLLBACK1 and ROLLBACK2 views have been created, two simple SQL scripts are used to monitor rollback segments. These scripts are shown in Source 11.23; their output is shown in Listing 11.19.

SOURCE 11.23 Example of rollback report scripts.

REM NAME                : RBK1.SQL
REM FUNCTION            : REPORT ON ROLLBACK SEGMENT STORAGE
REM FUNCTION            : USES THE ROLLBACK1 VIEW
REM USE                 : FROM SQLPLUS
REM Limitations         : None
REM
COLUMN hwmsize          FORMAT 9999999999    HEADING 'LARGEST TRANS'
COLUMN tablespace_name  FORMAT a10          HEADING 'TABLESPACE'
COLUMN segment_name     FORMAT A10           HEADING 'ROLLBACK'
COLUMN optsize          FORMAT 9999999999   HEADING 'OPTL|SIZE'
COLUMN shrinks          FORMAT 9999           HEADING 'SHRINKS'
COLUMN aveshrink        FORMAT 9999999999   HEADING 'AVE|SHRINK'
COLUMN aveactive        FORMAT 9999999999   HEADING 'AVE|TRANS'
COLUMN waits            FORMAT 99999              HEADING 'WAITS'
COLUMN wraps            FORMAT 99999              HEADING 'WRAPS'
COLUMN extends          FORMAT 9999              HEADING 'EXTENDS'
rem
BREAK ON REPORT
COMPUTE AVG OF AVESHRINK ON REPORT
COMPUTE AVG OF AVEACTIVE ON REPORT
COMPUTE AVG OF SHRINKS ON REPORT
COMPUTE AVG OF WAITS ON REPORT
COMPUTE AVG OF WRAPS ON REPORT
COMPUTE AVG OF EXTENDS ON REPORT
COMPUTE AVG OF HWMSIZE ON REPORT
SET FEEDBACK OFF VERIFY OFF LINES 132 PAGES 58
@title132 "ROLLBACK SEGMENT STORAGE"
SPOOL rep_out\&db\rbk1
rem
SELECT
  a.SEGMENT_NAME,a.OPTSIZE,a.SHRINKS,        
  a.AVESHRINK,a.AVEACTIVE,b.HWMSIZE,
  b.WAITS,b.WRAPS,b.EXTENDS,A.STATUS
FROM rollback1 a, rollback2 b
WHERE A.SEGMENT_NAME=B.SEGMENT_NAME
ORDER BY segment_name;
SPOOL OFF
CLEAR COLUMNS
TTITLE OFF
SET FEEDBACK ON VERIFY ON LINES 80 PAGES 22
PAUSE Press enter to continue

REM
REM NAME        : RBK2.SQL
REM FUNCTION    : REPORT ON ROLLBACK SEGMENT STATISTICS
REM FUNCTION    : USES THE ROLLBACK2 VIEW
REM USE         : FROM SQLPLUS
REM Limitations : None
REM
COLUMN segment_name     FORMAT A8              HEADING 'ROLLBACK'
COLUMN extents          FORMAT 99999            HEADING 'EXTENTS'
COLUMN xacts            FORMAT 9999            HEADING 'TRANS'
COLUMN hwmsize          FORMAT 9999999999    HEADING 'LARGEST TRANS'
COLUMN rssize           FORMAT 9999999999    HEADING 'CUR SIZE'
COLUMN waits            FORMAT 99999              HEADING 'WAITS'
COLUMN wraps            FORMAT 99999              HEADING 'WRAPS'
COLUMN extends          FORMAT 9999              HEADING 'EXTENDS'
rem
SET FEEDBACK OFF VERIFY OFF lines 132 pages 58
BREAK ON REPORT
COMPUTE AVG OF WAITS ON REPORT
COMPUTE AVG OF WRAPS ON REPORT
COMPUTE AVG OF EXTENDS ON REPORT
COMPUTE AVG OF HWMSIZE ON REPORT
rem
@title132 "ROLLBACK SEGMENT STATISTICS"
SPOOL rep_out\&db\rbk2
rem
SELECT * FROM rollback2 ORDER BY segment_name;
SPOOL OFF
SET LINES 80 PAGES 20 FEEDBACK ON VERIFY ON
TTITLE OFF
CLEAR COLUMNS
PAUSE Press enter to continue

REM
REM NAME                : RBK3.SQL
REM FUNCTION            : REPORT ON ROLLBACK SEGMENT HEALTH
REM FUNCTION            : USES THE ROLLBACK1 and ROLLBACK2 VIEWs
REM USE                 : FROM SQLPLUS
REM Limitations         : None
REM
COLUMN hwmsize          FORMAT 9999999999    HEADING 'LARGEST TRANS'
COLUMN tablespace_name  FORMAT a10          HEADING 'TABLESPACE'
COLUMN segment_name     FORMAT A10           HEADING 'ROLLBACK'
COLUMN optsize          FORMAT 9999999999   HEADING 'OPTL|SIZE'
COLUMN shrinks          FORMAT 9999           HEADING 'SHRINKS'
COLUMN aveshrink        FORMAT 9999999999   HEADING 'AVE|SHRINK'
COLUMN aveactive        FORMAT 9999999999   HEADING 'AVE|TRANS'
COLUMN waits            FORMAT 99999              HEADING 'WAITS'
COLUMN wraps            FORMAT 99999              HEADING 'WRAPS'
COLUMN extends          FORMAT 9999              HEADING 'EXTENDS'
rem
BREAK ON REPORT
COMPUTE AVG OF AVESHRINK ON REPORT
COMPUTE AVG OF AVEACTIVE ON REPORT
COMPUTE AVG OF SHRINKS ON REPORT
COMPUTE AVG OF WAITS ON REPORT
COMPUTE AVG OF WRAPS ON REPORT
COMPUTE AVG OF EXTENDS ON REPORT
COMPUTE AVG OF HWMSIZE ON REPORT
SET FEEDBACK OFF VERIFY OFF LINES 132 PAGES 47
@title132 "ROLLBACK SEGMENT HEALTH"
SPOOL rep_out\&db\rbk3
rem
SELECT c.tablespace_name, a.segment_name, a.optsize, a.shrinks, a.aveshrink,
a.aveactive,
       b.hwmsize, b.waits, b.wraps, b.extends
FROM rollback1 a, rollback2 b, DBA_rollback_segs c
where a.segment_name=b.segment_name
and c.segment_name=a.segment_name
ORDER BY tablespace_name, segment_name;
SPOOL OFF
CLEAR COLUMNS
TTITLE OFF
SET FEEDBACK ON VERIFY ON LINES 80 PAGES 22
PAUSE Press enter to continue

LISTING 11.19 Example of rollback segment reports output.

Date: 10/14/01                                                                      Page:   1
Time: 03:21 PM                 ROLLBACK SEGMENT STORAGE                          DBAUTIL
                                  galinux1 database

                  OPTL                 AVE         AVE
ROLLBACK          SIZE SHRINKS      SHRINK       TRANS LARGEST TRANS  WAITS  WRAPS
EXTENDS STATUS
---------- ----------- ------- ----------- ----------- ------------- ------ ------ -----SYSTEM                       0           0           0        401408      0      0       0 ONLINE
_SYSSMU1$                    0           0        6553        122880      0      1      
0 ONLINE
_SYSSMU10$                   0           0           0        122880      0      0       0 ONLINE
_SYSSMU2$                    0           0           0        122880      0      0       0 ONLINE
_SYSSMU3$                    0           0           0        122880      0      0       0 ONLINE
_SYSSMU4$                    0           0           0        122880      0      0       0 ONLINE
_SYSSMU5$                    0           0        6553        122880      0      1       0 ONLINE
_SYSSMU6$                    0           0        6553        122880      0      1       0 ONLINE
_SYSSMU7$                    0           0        6553        122880      0      1       0 ONLINE
_SYSSMU8$                    0           0           0        122880      0      0       0 ONLINE
_SYSSMU9$                    0           0        5734        122880      0      1       0 ONLINE
                       ------- ----------- ----------- ------------- ------ ------ -----
avg                          0           0        2904        148201      0      0       0

Press enter to continue

Date: 10/14/01                                                                        Page:   1
Time: 03:24 PM                   ROLLBACK SEGMENT STATISTICS                    DBAUTIL
                                       galinux1 database

ROLLBACK   EXTENTS TRANS LARGEST TRANS    CUR SIZE  WAITS  WRAPS EXTENDS
---------- ------- ----- ------------- ----------- ------ ------ -------
SYSTEM           5     0        401408      401408      0      0       0
_SYSSMU1$        2     0        122880      122880      0      1       0
_SYSSMU10$       2     0        122880      122880      0      0       0
_SYSSMU2$        2     0        122880      122880      0      0       0
_SYSSMU3$        2     0        122880      122880      0      0       0
_SYSSMU4$        2     0        122880      122880      0      0       0
_SYSSMU5$        2     0        122880      122880      0      1       0
_SYSSMU6$        2     0        122880      122880      0      1       0
_SYSSMU7$        2     0        122880      122880      0      1       0
_SYSSMU8$        2     0        122880      122880      0      0       0
_SYSSMU9$        2     0        122880      122880      0      1       0
                         -------------             ------ ------ -------
avg                             148201                  0      0       0

Press enter to continue

Date: 10/14/01                                                                          Page:   1
Time: 03:25 PM                         ROLLBACK SEGMENT HEALTH                 DBAUTIL
                                          galinux1 database

                         OPTL                 AVE         AVE
TABLESPACE ROLLBACK      SIZE SHRINKS      SHRINK       TRANS LARGEST TRANS  WAITS  WRAPS EXTENDS
---------- ---------- ------- ------- ----------- ----------- ------------- ------ -----SYSTEM     SYSTEM                   0           0           0        401408      0      0       0
UNDOTBS    _SYSSMU1$                0           0        6553        122880      0      1       0
UNDOTBS    _SYSSMU10$               0           0           0        122880      0      0       0
UNDOTBS    _SYSSMU2$                0           0           0        122880      0      0       0
UNDOTBS    _SYSSMU3$                0           0           0        122880      0      0       0
UNDOTBS    _SYSSMU4$                0           0           0        122880      0      0       0
UNDOTBS    _SYSSMU5$                0           0        6553        122880      0      1       0
UNDOTBS    _SYSSMU6$                0           0        6553        122880      0      1       0
UNDOTBS    _SYSSMU7$                0           0        6553        122880      0      1       0
UNDOTBS    _SYSSMU8$                0           0           0        122880      0      0       0
UNDOTBS    _SYSSMU9$                0           0        5734        122880      0      1       0
                              ------- ----------- ----------- ------------- ------ -----
avg                                 0           0        2904        148201      0      0       0

Press enter to continue

The report in listing 11.19 is from an Oracle9i database: notice that all of the segments have those wonderful system-generated names and that all are in the UNDOTBS tablespace. In the reports shown in this listing, the parameters of concern to the DBA are location, status, and sizing data. The DBA needs to verify that no rollback segments have been created outside of the prescribed tablespaces. In addition, the DBA should verify that all rollback segments that are supposed to be online are in fact online, and that those that are supposed to be offline are offline. Excessive waits indicate the need for more rollback segments. Excessive extends indicate you may need larger extent sizes. If optimal is set, and you get excessive shrinks, this indicates that you need larger rollback segment extents. Usually, wraps aren’t of concern, although excessive wraps may be indicative of a too-small rollback segment extent size. If you are using Oracle-managed UNDO, then the reports in Listing 11.19 are informational only.

A report similar to Source 11.24 should be used to monitor undo usage. The UNDO_RETENTION parameter should be based on desired undo retention time, in minutes multiplied by the undo usage showed in this report. The output from the report in Source 11.24 is shown in Listing 11.20.

SOURCE 11.24 Example UNDO usage report.

REM undo_usage.sql
REM Function: reports undo usage for Oracle9i
REM
REM MRA 10/14/01 Initial Creation
REM
COLUMN undo_usage FORMAT 99,999,999.999 HEADING 'Undo Usage|Blocks/Min'
COLUMN oer_old_errors FORMAT 99,999,999 HEADING 'Undo|Old Errors'
COLUMN oer_space_errors FORMAT 9,999,999,999 HEADING 'Undo|Space Errors'
SET FEEDBACK OFF
@title80 'Undo Usage'
spool rep_out/&db/undo_usage
select
  sum(undoblks)/sum((end_time-begin_time)*24*60) undo_usage,
  sum(ssolderrcnt) OER_old_errors,
  sum(nospaceerrcnt) OER_space_errors
from
  v$undostat
where
  undoblks>0
/
spool off
SET FEEDBACK ON
TTITLE OFF

LISTING 11.20 Example UNDO usage report.

Date: 10/14/01                                         Page:   1
Time: 03:43 PM              Undo Usage                 DBAUTIL
                          galinux1 database

    Undo Usage        Undo           Undo
    Blocks/Min  Old Errors   Space Errors
-------------- ----------- --------------
          .123           0              0


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter