Re: Tuning problem: dropping roles too slow

<zstringer_at_my-deja.com> wrote in message news:84t1ts$5qt$1_at_nnrp1.deja.com...
> 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!

It seems that you analyed SYS's schema, if so, drop the statistic data of SYS'S schema. Received on Wed Jan 05 2000 - 09:15:01 CST