在Oracle中简单的查看SQL执行计划

如果有多个SQL的话可以把SQL写入一个文件保存在服务器端,例如test.sql

首先连接到数据库中:

sqlplus apps/apps

然后开启trace

SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;

然后依次执行以下命令:

1. alter session set max_dump_file_size='UNLIMITED';

2. alter session set tracefile_identifier='APPSPERF';(用于标记trace文件名称)

3. alter session set events '10046 trace name context forever, level 8';

4. alter session set statistics_level = 'ALL';

5. alter session set timed_statistics=true;

6. Execute the plsql/sql(@/path_of_test.sql)

7. exec dbms_session.reset_package;

8. exit.

然后可以找到 名称中有APPSPERF的trace文件.

可以通过以下命令来确定trace文件的地址:

select u_dump.value || '/' || instance.value || '_ora_' || v$process.spid ||
       nvl2(v$process.traceid, '_' || v$process.traceid, null) || '.trc' "Trace File"
  from V$PARAMETER u_dump
 cross join V$PARAMETER instance
 cross join V$PROCESS
  join V$SESSION
    on v$process.addr = V$SESSION.paddr
 where u_dump.name = 'user_dump_dest'
   and instance.name = 'instance_name'
   and V$SESSION.audsid = sys_context('userenv', 'sessionid');

将.trc文件转为可读的.prf文件并查看执行计划:

$ tkprof orcl102_ora_3064.trc output.prf EXPLAIN=scott/tiger SYS=NO

.