grants now take ~30 sec.. why?

Here's a puzzler...

I'm on a severely underutilized instance of 8.0.5.1 on a Sun/Solaris 2.6 box. When I execute a grant on any object, it takes about 30 seconds to return. A quick check if v$open_cursors and it's friends turn up these two statements being executed:

• 1 -- insert into objauth$ (option$, grantor#, obj#, privilege#, grantee#,col#,sequence#) values(decode(:1,0, null, :1), :2,:3,:4,:5,decode(:6,0,null,:6),object_grant.nextval)
• 2 -- select grantee#,privilege#,nvl(col#,0),max(nvl(option$,0)) from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#

The select, when fired off manually with a valid obj# comes back quickly. I haven't tried the insert, but there aren't any FK constraints on objauth$ that I can find.

When I first stumbled on this, I checked the SYS tables. They had been analyzed, so I deleted all the statistics and confirmed that with dba_tables and dba_indexes.

One other curiosity, a search through obj$ turns up an extra object for most of the object_names in my system (I haven't counted exact numbers). It's an object type number 10 (obj$.type#). What's a 10, and could it be the culprit? (e.g. I have a package and synonym Z. when I query obj$, I see the Z (body), Z (header), Z (synonym) and Z (type#=10) ).

Any help would be greatly appreciated.

Clif Deanhardt

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Mar 03 2000 - 00:00:00 CST