This content has been marked as final. Show 9 replies
You cannot use "explain plan" as it means inserting into "plan_table" table. So your standby is in read only mode, isnt it :)
Activate the sql trace and execute the query
Best would be convert your standby to snapshot standby :), Use it for any kind of testing like explain plan or even run that query and monitor, when you are satisfied come back to physical standby.
Perform the following steps to convert a physical standby database into a snapshot standby database:
Stop Redo Apply, if it is active.
On an Oracle Real Applications Cluster (RAC) database, shut down all but one instance.
Ensure that the database is mounted, but not open.
Issue the following SQL statement to perform the conversion:
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
The database is dismounted after conversion and must be restarted.
Edited by: Karan on Feb 8, 2013 12:00 AM
969257 wrote:I haven't tersted this (I don't have a suitable environment to hand) but how about this:
How can we execute explain plan for queries connecting to Physical standby database over DB link.
explain plan for select * from test.test_table@stby;
Getting ORA-16000 when trying to that.
Tried to set transaction to read only.
Then tried to run explain plan, that gives error for ora-02047 cannot join the distributed
Any insight will be appreciated.
In the primary create a database link,
create database link to_primary connect to system identified by manager using 'primary';
Then generate your plans like this,
explain plan into plan_table@to_primary for .....
Suppose your snapshot standby was created at scn 100, and you test on that till 500, now once you go back to physical standby , your database will be flashbacked with the help of guaranteed restore point back to scn 100, so all your objects will get lost which were created during snapshot standby working.