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: 68625
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 messageGo to next message
Michel Cadot
Messages: 68625
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
Re: how to get sql plan in standby database [message #665459 is a reply to message #547504] Wed, 06 September 2017 08:23 Go to previous messageGo to next message
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'));

Re: how to get sql plan in standby database [message #665460 is a reply to message #547504] Wed, 06 September 2017 08:37 Go to previous messageGo to next message
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.
Re: how to get sql plan in standby database [message #665461 is a reply to message #547504] Wed, 06 September 2017 08:38 Go to previous messageGo to next message
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.
Re: how to get sql plan in standby database [message #665462 is a reply to message #547485] Wed, 06 September 2017 08:38 Go to previous messageGo to next message
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.
Re: how to get sql plan in standby database [message #665463 is a reply to message #665462] Wed, 06 September 2017 09:02 Go to previous messageGo to next message
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.
Re: how to get sql plan in standby database [message #673465 is a reply to message #665463] Tue, 20 November 2018 17:36 Go to previous messageGo to next message
stonedat420
Messages: 1
Registered: November 2018
Junior Member
It doesn't cost money.
It's good to share knowledge.
Updates are welcome.
I learnt something new.
Re: how to get sql plan in standby database [message #673490 is a reply to message #673465] Wed, 21 November 2018 06:54 Go to previous message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Irony Very Happy
Previous Topic: Advantage of having Standby redo logs
Next Topic: PING[ARCX]: Heartbeat failed to connect to standby 'XXX'. Error is 1034
Goto Forum:
  


Current Time: Thu Mar 28 16:42:24 CDT 2024