发表于: 2008.03.29 23:16
分类: ORACLE , Bug
出处: http://yangtingkun.itpub.net/post/468/458549
---------------------------------------------------------------
一个经常碰到的bug,访问DBA_JOBS_RUNNING视图长时间得不到结果。
在10.2以前的版本,查询DBA_JOBS_RUNNING视图经常会发生等待:
SQL> SET TIMING ON
SQL> SELECT * FROM DBA_JOBS_RUNNING;
no rows selected
Elapsed: 00:00:28.57
上面这个查询中虽然一条记录都没有查询到,但是仍然用了将近30秒,而如果采用RBO的方式,则1秒之内就可以得到查询的结果:
SQL> SELECT /*+ RULE */ * FROM DBA_JOBS_RUNNING;
no rows selected
Elapsed: 00:00:00.04
Oracle在Bug No. 3402490中描述了这个bug,这个bug会影响9.2和10.1版本,在10.2中这个bug被fixed。
SQL> SET TIMING ON
SQL> SELECT * FROM DBA_JOBS_RUNNING;
未选定行
已用时间: 00: 00: 00.05
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
已用时间: 00: 00: 00.03
DOC:329617.1
Solution
Gather Fixed Object Stats using DBMS_STATS.GATHER_FIXED_OBJECTS_STATS:
SQL> connect / as sysdba
SQL> exec dbms_stats.gather_fixed_objects_stats
-
gather fixed object statistics only after the database is running under a representative workload. This is necessary in order to ensure that the sampled fixed objects (X$ memory tables) contain representative data. E.g. gathering stats immediately after a database restart will not achieve a good sample.
-
after gathering fixed object statistics the query has to be hard parsed in order for the Optimizer to consider the new statistics and generate a revised execution plan
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/611609/viewspace-670261/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/611609/viewspace-670261/