We have a Primary database environment with an physical standby using Active Data Guard. We have a tool that’s only allowed to access the database from the standby site. We have come up on a situation where a particular query being produced by the tool needs tuning. It has generated dozens of execution plans and some that it’s activity trying to use are bad plans that run very long. I’d like to create a SQL Profile and attach to the SQL ID hash values I know to run good.
The problem is since it’s open read only I can’t run SQL tuning adviser on the query. I get an error that the database is open read only. I’m unable to get the query to run on the primary site and produce the same SQL ID so that I can tune it there.
How do I tune a query on a read only standby database that has never run on the primary database?
Edit: We are running Oracle EE 12.1.0.2 on RHEL7.