Bug 3402490 : QUERY TO DBA_JOBS_RUNNING TAKES

g4as8030:d1epcis > sqlplus '/as sysdba'

SQL*Plus: Release 9.2.0.8.0 - Production on Tue Feb 18 14:51:36 2014

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

 

 

 

 

今天在数据库里查询一个job的信息，在查询到如下表时：
select  * from dba_jobs_running

一直在执行，根本停不下来。

表根本就不大，怎么需要这么多时间，实际查询：

 

SQL> set timing on
SQL>
SQL>
SQL> select count(1)  from sys.job$;
 
  COUNT(1)
----------
       259
 
Executed in 0.078 seconds
 
SQL> select count(1)  from v$lock;
 
  COUNT(1)
----------
       799
 
Executed in 0.265 seconds
 
SQL>

 

好快，

 

怎么查询这个表会这么慢呢..?

 

该不会又是oracle的bug？

查询metalink，

果然发现了。

Bug 3402490 : QUERY TO DBA_JOBS_RUNNING TAKES LONG TIME	转到底部

Bug 属性 …

---

类型	B - Defect	已在产品版本中修复	10.2.0.0
严重性	2 - Severe Loss of Service	产品版本	9.2.0.4
状态	80 - Development to QA/Fix Delivered Internal	平台	23 - Oracle Solaris on SPARC (64-bit)
创建时间	2004-1-27	平台版本	9.2.0.4
更新时间	2013-4-18	基本 Bug	N/A
数据库版本	9.2.0.4	影响平台	Generic
产品源	Oracle

相关产品 …

---

产品线	Oracle Database Products	系列	Oracle Database Suite
区域	Oracle Database	产品	5 - Oracle Database - Enterprise Edition

Hdr: 3402490 9.2.0.4 RDBMS 9.2.0.4 VOS ENQUEUES PRODID-5 PORTID-23
Abstract: QUERY TO DBA_JOBS_RUNNING TAKES LONG TIME
 
*** 01/27/04 01:33 am ***
TAR:
----
 
PROBLEM:
--------
We run query to DBA_JOBS_RUNNING it takes an enormous amount of time 
to execute. On CT's site, it takes more than 10 minutes. This problem
is solvable by using RULE hint. But the query to DBA_JOBS_RUNNING is 
executed from Oracle Portal. This means this query is hardcoded, 
so CT can't use this workaround. Oracle Portal bundled by EBS 11.5.8. 
 
DIAGNOSTIC ANALYSIS:
--------------------
The problem that query to dictionary takes long time occurs
not only DBA_JOBS_RUNNING but JOIN of V$LOCK and some dictionary view.
 
1.
We need fix on the future version because we cannot understand 
the basis that BUG#2624130 is not a bug. And Rule-hint is not 
supported from 10g.
 
2.
If it can't fix, we'd like you to support recreate DBA_JOBS_RUNNING 
like the work around of BUG#2624130.
 
WORKAROUND:
-----------
none
 
RELATED BUGS:
-------------
Bug#269898
BUG#2624130
 
REPRODUCIBILITY:
----------------
Rep?    Platform                       RDBMS Ver. 
------- ------------------------------ ---------- 
Y(100%) 23 Sun SPARC Solaris (64-BIT)  9.2.0.3.0
Y(100%) 100 MS Windows 2000            9.2.0.4.0 
Y(100%) 453 Sun SPARC Solaris          9.2.0.4.0
 
TEST CASE:
----------
None
 
STACK TRACE:
------------
None
 
SUPPORTING INFORMATION:
-----------------------
I put files.tar.Z  on ess30 in /bug3402490/
 
-query to DBA_JOBS_RUNNING without RULE
ora920_ora_1584.trc(sqltrace)
1584.tkp(tkprof)
-query to DBA_JOBS_RUNNING with RULE
ora920_ora_660.trc(sqltrace)
660.tkp(tkprof)
 
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------
N/A
 
DIAL-IN INFORMATION:
--------------------
N/A
 
IMPACT DATE:
------------
N/A
 
