Xref: alice comp.databases.oracle.server:75228
Path: alice!news-feed.fnsi.net!newsfeed.icl.net!newsfeed.icl.net!newspeer.clara.net!news.clara.net!remarQ-uK!rQdQ!supernews.com!remarQ.com!news.supernews.com!not-for-mail
From: Jon Derman <derman@optonline.net>
Newsgroups: comp.databases.oracle.server
Subject: Dropping Many Synonyms?
Date: Tue, 23 Nov 1999 11:23:24 -0500
Organization: Posted via Supernews, http://www.supernews.com
Lines: 30
Message-ID: <2ofl3sgvar00hemial8vegjpple8tv2uem@4ax.com>
X-Complaints-To: newsabuse@supernews.com
X-Newsreader: Forte Agent 1.7/32.534
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Here's another hypothetical/testing question related to my last
scenario: 

BTW, this is Oracle 7.3.3.6 on DEC Alpha OpenVMS 7.1.

Again, let's say I have 1 million private synonyms that I want to
drop. I find that after dropping a large number of synonyms, the
shutdown that follows takes many hours to complete. I find that
increasing db_block_buffers and shared_pool_size can greatly improve
the speed of the shutdown, but it still takes a long time. 

For example, after increasing the init.ora parameters as high as my
test box can accommodate (db_block_buffers=85,000 and
shared_pool_size=250,000,000): Drop 25,000 synonyms, then wait 5 hours
for a normal shutdown to complete. Drop 50,000 synonyms, then wait 10
hours. Drop 100,000 synonyms, wait 20 hours. 

During the shutdown, I can see at the OS level that SMON is cranking
away like mad the whole time.  I'm assuming that it is doing some kind
of cleanup work related to all those dropped synonyms. 

Any suggestions as to what I might do to make this process go faster?
Any idea just what it is that SMON is doing all that time? 

Any and all comments would be greatly appreciated! 

Thank you. 

--Jon