I encountered this problem several years ago on an old Oracle 8i/Forms 6i system. After increasing the number of application users from about 250 to 280, the application had become unacceptably slow opening new forms, taking about 5 seconds to open each new form. When a large number of LOVs were actually implemented as new forms this was unacceptable.
I had recently been learning how to interpret statspack reports and I attempted to drill down from the top 5 waits to a suspect Query. Ultimately having identified the problem wait and the suspect hot block I proposed a solution.
Possibly with more experience, I’d have spotted the fact that in the section for most expensive SQL (in terms of buffer gets) one apparently simple SQL statement generated more than 70% of buffer gets:
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
99,955,919 593 168,559.7 71.5 2686798499
select ROLE GRPNAME ,FLAG GRPFLG from FRM50_ENABLED_ROLES orde
r by ROLE desc
The question is, why should such a simple query be so resource intensive?
Part of the answer to that question is that the query wasn’t as simple as it appeared in the statspack report. The FRM50_ENABLED_ROLES view is a view installed to support menu security in oracle forms and it is a view created by the script frmsec.sql and based on other pre-existing views:
create or replace view FRM50_ENABLED_ROLES as
select urp.granted_role role,
sum(distinct decode(rrp.granted_role,
'ORAFORMS$OSC',2,
'ORAFORMS$BGM',4,
'ORAFORMS$DBG',1,0)) flag
from sys.user_role_privs urp, role_role_privs rrp
where urp.granted_role = rrp.role (+)
and urp.granted_role not like 'ORAFORMS$%'
group by urp.granted_role;
If you analyse the query it becomes evident that the query isn’t as simple as first anticipated.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=31373358 Card=2157584180 Bytes=64727525400)
1 0 SORT (ORDER BY) (Cost=31373358 Card=2157584180 Bytes=64727525400)
2 1 VIEW OF 'FRM50_ENABLED_ROLES' (Cost=7471144 Card=2157584180 Bytes=64727525400)
3 2 SORT (GROUP BY) (Cost=7471144 Card=2157584180 Bytes=110036793180)
4 3 MERGE JOIN (OUTER) (Cost=7471144 Card=2157584180 Bytes=110036793180)
5 4 SORT (JOIN) (Cost=61 Card=1010 Bytes=17170)
6 5 VIEW OF 'USER_ROLE_PRIVS' (Cost=53 Card=1010 Bytes=17170)
7 6 SORT (UNIQUE) (Cost=53 Card=1010 Bytes=102161)
8 7 UNION-ALL
9 8 SORT (GROUP BY) (Cost=38 Card=601 Bytes=82938)
10 9 NESTED LOOPS (OUTER) (Cost=6 Card=601 Bytes=82938)
11 10 NESTED LOOPS (Cost=6 Card=601 Bytes=67312)
12 11 NESTED LOOPS (Cost=6 Card=147 Bytes=12054)
13 12 INLIST ITERATOR
14 13 TABLE ACCESS (BY INDEX ROWID) OF 'SYSAUTH$' (Cost=6 Card=179 Bytes=6981)
15 14 INDEX (RANGE SCAN) OF 'I_SYSAUTH1' (UNIQUE) (Cost=2 Card=179)
16 12 INLIST ITERATOR
17 16 TABLE ACCESS (CLUSTER) OF 'USER$'
18 17 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
19 11 TABLE ACCESS (CLUSTER) OF 'USER$'
20 19 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
21 10 INDEX (UNIQUE SCAN) OF 'I_DEFROLE1' (UNIQUE)
22 8 NESTED LOOPS (Cost=7 Card=409 Bytes=19223)
23 22 FIXED TABLE (FULL) OF 'X$KZDOS' (Cost=7 Card=100 Bytes=1700)
24 22 TABLE ACCESS (CLUSTER) OF 'USER$'
25 24 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
26 4 SORT (JOIN) (Cost=7471083 Card=213622196 Bytes=7263154664)
27 26 VIEW OF 'ROLE_ROLE_PRIVS' (Cost=5500690 Card=213622196 Bytes=7263154664)
28 27 SORT (GROUP BY) (Cost=5500690 Card=213622196 Bytes=23925685952)
29 28 NESTED LOOPS (Cost=26 Card=213622196 Bytes=23925685952)
30 29 HASH JOIN (Cost=26 Card=2615355 Bytes=214459110)
31 30 VIEW OF 'VW_NSO_1' (Cost=7 Card=179 Bytes=2327)
32 31 SORT (UNIQUE) (Cost=7 Card=179 Bytes=4654)
33 32 FILTER
34 33 CONNECT BY
35 34 FILTER
36 35 INDEX (FAST FULL SCAN) OF 'I_SYSAUTH1' (UNIQUE) (Cost=2 Card=1065 Bytes=13845)
37 35 FIXED TABLE (FULL) OF 'X$KZDOS' (Cost=7 Card=1 Bytes=13)
38 34 TABLE ACCESS (BY USER ROWID) OF 'SYSAUTH$'
39 34 INDEX (RANGE SCAN) OF 'I_SYSAUTH1' (UNIQUE) (Cost=2 Card=179 Bytes=4654)
40 30 NESTED LOOPS (Cost=14 Card=1461092 Bytes=100815348)
41 40 TABLE ACCESS (FULL) OF 'SYSAUTH$' (Cost=14 Card=17888 Bytes=697632)
42 40 TABLE ACCESS (CLUSTER) OF 'USER$'
43 42 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
44 29 TABLE ACCESS (CLUSTER) OF 'USER$'
45 44 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
Ultimately, I believe that the problem was a combination of application design and business process.
Firstly an overly granular security model resulted in up to 59 roles being granted to each user and secondly an absolute refusal to delete expired user accounts and only lock them when they became defunct led to a combination of over 2000 user accounts and over 80000 granted roles.
However without a change in corporate policy, could the problem be resolved in an acceptable manner?
Luckily Metalink has a couple of documents that propose alternative definitions of the FRM50_ENABLED_ROLES view: 107761.1 & 256561.1
While not intended primarily to increase performance, in testing the alternative implementations looked promising.
I raised an SR with Oracle to see what their approach would be and to confirm that they would support these alternative implementations of the view.
Oracle responded with the response that both Forms 6i and the view were unsupported.
The customer might have misunderstood when I explained that the level of support for the new version of the view was the same as that for Forms 6i.
Ultimately the new view definition was implemented and resolved the problem up till the decommissioning of the application earlier this year.
The essential lesson is that when you over-engineer a solution (and by the time I arrived, no one actually really had any understanding as to what the roles actually granted access to!) then even the oracle supplied views can fail!
The chances are that none of the developers at oracle working on Oracle 8i envisioned a system where only 20% of the created users were active and the total number of granted roles reached 80,000, so the issue can’t realistically been seen as foreseeable.
Ultimately I’m confident that the changes since Oracle 8i, where the cost base optimiser is now used to access the data dictionary, are likely to have resolved this issue for later versions, but if you do pick-up a long established Forms application where the back end technology hasn’t been brought up to date then as the number of users grows then this might become a problem for you.
