Re: Dropping Many Synonyms?

svrmgrl
> oradebug setospid {Unix pid for smon}
> oradebug event 10046 trace name context forever, level 4

    wait a bit

> oradebug event 10046 trace name context off

This will give you a trace file with bind variable values of smon's activity. Let us know the results. (NB change level 4 to level 8 to get a list of wait events, or level 12 to get bind values and wait events).

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Jon Derman wrote in message <2ofl3sgvar00hemial8vegjpple8tv2uem_at_4ax.com>...
>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:29:20 CST