Re: Tuning problem: dropping roles too slow

Fred Petillot wrote:

> 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 | |
> +-------------------------------------+----------------------------+

Agreed. Looks like someone fired off a script that analyzed all schema's, SYS included.
Don't. Delete'em.

--
Met vriendelijke groet/kind regards,

Frank van Bortel
Technical consultant Oracle Received on Wed Jan 05 2000 - 11:54:26 CST