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

Object type 10 is (from memory) the 'non-existent' object used for negative dependency checking.

To find out where the 30 seconds is going, set sql_trace and timed_statistics true before you try a grant, and look at the trace file.

stats on SYS objects, are (as you guessed) often the cause of such funny timings, but there are bugs in the recursive SQL from time to time.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

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.