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$;
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 ***