Home » RDBMS Server » Server Administration » Dropping private synonyms (Solaris 11.3 / Oracle 12.2.0.2)
Dropping private synonyms [message #673544] Fri, 23 November 2018 06:22 Go to next message
gazzag
Messages: 1063
Registered: November 2010
Location: Bristol, 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 Wink 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 messageGo to next message
John Watson
Messages: 7777
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 messageGo to next message
gazzag
Messages: 1063
Registered: November 2010
Location: Bristol, UK
Senior Member
John Watson wrote on Fri, 23 November 2018 12:31
I 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 #673547 is a reply to message #673546] Fri, 23 November 2018 06:51 Go to previous messageGo to next message
John Watson
Messages: 7777
Registered: January 2010
Location: Global Village
Senior Member
Another idea (I'm full of them this morning Smile ) is that dropping/creating those things is 7m transactions. Could redo be a hassle? How about setting commit_write='batch,nowait' for the duration?

I did think of using CREATE SCHEMA to put the whole lot into one transaction, but that works only for create table and view and grant.
Re: Dropping private synonyms [message #673549 is a reply to message #673544] Fri, 23 November 2018 07:09 Go to previous messageGo to next message
EdStevens
Messages: 1044
Registered: September 2013
Senior Member
You may not be able to control the structure and expectations of the app, but you obviously control the creation of the synonyms. If every user has the same set of private synonyms, why not just drop all of them (the synonyms, not the users Wink ) and replace them with one set of public synonyms?
Re: Dropping private synonyms [message #673550 is a reply to message #673549] Fri, 23 November 2018 08:05 Go to previous message
cookiemonster
Messages: 13461
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.
Previous Topic: Table size with BLOB column (CM merged 2)
Next Topic: Determine which Standard Edition is installed
Goto Forum:
  


Current Time: Sat Feb 23 13:31:54 CST 2019