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@xxxxxxxxx> 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 ** > -- > //www.freelists.org/webpage/oracle-l > > >