Fabio,
I will answer your questions below...
On Tue, 8 Jun 1999 fparacchini@alteanet.it wrote:
> Ari,
> pardon me if I contact you directly, but it seems that after days of
> browsing you're the only one with the answer.
No problem. I hope that I can help.
>
> The problem is this: I have an Oracle8 DB for BaaN IV (an ERP system) on
> IBM AIX 4.2 that shows a very bad response time with table creation and
> deletion. I tried to optimize the system, that now performs reasonably fast
> with operations on tables (insert, update, delete), and for what I found
> the symptom is an excessive reading of the disk where the SYSTEM tablespace
> resides. Normally the disks are not busy and there are a few Mb free at the
> OS level.
> I did an ANALYZE TABLE COMPUTE STATISTICS on every table of SYS, but the
> SYSTEM disk still works a lot.
> I found that the DB setup had a %increase of the SYSTEM tablespace of 50,
> and that seems bad for tablespace fragmentation, based on your
> explanations. I presume that now SYSTEM is badly fragmented, and needs a
> defragmentation.
>
> The questions are:
> 1 - Are my assumption correct ? SYSTEM fragmentation really impairs the
> performance of CREATE and DROP ?
I have not seen this behavior before (slow DROP/CREATEs). With Oracle8,
you can have dozens or even hundreds of extents before there is a
performance problem. Oracle does say that you should NOT do statistics on
any SYS or SYSTEM object though.
The most likely cause is that you have a rollback segment or table/index
incorrectly stored in the SYSTEM tablespace.
One thing I do know is that if you drop and recreate a package or stored
procedure, some of the data dictionary tables grow and things slow down.
The only way to get around this is to drop and recreate the database (see
next step).
> 2 - How can I defragment SYSTEM, since it is not possible to export, delete
> and import like the other tablespaces ?
The only way is to export the entire database, drop the entire database
(shut it down and remove all files), recreate the database, and import the
database. If you do this be sure to do a full cold backup first!
> 3 - Are there any other issues that I can check for this
behaviour ? >
Check to see if any objects (rollbacks, tables, etc.) are stored
improperly in SYSTEM:
SELECT * FROM DBA_OBJECTS WHERE TABLESPACE_NAME = 'SYSTEM'
AND OWNER NOT IN ('SYS','SYSTEM');
SELECT * FROM DBA_ROLLBACK_SEGS WHERE TABLESPACE_NAME = 'SYSTEM';
Look at the wait statistics, the hit ratios, see if there is memory
swapping/paging on the system.
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 300+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
> Thank you in advance for your patience, I look forward for your helpful
> answer.
> Fabio Paracchini
> FParacchini@alteanet.it
Back to Ari Kaplan's Home Page