监控操作系统进程对应SQL语句
主要讲解了如何通过操作系统,SQL语句,TKPROF来进行ORACLE使用情况的分析.对解决一些SQL优化问题有帮助.
一、TOP情况
System: cqrtc1 Tue Apr 10 17:42:45 2007
Load averages: 0.73, 0.72, 0.72
300 processes: 259 sleeping, 41 running
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 0.80 69.6% 0.0% 3.6% 26.8% 0.0% 0.0% 0.0% 0.0%
1 0.65 83.3% 0.0% 0.4% 16.3% 0.0% 0.0% 0.0% 0.0%
2 0.93 59.1% 0.0% 5.4% 35.5% 0.0% 0.0% 0.0% 0.0%
3 0.72 31.0% 0.0% 3.4% 65.7% 0.0% 0.0% 0.0% 0.0%
4 0.81 11.1% 0.0% 19.2% 69.6% 0.0% 0.0% 0.0% 0.0%
5 0.57 11.9% 0.0% 5.2% 82.9% 0.0% 0.0% 0.0% 0.0%
6 0.76 22.4% 0.0% 4.6% 73.0% 0.0% 0.0% 0.0% 0.0%
7 0.62 32.7% 0.0% 4.8% 62.5% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 0.73 40.1% 0.0% 6.0% 54.0% 0.0% 0.0% 0.0% 0.0%
Memory: 17519428K (4956172K) real, 19105896K (6173700K) virtual, 11877864K free Page# 1/8
CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
0 ? 13597 oracle 241 20 11303M 17216K run 8:13 80.39 80.25 oracleora8
6 ? 29940 oracle 154 20 11288M 1792K sleep 0:09 94.02 36.99 oracleora8
4 ? 15943 oracle 154 20 11289M 2880K sleep 14:57 33.40 33.34 oracleora8
7 ? 15733 billing 168 20 378M 366M sleep 3:42 18.30 18.27 stand
7 ? 15958 oracle 154 20 11289M 2880K sleep 9:54 16.58 16.55 oracleora8
7 ? 15922 oracle 154 20 11289M 2880K sleep 17:20 12.25 12.23 oracleora8
6 ? 15314 root 154 20 5908K 708K run 0:22 11.07 11.05 ftpd:
2 ? 15981 oracle 154 20 11289M 2880K sleep 9:11 9.52 9.50 oracleora8
7 ? 15894 oracle 154 20 11289M 2880K sleep 10:32 7.45 7.44 oracleora8
2 ? 15736 billing 168 20 378M 356M sleep 1:03 5.15 5.14 stand
1 ? 15731 billing 168 20 378M 366M sleep 5:10 4.77 4.76 stand
2 ? 15911 oracle 154 20 11289M 2880K sleep 11:09 3.07 3.07 oracleora8
1 ? 15977 oracle 154 20 11289M 2880K sleep 8:09 2.95 2.95 oracleora8
4 ? 1792 root -16 20 40496K 18820K run 122:53 2.76 2.76 midaemon
4 ? 24571 emedia 168 20 26892K 5492K sleep 144:57 2.68 2.68 emec_18
7 ? 24114 oracle 156 20 11295M 8960K sleep 3:20 2.10 2.09 ora_dbw1_ora8
3 ? 24112 oracle 156 20 11295M 8960K sleep 3:23 2.08 2.07 ora_dbw0_ora8
4 ? 24118 oracle 156 20 11295M 8960K sleep 3:20 2.08 2.07 ora_dbw3_ora8
3 ? 24116 oracle 156 20 11295M 8960K sleep 3:21 2.06 2.05 ora_dbw2_ora8
7 ? 15979 oracle 154 20 11289M 2880K sleep 6:22 1.97 1.97 oracleora8
6 ? 15903 oracle 154 20 11289M 2880K sleep 12:03 1.85 1.85 oracleora8
2 ? 15950 oracle 203 20 11289M 2880K run 8:04 1.62 1.62 oracleora8
5 ? 41 root 152 20 16032K 16032K run 91:31 1.58 1.58 vxfsd
6 ? 2197 root 152 20 286M 86384K run 83:45 1.55 1.55 java
5 ? 24124 oracle 156 20 11289M 2880K sleep 0:57 1.51 1.51 ora_smon_ora8
2 ? 15932 oracle 154 20 11289M 2880K sleep 8:44 1.43 1.43 oracleora8
3 ? 15734 billing 168 20 378M 366M sleep 1:25 1.36 1.36 stand
2 ? 27582 weblogic 152 20 560M 298M run 727:30 1.26 1.25 java
7 ? 24120 oracle 156 20 11288M
1792K sleep 7:49 1.14 1.14 ora_lgwr_ora8
0 ? 15983 oracle 154 20 11289M 2880K sleep 8:33 1.11 1.10 oracleora8
2 ? 17281 root -27 20 11128K 7236K run 45:09 0.93 0.93 cmcld
6 ? 24685 emedia 168 20 23512K 3564K sleep 52:39 0.89 0.88 emec_23
4 ? 15735 billing 168 20 378M 366M sleep 2:56 0.82 0.81 stand
7 ? 24122 oracle 156 20 11288M 1792K sleep 2:07 0.60 0.60 ora_ckpt_ora8
3 ? 1404 root 152 20 220M 25820K run 35:50 0.51 0.51 java
7 pts/tk 26831 root 168 20 7224K 988K sleep 0:34 0.44 0.44 top
5 ? 27576 weblogic 152 20 290M 85048K run 14:12 0.44 0.44 java
3 pts/tq 6443 oracle 178 20 7224K 988K run 0:03 0.41 0.40 top
二、通过进程ID查看ORACLE的SID
比如，我们看到上面TOP语句中PID，我们可以根据这个PID，提取出ORACLE对应会话ID。
SQL>select ses.sid,ses.serial#,ses.username from v$session ses,v$process pro where pro.spid=&spid and ses.paddr=pro.addr;
在出现SPID提示时，输入上面显示的PID，我们这里输入15943
SQL> select ses.sid,ses.serial#,ses.username from v$session ses,v$process pro where pro.spid=&spid and ses.paddr=pro.addr;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
106 5654 BILL
这里，查询到15943进程对应的会话ID为106。
三、跟踪会话
如果我们只看一次SQL语句，可能无法判断程序性能，因此，我们最好跟踪一段时间该进程，这时，我们就要使用ORACLE提供的sys.dbms_system.set_sql_trace_in_session
函数进行跟踪，比如我们对上面的会话ID为106的会话进行跟踪，直接执行以下语句即可：
SQL> exec sys.dbms_system.set_sql_trace_in_session(106,5654,true);
--在一段时间后，一般是30分钟后，再把这个跟踪关闭。注意，一定要关闭，否则，TRACE文件会很大，也影响性能。要进行关闭，执行下面语句即可。
SQL> exec sys.dbms_system.set_sql_trace_in_session(106,5654,FALSE);
就是把最后的TRUE改为FALSE即可。
在关闭后，在后台就会产生一个跟踪文件，具体位置见：%oracle_base%/admin/sid/udump/ora_，之后把这个文件通过FTP拷贝到本地来进行分析。
四、分析TRACE文件
我们这里使用ORACLE提供的一个分析工具，名字叫TKPROF，在DOS提示符下输入TKPROF，就可以看到帮助了。
命令格式如下：
tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
Tracefile为trace文件
Outputfile为格化后的输出文件
Explain 登录的用户名及密码
SYS 是否生成属于SYS用户的信息，一般设置为no
例如：tkprof E:ora_ sys=no
将生成文件。
五、阅读TKPROF格式化的文件
经过TKPROF格式化后的文件内容，就比较容易阅读了，我截取了格式化后的文件中的一段：
********************************************************************************
sql优化的几种方式
第一部分为sql语句文本。
insert into audit_result_temp Select nvl(co
unt(distinct file_id), :"SYS_B_0")
from (Select file_id from log_collect where standard_flag=:"SYS_B_1" and
Billing_cycle_id=:"SYS_B_2" Minus Select file_id from log_import where
billing_cycle_id=:"SYS_B_3")
第二部分主要反映执行效率，主要关注cpu、disk、query、rows几项
Cpu反映SQL语句的 CPU解析和执行时间
Disk反映磁盘读取情况，可以理解成物理读次数，越小越好。
Query主要是反映查询的中间结果有多少行次，执行了多少次查询。
Rows最终结果返回了多少行
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.01 0.00 0 0 0 0
Execute 11 48.26 74.81 0 1477212 398 11
Fetch 0 0.00 0.00 0 0 0 0
-
------ ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 48.27 74.81 0 1477212 398 11
第三部分，是此语句的执行计划，主要关注是否进行了全表扫描。
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 22 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2 SORT GROUP BY
0 VIEW
0 MINUS
0 SORT UNIQUE
0 TABLE ACCESS FULL LOG_COLLECT
0 SORT UNIQUE
0 TABLE ACCESS FULL LOG_IMPORT
********************************************************************************
几种典型情况：
1、 如果query很大，rows很小，就明显有问题，表示运行了很多的中间查询，而最终只返回了很少的行。
2、 Disk读很大。Disk如果很大，要检查下面的执行计划，是否用全表扫描，使用了不正确的索引等。从磁盘读取过多，速度肯定很慢。
3、 CPU时间过大，可能是SQL语句很复杂，或没有使用绑定变量的原因
通过以上过程，就可以对所有的耗CPU时间比较大的进程的SQL进行分析了。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。