在standby备库中如何查看执行计划

环境:

OS:Red Hat Linux As5

DB:10.2.0.1

 

在Oracle standby备库中使用explain输出某个SQL的执行计划的时候报如下错误,原因是备库只能在read only模式,不能往plan_table里写数据.

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 open_mode from v$database;

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

 

使用explain不能输出执行计划,看来只能使用设置sql_trace通过分析跟踪文件来查看执行计划,步骤如下:

 

1.设置sql_trace

SQL> alter session set sql_trace=true;

 

2.执行某个SQL

select count(*) from hxl.tb_objects;

 

3.定位找到生成的跟踪文件

 

select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
       p.spid || '.trc' trace_file_name
  from (select p.spid
          from v$mystat m, v$session s, v$process p
         where m.statistic# = 1
           and s.sid = m.sid
           and p.addr = s.paddr
        ) p,
       (select t.instance
          from v$thread t, v$parameter v
         where v.name = 'thread'
           and (v.value = 0 or t.thread# = to_number(v.value))) i,
       (select value from v$parameter where name = 'user_dump_dest') dTRACE_FILE_NAME
----------------------------------------------------/u01/app/oracle/admin/oracl/udump/oracl_ora_5963.trc

 

4.使用tkprof格式化输出跟踪文件的内容

 

$tkprof /u01/app/oracle/admin/oracl/udump/oracl_ora_5963.trc /u01/app/oracle/output.txt

 

 

-- The End--