Home » Server Options » Data Guard » how to get sql plan in standby database (10.2.0.1)
how to get sql plan in standby database [message #547485] Wed, 14 March 2012 07:33 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Dear all,
I want to get the execute plan of a sql in standby database(read only),but failed,how can i do?

SQL> explain plan for select count(1) from hxl.tb_objects;
explain plan for select count(1) from hxl.tb_objects
                        *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-16000: database open for read-only access


SQL> select count(1) from hxl.tb_objects;

  COUNT(1)
----------
  22238018

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ ONLY

Re: how to get sql plan in standby database [message #547494 is a reply to message #547485] Wed, 14 March 2012 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 58960
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Activate the sql trace and execute the query.
You cannot use "explain plan" as it means inserting into "plan_table" table.

Regards
Michel
Re: how to get sql plan in standby database [message #547499 is a reply to message #547494] Wed, 14 March 2012 08:09 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks Michel!
Re: how to get sql plan in standby database [message #547502 is a reply to message #547499] Wed, 14 March 2012 08:34 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
how to set the autotrace to show the explain plan?
SQL> set autotrace on explain;
SQL> select count(1) from hxl.tb_objects;

  COUNT(1)
----------
  22238018


Execution Plan
----------------------------------------------------------
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-16000: database open for read-only access
Re: how to get sql plan in standby database [message #547504 is a reply to message #547502] Wed, 14 March 2012 08:52 Go to previous message
Michel Cadot
Messages: 58960
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I said "sql trace" not "autotrace", "autotrace" makes an "explain plan" behind the scene.
"SQL trace" is activated using "alter session set sql_trace=true;".

Regards
Michel
Previous Topic: Archive Gap Script
Next Topic: Rman backup in Dataguard
Goto Forum:
  


Current Time: Wed Sep 03 03:43:46 CDT 2014

Total time taken to generate the page: 0.06854 seconds