Oracle Observations

April 20, 2012

Keep calm and Gather stats!

Filed under: Uncategorized — bigdaveroberts @ 12:53 am

Everything already exists!

Or at least everything exists on the Internet before I’ve thought of it!

The topic of #optimizerbumperstickers hit twitter courtesy of @SQLMaria.

Someone suggested: Keep calm and gather stats!

Short pause, google: ‘keep calm poster generator’, finds: http://www.keepcalm-o-matic.co.uk

A short while later, a mug and a tea-shirt are on their way!

Advertisements

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.

Blog at WordPress.com.