 |
|
Monitoring Tablespace
Autoextend Settings
Oracle Tips by Burleson Consulting
|
In addition to space usage and fragmentation
monitoring, if you use the autoextend feature, you need to monitor
database autoextend status and data file locations. Source 11.16 shows
the SQL to create a view that monitors autoextend data for
pre-Oracle8. In pre-Oracle8 versions, the only way to get this
information was to query the SYS table FILEXT$, which, unfortunately,
looks like this:
Name Null? Type
-------------------------------- -------------- ------
FILE# NOT NULL NUMBER
MAXEXTEND NOT NULL NUMBER
INC NOT NULL NUMBER
This structure means that, in order to get
back to the actual filename and tablespace, you need to join to
several other tables, namely, FILE$, TS$, and V$DBFILE. A script to
create a data file view is shown in Source 11.16.
SOURCE 11.16 Script to create a data file
view.
CREATE VIEW
Remote DBA_file_data AS
SELECT
a.name tablespace,a.dflminext min_extents,
a.dflmaxext max_extents,
a.dflinit init,a.dflincr next,
a.dflextpct pct_increase, d.name datafile,
b.blocks datafile_size, c.maxextend max_extend,
c.inc ext_incr
FROM ts$ a, file$ b, filext$ c, v$dbfile d
WHERE
a.ts#=b.ts# and b.file#=c.file# and b.file#=d.file#
/
This script creates the view Remote DBA_FILE_DATA,
which will look like this when queried:
Name Null? Type
----------------------------- -------- ------------
TABLESPACE NOT NULL VARCHAR2(30)
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NOT NULL NUMBER
INIT NOT NULL NUMBER
NEXT NOT NULL NUMBER
PCT_INCREASE NOT NULL NUMBER
DATAFILE VARCHAR2(257)
DATAFILE_SIZE NOT NULL NUMBER
MAX_EXTEND NOT NULL NUMBER
EXT_INCR NOT NULL NUMBER
Using this view, it is then easy to create a
simple SELECT that gets autoextend and data file location information
from a single view, along with all of the pertinent sizing
information. The Oracle Administrator Storage Manager does show this
information under the data files section. In Oracle8i, the view
Remote DBA_DATA_FILES contains the columns AUTOEXTENSIBLE (YES or NO),
MAXBYTES, MAXBLOCKS, and INCREMENT_BY, which allow for ease of report
generation and monitoring of the autoextension capabilities of all
data files.
Monitoring Tablespace Data Files
As the Remote DBA, you should also monitor the size
and location of the data files associated with the tablespaces under
your control. If for no other reason than to prevent yourself from
placing index tablespace data files alongside those that deal with
table data, you need to have an accurate map of data files. A script
to document tablespace data files is shown in Source 11.17; it
produces the report in Listing 11.14 that provides this data file map.
SOURCE 11.17 Script to document tablespace
data files.
REM
REM Name: dbfiles.sql
REM FUNCTION: Document file sizes and locations
REM Use: From SQLPLUS
REM MRA 05/16/99 Added autoextend monitoring
REM MRA 10/14/99 Added temp file monitoring 9i
REM
CLEAR COMPUTES
COLUMN file_name FORMAT A51 HEADING 'File Name'
COLUMN tablespace_name FORMAT A15 HEADING 'Tablespace'
COLUMN meg FORMAT 99,999.90 HEADING 'Megabytes'
COLUMN status FORMAT A10 HEADING 'Status'
COLUMN autoextensible FORMAT A3 HEADING 'AE?'
COLUMN maxmeg FORMAT 99,999 HEADING 'Max|Megabytes'
COLUMN Increment_by FORMAT 9,999 HEADING 'Inc|By'
SET LINES 130 PAGES 47 VERIFY OFF feedback OFF
START title132 'DATABASE DATA FILES'
SPOOL rep_out\&db\datafile
BREAK ON tablespace_name SKIP 1 ON REPORT
COMPUTE SUM OF meg ON tablespace_name
COMPUTE SUM OF meg ON REPORT
SELECT
tablespace_name,file_name,
bytes/1048576 meg,
status,autoextensible,
maxbytes/1048576 maxmeg,
increment_by
FROM
Remote DBA_data_files
UNION
SELECT
tablespace_name,file_name,
bytes/1048576 meg,
status,autoextensible,
maxbytes/1048576 maxmeg,
increment_by
FROM
Remote DBA_temp_files
ORDER BY
tablespace_name
/
SPOOL OFF
SET VERIFY ON feedback ON
TTITLE OFF
CLEAR COLUMNS
CLEAR COMPUTES
PAUSE Press Enter to continue
LISTING 11.14 Example of output from data file
report.
Date:
10/14/01
Page: 1
Time: 12:59 PM DATABASE DATA
FILES Remote DBAUTIL
galinux1 database
Max Inc
Tablespace File Name Megabytes
Status AE? Megabytes By
------------- ------------------------------------------ ----------
---------- --- --------- -------
CWMLITE /var/oracle/oradata/galinux1/cwmlite01.dbf 20.00
AVAILABLE YES 32,768 80
*************
----------
sum 20.00
Remote DBAUTIL_DATA /opt/oracle/oradata/galinux1/dbtl_dat1.dbf 10.00
AVAILABLE NO 0 0
*************
sum 10.00
Remote DBAUTIL_INDEX /opt/oracle/oradata/galinux1/dbtl_idx1.dbf 10.00
AVAILABLE NO 0 0
*************
sum 10.00
DRSYS /var/oracle/oradata/galinux1/drsys01.dbf 20.00
AVAILABLE YES 32,768 80
************* ----------
sum 20.00
EXAMPLE /var/oracle/oradata/galinux1/example01.dbf 36.25
AVAILABLE YES 32,768 80
*************
----------sum
36.25
INDX /var/oracle/oradata/galinux1/indx01.dbf 25.00
AVAILABLE YES 32,768 160
*************
----------sum
25.00
SYSTEM /var/oracle/oradata/galinux1/system01.dbf 465.00
AVAILABLE YES 32,768 1,280
*************
----------sum
465.00
TEMP /var/oracle/oradata/galinux1/temp01.dbf 40.00
AVAILABLE NO 0 0
/var/oracle/oradata/galinux1/temp02.dbf 50.00
AVAILABLE NO 0 0
************* ----------
sum 90.00
TOOLS /var/oracle/oradata/galinux1/tools01.dbf 10.00
AVAILABLE YES 32,768 40
*************
----------sum
10.00
UNDOTBS /var/oracle/oradata/galinux1/undotbs01.dbf 200.00
AVAILABLE YES 32,768 640
*************
----------sum
200.00
UNDO_TBS2 /opt/oracle/oradata/galinux1/undo_tbs2.dbf 20.00
AVAILABLE YES 32,768 1
*************
----------sum
20.00
UNDO_TBS3 /opt/oracle/oradata/galinux1/undo_tbs3.dbf 20.00
AVAILABLE YES 30 1
************* ----------
sum 20.00
USERS /var/oracle/oradata/galinux1/users01.dbf 25.00
AVAILABLE YES 32,768 160
*************
----------sum
25.00
----------
sum 951.25
Monitoring Tablespace Extent Mapping
A final set of freespace information that is
handy to have around (OEM and many third-party tools will provide a
GUI-based map) is the location of the freespace in a tablespace and
the size of the fragments themselves. The script in Source 11.18
provides this data. The output from the script in Source 11.18 is
shown in Listing 11.15.
SOURCE 11.18 Script to document freespace
extents inside a tablespace.
rem
rem Name: mapper.sql
rem Function: create an extent map for a specific tablespace
rem Based on a technique from Remote DBA Handbook
rem Mike Ault 7/19/96
rem
SET PAGES 47 LINES 132 VERIFY OFF feedback OFF
COLUMN file_id HEADING 'File|id'
COLUMN value NEW_VALUE dbblksiz NOPRINT
COLUMN meg FORMAT 9,999.99 HEADING 'Meg'
COLUMN partition_name FORMAT a30 HEADING 'Partition|Name'
SELECT value FROM v$parameter WHERE name='db_block_size';
START title132 '&&ts Mapping Report'
SPOOL rep_out/&db/ts_map
SELECT
'free space' owner, ' ' object,'Not Part.' partition
file_id, block_id, blocks,
(blocks*&dbblksiz)/(1024*1024) meg
FROM
Remote DBA_free_space
WHERE
tablespace_name=UPPER('&&ts')
UNION
SELECT
SUBSTR(owner,1,20), SUBSTR(segment_name, 1,32),partition_name
file_id, block_id, blocks,
(blocks*&dbblksiz)/(1024*1024) meg
FROM
Remote DBA_extents
WHERE
tablespace_name = UPPER('&&ts')
ORDER BY 3,4;
SPOOL OFF
UNDEF ts
SET PAGES 22 LINES 80 VERIFY ON feedback ON
CLEAR COLUMNS
TTITLE OFF
LISTING 11.15 Example of output of the
mapper script.
Date:
06/13/97 Page: 1
Time: 07:29 PM raw_data Mapping Report SYSTEM
ORTEST1 database
File
OWNER OBJECT id BLOCK_ID BLOCKS MEG
---------- -------------- ---- -------- ------ ------
TELE_Remote DBA LOAD_TEST 11 2 102655 401.00
TELE_Remote DBA LOAD_TEST 11 102657 25600 100.00
TELE_Remote DBA LOAD_TEST 11 128257 25600 100.00
SYSTEM PARTITION_TEST 11 153857 260 1.02
SYSTEM PARTITION_TEST 11 154117 260 1.02
SYSTEM PARTITION_TEST 11 154377 260 1.02
free space 11 154637 24564 95.95
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. |
 |
|
