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语句进行优化