Re: grants now take ~30 sec.. why?

If you say the instance is severely underutilized, are you sure the power management is switched off (hard disk or server was 'sleeping' ?).

Marc

Clif Deanhardt wrote in message <89ov1i$q05$1_at_nnrp1.deja.com>...
>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 Mon Mar 06 2000 - 00:00:00 CST