Home » RDBMS Server » Performance Tuning » grants running extemely slow (Oracle 10.2.0.3 on Linux)
icon4.gif  grants running extemely slow [message #435299] Tue, 15 December 2009 08:10 Go to next message
king303
Messages: 10
Registered: September 2009
Location: India
Junior Member
Hello,

we are trying to run grants on tables and views for an ERP and each grant takes about 15 seconds.

Memory and logs look good.

please share your thoughts.

thanks!
Re: grants running extemely slow [message #435302 is a reply to message #435299] Tue, 15 December 2009 08:12 Go to previous messageGo to next message
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
Senior Member
trace the session.
Re: grants running extemely slow [message #435303 is a reply to message #435302] Tue, 15 December 2009 08:14 Go to previous messageGo to next message
king303
Messages: 10
Registered: September 2009
Location: India
Junior Member
thanks! please let me know how to trace DDL statments...
Re: grants running extemely slow [message #435304 is a reply to message #435299] Tue, 15 December 2009 08:19 Go to previous messageGo to next message
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
Senior Member
http://www.orafaq.com/wiki/SQL_Trace
Re: grants running extemely slow [message #435309 is a reply to message #435304] Tue, 15 December 2009 08:46 Go to previous messageGo to next message
king303
Messages: 10
Registered: September 2009
Location: India
Junior Member
thanks! cookiemonster.

please find the attached trace...
  • Attachment: output.txt
    (Size: 30.96KB, Downloaded 1232 times)
Re: grants running extemely slow [message #435311 is a reply to message #435309] Tue, 15 December 2009 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the raw trace not tkprof one.
But in your file there is some clue you flushed the SGA (or restart) just before the test which lead to long queries to rebuild in-memory dictionary information.
Do not do that.

Regards
Michel
Re: grants running extemely slow [message #435335 is a reply to message #435309] Tue, 15 December 2009 11:51 Go to previous messageGo to next message
king303
Messages: 10
Registered: September 2009
Location: India
Junior Member
Please find teh attched trace file.
Re: grants running extemely slow [message #435338 is a reply to message #435335] Tue, 15 December 2009 12:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
below is problem query
select c.name, u.name
from
 con$ c, cdef$ cd, user$ u  where c.con# = cd.con# and cd.enabled = :1 and
  c.owner# = u.user#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        8      0.00       0.00          0          0          0           0
Execute      8      0.00       0.00          0          0          0           0
Fetch        8      1.62      31.71      64399      65840          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       24      1.62      31.72      64399      65840          0           0

Re: grants running extemely slow [message #435343 is a reply to message #435335] Tue, 15 December 2009 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't activate the trace with waits option, this is too bad we can't see them.
Anyway, as I said (and BlackSwan explicitly pointed it), the reason is that Oracle is loading its dictionary in memory (because you emptied it just before).

Regards
Michel
Re: grants running extemely slow [message #435410 is a reply to message #435343] Wed, 16 December 2009 02:55 Go to previous message
king303
Messages: 10
Registered: September 2009
Location: India
Junior Member
Thanks everyone,

After deleting stats from con$, cdef$, user$ grants are ruuning quickly.
Previous Topic: Cost of a Rollback
Next Topic: select clause
Goto Forum:
  


Current Time: Sat May 18 05:55:54 CDT 2024