Re: grant select command so slow
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxxxx>
- To: joan.hsieh@xxxxxxxxx
- Date: Thu, 15 May 2008 12:39:19 -0700
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 **
--
//www.freelists.org/webpage/oracle-l
Other related posts:
