Tuning problem: dropping roles too slow

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. Received on Tue Jan 04 2000 - 09:00:21 CST