Dropping private synonyms [message #673544] |
Fri, 23 November 2018 06:22 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
gazzag
Messages: 1118 Registered: November 2010 Location: Bedwas, UK
|
Senior Member |
|
|
We have an application that front-ends an Oracle database. The way that the application is written means that every application user has private synonyms pointing to application schema objects. We have ~ 10,000 Oracle schemas which translates into ~ 3.5 million private synonyms.
Whenever the application vendor sends an upgrade, upgrading typically takes around 12 hours. Recently we discovered that dropping all of the private synonymns prior to the upgrade dramatically reduces the time taken and, therefore, the downtime. We then recreate the private synonyms after the upgrade before bringing the system back up.
The trouble is, dropping this amount of synonyms takes almost two hours. We do it by generating a 'DROP OWNER.SYNONYN_NAME' script with dynamic SQL. If possible, we would like to reduce this time to reduce the overall downtime during upgrade.
I wondered if anyone has had a similar experience or has any tips on how performance could be improved.
Please note: "I wouldn't start from here if I was you" is not a helpful answer It is what it is; I can't change that.
|
|
|
Re: Dropping private synonyms [message #673545 is a reply to message #673544] |
Fri, 23 November 2018 06:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8932 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
My first thought was to drop all the synonyms permanently, and put in a logon trigger with EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=APPS' but I guess your closing comment precludes that?
Dropping and creating 3.5m objects may be messing up your dictionary stat's hugely. Might it help to re-gather them before, after, and during the process?
|
|
|
Re: Dropping private synonyms [message #673546 is a reply to message #673545] |
Fri, 23 November 2018 06:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
gazzag
Messages: 1118 Registered: November 2010 Location: Bedwas, UK
|
Senior Member |
|
|
John Watson wrote on Fri, 23 November 2018 12:31I guess your closing comment precludes that?
Unfortunately yes, John. The application is provided by a third-party; we cannot alter the code. We experience an awful lot of sessions blocking each other so that will give you some idea of what/with whom I'm dealing with! Thanks for the stats suggestion I will give that a go.
|
|
|
|
|
Re: Dropping private synonyms [message #673550 is a reply to message #673549] |
Fri, 23 November 2018 08:05 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13923 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Divide the users into groups of say 1000.
Have a script that runs through the users in a given group and drops the synonyms for all of them.
Run the 10 scripts simultaneously.
|
|
|