*** 01/27/04 02:59 am *** 
*** 01/27/04 03:19 am *** (CHG: Sta->10)
*** 01/27/04 03:19 am ***
*** 01/27/04 07:49 pm *** (CHG: Sta->16)
*** 01/27/04 07:49 pm ***
*** 01/27/04 09:26 pm *** (CHG: Sta->10)
*** 01/27/04 09:26 pm ***
*** 01/28/04 12:35 am *** (CHG: Sta->16)
*** 01/28/04 12:35 am ***
*** 01/28/04 01:19 am ***
*** 01/28/04 02:21 am ***
*** 01/28/04 02:33 am ***
*** 01/28/04 02:45 am *** (CHG: Sta->10)
*** 01/28/04 02:45 am ***
*** 01/28/04 02:47 am ***
*** 01/28/04 06:01 pm *** (CHG: Sta->16)
*** 01/28/04 06:01 pm ***
*** 01/28/04 08:33 pm ***
*** 01/28/04 08:46 pm *** (CHG: Sta->11 Asg->RDBMSREP SubComp->QRY OPTIMIZER)
*** 01/28/04 08:46 pm ***
*** 01/28/04 08:50 pm ***
*** 01/28/04 09:10 pm *** 
*** 01/28/04 09:10 pm ***
*** 02/04/04 05:27 pm ***
*** 02/24/04 05:30 pm ***
*** 02/26/04 01:16 am ***
*** 03/08/04 12:55 am *** ESCALATED
*** 03/08/04 12:55 am ***
*** 03/08/04 01:00 am *** 
*** 03/08/04 01:00 am ***
*** 03/08/04 11:09 am *** (CHG: Sta->30)
*** 03/08/04 11:09 am ***
*** 03/10/04 06:21 am *** (CHG: Sta->11)
*** 03/10/04 06:21 am ***
*** 03/10/04 12:59 pm *** (CHG: Sta->30)
*** 03/10/04 12:59 pm ***
*** 03/10/04 01:03 pm ***
*** 03/16/04 03:08 am *** (CHG: Sta->11)
*** 03/16/04 03:08 am ***
*** 03/16/04 12:48 pm *** (CHG: Sta->30)
*** 03/16/04 12:48 pm ***
*** 03/19/04 01:30 am *** (CHG: Sta->11)
*** 03/19/04 01:30 am ***
*** 03/19/04 04:43 pm *** (CHG: Sta->30)
*** 03/19/04 04:43 pm ***
*** 03/23/04 05:23 am *** (CHG: Sta->11)
*** 03/23/04 05:23 am ***
*** 03/23/04 12:12 pm ***
*** 03/23/04 12:23 pm *** 
*** 03/23/04 12:26 pm *** 
*** 03/23/04 12:26 pm ***
 
*** 03/24/04 02:20 am *** 
*** 03/24/04 02:20 am *** 
*** 03/30/04 06:13 am ***
*** 04/06/04 03:06 am ***
*** 04/06/04 03:09 am *** 
*** 04/06/04 03:09 am *** (CHG: Sta->80)
*** 04/06/04 03:09 am ***
Rediscovery Information:
A query based on GV$LOCK, GV$ENQUEUE_LOCK or GV$TRANSACTION_ENQUEUE may 
perform badly if stats have not been gathered on SYS objects.
 
Workaround: 
Create an equivalent view without the hints.
Use RULE hint in query.
 
Release Notes:
]]The use of GV$LOCK, GV$ENQUEUE_LOCK and GV$TRANSACTION_ENQUEUE could
]]cause a bad plan. This has been corrected.
*** 04/06/04 11:15 pm *** ESCALATION -> CLOSED
*** 04/06/04 11:16 pm ***
*** 04/07/04 12:11 am ***
*** 04/07/04 12:11 am ***
*** 06/17/04 04:20 pm ***
*** 06/22/04 10:14 am ***
*** 04/17/13 06:38 pm ***

 

换种方式：

SQL> select/*+ rule*/ * from dba_jobs_running;
 
       SID        JOB   FAILURES LAST_DATE LAST_SEC  THIS_DATE THIS_SEC     INSTANCE
---------- ---------- ---------- ----------- ---------------- ----------- ---------------- ----------
 
Executed in 0.281 seconds
 
SQL>

 

 

 

mark下~~