ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Bug 5373923 : RECURSIVE STATEMENT DOES FULL TABLE SCAN ON TAB$

Bug 5373923 : RECURSIVE STATEMENT DOES FULL TABLE SCAN ON TAB$

Linux操作系统 作者:psufnxk2000 时间:2013-11-13 23:56:56 0 删除 编辑







Bug 5373923 : RECURSIVE STATEMENT DOES FULL TABLE SCAN ON TAB$
Click to add to Favorites Email link to this document Printable Page To BottomTo Bottom
 

Bug Attributes

 

Type B - Defect Fixed in Product Version 11.2
Severity 2 - Severe Loss of Service Product Version 10.2.0.2
Status 80 - Development to QA/Fix Delivered Internal Platform 176 - z*OBSOLETE: Microsoft Windows
Created 05-Jul-2006 Platform Version XP
Updated 14-Apr-2010 Base Bug N/A
Database Version 10.2.0.2 Affects Platforms Generic
Product Source Oracle
 

Related Products

 

Line Oracle Database Products Family Oracle Database
Area Oracle Database Product 5 - Oracle Database - Enterprise Edition
Hdr: 5373923 10.2.0.2 RDBMS 10.2.0.2 DICTIONARY PRODID-5 PORTID-176
Abstract: RECURSIVE STATEMENT DOES FULL TABLE SCAN ON TAB$ *** 07/05/06 03:56 am *** TAR:
----
 
PROBLEM:
--------
In a SQL tuning session we found the following statement doing a lot of IO:
 
select u.name, o.name, t.spare1, t.pctfree$    
from   sys.obj$ o, sys.user$ u, sys.tab$ t    
where  (bitand(t.trigflag, 1048576) = 1048576) 
and    o.obj#=t.obj# 
and    o.owner# = u.user# 
 
On a system with a couple of 10,000s tables, this does a lot of IO and blocks 
smon for doing other tasks.
 
DIAGNOSTIC ANALYSIS:
--------------------
The execution plan we get is:
 
-----------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |   163 |  9617 |  1926   (3)|
|*  1 |  HASH JOIN                    |        |   163 |  9617 |  1926   (3)|
|   2 |   TABLE ACCESS FULL           | USER$  |    57 |   855 |     2   (0)|
|   3 |   NESTED LOOPS                |        |   163 |  7172 |  1924   (3)|
|*  4 |    TABLE ACCESS FULL          | TAB$   |   162 |  2592 |  1599   (3)|
|   5 |    TABLE ACCESS BY INDEX ROWID| OBJ$   |     1 |    28 |     2   (0)|
|*  6 |     INDEX UNIQUE SCAN         | I_OBJ1 |     1 |       |     1   (0)|
-----------------------------------------------------------------------------
 
The system, where i did the test has only 17,000 entries in tab$, we have 
system with up to 100,000 tables.
 
It would be a lot better, if such information (bitand(t.trigflag, 1048576) = 
1048576) would be captured in a specific table, where the size of the table 
is independent of the size of the datadictionary.
 
WORKAROUND:
-----------
none
 
RELATED BUGS:
-------------
 
REPRODUCIBILITY:
----------------
always on huge systems.
 
TEST CASE:
----------
none
 
STACK TRACE:
------------
 
SUPPORTING INFORMATION:
-----------------------
 
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------
 
DIAL-IN INFORMATION:
--------------------
 
IMPACT DATE:
------------ *** 07/05/06 04:32 am ***  *** 02/28/07 02:55 am ***  *** 02/28/07 02:55 am *** *** 06/15/07 06:55 am *** ESCALATED *** 06/15/07 06:56 am *** *** 06/19/07 02:59 am *** *** 06/19/07 11:51 pm ***  *** 06/20/07 09:25 pm ***  *** 06/20/07 09:25 pm *** (CHG: Sta->30) *** 06/20/07 09:25 pm *** *** 06/28/07 02:28 am *** (CHG: Sta->11) *** 06/28/07 02:28 am *** *** 07/01/07 11:38 pm ***  *** 07/01/07 11:38 pm *** *** 07/26/07 04:38 am *** *** 07/26/07 05:12 am *** *** 11/09/07 03:17 pm ***  *** 11/09/07 03:17 pm *** *** 11/13/07 03:44 am ***  *** 11/13/07 03:44 am *** *** 03/06/08 09:48 am *** RELEASE NOTES:
]]SMON periodically runs a query which requires a full table scan of
]]SYS.TAB$, and this can be a performance problem when the system has
]]many tables and TAB$ is large.
REDISCOVERY INFORMATION:
If you see performance problems because SMON is running the following
query, then you have hit this bug:
 
 
 select o.obj#, u.name, o.name, t.spare1,
        DECODE(bitand(t.flags, 268435456), 268435456, t.initrans, t.pctfree$)
   from sys.obj$ o, sys.user$ u, sys.tab$ t
   where  (bitand(t.trigflag, 1048576) = 1048576) and
          o.obj#=t.obj# and o.owner# = u.user#;
WORKAROUND:
None *** 03/20/08 03:48 pm ***  *** 03/20/08 03:48 pm *** (CHG: Sta->80) *** 03/21/08 04:08 am *** *** 03/21/08 10:44 am *** *** 03/21/08 06:09 pm *** *** 03/21/08 09:41 pm *** *** 03/22/08 01:27 pm *** *** 03/24/08 04:21 am *** *** 03/25/08 12:35 pm *** *** 04/02/08 08:35 am *** *** 04/03/08 08:38 am *** *** 04/07/08 07:50 am *** *** 04/07/08 06:04 pm *** *** 04/10/08 10:51 pm *** *** 04/18/08 07:14 am *** *** 04/18/08 07:50 am *** *** 04/18/08 02:52 pm *** *** 04/21/08 09:39 am *** *** 04/23/08 06:29 am *** *** 04/23/08 06:53 am *** *** 04/25/08 05:19 pm *** *** 05/07/08 05:59 am *** *** 05/07/08 06:25 am *** *** 05/09/08 07:05 pm *** *** 05/10/08 12:23 am *** *** 05/12/08 06:19 pm *** *** 05/13/08 07:57 pm *** *** 05/17/08 11:51 am *** *** 05/30/08 03:50 am *** *** 07/23/08 08:38 pm *** *** 09/09/08 10:09 pm *** *** 09/10/08 07:15 pm *** 



sql_id :cfz686a6qp0kg

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25099483/viewspace-776537/,如需转载,请注明出处,否则将追究法律责任。

上一篇: parallel_max_servers
下一篇: ora-600 729
请登录后发表评论 登录
全部评论

注册时间:2011-05-31

  • 博文量
    215
  • 访问量
    627173