方法一、
如果ORACLE的进程开销比较大,我们可以用如下的方法来查询具体的进程在干什么事情,例如我们要查询进程ora_j000_ora92,PID=344612,可以使用下面的方法:
$su – oracle
SQL>sqlplus “/as sysdba”
SQL>oradebug setospid 344612
SQL>oradebug event 10046 trace name context forever, level 8
SQL>oradebug tracefile_name –这个命令我们获得输出文件的绝对路径和文件名
SQL>oradebug event 10046 trace name context off
$tkprof /opt/oracle/app/oracle/admin/ora92/bdump/ora92_j000_344612.trc tracepid.txt
$more tracepid.txt
在tracepid.txt中,我们就可以看到这个进程中具体运行的语句、过程等,以及所有的SQL的cpu消耗、物理读、逻辑读、执行计划等信息。
具体案例:
1,查找CPU开销最大的前20个进程
p550a:/#ps aux |head -20
USER PID %CPU %MEM SZ RSS TTY STAT STIME TIME COMMAND
root 57372 5.8 0.0 384 384 - A Apr 12 32949:41 wait
root 16680 5.7 0.0 384 320 - A Apr 12 32737:24 wait
root 61470 5.7 0.0 384 384 - A Apr 12 32521:24 wait
root 20778 5.7 0.0 384 384 - A Apr 12 32329:11 wait
root 8196 5.7 0.0 384 384 - A Apr 12 32328:37 wait
root 288 5.6 0.0 384 320 - A Apr 12 32191:06 wait
root 53274 5.6 0.0 384 384 - A Apr 12 32005:49 wait
root 12582 5.6 0.0 384 320 - A Apr 12 31925:35 wait
oracle 585936 0.6 1.0 96668 51780 - A 13:08:56 4:56 oracleTCSF21 (LO
oracle 639120 0.3 1.0 97340 52452 - A 14:26:20 0:47 oracleTCSF21 (LO
oracle 541008 0.2 1.0 104144 59256 - A 13:49:29 0:57 oracleTCSF21 (LO
root 188646 0.1 0.0 56724 7288 - A Apr 12 716:38 /oracle/product/
oracle 327758 0.1 1.0 107644 55236 - A Apr 12 418:13 ora_lms0_TCSF21
oracle 327990 0.1 1.0 107648 55256 - A Apr 12 415:57 ora_lms1_TCSF21
root 561318 0.0 0.0 1648 1668 - A 11:34:16 0:47 /usr/dt/bin/dtte
root 139706 0.0 0.0 784 472 - A Apr 12 172:23 /bin/sh /etc/ini
oracle 319562 0.0 1.0 107168 46120 - A Apr 12 85:43 ora_lmon_TCSF21
oracle 159996 0.0 0.0 13620 3424 - A Apr 12 79:37 /oracle/product/
oracle 303364 0.0 1.0 107168 46144 - A Apr 12 71:36 ora_lmon_TCSF1
2,准备跟踪开销最大的Oracle数据库进程585936 的具体运行语句等信息
p550a:/#su - oracle
p550a:/home/oracle$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 31 15:00:22 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> oradebug setospid 585936
Oracle pid: 41, Unix process pid: 585936, image: oracle@p550a
3,启用10046跟踪事件
SQL> oradebug event 10046 trace name context forever, level 8
Statement processed.
4,获得参数文件的绝对路径和文件名
SQL> oradebug tracefile_name
/oracle/admin/TCSF2/udump/tcsf21_ora_585936.trc
5,关闭10046跟踪事件
SQL> oradebug event 10046 trace name context off
Statement processed.
6,使用Oracle tkprof工具格式化跟踪文件
p550a:/home/oracle$tkprof /oracle/admin/TCSF2/udump/tcsf21_ora_585936.trc trace41.txt
TKPROF: Release 10.2.0.4.0 - Production on Thu May 31 15:02:18 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
7,查看格式化后的跟踪文件
p550a:/home/oracle$vi trace41.txt
TKPROF: Release 10.2.0.4.0 - Production on Thu May 31 15:02:18 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: /oracle/admin/TCSF2/udump/tcsf21_ora_585936.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
********************************************************************************
Trace file: /oracle/admin/TCSF2/udump/tcsf21_ora_585936.trc
Trace file compatibility: 10.01.00
Sort options: default
2 sessions in tracefile.
0 user SQL statements in trace file.
0 internal SQL statements in trace file.
0 SQL statements in trace file.
0 unique SQL statements in trace file.
48 lines in trace file.
0 elapsed seconds in trace file.
方法二、
另外,我们也可以执行下面的语句查看进程具体运行的SQL语句的文本:
SELECT /*+ ORDERED */ sql_text FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,0, prev_hash_value,sql_hash_value),
DECODE (sql_hash_value,0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '&pid'))
ORDER BY piece ASC
具体案例:
1,查找CPU开销最大的前20个进程
p550a:/#ps aux |head -20
USER PID %CPU %MEM SZ RSS TTY STAT STIME TIME COMMAND
root 57372 5.8 0.0 384 384 - A Apr 12 32970:07 wait
root 16680 5.7 0.0 384 320 - A Apr 12 32758:07 wait
root 61470 5.7 0.0 384 384 - A Apr 12 32541:32 wait
root 20778 5.7 0.0 384 384 - A Apr 12 32349:41 wait
root 8196 5.7 0.0 384 384 - A Apr 12 32348:53 wait
root 288 5.6 0.0 384 320 - A Apr 12 32211:46 wait
root 53274 5.6 0.0 384 384 - A Apr 12 32025:52 wait
root 12582 5.6 0.0 384 320 - A Apr 12 31946:04 wait
oracle 668064 0.2 1.0 96696 51848 - A 15:07:02 0:33 oracleTCSF21 (LO
oracle 544788 0.1 1.0 104296 59448 - A 15:36:58 0:04 oracleTCSF21 (LO
root 188646 0.1 0.0 56724 7288 - A Apr 12 717:04 /oracle/product/
oracle 541008 0.1 1.0 104144 59296 - A 13:49:29 0:57 oracleTCSF21 (LO
oracle 327758 0.1 1.0 107644 55276 - A Apr 12 419:10 ora_lms0_TCSF21
oracle 327990 0.1 1.0 107648 55296 - A Apr 12 416:54 ora_lms1_TCSF21
root 561318 0.0 0.0 1684 1704 - A 11:34:16 0:57 /usr/dt/bin/dtte
oracle 651748 0.0 1.0 94348 49500 - A 15:38:05 0:01 ora_j000_TCSF21
oracle 622600 0.0 1.0 98168 53320 - A 15:20:08 0:04 oracleTCSF21 (LO
root 139706 0.0 0.0 784 472 - A Apr 12 172:29 /bin/sh /etc/ini
oracle 319562 0.0 1.0 107168 46160 - A Apr 12 85:48 ora_lmon_TCSF21
2,准备跟踪开销最大的Oracle数据库进程668064的具体运行语句等信息
p550a:/home/oracle$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 31 15:43:38 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> SELECT /*+ ORDERED */ sql_text FROM v$sqltext a
2 WHERE (a.hash_value, a.address) IN (
3 SELECT DECODE (sql_hash_value,0, prev_hash_value,sql_hash_value),
DECODE (sql_hash_value,0, prev_sql_addr, sql_address)
FROM v$session b
4 5 6 WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '&pid'))
ORDER BY piece ASC 7 8 9 ;
Enter value for pid: 668064
old 8: WHERE c.spid = '&pid'))
new 8: WHERE c.spid = '668064'))
SQL_TEXT
----------------------------------------------------------------
SELECT 1 FROM DUAL
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14359/viewspace-731938/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14359/viewspace-731938/