Re: Should/Can I shrink dictionary table?
Date: Wed, 15 Oct 2008 11:31:30 -0600
This entire thread of discussion is a textbook example of compulsive tuning disorder (thank you Gaja!) and, as Riyaj has stated, please first provide *any* evidence whatsoever that a performance problem even exists, before soliciting (and discussing) possible solutions. All that has been presented so far is the baseless conclusion that, since "SYS.VIEW$ has grown to 600Mb", it therefore is both a performance problem as well as a space problem. There is likely no evidence that this is a performance problem.
From the standpoint of a problem with space, if the application is adding views and then dropping them, then the SYS.VIEW$ table will eventually settle on a size dependent on the highest volume of data it has contained. Struggling to reduce it in size with this pattern of usage will be an endless (and useless) exercise, similar to the analogy of one person constantly loading 20 gallons of fertilizer into a flexible bag and then emptying it, while someone else keeps trying to keep that bag sized for holding only 10 gallons. The only way to keep the bag sized at 10 gallons is to change the behavior of the person cramming the fertilizer; never mind the question of why the bag must be sized at 10 gallons.
Maybe 600 Mb is the maximum size of the table based on the number of views existent at any one time; maybe not. But whether or not it grows indefinitely is completely dependent on the application's behavior, as in the analogy of bagging fertilizer...
So, the answer to the question in the "subject" line of this email thread is "no".
Riyaj Shamsudeen wrote:
> >> I am little concerned of oversized sys.view$ table and potential
> performance impact, since application relies heavily on views and
> sys.view$ may be accessed heavily by recursive sql.
> >> How can I reclaim (shrink) space from sys.view$? sys.view$ does
> have long column.
> There is an assumption here that as as view$ table size grows,
> performance will worsen. It is most probably an incorrect assumption.
> Performance worsens due to inefficient access path, not necessarily
> due to size of the table. Normal application processes and recursive
> SQL should not suffer from performance issues, as access to this
> table is through indices, not a full table scan.
> As Stephen pointed out, only supported way is to create dummy database
> and use transportable tablespaces & exp/imp to move everything to new
> database. Unless, you have a specific performance issue, this is not
> exactly warranted.