Re: grant select command so slow

If that was the case, how did the grant run very fast the first time, according to the OP.

On 5/15/08, John Kanagaraj <john.kanagaraj_at_gmail.com> wrote:
>
> Joan,
>
> > I have created a role with just select privilege on all sysadm objects.
> The
> > script run very fast at first time. Since there maybe new tables created
> in
> > the prod. So I cron the script, run it every night. Now the script ran so
> > slow, each grant statement at least took 2 to 3 seconds. So it never
> > finished to run in one day. (totally had more than 49000 tables and
> views).
> > The system is not loaded, most time is just this session is active. The
> > other function seems normal, users didn't complain for their activities.
> Do
> > you know any reason why the "grant select on syadm.xxxx to role" run so
> > slow?
>
> I had exactly this situation a while ago. It turned out to be Shared
> pool issue - Latching in the shared pool and library cache, and a
> steep spike in parsing. You might want to check STATSPACK or AWR (if
> 10g) during that period. In my case, the previous DBA had written a
> SQL script to generate GRANT statements on *ALL* objects, and I
> rewrote this to GRANT only on *New* objects, and this problem went
> away..
>
> What was happening is this: From a DB shared pool perspective, these
> thousands of GRANT statements were non-bind SQLs and each needed to be
> parsed. This flood of non-bind SQL shredded the shared pool and the DB
> went crazy trying to juggle free space in the shared pool (latching,
> etc).
>
> At the risk of violating my BAAG agreement, I suspect this is also
> happening in your DB as well (Alex G: Please excuse my trespass :)
>
> --
> John Kanagaraj <><
> DB Soft Inc
> http://www.linkedin.com/in/johnkanagaraj
> http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!)
> ** The opinions and facts contained in this message are entirely mine
> and do not reflect those of my employer or customers **
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l