Re: Slow response from Data Dictionary?

Suresh Bhat is right. It's an error in my text when i wrote: 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).

Sorry
Andreas Prusch

In article <01bdb599$4c335c20$a504fa80_at_mndnet>,   "Suresh Bhat" <suresh.bhat_at_mitchell-energy.com> wrote:
> Unfortunately
>
> command
> alter table table1 deallocate unused;
>
> frees up space only up to the High water mark, therefore it does not help
> much. For example, if you had 10000 rows inserted into table table1 and
> then deleted all of them your HWM is still at 10000 rows.
>
> Only command that reclaims all space in a table is the dangerous command
> TRUNCATE, with no option to rollback.
>
> As an aside, if you want to reclaim space upto HWM and keep 1M above it for
> expansion then use
> alter table table1 deallocate unused keep 1M;
>
> suresh.bhat_at_mitchell-energy.com
>
> andreas.prusch_at_sintec.de wrote in article
> <6p42og$gn2$1_at_nnrp1.dejanews.com>...
> > 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
> >
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Jul 23 1998 - 00:58:54 CDT