Oracle Observations

April 17, 2012

Oracle forms application slow when opening new forms with menu role security enabled.

Filed under: 8i,Oracle forms,Performance — bigdaveroberts @ 9:07 pm

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
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$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.

March 6, 2007

Problems encountered migrating to Oracle 8i

Filed under: 8i — bigdaveroberts @ 11:49 am

Another document that I produced long ago was the issues encounterd upgrading a small document management system to Oracle8i.

With hindsight, the fact that we also changed from the RBO to the CBO explains at least the first issue, but may also be implicated in the other two issues as well.

1 SQL Order of execution.

It has always been my experience that if there are no other factors to consider, Oracle will apply conditions in reverse order, executing the last where clause first. This no longer appears to be the case.

For example:

The table testdate has a column logdate that consists of either a single space or a date as a character string in YYYY MM DD HH24:MI format.

The following select statement works fine under Oracle 7 as all of the rows where the value is a single space are ignored by the last line.

Select count (*) from testdate
Where to_date(logdate, 'yyyy-mm-dd HH24:MI’) <> sysdate
And length(logdate) = 16 ;

However under oracle 8 the following error occurs:

ORA-01840: input value not long enough for date format

Indicating that the first where clause is executed on the rows first!

Note also, that an attempt to get round the problem was made by creating a view that excluded the rows containing a space, however this still failed as Oracle appeared to optimise the query and apply the to_date function on the fields that were not selected by the view!

2 Change in behaviour for invalid private synonyms.

In oracle 7, if an invalid private synonym (where the target object does not exist) and a valid public synonym with the same name exists, then the system will use the public synonym without raising an error. In oracle 8 an error is raised.

3  Ambiguous column names

You are no longer allows to have duplicate column names in a subselect. In oracle 7 a column name could be identical to a second column name as long as they were given separate aliases in the enclosing select statement.

I.e. the following worked under Oracle 7 but not under oracle 8:

Select description d1, description d2
From (select t1.description, t2.description
From t1, t2);
Select description d1, description d2                *
ERROR at line 1:
ORA-00918: column ambiguously defined

Under oracle 8 it would need to be rewritten thus:

Select d1 d2
From (select t1.description d1, t2.description d2
From t1, t2);

Blog at