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