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的初学者,看的还不太明白阿!还望大侠们不吝赐教!
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
等待事件你新库上不应该有这个问题的呀。