Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning problem: dropping roles too slow
Strange thing is, you're using CBO on data dictionnary, which is a
big nono. You should not have any statistics on OBJAUTH$ nor OBJ$.
zstringer_at_my-deja.com wrote:
> We have two Oracle 7.3.3.6.0 instances running on an Alpha OpenVMS
> server. When I drop a role on one instance (named FAST) it takes about
> 10 minutes. However, when I drop a role in the other instance (named
> SLOW) it can take 5 hours! I did a SQL trace and found big differences
> in time for the statement:
>
> select grantor#,ta.obj#,o.type
> from
> sys.objauth$ ta, sys.obj$ o where grantee#=:1 and ta.obj#=o.obj# group
> by grantor#,
> ta.obj#,o.type
>
> I ran explain plan for the FAST instance:
>
> SELECT STATEMENT, COST=0, CARDINALITY=0
> SORT GROUP BY, COST=0, CARDINALITY=0
> NESTED LOOPS, COST=0, CARDINALITY=0
> TABLE ACCESS BY ROWID OBJAUTH$, COST=0, CARDINALITY=0
> INDEX RANGE SCAN I_OBJAUTH2, COST=0, CARDINALITY=0
> TABLE ACCESS BY ROWID OBJ$, COST=0, CARDINALITY=0
> INDEX UNIQUE SCAN I_OBJ1, COST=0, CARDINALITY=0
>
> And here are the results for SLOW:
>
> SELECT STATEMENT, COST=1183, CARDINALITY=3391
> SORT GROUP BY, COST=0, CARDINALITY=0
> HASH JOIN, COST=1183, CARDINALITY=3391
> TABLE ACCESS FULL OBJAUTH$, COST=1098, CARDINALITY=3391
> TABLE ACCESS FULL OBJ$, COST=21, CARDINALITY=23416
>
> It's obvious that difference in performance is because the SLOW
> instance is doing a full table scan, while the FAST instance is using
> indexes. The indexes exist in both instances, and have similar sizes,
> number of extents, etc.
>
> Can someone help me figure out how to get the SLOW instance to use this
> index? I'm running out of things to try - thanks for any help!
>
> Peter Mroz
> PAREXEL International
>
> peterabc.mrozabc_at_parexelabc.com
>
> (remove the abc's to email)
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
--
+-------------------------------------+----------------------------+ | Fred Petillot | fpetillo_at_fr.oracle.com | | Oracle France - Support Technique | +33 1 4762 8201 | | 65, rue des Trois Fontanot | +33 6 8089 5135 | | 92732 Nanterre Cedex | | +-------------------------------------+----------------------------+Received on Tue Jan 04 2000 - 14:12:41 CST
![]() |
![]() |