show parameter diagnostic_dest;
sql> show parameter diagnostic_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /opt/oracle/app
linux命令查看如下：
ll /u01/app/oracle/diag/rdbms/orcl/orcl/trace/
sql> show parameter dump;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /opt/oracle/app/diag/rdbms/orc
l/orcl/trace
core_dump_dest string /opt/oracle/app/diag/rdbms/orc
l/orcl/cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /opt/oracle/app/diag/rdbms/orc
l/orcl/trace
诊断文件类型及作用：
[oracle@s80sit ~]$ ll /opt/oracle/app/diag/rdbms/orcl/orcl/trace/
-rw-r----- 1 oracle oinstall 213614 Aug 24 12:35 alert_orcl.log
-rw-r----- 1 oracle oinstall 5008 Aug 11 22:00 orcl_cjq0_2829.trc
-rw-r----- 1 oracle oinstall 380 Aug 11 22:00 orcl_cjq0_2829.trm
-rw-r----- 1 oracle oinstall 3088 Aug 23 22:00 orcl_cjq0_3527.trc
-rw-r----- 1 oracle oinstall 240 Aug 23 22:00 orcl_cjq0_3527.trm
-rw-r----- 1 oracle oinstall 962 Aug 1 22:27 orcl_ckpt_2582.trc
-rw-r----- 1 oracle oinstall 59 Aug 1 22:27 orcl_ckpt_2582.trm
-rw-r----- 1 oracle oinstall 997 Jul 27 02:00 orcl_vkrm_815.trc
-rw-r----- 1 oracle oinstall 71 Jul 27 02:00 orcl_vkrm_815.trm
-rw-r----- 1 oracle oinstall 1001 Jul 30 02:00 orcl_vkrm_9998.trc
-rw-r----- 1 oracle oinstall 72 Jul 30 02:00 orcl_vkrm_9998.trm
-rw-r----- 1 oracle oinstall 1579 Jul 27 09:54 orcl_vktm_2560.trc
-rw-r----- 1 oracle oinstall 127 Jul 27 09:54 orcl_vktm_2560.trm
-rw-r----- 1 oracle oinstall 1467 Aug 19 00:38 orcl_vktm_3049.trc
-rw-r----- 1 oracle oinstall 105 Aug 19 00:38 orcl_vktm_3049.trm
1： alterSID.log -----background trace files （后台进程跟踪文件）
2： trace files -----user trace file （用户trace 文件）
alert_<sid>.log
数据库的启动、停止
记录所有非默认值的初始化参数
记录日志的切换情况
记录检查点的完成情况
记录数据库工作时遭遇的错误信息
后台进程的跟踪文件：
<sid>_进程名字_进程pid.trc
记录后台进程工作时的状态信息和报错信息
只与故障诊断相关，与性能无关！
通过警报日志的报错信息概要找到有意义的trc文件
用户进程的跟踪文件：
<sid>_ora_服务进程的系统pid.trc
记录user process所发出的信息
可以通过命令截获user process发出的sql语句
与故障诊断和性能调整都相关
下面实战
打开指定用户的后台跟踪功能
找到想跟踪的用户:
select sid,serial#,username,machine from v$session where username='SCOTT'
select * from dept;
select sid,machine from v$session where username='SCOTT';
SID SERIAL# USERNAME
---------- ---------- ------------------------------
MACHINE
----------------------------------------------------------------
15 23 SCOTT
oracle0.example.com
用系统包打开跟踪
exec dbms_system.SET_sql_TRACE_IN_SESSION(15,23,true);
exec dbms_system.SET_sql_TRACE_IN_SESSION(15,false);
查找跟踪文件：
select spid from v$process p,v$session s where p.addr=s.paddr and s.sid=15;
sql> select spid from v$process p,v$session s where p.addr=s.paddr and s.sid=15;
SPID
------------------------
3629
trace
$ORACLE_SID_ora_pid.trc
orcl_ora_3629.trc
跟踪文件:
Trace file: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3791.trc
使用tkprof程序格式化用户跟踪文件
tkprof orcl_ora_3629.trc 1.txt
使用tkprof程序格式化用户跟踪文件的时候屏蔽无用的第归sql
tkprof orcl_ora_3629.trc 1.txt sys=no
cat 1.txt 内容如下：
TKPROF: Release 11.2.0.1.0 - Development on Wed Aug 24 15:59:34 2016
Copyright (c) 1982,2009,Oracle and/or its affiliates. All rights reserved.
Trace file: /opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_30360.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
sql ID: 3154rqzb8xudy
Plan Hash: 3383998547
select *fromdeptcall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 9 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.00 0.00 5 8 0 4------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 0.00 0.00 5 17 0 4Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 84Rows Row Source Operation------- --------------------------------------------------- 4 TABLE ACCESS FULL DEPT (cr=8 pr=5 pw=0 time=0 us cost=3 size=80 card=4)********************************************************************************OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 9 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.00 0.00 5 8 0 4------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 0.00 0.00 5 17 0 4Misses in library cache during parse: 1OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 3 0.00 0.00 0 0 0 0Fetch 3 0.00 0.00 0 9 0 3------- ------ -------- ---------- ---------- ---------- ---------- ----------total 7 0.00 0.00 0 9 0 3Misses in library cache during parse: 1Misses in library cache during execute: 1 1 user sql statements in session. 3 internal sql statements in session. 4 sql statements in session.********************************************************************************Trace file: /opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_30360.trcTrace file compatibility: 11.1.0.7Sort options: default 1 session in tracefile. 1 user sql statements in trace file. 3 internal sql statements in trace file. 4 sql statements in trace file. 2 unique sql statements in trace file. 56 lines in trace file. 0 elapsed seconds in trace file.
