Home » RDBMS Server » Performance Tuning » query to data dictionary tables taking long time (11.2.0.4 on Linux)
query to data dictionary tables taking long time [message #633680] Tue, 24 February 2015 03:57 Go to next message
orausern
Messages: 819
Registered: December 2005
Senior Member
Hi,

In my database, we have the following sql taking a lot of time:

SELECT COUNT (1) FROM USER_TAB_PRIVS WHERE TABLE_NAME = UPPER (:B1 ) AND PRIVILEGE = 'SELECT' AND GRANTEE = 'PUBLIC';

The sql gets fired during DB deployment and it is executed 2100 times to check priv.s on DB objects before granting SELECT on them if the privilege is not already given. It takes 1.1 second per execution and so it takes overall about 40 minutes.

Any suggestion on how to improve it? For example, can calculating the fixed schema stats help on it?

Thanks,
OrauserN
Re: query to data dictionary tables taking long time [message #633681 is a reply to message #633680] Tue, 24 February 2015 03:59 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
You'd be a lot quicker just granting select anyway Smile
Re: query to data dictionary tables taking long time [message #633685 is a reply to message #633681] Tue, 24 February 2015 04:16 Go to previous messageGo to next message
cookiemonster
Messages: 13264
Registered: September 2008
Location: Rainy Manchester
Senior Member
As Roachcoach says this is completely pointless. You don't get an error granting a priv that already exists so just grant everything you need to grant.
Re: query to data dictionary tables taking long time [message #633687 is a reply to message #633685] Tue, 24 February 2015 04:25 Go to previous messageGo to next message
orausern
Messages: 819
Registered: December 2005
Senior Member
Thank you both. Yes that is certainly an excellent point to implement. That apart, is there something that can be done to make that query go faster? (it will take me at least a few days to get approval and go ahead etc.)

Since it is a very simple select on a data dictionary table, can gathering the fixed stats help us on it?

Thanks,
Re: query to data dictionary tables taking long time [message #633689 is a reply to message #633687] Tue, 24 February 2015 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is not a table a view which definition is:
select ue.name, u.name, o.name, ur.name, tpm.name,
       decode(mod(oa.option$,2), 1, 'YES', 'NO'),
       decode(bitand(oa.option$,2), 2, 'YES', 'NO')
from sys.objauth$ oa, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u, sys.user$ ur,
     sys.user$ ue, table_privilege_map tpm
where oa.obj# = o.obj#
  and oa.grantor# = ur.user#
  and oa.grantee# = ue.user#
  and oa.col# is null
  and u.user# = o.owner#
  and oa.privilege# = tpm.privilege
  and userenv('SCHEMAID') in (oa.grantor#, oa.grantee#, o.owner#)

And counting all occurrences when you just want to know if one exists is not an optimal way.
Re: query to data dictionary tables taking long time [message #633690 is a reply to message #633687] Tue, 24 February 2015 04:30 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
It depends. Dictionary queries are hard to make behave - baselines work where I've employed them though (OEM queries over DBA_HIST stuff).

However, you need to approach them with the same eye as you do a normal problem. That is to say, find out where it is spending it's time and see if that can be tuned down. You're going to have two possibilities - that there's nothing you can do without hardware or there are elements you can speed up. Dig into the queries and see what happens, maybe stats will help - or maybe you're just at the limits of the kit.

Note that keep performance on these between versions may prove to be irksome Smile
Re: query to data dictionary tables taking long time [message #633691 is a reply to message #633687] Tue, 24 February 2015 04:32 Go to previous message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
Analyze your daata dictionary, dbms_stats.gather_dictionary_stats. You need to to do this from time to time, anyway.
Previous Topic: A oracle query causing 100% cpu utilization and blocking other sessions.
Next Topic: Parallel direct path insert no quicker (merged 2 )
Goto Forum:
  


Current Time: Mon Oct 15 20:18:47 CDT 2018