| Dropping private synonyms [message #673544] |
Fri, 23 November 2018 06:22  |
gazzag
Messages: 1119
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  |
John Watson
Messages: 8960
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 |
gazzag
Messages: 1119
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 |
cookiemonster
Messages: 13959
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
) is that dropping/creating those things is 7m transactions. Could redo be a hassle? How about setting commit_write='batch,nowait' for the duration?