OraFAQ Forum: Data Guard » how to get sql plan in standby database

Home » Server Options » Data Guard » how to get sql plan in standby database (10.2.0.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to get sql plan in standby database [message #547485]

Wed, 14 March 2012 07:33

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

Report message to a moderator

Re: how to get sql plan in standby database [message #547494 is a reply to message #547485]

Wed, 14 March 2012 07:59

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: how to get sql plan in standby database [message #547499 is a reply to message #547494]

Wed, 14 March 2012 08:09

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks Michel!

Report message to a moderator

Re: how to get sql plan in standby database [message #547502 is a reply to message #547499]

Wed, 14 March 2012 08:34

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

Report message to a moderator

Re: how to get sql plan in standby database [message #547504 is a reply to message #547502]

Wed, 14 March 2012 08:52

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: how to get sql plan in standby database [message #665459 is a reply to message #547504]

Wed, 06 September 2017 08:23

aitorit0
Messages: 4
Registered: September 2017

Junior Member

I found a solution to obtain explain plan on physical standby Smile

first execute your query and then execute it:
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST +cost +bytes'));

Report message to a moderator

Re: how to get sql plan in standby database [message #665460 is a reply to message #547504]

Wed, 06 September 2017 08:37

aitorit0
Messages: 4
Registered: September 2017

Junior Member

You can execute TABLE DBMS_XPLAN after run your query to obtain explain plan with the cost

SQL> select * from launch.ed_booking where id=1 and 1=0; -- query to obtain the plan

no rows selected

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID d059bxx501kpx, child number 0
-------------------------------------
select * from launch.ed_booking where id=1 and 1=0

Plan hash value: 2838877488

----------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ED_BOOKING | 1 | 308 | 1 (0)|
|* 3 | INDEX RANGE SCAN | IDX_META_ID | 1 | | 1 (0)|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NULL IS NOT NULL)
3 - access("ID"=1)

Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

27 rows selected.

Report message to a moderator

Re: how to get sql plan in standby database [message #665461 is a reply to message #547504]

Wed, 06 September 2017 08:38

aitorit0
Messages: 4
Registered: September 2017

Junior Member

Report message to a moderator

Re: how to get sql plan in standby database [message #665462 is a reply to message #547485]

Wed, 06 September 2017 08:38

aitorit0
Messages: 4
Registered: September 2017

Junior Member

Report message to a moderator

Re: how to get sql plan in standby database [message #665463 is a reply to message #665462]

Wed, 06 September 2017 09:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

OP has NOT visited this forum in more than 3+ years & this thread is more than 5+ years old.

Please do NOT resurrect dead zombie threads in the future.

Report message to a moderator