Re: Slow response from Data Dictionary?

Dropping Objects is mainly a thing of freeing space and updating dependencies in the data dictionary. Because of the behaviour, that deleting rows not freeing space tables may have many extents but no rows. By the way, select count(*) then works very slow, too. You have to free the space of those tables manually with alter table deallocate ... (see reference).
Analyzing dictionary objects don't work, because there are table locks on the tables the analyze command works on. In case of the dictionary tables, the tables will locked in which the analyze results should be written. That doesn't work. On the other hand dictionary performance is more important for overall database performance for internal use. See v$rowcache and v$librarycache for information. May be increase your shared pool size (30MB - 60 MB).

Good luck
Andreas Prusch

In article <01bdb4e9$f963f860$4244ddcc_at_jmara.connects.com>,   "John Mara" <john.mara_at_connects.com> wrote:
> We are running a rather large development server (7000 tables over 20
> schemas) and have some pretty slow response times when accessing the DBA_
> tables. This is somewhat understandable given the volume. Are there any
> tuning options to speed things up? I have heard that it is not a good idea
> to ANALYZE the Data Dictionary tables, that they are self maintaining.
>
> We tried an export/import of the entire database to compress extents, and
> surprisingly found that our response times got worse? In particular,
> dropping any objects that have storage allocated has become extremely slow
> (minutes for a table with no data???).
>
> Any suggestions would be appreciated. Thanks.
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Jul 22 1998 - 02:01:36 CDT