query to data dictionary tables taking long time [message #633680] |
Tue, 24 February 2015 03:57 |
orausern
Messages: 826 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 #633687 is a reply to message #633685] |
Tue, 24 February 2015 04:25 |
orausern
Messages: 826 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 |
|
Michel Cadot
Messages: 68716 Registered: March 2007 Location: Saint-Maur, France, https...
|
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.
|
|
|
|
|