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

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%'

4.找到执行额SQL语句

select *from oms_so_order_header

5.使用相应EBS用户登陆数据库,执行得到的SQL语句，查看执行计划

可知道该SQL语句对表oms_so_order_header进行全表扫面，可针对特定SQL语句进行优化