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

Advertisements

December 18, 2011

The UKOUG performance tuning round table.

Filed under: Performance — bigdaveroberts @ 2:59 pm

At the tuning round table there were three experts: Jonathan Lewis, Maria Coulgan and Greg Rhan.

Hopefully Jonathan Lewis needs no introduction, Maria Colgan has worked at oracle in the server technology performance team and Greg Rahn works in the real world performance group, specialising in data warehouses and parallel query.

The first question was how the merge command speeded up processing.

The answer was that it produces 2 arrays and does bulk inserts and bulk updates.

However it was stated that depending on the actual data, array processing with exceptions might be a more effective solution.

In a discussion on sub-query un-nesting it was observed that giving the oracle optimiser a nested sub-query gives oracle the opportunity to un-nest the query, if you un-nest the query yourself Oracle has less potential optimisations to consider.

However, when Oracle un-nests a query, it doesn’t always produce the same plan that would be generated if the optimiser was presented with an already un-nested query!

In a discussion of AutoDoP (Degree of Parallelism), it was noted that it doesn’t consider the cost of the insert. I.e. a CTAS might select the wrong degree of parallelism for the select if the insert cannot cope with the rate that data is generated by the query!

AutoDoP can also be confused by compressed data.

It was suggested that the lack of persistence in Oracle’s implementation of cardinality feedback was a problem.

The response was: ‘watch this space’, presumably indicating that functionality in this area is still evolving and this is one of the things that might change!

One interesting thing that I had not considered was the possibility of forcing the optimiser version used to execute a query can be forced using a hint.

Standard edition was then considered, primarily because Standard edition included stored outlines, which are now deprecated but can’t be replaced by SQL plan management because this is currently an enterprise only feature!

It was suggested that while stored outlines were deprecated, there were currently no plans to de-support this feature.

Slightly off topic the question was asked: What is your favourite new feature in standard edition?

The two answers offered were:

1. Extended statistics
and
2. Pending statistics

On the subject of flash cache, it was suggested that using flash cache was only of potential value when you had maxed out the ram in the machine. i.e. the overhead of managing data in memory was much lower than the overhead of managing data in flash cache through the disk i/o code path.

Blog at WordPress.com.