EBS开发性能优化之查找需要优化的程序

转载

mob60475707d572 2013-12-12 15:59:00

文章标签 oracle sql sql语句 sed 数据库 文章分类 Oracle 数据库

1.登陆数据库LINUX环境

使用 top 命令查看进程状况

[oratest@ebsdb~]$top

top - 15:58:59 up 8 days, 22:04, 1 user, load average: 0.96, 1.32, 1.05
Tasks: 1870 total,   1 running, 1869 sleeping,   0 stopped,   0 zombie
Cpu(s): 0.2%us, 0.1%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 132148008k total, 123992200k used, 8155808k free,   647708k buffers
Swap: 4194296k total,        0k used, 4194296k free, 114719948k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

8700 oratest 20 0 16384 2664 956 R 3.2 0.0 0:02.34 top

861 root 39 19 0 0 0S 0.3 0.0 40:37.98 kipmi0

4026 root 20 0 11048 828 480 S 0.3 0.0 6:35.89 irqbalance

5646 oratest 20 0 1255m 16m 14m S 0.3 0.0 2:28.97 oracle

5656 oratest 20 0 1257m 27m 23m S 0.3 0.0 13:55.09 oracle

6971 orauat 20 0 20.2g 293m 285m S 0.3 0.2 0:31.51 oracle

6983 orauat 20 0 20.2g 155m 151m S 0.3 0.1 3:52.83 oracle

6991 orauat 20 0 20.4g 272m 41m S 0.3 0.2 12:59.16 oracle

56773oraprod 20 0 20.2g 16m 14m S 0.3 0.0 0:33.26 oracle

56783oraprod 20 0 20.2g 27m 23m S 0.3 0.0 4:45.40 oracle

56811oraprod 20 0 20.2g 89m 84m S 0.3 0.1 0:46.99 oracle

77998orauat 20 0 20.2g 17m 15m S 0.3 0.0 0:10.46 oracle

78000oraprod 20 0 20.2g 17m 15m S 0.3 0.0 0:10.19 oracle

1 root 20 0 19360 1536 1224 S 0.0 0.0 0:13.89 init

2.找到占用CPU利用率比较长的进程例如 PID为 8700

3.在SQL命令窗口运行如下SQL语句，输入进程id

SELECT pro．pid pid,
       ses．sid sid,
       pro．spid spid,
       ses．username username,
       ses．osuser osuser,
       pro.serial# serial_#,
       pro．terminal terminal,
       pro．program program,
       pro．background background,
       ses．status status,
       rtrim(substr(sql．sql_text，1，80)) SQL
  FROM v$process pro, v$session ses, v$sqlarea SQL
 WHERE pro.addr = ses．paddr
   AND ses.sql_address = sql.address(+)
   AND pro.spid LIKE '%&variance%'

EBS开发性能优化之查找需要优化的程序_sed