Filed 05-FEB-1999
Product Oracle Server - Enterprise Edition V7 Product Version  8.0.5.1.0 
Platform Sequent DYNIX/ptx Platform Version 4.4 
RDBMS Version 8.0.5.1.0 Affects Platforms  Generic 
Priority Severe Loss of Service Status Q/A To Development 
Base Bug N/A Fixed in Product Version 8.0.6 

Problem statement:

SELECT FROM DBA_ROLE_PRIVS EXCESSIVELY SLOW 
--------------------------------------------------------------------------------


Hdr: 816507 8.0.5.1.0 RDBMS 8.0.5.1.0 PERFORMANCE PRODID-5 PORTID-198
Abstract: SELECT FROM DBA_ROLE_PRIVS EXCESSIVELY SLOW

*** MZCLARKE 02/05/99 08:08 am ***
@TAR# 2068784.1
=========================
PROBLEM:
Select against dba_role_privs using bind variable with large
 number of users and/or roles is slow
Customer has an application which checks granted roles at connect time so
 logins are very slow.
=========================
DIAGNOSTIC ANALYSIS:
I have used testcase for bug 675022 and produced tkprof stats to confirm.
=========================
WORKAROUND:
 Use literals rather than bind variables.
=========================
RELATED BUGS:
 This problem has been logged previously:
 [BUG:675022] against 7.3 - no activity since 05/27/98
 [BUG:651681] against 7.3.2.3 - no activity since being assigned on 04/03/98
=========================
REPRODUCIBILITY:
 As this seems to be to do with the way the optimiser works it is reproducible
 every time.
=========================
TESTCASE:
 See Bug 675022
=========================
*** AGARDNER 02/05/99 08:43 am *** (CHG: Asg->SBEDARKA)
*** AGARDNER 02/05/99 08:43 am ***
@ Suhas, a duplicate of bug#675022 assigned to you. Mark has another duplicate
@ entry bug#651681 ... I've not assigned to Mark as he will be ooo next week.
*** SBEDARKA 02/09/99 04:09 am *** (CHG: Sta->30)
*** SBEDARKA 02/09/99 04:09 am ***
Rather than relying on reordering of tables in the from clause, as mentioned in
bug 651681, it would be better to use the ordered hint with the objects in the
from clause changed to sysauth$ sa, user$ u1, user$ u2, defrole$ ud. This seems
to give a reasonable response, is consistent for both literal and bind values,
in my environment. It also uses the index on grantee#.
If you have an environment with larger volumes of data than mine, then perhaps
you could test out this alternative and let me know the results. Thanks.
*** SBEDARKA 02/10/99 03:53 am ***
@While waiting for response from the filer, I have submitted the proposed fix
@for internal review.
*** SBEDARKA 03/03/99 09:16 am ***
@Reviews completed. Seeking merge request to 8.0.6 next.
*** SBEDARKA 03/04/99 01:25 pm *** (CHG: Sta->80)
*** SBEDARKA 03/04/99 01:25 pm *** (CHG: Fixed->8.0.6)
*** SBEDARKA 03/04/99 01:25 pm ***
Merge approved. Merged into 8.0.6 on 04Mar99.
@} txn st_rdbms_sbedarka_bug-816507.
@} Files are:
@}   ./admin/catalog.sql@@
@}        /main/st_rdbms_big_dev/st_rdbms_sbedarka_bug-816507/LATEST
@ Please refer to URL http://www-st.us.oracle.com:90/ddr-bin/bugfix for the
@ bug fix description details.

]]Queries on USER_ or DBA_ROLE_PRIVS were slow, owing to incorrect choice of
]]execution plan.
*** SBEDARKA 03/08/99 03:13 pm ***
Rediscovery Information:
If a query on dba_ or user_role_privs, with a like predicate on the
granted_role, performs slowly, then this is likely to be the problem.
*** SBEDARKA 04/20/99 06:00 am ***
@ Forward merged to main(8.1.6) on 20apr99.