oracle 11g R1企业版, 安装完毕后创建表空间是及其缓慢,cpu占100%,完毕创建表后的,空闲时cpu可能会降低到5%,可是执行sql任何语句时cpu立刻升至100%,而且长时间不下降
安装环境为:center os 5.3,8G,内存,300G sata硬盘,cpu为两颗4核志强
创建表空间时执行时cpu的占有率为100%,此时执行
SELECT b.EVENT, b.SID, b.SERIAL#, b.SQL_ID, a.ELAPSED_TIME, a.SQL_TEXT, a.SQL_FULLTEXT
FROM gv$sqlarea a, gv$session b
WHERE a.SQL_ID = b.SQL_ID后的结果为:
SQL> SELECT b.EVENT, b.SID, b.SERIAL#, b.SQL_ID, a.ELAPSED_TIME, a.SQL_TEXT, a.SQL_FULLTEXT FROM gv$sqlarea a, gv$session b WHERE a.SQL_ID = b.SQL_ID;EVENT
--------------------------------------------------------------------------------
SID SERIAL# SQL_ID ELAPSED_TIME
---------- ---------- -------------------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
SQL*Net message from client
124 92 8t8skhfa5hj38 124428
SELECT b.EVENT, b.SID, b.SERIAL#, b.SQL_ID, a.ELAPSED_TIME, a.SQL_TEXT, a.SQL_FU
LLTEXT FROM gv$sqlarea a, gv$session b WHERE a.SQL_ID = b.SQL_ID
SELECT b.EVENT, b.SID, b.SERIAL#, b.SQL_ID, a.ELAPSED_TIME, a.SQL_TEXT, a.SQL_FUEVENT
--------------------------------------------------------------------------------
SID SERIAL# SQL_ID ELAPSED_TIME
---------- ---------- -------------------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------wait for unread message on broadcast channel
137 3 2b064ybzkwf1y 157767
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;EVENT
--------------------------------------------------------------------------------
SID SERIAL# SQL_ID ELAPSED_TIME
---------- ---------- -------------------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
不过小弟是oracle的初学者,看的还不太明白阿!还望大侠们不吝赐教!
安装环境为:center os 5.3,8G,内存,300G sata硬盘,cpu为两颗4核志强
创建表空间时执行时cpu的占有率为100%,此时执行
SELECT b.EVENT, b.SID, b.SERIAL#, b.SQL_ID, a.ELAPSED_TIME, a.SQL_TEXT, a.SQL_FULLTEXT
FROM gv$sqlarea a, gv$session b
WHERE a.SQL_ID = b.SQL_ID后的结果为:
SQL> SELECT b.EVENT, b.SID, b.SERIAL#, b.SQL_ID, a.ELAPSED_TIME, a.SQL_TEXT, a.SQL_FULLTEXT FROM gv$sqlarea a, gv$session b WHERE a.SQL_ID = b.SQL_ID;EVENT
--------------------------------------------------------------------------------
SID SERIAL# SQL_ID ELAPSED_TIME
---------- ---------- -------------------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
SQL*Net message from client
124 92 8t8skhfa5hj38 124428
SELECT b.EVENT, b.SID, b.SERIAL#, b.SQL_ID, a.ELAPSED_TIME, a.SQL_TEXT, a.SQL_FU
LLTEXT FROM gv$sqlarea a, gv$session b WHERE a.SQL_ID = b.SQL_ID
SELECT b.EVENT, b.SID, b.SERIAL#, b.SQL_ID, a.ELAPSED_TIME, a.SQL_TEXT, a.SQL_FUEVENT
--------------------------------------------------------------------------------
SID SERIAL# SQL_ID ELAPSED_TIME
---------- ---------- -------------------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------wait for unread message on broadcast channel
137 3 2b064ybzkwf1y 157767
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;EVENT
--------------------------------------------------------------------------------
SID SERIAL# SQL_ID ELAPSED_TIME
---------- ---------- -------------------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
不过小弟是oracle的初学者,看的还不太明白阿!还望大侠们不吝赐教!
解决方案 »
- 我的DDLl触发器为什么触发不了?
- Oracle 8i的最新补丁集是多少
- 在oracle中定义的Number(17,3),但是取出的位数变成(15,1)?
- 我想将我查询出结果集中的记录中的id字段的值都在原来值上加1,该怎么写啊?
- ORACL下如何建表,其他数据库有工具,ORACLE有吗?
- 在SQL*PLUS中打开SQL脚本文件出错
- 怎么连接oracle数据库和sybase数据库并从sybase库里把数据读到oracle里
- oracle中的对象类型字段在delphi或vc中应如何访问
- 各位高手,请问在ORACLE中的哪个包可以执行外部命令(比如作备件)?
- 用什么做全局变量?
- 各位高手,小弟求书
- 17003 无效的列索引
to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') as login_time
from v$session where paddr in (select addr from v$process where spid in ('5648612','256523'));3:如果上一步sql_id或者 hash_value不为空,则可用v$sqlarea查出当前正在使用的sql
select * from v$sql where sql_id=''
7868 oracle 25 0 5769m 138m 126m R 100.1 1.7 1237:52 oracle
19962 oracle 15 0 12736 1180 808 R 0.3 0.0 0:26.55 top
1 root 15 0 10344 676 568 S 0.0 0.0 0:02.76 init
2 root RT -5 0 0 0 S 0.0 0.0 0:00.08 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/0
4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
5 root RT -5 0 0 0 S 0.0 0.0 0:00.06 migration/1
硬解析
大量的IO
等待事件你新库上不应该有这个问题的呀。