Re: grant select command so slow

Hi,

I remember I replied to some individual account and until today I had time to visit the list again. I thought I have obligation to share the solution. I create the new script just grant to the new tables, although I am still not knowing the original cause. Anyway, this solution give me a piece of mind for now at least.

  select 'grant select on SYSADM.'||table_name||' to SYSADM_SELECT;' from    dba_tables where owner='SYSADM' and not table_name=any (select table_name    from dba_tab_privs where owner='SYSADM' and grantee='SYSADM_SELECT')   /

Riyaj Shamsudeen wrote:
> Joan
>
> It might be easier to turn on 10046 event in your session, run grant
> statement, tkprof the trace file and see which action is consuming time.
>
> Cheers
> Riyaj Shamsudeen
> The Pythian Group www.pythian.com <http://www.pythian.com>
> orainternals.wordpress.com <http://orainternals.wordpress.com>
>
>
> On Thu, May 22, 2008 at 11:41 AM, John Kanagaraj
> <john.kanagaraj_at_gmail.com <mailto:john.kanagaraj_at_gmail.com>> wrote:
>
> > If that was the case, how did the grant run very fast the first time,
> > according to the OP.
>
> Not sure if that question was addressed to me, but here's a stab at
> the answer (Disclaimer: I am a member of the BAAG party, so guessing
> violates my agreement 8-)
>
> The OP wasn't clear *when* the grants were first run. Was this when
> the number of tables/views were less? What was the other load at that
> time? Tracing the event and providing some stats would give us the
> right clues. Otherwise we are just guessing here.
>
> In general, the principles that I explained above applies: The
> execution of thousands of GRANT statements translates to thousands of
> hard parses and every SQL statement will get a different hash value,
> chopping up your shared pool into fine bits.
>
> --
> 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
>
>
>
>
>
> --
> Cheers
> Riyaj Shamsudeen
> The Pythian Group - www.pythian.com <http://www.pythian.com>
> orainternals.wordpress.com <http://orainternals.wordpress.com>

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