 |
|
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. |
 |
|
