生成trace文件
第一步:使用sqlplus登陆oracle,命令为 sqlplus /nolog。
第二步:使用sys用户登陆,命令为conn / as sysdba。
第三步:查询当前数据库trace的状态,命令为
show parameter sql_trace(如果value是false表示系统当前不会产生trace文件.采取如下操作让系统产生trace文件:),执行结果如下:
Name Type Value
-------------------------------------------
sql_trace boolean false
第四步:设置trace的状态为true,命令为alter session set sql_trace=true;(执行该命令只是将当前session的sql_trace做了设置,退出该session就失效了,如果想始终有效的话,就得执行命令:alter system set sql_trace=true)。
第五步:启动trace模式,命令为 set autotrace on;
第六步:执行 select count(*) from v$session; 即可看到结果,执行结果如下:
SQL> select count(*) from v$session;
COUNT(*)
----------
17
Execution Plan
----------------------------------------------------------
Plan hash value: 2376410614
--------------------------------------------------------------------------------
-------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |
--------------------------------------------------------------------------------
-------------
| 0 | SELECT STATEMENT | | 1 | 65 | 0 (0
)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 65 |
| |
| 2 | NESTED LOOPS | | 1 | 65 | 0 (0
)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSUSE | 1 | 52 | 0 (0
)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 13 | 0 (0
)| 00:00:01 |
--------------------------------------------------------------------------------
-------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0
AND
BITAND("S"."KSUSEFLG",1)<>0)
4 - filter("S"."KSUSEOPC"="E"."INDX")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
COUNT(*)
----------
17
Execution Plan
----------------------------------------------------------
Plan hash value: 2376410614
--------------------------------------------------------------------------------
-------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |
--------------------------------------------------------------------------------
-------------
| 0 | SELECT STATEMENT | | 1 | 65 | 0 (0
)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 65 |
| |
| 2 | NESTED LOOPS | | 1 | 65 | 0 (0
)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSUSE | 1 | 52 | 0 (0
)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 13 | 0 (0
)| 00:00:01 |
--------------------------------------------------------------------------------
-------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0
AND
BITAND("S"."KSUSEFLG",1)<>0)
4 - filter("S"."KSUSEOPC"="E"."INDX")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed