| |
 |
|
Monitoring Size of
Tables
Oracle Tips by Burleson Consulting |
One method to determine whether your default
storage sizing is correct for a tablespace is to monitor the extents
for each of the tables that reside in the tablespace. Another method
is to monitor the used space against the available space for each
table. Scripts to perform these functions are shown in Sources 10.2
and 10.3; Listing 10.2 shows the output from the report in Source
10.2.
SOURCE 10.2 SQL*Plus report to show extents
for each table in each tablespace.
REM
REM NAME
: EXTENTS.SQL
REM FUNCTION
: GENERATE EXTENTS REPORT
REM USE
: FROM SQLPLUS OR OTHER FRONT END
REM LIMITATIONS : NONE
REM
CLEAR COLUMNS
COLUMN segment_name HEADING 'Segment'
FORMAT A15
COLUMN tablespace_name HEADING 'Tablespace' FORMAT A10
COLUMN owner
HEADING 'Owner' FORMAT A10
COLUMN segment_type HEADING 'Type'
FORMAT A10
COLUMN size
HEADING 'Size' FORMAT
999,999,999
COLUMN extents HEADING
'Current|Extents'
COLUMN max_extents HEADING 'Max|Extents'
COLUMN bytes HEADING 'Size|(Bytes)'
SET PAGESIZE 58 NEWPAGE 0 LINESIZE 130 FEEDBACK OFF
SET ECHO OFF VERIFY OFF
ACCEPT extents PROMPT 'Enter max number of extents: '
BREAK ON tablespace_name SKIP PAGE ON owner
START TITLE132 "Extents Report"
DEFINE output = rep_out\&db\extent
SPOOL &output
SELECT tablespace_name,
segment_name,
extents,
max_extents,
bytes,
owner "owner",
segment_type
FROM DBA_segments
WHERE extents >= &extents AND owner LIKE UPPER('%&owner%')
ORDER BY tablespace_name,owner,segment_type,segment_name;
SPOOL OFF
CLEAR COLUMNS
CLEAR BREAKS
SET TERMOUT ON FEEDBACK ON VERIFY ON
UNDEF extents
UNDEF owner
TTITLE OFF
UNDEF OUTPUT
PAUSE Press enter to continue
LISTING 10.2 Example of output from the
extents script.
Date:
06/12/97
Page: 1
Time: 09:55 PM
Extents Report
SYSTEM
ORTEST1 database
Current Max Size
Tablespace Segment
Extents Extents (Bytes) Owner Type
---------- -------------- ------- ------- ------- ---------
-------
SYSTEM C_OBJ#
10 249 1323008 SYS
CLUSTER
C_TOID_VERSION#
7 249 352256
CLUSTER
I_ARGUMENT1
6 249 229376
INDEX
I_COL1
8 249 565248
INDEX
I_COL2
6 249 258048
INDEX
I_COL3
5 249 176128
INDEX
I_DEPENDENCY1
5 249 147456
INDEX
I_DEPENDENCY2
5 249 147456
INDEX
I_OBJ2
5 249 147456
INDEX
I_Source1
11 249 1765376
INDEX
SYSTEM
16 249 983040
ROLLBACK
ACCESS$
6 249 229376
TABLE
ARGUMENT$
6 249 229376
TABLE
COM$
5 249 147456
TABLE
DEPENDENCY$
5 249 147456
TABLE
IDL_CHAR$
5 249 147456
TABLE
IDL_SB4$
6 249 229376
TABLE
IDL_UB1$
9 249 802816
TABLE
IDL_UB2$
10 249 1191936
TABLE
OBJ$
6 249 229376
TABLE
Source$
13 249 3915776
TABLE
VIEW$
9 249 802816
SOURCE 10.3
Actual size report.
rem
*************************************************************
rem
rem NAME: ACT_SIZE.sql
rem
rem HISTORY:
rem Date
Who
What
rem --------------- ----------------
-------------------------------
rem 09/??/90 Maurice C. Manton Creation
for IOUG
rem 12/23/92 Michael Brouillette Assume
TEMP_SIZE_TABLE exists.Use
rem DBA info.
rem Prompt for user name. Spool file = owner.
rem 07/15/96 Mike Ault Updated for Oracle 7.x, added
indexes
rem 06/12/97 Mike Ault Updated for Oracle 8.x (use
DBMS_ROWID)
rem FUNCTION: Will show actual blocks used vs allocated
for all tables rem for a user
rem INPUTS: owner = Table owner name.
rem ************************************************************
ACCEPT owner PROMPT 'Enter table owner name: '
SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF RECSEP OFF PAGES 0
COLUMN db_block_size NEW_VALUE blocksize NOPRINT
TTITLE OFF
DEFINE cr='chr(10)'
DEFINE qt='chr(39)'
TRUNCATE TABLE temp_size_table;
SELECT value db_block_size FROM v$parameter WHERE name='db_block_size';
SPOOL fill_sz.sql
SELECT
'INSERT INTO temp_size_table'||&&cr||
'SELECT '||&&qt||segment_name||&&qt||&&cr||
',COUNT(DISTINCT(dbms_rowid.rowid_block_number(rowid))) blocks'||&&cr||
'FROM &&owner..'||segment_name, ';'
FROM
DBA_segments
WHERE
segment_type ='TABLE'
AND owner = UPPER('&owner');
SPOOL OFF
SPOOL index_sz.sql
SELECT
'CREATE TABLE stat_temp AS SELECT * FROM index_stats;'||&&cr||
'TRUNCATE TABLE stat_temp;'
FROM
dual;
SELECT
'ANALYZE INDEX '||owner||'.'||index_name||' VALIDATE STRUCTURE;'||&&cr||
'INSERT INTO stat_temp SELECT * FROM index_stats;'||&&cr||
'COMMIT;'
FROM
DBA_indexes
WHERE
owner=UPPER('&owner');
SPOOL OFF
SET FEEDBACK ON TERMOUT ON LINES 132
START index_sz.sql
INSERT INTO temp_size_table SELECT name,trunc(used_space/&&blocksize)
FROM stat_temp;
DROP TABLE stat_temp;
DEFINE temp_var = &&qt;
START fill_sz
HOST rm fill_size_table.sql
DEFINE bs = '&&blocksize K'
COLUMN t_date NOPRINT NEW_VALUE t_date
COLUMN user_id NOPRINT NEW_VALUE user_id
COLUMN segment_name FORMAT A25
HEADING "SEGMENT|NAME"
COLUMN segment_type FORMAT A7
HEADING "SEGMENT|TYPE"
COLUMN extents
FORMAT 999 HEADING
"EXTENTS"
COLUMN kbytes
FORMAT 999,999,999 HEADING "KILOBYTES"
COLUMN blocks
FORMAT 9,999,999 HEADING "ALLOC.|&&bs|BLOCKS"
COLUMN act_blocks FORMAT
9,999,990 HEADING "USED|&&bs|BLOCKS"
COLUMN pct_block
FORMAT 999.99 HEADING "PCT|BLOCKS|USED"
START title132 "Actual Size Report for &owner"
SET PAGES 55
BREAK ON REPORT ON segment_type SKIP 1
COMPUTE SUM OF kbytes ON segment_type REPORT
SPOOL rep_out\&db\&owner
SELECT
segment_name,
segment_type,
SUM(extents) extents,
SUM(bytes)/1024 kbytes,
SUM(a.blocks) blocks,
NVL(MAX(b.blocks),0) act_blocks,
(MAX(b.blocks)/SUM(a.blocks))*100 pct_block
FROM
sys.DBA_segments a,
temp_size_table b
WHERE
segment_name = UPPER( b.table_name )
GROUP BY
segment_name,
segment_type
ORDER BY
segment_type,
segment_name;
SPOOL OFF
TRUNCATE TABLE temp_size_table;
SET TERMOUT ON FEEDBACK 15 VERIFY ON PAGESIZE 20 LINESIZE 80 SPACE 1
UNDEF qt
UNDEF cr
TTITLE OFF
CLEAR COLUMNS
CLEAR COMPUTES
PAUSE press enter to continue
The script to calculate the actual size of a
table or index (shown in Source 10.3) uses the TEMP_SIZE_TABLE, which
is created with the script shown in Source 10.4. As shown, the
act_size script will work only with Oracle8 and Oracle8i. To use
act_size with Oracle7, replace the call to the
dbms_rowid.rowid_block_number procedure with SUBSTR( ROWID,1,8). The
act_size.sql report cannot resolve index-only overflow tables or
complex objects involving nested tables. Output from the act_size
report is shown in Listing 10.3.
SOURCE 10.4 Script to create TEMP_SIZE_TABLE.
rem
rem Create
temp_size_table for use by actsize.sql
rem
CREATE TABLE
temp_size_table (
table_name VARCHAR2(64),
blocks NUMBER);
LISTING 10.3 Example of output of actual size
report.
Date:
06/12/97
Page: 1
Time: 11:28 PM
Actual Size Report for tele_DBA
SYSTEM
ORTEST1 database
ALLOC. USED
PC
SEGMENT
SEGMENT
4096 K 4096 K
BLOCKS
NAME
TYPE EXTENTS KILOBYTES
BLOCKS BLOCKS
USED
------------------------- ------- -------
--------- ------
------ ----
FK_ACCOUNT_EXECS_1
INDEX 1
12 3
0 .00
FK_ADDRESSES_1
1 10,240
2,560 0 .00
FK_ADDRESSES_2
1 51,200
12,800 2,480 19.38
FK_ADDRESSES_3
1 51,200
12,800 2,967 23.18
FK_FRANCHISE_CODES_1
1 10,240
2,560 461 18.01
FK_SIC_CODES_1 1 51,200
12,800 3,893 30.41
FK_USERS_1 1 102,400 25,600
0 .00
LI_LOAD_TEST 1 40,960
10,240 5,536 54.06
OID_CLIENTSV8
1 20
5 0 .00
OID_EARNINGS_INFO_NMBRS 1 20
5 0 .00
...
PK_ADDRESSES 1 102,400 25,600
5,203 20.32
PK_CLIENTS
1 102,400
25,600 3,212 12.55
PK_EARNINGS_INFO_NMBRS 1 102,400 25,600
2,780 10.86
PK_FRANCHISE_CODES 1 51,200
12,800 573 4.48
PK_SIC_CODES 1 51,200
12,800 4,863 37.99
UI_EARNINGS_INFO_NMBRS_ID 1 51,200
12,800 4,466 34.89
UK_CLIENTS 1 51,200
12,800 4,292 33.53
UK_LOAD_TEST 1 51,200
12,800 4,650 36.33
*******
---------
sum
1,116,564
ACCOUNT_EXECS
TABLE 1
12 3
0 .00
ADDRESSES 1 204,800 51,200
32,827 64.12
ADDRESS_TEST 1 20
5 1 20.00
CLIENTS 2 307,200 76,800
61,587 80.19
....
EARNINGS_INFO_NMBRS 1 204,800 51,200
28,485 55.63
EARNINGS_INFO_NUMBERSV8 1 20,480
5,120 0 .00
EMPLOYEES 1 20
5 0 .00
FRANCHISE_CODES 1 76,800
19,200 803 4.18
INTERACTION_LOG_ACTIVITY 1 12
3 0 .00
LOAD_TEST 3 615,420 153,855
140,441 91.28
LOOKUPS 1 12
3 0 .00
SIC_CODES
1 102,400
25,600 16,765 65.49
USERS 1 204,800 51,200
1 .00
*******
---------
sum
2,036,056
-------
sum
3,152,620
Each of
the above reports gives specific information. In the report from
Source 10.2, if a table shows more than 1,000 extents, the DBA should
review its size usage via the report in Source 10.3, and rebuild the
table with better storage parameters. In the report in Listing 10.3,
if a table shows that it is using far less space than it has been
allocated, and history has shown it won’t grow into the space, it
should be re-created accordingly.
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. |
 |
|
