我在执行select count(*) from dba_jobs_running;时好久不能得到结果,
可以知道我的当前SID=251,于是另开一SESSION,提交命令
SQL> select event,p1text,p1,p2text,p2 from v$session_wait
2 where sid=251;
得到结果:
EVENT P1TEXT P1 P2TEXT P2
------------------ -------------- ---------- ---------- ----------
buffer busy waits file# 2 block# 9
于是继续执行
SQL> select file_name,tablespace_name from dba_data_files
2 where file_id=2
3 /
得到结果
FILE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
/dev/vg_db_jssys1_1/rrbs4G07 UNDOTBS
可见一直在等待UNDO块.
--------------------
ITPUB上的朋友给的资料
Subject: Query on DBA_JOBS_RUNNING is Slow in Oracle 9.2
Doc ID: Note:240058.1 Type: PROBLEM
Last Revision Date: 05-MAY-2004 Status: PUBLISHED
fact: Oracle Server - Enterprise Edition 9.2
symptom: Query on DBA_JOBS_RUNNING is slow
cause: This problem is described in the Bug 2624130 - QUERY AGAINST
DBA_JOBS_RUNNING IS USING CBO EVEN WHEN NO STATISITICS
fix:
Workaround:
Use /*+ rule */ hint.
I.e: select /*+ rule */ * from dba_jobs_running;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/85922/viewspace-898849/ ，如需转载，请注明出处，否则将追究法律责任。
