Oracle current high CPU load SQL
来源:互联网 发布:banner请求网络图片 编辑:程序博客网 时间:2024/06/05 06:11
ERP数据库某些时段出现CPU使用率接近100%,如何找出这些高CPU消耗的SQL语句?
SQL> select sql_text from v$sql where sql_id='dg5t8nfkaz8nk';
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
BEGIN :1:=FND_GLOBAL.USER_ID; :2:=FND_GLOBAL.RESP_ID; :3:=FND_GLOBAL.RESP_APPL_ID; :4:=FND_GLOBAL.SECURITY_GROUP_ID; :5:=FND_GL
OBAL.LOGIN_ID; :6:=FND_GLOBAL.NLS_LANGUAGE; :7:=FND_GLOBAL.NLS_DATE_FORMAT; :8:=FND_GLOBAL.NLS_DATE_LANGUAGE; :9:=FND_GLOBAL.NLS
_TERRITORY; :10:=FND_GLOBAL.NLS_NUMERIC_CHARACTERS; :11:=FND_GLOBAL.NLS_SORT;END;
小技巧:
select dbms_lob.substr(a.SQL_FULLTEXT) from v$sql a where sql_id='dg5t8nfkaz8nk';
--从AWR的snapshot中显示完整SQL,执行计划,绑定变量。
select * from table(dbms_xplan.display_cursor('dg5t8nfkaz8nk',null,'ADVANCED'));
1,查询高CPU消耗的pid
$ top
2,查询该pid的ADDR
SQL> select addr from v$process where spid='24764';
ADDR
----------------
00000007D3112C88
3,查询SQL_ID与用户session信息。
SQL> select sql_id from v$session where paddr='00000007D3112C88';
SQL_ID
-------------
dg5t8nfkaz8nk
SQL> select sid,SERIAL#,USERNAME,STATUS,sql_id from v$session where paddr='00000007D3112C88';
SID SERIAL# USERNAME STATUS SQL_ID
---------- ---------- ------------------------------ -------- -------------
2067 9786 APPS INACTIVE
2340 2720 APPS ACTIVE dg5t8nfkaz8nk
SQL> select sql_text from v$sql where sql_id='dg5t8nfkaz8nk';
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
BEGIN :1:=FND_GLOBAL.USER_ID; :2:=FND_GLOBAL.RESP_ID; :3:=FND_GLOBAL.RESP_APPL_ID; :4:=FND_GLOBAL.SECURITY_GROUP_ID; :5:=FND_GL
OBAL.LOGIN_ID; :6:=FND_GLOBAL.NLS_LANGUAGE; :7:=FND_GLOBAL.NLS_DATE_FORMAT; :8:=FND_GLOBAL.NLS_DATE_LANGUAGE; :9:=FND_GLOBAL.NLS
_TERRITORY; :10:=FND_GLOBAL.NLS_NUMERIC_CHARACTERS; :11:=FND_GLOBAL.NLS_SORT;END;
小技巧:
有时SQL_TEXT不能完整输出SQL语句,可以使用下面的函数进行输出转换
--从当前的内存中显示完整SQL语句select dbms_lob.substr(a.SQL_FULLTEXT) from v$sql a where sql_id='dg5t8nfkaz8nk';
--从AWR的snapshot中显示完整SQL,执行计划,绑定变量。
select * from table(dbms_xplan.display_cursor('dg5t8nfkaz8nk',null,'ADVANCED'));
- Oracle current high CPU load SQL
- [RabbitMQ] beam.smp high cpu load
- oracle 11.2.0.4暴重大bug!!-->sysauth$ 存在登录查询的bug:High CPU/IO for dictionary SQL against SYSAUTH$
- oracle的sql load
- Resolving High CPU Usage on Oracle Servers(读书笔记)
- Jetty/Howto/High Load
- Codeforces828 D. High Load
- High Load CodeForces
- 网站High CPU分析
- CPU load
- cpu load
- TTY Background causing high CPU load May 26th, 2009 · No Comments Using an old switch cisco C3548XL
- Isolating Linux High System Load
- Codeforces 828 D High Load
- how to check oracle current running sql status?
- how to check oracle current running sql status?
- SQL server Profiler and excution plan usage for CPU usage high analysis
- ORACLE sql load 数据导入笔记
- A*搜索算法随想
- sql*plus 学习
- hdu 1569 方格取数(2)
- git 官方简介
- 从零开始at91sam9263的linux编程----内核编译
- Oracle current high CPU load SQL
- ECMall模块开发2
- myeclipse工程下的js文件中文乱码问题
- forever: 让nodejs应用后台执行
- Jrebel的使用
- Android基础面试题
- at91sam9263的linux编程----u-boot编译
- 【黑马程序员】- 面向对象_多态_接口
- 转义字符