Dropping Many Synonyms?

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 Received on Tue Nov 23 1999 - 10:23:24 CST