Re: grant select command so slow
- From: Joan Hsieh <joan.hsieh@xxxxxxxxx>
- To: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- Date: Fri, 11 Jul 2008 14:18:21 -0400
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@xxxxxxxxx <mailto:john.kanagaraj@xxxxxxxxx>> 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 **
--
//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>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
