Optimising space anaysis when moving from Oracle 8 -> 9
Date: Sat, 1 Nov 2003 11:51:58 +0000 (UTC)
Message-ID: <bo06ot$d0e$1_at_hercules.btinternet.com>
We have a need to update our existing "space analyzer" for Oracle that runs on Oracle 8. The program generates and runs a PL/SQL script that analyses space usage in a Oracle database.
This routine is unnecessary and demanding and does e.g.:
analyze table CTXSYS.DR$DELETE compute statistics;
for each of the tables. There are less resource intensive methods to analyse space on later Oracle versions.
(See attached how this sql looks like when run on our test database. As I
understood it the problem is related to this method:
analyze table <table> compute statistics; analyze table <table> delete statistics;
)
We need to know how this PL/SQL can be improved to reduce the system overhead. We seek assistance on how we can rewrite the "space analyzer" program to make the PL/SQL script as efficient as possible.
TIA for any help
Jon
set newpage 0
set space 0
set linesize 32000
set pagesize 0
set echo off
set feedback off
set heading off
set colsep ','
set null ' 0'
/* Get info to determine block size */
select min(bytes), min(blocks) from dba_segments;
host echo '==='
select tablespace_name, username, bytes, max_bytes, blocks, max_blocks from dba_ts_quotas;
host echo '==='
select c.tablespace, sum(s.bytes) seg_bytes, sum(s.blocks) seg_blocks,
sum(s.extents) extents, sum(freebytes) free_bytes,
sum(freeblocks) free_blocks, sum(filebytes) filebytes,
sum(file_blocks) fileblocks,
round(100*sum(s.bytes)/sum(filebytes), 2) pct
from ( select d.tablespace_name tablespace, sum(d.bytes) filebytes,
sum(d.blocks) file_blocks, sum(f.bytes) freebytes,
sum(f.blocks) freeblocks
from ( select tablespace_name, sum(bytes) bytes, sum(blocks)
blocks
from dba_data_files
group by tablespace_name ) d,
( select tablespace_name, sum(bytes) bytes, sum(blocks)
blocks
from dba_free_space
group by tablespace_name ) f
where d.tablespace_name = f.tablespace_name (+)
group by d.tablespace_name ) c,
( select tablespace_name, sum(bytes) bytes, sum(blocks) blocks,
sum(extents) extents
from dba_segments
group by tablespace_name ) s
where c.tablespace = s.tablespace_name (+) group by c.tablespace;
host echo '==='
analyze table SYS.ATEMPTAB$ compute statistics;
analyze table CTXSYS.DR$PARAMETER compute statistics; analyze table CTXSYS.DR$CLASS compute statistics; analyze table CTXSYS.DR$OBJECT compute statistics; analyze table CTXSYS.DR$OBJECT_ATTRIBUTE compute statistics; analyze table CTXSYS.DR$OBJECT_ATTRIBUTE_LOV compute statistics; analyze table CTXSYS.DR$PREFERENCE compute statistics; analyze table CTXSYS.DR$PREFERENCE_VALUE compute statistics; analyze table CTXSYS.DR$INDEX compute statistics; analyze table CTXSYS.DR$INDEX_VALUE compute statistics; analyze table CTXSYS.DR$INDEX_OBJECT compute statistics; analyze table CTXSYS.DR$SQE compute statistics; analyze table CTXSYS.DR$THS compute statistics; analyze table CTXSYS.DR$THS_PHRASE compute statistics; analyze table CTXSYS.DR$THS_FPHRASE compute statistics; analyze table CTXSYS.DR$THS_BT compute statistics; analyze table CTXSYS.DR$SECTION_GROUP compute statistics; analyze table CTXSYS.DR$SECTION compute statistics; analyze table CTXSYS.DR$STOPLIST compute statistics; analyze table CTXSYS.DR$STOPWORD compute statistics; analyze table CTXSYS.DR$SUB_LEXER compute statistics; analyze table CTXSYS.DR$INDEX_SET compute statistics; analyze table CTXSYS.DR$INDEX_SET_INDEX compute statistics; analyze table CTXSYS.DR$SERVER compute statistics; analyze table CTXSYS.DR$PENDING compute statistics; analyze table CTXSYS.DR$WAITING compute statistics; analyze table CTXSYS.DR$DELETE compute statistics; analyze table CTXSYS.DR$INDEX_ERROR compute statistics; analyze table CTXSYS.DR$PARALLEL compute statistics; analyze table CTXSYS.DR$STATS compute statistics;
/* For tables */
select distinct tablespace_name, owner, table_name, empty_blocks, avg_space,
avg_row_len, num_rows, blocks
from dba_tables
where
(table_name='ATEMPTAB$' and owner='SYS')
or (table_name='DR$PARAMETER' and owner='CTXSYS') or (table_name='DR$CLASS' and owner='CTXSYS') or (table_name='DR$OBJECT' and owner='CTXSYS') or (table_name='DR$OBJECT_ATTRIBUTE' and owner='CTXSYS') or (table_name='DR$OBJECT_ATTRIBUTE_LOV' and owner='CTXSYS') or (table_name='DR$PREFERENCE' and owner='CTXSYS') or (table_name='DR$PREFERENCE_VALUE' and owner='CTXSYS') or (table_name='DR$INDEX' and owner='CTXSYS') or (table_name='DR$INDEX_VALUE' and owner='CTXSYS') or (table_name='DR$INDEX_OBJECT' and owner='CTXSYS') or (table_name='DR$SQE' and owner='CTXSYS') or (table_name='DR$THS' and owner='CTXSYS') or (table_name='DR$THS_PHRASE' and owner='CTXSYS') or (table_name='DR$THS_FPHRASE' and owner='CTXSYS') or (table_name='DR$THS_BT' and owner='CTXSYS') or (table_name='DR$SECTION_GROUP' and owner='CTXSYS') or (table_name='DR$SECTION' and owner='CTXSYS') or (table_name='DR$STOPLIST' and owner='CTXSYS') or (table_name='DR$STOPWORD' and owner='CTXSYS') or (table_name='DR$SUB_LEXER' and owner='CTXSYS') or (table_name='DR$INDEX_SET' and owner='CTXSYS') or (table_name='DR$INDEX_SET_INDEX' and owner='CTXSYS') or (table_name='DR$SERVER' and owner='CTXSYS') or (table_name='DR$PENDING' and owner='CTXSYS') or (table_name='DR$WAITING' and owner='CTXSYS') or (table_name='DR$DELETE' and owner='CTXSYS') or (table_name='DR$INDEX_ERROR' and owner='CTXSYS') or (table_name='DR$PARALLEL' and owner='CTXSYS') or (table_name='DR$STATS' and owner='CTXSYS');
analyze table SYS.ATEMPTAB$ delete statistics;
analyze table CTXSYS.DR$PARAMETER delete statistics; analyze table CTXSYS.DR$CLASS delete statistics; analyze table CTXSYS.DR$OBJECT delete statistics; analyze table CTXSYS.DR$OBJECT_ATTRIBUTE delete statistics; analyze table CTXSYS.DR$OBJECT_ATTRIBUTE_LOV delete statistics; analyze table CTXSYS.DR$PREFERENCE delete statistics; analyze table CTXSYS.DR$PREFERENCE_VALUE delete statistics; analyze table CTXSYS.DR$INDEX delete statistics; analyze table CTXSYS.DR$INDEX_VALUE delete statistics; analyze table CTXSYS.DR$INDEX_OBJECT delete statistics; analyze table CTXSYS.DR$SQE delete statistics; analyze table CTXSYS.DR$THS delete statistics; analyze table CTXSYS.DR$THS_PHRASE delete statistics; analyze table CTXSYS.DR$THS_FPHRASE delete statistics; analyze table CTXSYS.DR$THS_BT delete statistics; analyze table CTXSYS.DR$SECTION_GROUP delete statistics; analyze table CTXSYS.DR$SECTION delete statistics; analyze table CTXSYS.DR$STOPLIST delete statistics; analyze table CTXSYS.DR$STOPWORD delete statistics; analyze table CTXSYS.DR$SUB_LEXER delete statistics; analyze table CTXSYS.DR$INDEX_SET delete statistics; analyze table CTXSYS.DR$INDEX_SET_INDEX delete statistics; analyze table CTXSYS.DR$SERVER delete statistics; analyze table CTXSYS.DR$PENDING delete statistics; analyze table CTXSYS.DR$WAITING delete statistics; analyze table CTXSYS.DR$DELETE delete statistics; analyze table CTXSYS.DR$INDEX_ERROR delete statistics; analyze table CTXSYS.DR$PARALLEL delete statistics; analyze table CTXSYS.DR$STATS delete statistics;
exit; Received on Sat Nov 01 2003 - 12:51:58 CET