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
To Bottom
