On snapshotting APEX dictionary views

Scott Wesley · May 16, 2018 8:36PM

G'day all,

Recently I noticed a performance issue with a plugin that referenced an APEX dictionary table.

One solution was to create a materialised view, but after I asked the question on twitter

"Do you create materialized views (snapshots) of APEX dictionary tables, to help performance of your application?"

Connor McDonald responded with

"That would (in most cases) be somewhat redundant.  Trace Apex and you'll find a huge amount of dictionary queries have the RESULT_CACHE hint (which is a "just in time" materialized view anyway).  Better option might be to increase result cache pool size"

So I thought I'd share my experience(s), try nut out exactly what's going on.

Performance problems are amplified in our development environment, particularly in the mornings. I think the VM takes a while to wake up and talk to people, like me.

Therefore, I noticed an consistent issue with the use of the dynamic quickpicks plugin.

(I'd like to modify this page to use classic report regions instead, but it highlights this dictionary problem)

So looking deeper, I found the bottleneck here

And by consistent, I mean the for the first few times (multiple) it would do this, then not be a problem.

The blip is even noticable in production:

Here is the query I found in the relevant location in the plugin

select affected_elements into l_item_names    from apex_application_page_da_acts    where application_id = v('APP_ID')    and page_id = v('APP_PAGE_ID')    and action_id = p_dynamic_action.id;

Even though I know these particular requests don't ping a table, I tried using explicit PL/SQL package variables instead

select affected_elements into l_item_names    from apex_application_page_da_acts    where application_id = apex_application.g_flow_id     and page_id = apex_application.g_flow_step_id    and action_id = p_dynamic_action.id;

I also tried scalar subqueries on the v() functions, removing app/page filter and just use action_id, with no affect.

Having previous success with using snapshots on dictionary tables, I created one for apex_application_page_da_acts, placed a unique index on action_id, kept just that as the filter, and I no longer have a performance issue.

The explain plan for the dictionary table looks clean, but large. The explain for my solutions is obviously just a unique index scan. The row count is ~20k (dev) and ~30k (prod).

But I would like to know more about Connor's suggestion.

I have another example. We've had a snapshot on apex_application_pages for years. We use it for menus, validation, lists, conditions, and no doubt other places I forget.

So let's take a simple query, and compare the dictionary query with the snapshot.

select * from apex_application_pages where application_id = p_app_id and page_id = p_page_id;

just a brute execution comparison:

iterations:10000     7.48 secs (.000748 secs per iteration) -- dictionary (7k rows)     0.81 secs (.000081 secs per iteration) -- snapshot (selective 2k rows, one unique scan)

This is why we use snapshots. Is there a better way to solve this problem?

We're currently on 5.1.3, on 12.1.

Scott

connor_mc_d-Oracle · May 16, 2018 10:25PM

So what do you get with:

select /*+ gather_plan_statistics */ ...

followed by:

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))

Scott Wesley · May 16, 2018 10:43PM

This is snapshot

select /*+ gather_plan_statistics */  * from apx_application_pages where application_id = 101 and pagE_id = 1;select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));SQL_ID  39baq696tjtqr, child number 0-------------------------------------select /*+ gather_plan_statistics */  * from apx_application_pageswhere application_id = 101 and pagE_id = 1Plan hash value: 2900579594------------------------------------------------------------------------------------------------------------------| Id  | Operation                      | Name                  | Starts | E-Rows | A-Rows |  A-Time  | Buffers |------------------------------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT              |                      |      1 |        |      1 |00:00:00.01 |      3 ||  1 |  MAT_VIEW ACCESS BY INDEX ROWID| APX_APPLICATION_PAGES |      1 |      1 |      1 |00:00:00.01 |      3 ||*  2 |  INDEX UNIQUE SCAN            | APX_APP_PAGE          |      1 |      1 |      1 |00:00:00.01 |      2 |------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------  2 - access("APPLICATION_ID"=101 AND "PAGE_ID"=1)

This is from the dictionary view

select /*+ gather_plan_statistics */ * from apex_application_pages where application_id = 101 and pagE_id = 1;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));SQL_ID  0c6tvsbkw870w, child number 0-------------------------------------select /*+ gather_plan_statistics */  * from apex_application_pageswhere application_id = 101 and pagE_id = 1Plan hash value: 966092870--------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                              | Name                          | Starts | E-Rows | A-Rows |  A-Time  | Buffers | Reads  |--------------------------------------------------------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT                      |                                |      1 |        |      1 |00:00:00.01 |    140 |      0 ||  1 |  TABLE ACCESS BY INDEX ROWID          | WWV_FLOW_TEMPLATES            |      1 |      1 |      0 |00:00:00.01 |      0 |      0 ||*  2 |  INDEX UNIQUE SCAN                    | WWV_FLOW_TEMPLATES_PK          |      1 |      1 |      0 |00:00:00.01 |      0 |      0 ||*  3 |  TABLE ACCESS BY INDEX ROWID BATCHED  | WWV_FLOW_SECURITY_SCHEMES      |      1 |      1 |      0 |00:00:00.01 |      3 |      0 ||*  4 |  INDEX RANGE SCAN                    | WWV_FLOW_SECURITY_SCHEM_FKIDX  |      1 |    20 |      7 |00:00:00.01 |      2 |      0 ||  5 |  TABLE ACCESS BY INDEX ROWID          | WWV_FLOW_PATCHES              |      1 |      1 |      0 |00:00:00.01 |      0 |      0 ||*  6 |  INDEX UNIQUE SCAN                    | WWV_FLOW_PATCHES_PK            |      1 |      1 |      0 |00:00:00.01 |      0 |      0 ||  7 |  TABLE ACCESS BY INDEX ROWID          | WWV_FLOW_LISTS                |      1 |      1 |      0 |00:00:00.01 |      0 |      0 ||*  8 |  INDEX UNIQUE SCAN                    | WWV_FLOW_LISTS_PK              |      1 |      1 |      0 |00:00:00.01 |      0 |      0 ||  9 |  TABLE ACCESS BY INDEX ROWID          | WWV_FLOW_LIST_TEMPLATES        |      1 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 10 |  INDEX UNIQUE SCAN                    | WWV_FLOW_LIST_TEMPLATE_PK      |      1 |      1 |      0 |00:00:00.01 |      0 |      0 ||  11 |  SORT AGGREGATE                        |                                |      1 |      1 |      1 |00:00:00.01 |      2 |      0 ||* 12 |  INDEX RANGE SCAN                    | WWV_FLOW_PAGE_PLUGS_FK_PAGE    |      1 |      1 |      9 |00:00:00.01 |      2 |      0 ||  13 |  SORT AGGREGATE                        |                                |      1 |      1 |      1 |00:00:00.01 |      2 |      0 ||* 14 |  INDEX RANGE SCAN                    | WWV_FLOW_STEP_ITEM_IDX1        |      1 |      1 |      6 |00:00:00.01 |      2 |      0 ||  15 |  SORT AGGREGATE                        |                                |      1 |      1 |      1 |00:00:00.01 |      2 |      0 ||* 16 |  INDEX RANGE SCAN                    | WWV_FLOW_STEP_BUTTONS_IDX1    |      1 |      1 |    12 |00:00:00.01 |      2 |      0 ||  17 |  SORT AGGREGATE                        |                                |      1 |      1 |      1 |00:00:00.01 |      2 |      0 ||* 18 |  INDEX RANGE SCAN                    | WWV_FLOW_STEP_COMPUTATI_FKIDX  |      1 |      1 |      1 |00:00:00.01 |      2 |      0 ||  19 |  SORT AGGREGATE                        |                                |      1 |      1 |      1 |00:00:00.01 |      2 |      0 ||* 20 |  INDEX RANGE SCAN                    | WWV_FLOW_STEP_VALIDATIO_FKIDX  |      1 |      1 |      0 |00:00:00.01 |      2 |      0 ||  21 |  SORT AGGREGATE                        |                                |      1 |      1 |      1 |00:00:00.01 |      2 |      0 ||* 22 |  INDEX RANGE SCAN                    | WWV_FLOW_STEP_PROCESSIN_FKIDX  |      1 |      1 |      4 |00:00:00.01 |      2 |      0 ||  23 |  SORT AGGREGATE                        |                                |      1 |      1 |      1 |00:00:00.01 |      2 |      0 ||* 24 |  INDEX RANGE SCAN                    | WWV_FLOW_STEP_BRANCHES_FKIDX  |      1 |      1 |      3 |00:00:00.01 |      2 |      0 ||  25 |  SORT AGGREGATE                        |                                |      1 |      1 |      1 |00:00:00.03 |      20 |      8 ||  26 |  NESTED LOOPS                        |                                |      1 |      1 |    27 |00:00:00.02 |      20 |      8 ||  27 |    NESTED LOOPS                        |                                |      1 |      6 |    27 |00:00:00.01 |      16 |      5 ||  28 |    TABLE ACCESS BY INDEX ROWID BATCHED| WWV_FLOW_PAGE_PLUGS            |      1 |      1 |      9 |00:00:00.01 |      5 |      0 ||* 29 |      INDEX RANGE SCAN                  | WWV_FLOW_PAGE_PLUGS_FK_PAGE    |      1 |      5 |      9 |00:00:00.01 |      2 |      0 ||* 30 |    INDEX RANGE SCAN                  | WWV_FLOW_REGION_REPORT_COL_IDX |      9 |      6 |    27 |00:00:00.01 |      11 |      5 ||* 31 |    TABLE ACCESS BY INDEX ROWID        | WWV_FLOW_REGION_REPORT_COLUMN  |    27 |      1 |    27 |00:00:00.02 |      4 |      3 ||* 32 |  TABLE ACCESS BY INDEX ROWID          | WWV_FLOW_PAGE_GROUPS          |      1 |      1 |      1 |00:00:00.01 |      3 |      0 ||* 33 |  INDEX UNIQUE SCAN                    | WWV_FLOW_PAGE_GRP_PK          |      1 |      1 |      1 |00:00:00.01 |      2 |      0 ||* 34 |  FILTER                                |                                |      1 |        |      0 |00:00:00.01 |      0 |      0 ||  35 |  VIEW                                | APEX_STANDARD_CONDITIONS      |      0 |    62 |      0 |00:00:00.01 |      0 |      0 ||  36 |    UNION-ALL                          |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||* 37 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  38 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 39 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  40 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 41 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  42 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 43 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  44 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 45 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  46 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 47 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  48 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 49 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  50 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 51 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  52 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 53 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  54 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 55 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  56 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 57 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  58 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 59 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  60 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 61 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  62 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 63 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  64 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 65 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  66 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 67 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  68 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 69 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  70 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 71 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  72 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 73 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  74 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 75 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  76 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 77 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  78 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 79 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  80 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 81 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  82 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 83 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  84 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 85 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  86 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 87 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  88 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 89 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  90 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 91 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  92 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 93 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  94 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 95 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  96 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 97 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 ||  98 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||* 99 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 100 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*101 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 102 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*103 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 104 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*105 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 106 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*107 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 108 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*109 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 110 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*111 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 112 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*113 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 114 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*115 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 116 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*117 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 118 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*119 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 120 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*121 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 122 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*123 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 124 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*125 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 126 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*127 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 128 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*129 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 130 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*131 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 132 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*133 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 134 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*135 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 136 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*137 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 138 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*139 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 140 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*141 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 142 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*143 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 144 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*145 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 146 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*147 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 148 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*149 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 150 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*151 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 152 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*153 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 154 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*155 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 156 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*157 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 158 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 ||*159 |    FILTER                            |                                |      0 |        |      0 |00:00:00.01 |      0 |      0 || 160 |      FAST DUAL                        |                                |      0 |      1 |      0 |00:00:00.01 |      0 |      0 || 161 |  SORT AGGREGATE                        |                                |      1 |      1 |      1 |00:00:00.01 |      5 |      0 ||*162 |  TABLE ACCESS BY INDEX ROWID BATCHED  | WWV_FLOW_PAGE_PLUGS            |      1 |      1 |      0 |00:00:00.01 |      5 |      0 ||*163 |    INDEX RANGE SCAN                    | WWV_FLOW_PAGE_PLUGS_FK_PAGE    |      1 |      5 |      9 |00:00:00.01 |      2 |      0 || 164 |  TABLE ACCESS BY INDEX ROWID          | WWV_FLOW_TEMPLATES            |      1 |      1 |      0 |00:00:00.01 |      0 |      0 ||*165 |  INDEX UNIQUE SCAN                    | WWV_FLOW_TEMPLATES_PK          |      1 |      1 |      0 |00:00:00.01 |      0 |      0 ||*166 |  TABLE ACCESS BY INDEX ROWID BATCHED  | WWV_FLOW_SECURITY_SCHEMES      |      1 |      1 |      0 |00:00:00.01 |      3 |      0 ||*167 |    INDEX RANGE SCAN                    | WWV_FLOW_SECURITY_SCHEM_FKIDX  |      1 |    20 |      7 |00:00:00.01 |      2 |      0 ||*168 |    TABLE ACCESS BY INDEX ROWID BATCHED | WWV_FLOW_SECURITY_SCHEMES      |      1 |      1 |      0 |00:00:00.01 |      3 |      0 ||*169 |    INDEX RANGE SCAN                  | WWV_FLOW_SECURITY_SCHEM_FKIDX  |      1 |    20 |      7 |00:00:00.01 |      2 |      0 || 170 |    TABLE ACCESS BY INDEX ROWID        | WWV_FLOW_PATCHES              |      1 |      1 |      0 |00:00:00.01 |      0 |      0 ||*171 |      INDEX UNIQUE SCAN                | WWV_FLOW_PATCHES_PK            |      1 |      1 |      0 |00:00:00.01 |      0 |      0 ||*172 |  FILTER                                |                                |      1 |        |      1 |00:00:00.01 |    140 |      0 || 173 |  FAST DUAL                            |                                |      1 |      1 |      1 |00:00:00.01 |      0 |      0 || 174 |  NESTED LOOPS                        |                                |      1 |      1 |      1 |00:00:00.01 |      8 |      0 || 175 |    NESTED LOOPS                        |                                |      1 |      1 |      1 |00:00:00.01 |      8 |      0 || 176 |    NESTED LOOPS                      |                                |      1 |      1 |      1 |00:00:00.01 |      7 |      0 || 177 |      NESTED LOOPS                      |                                |      1 |      1 |      1 |00:00:00.01 |      5 |      0 ||*178 |      TABLE ACCESS BY INDEX ROWID      | WWV_FLOWS                      |      1 |      1 |      1 |00:00:00.01 |      2 |      0 ||*179 |        INDEX UNIQUE SCAN              | WWV_FLOW_FLOW_PK              |      1 |      1 |      1 |00:00:00.01 |      1 |      0 || 180 |      TABLE ACCESS BY INDEX ROWID      | WWV_FLOW_STEPS                |      1 |      1 |      1 |00:00:00.01 |      3 |      0 ||*181 |        INDEX UNIQUE SCAN              | WWV_FLOW_PAGE_ID_IDX2          |      1 |      1 |      1 |00:00:00.01 |      2 |      0 || 182 |      TABLE ACCESS BY INDEX ROWID      | WWV_FLOW_COMPANIES            |      1 |      1 |      1 |00:00:00.01 |      2 |      0 ||*183 |      INDEX UNIQUE SCAN                | WWV_FLOW_COMPANIES_PROV_UK    |      1 |      1 |      1 |00:00:00.01 |      1 |      0 ||*184 |    INDEX UNIQUE SCAN                  | WWV_FLOW_COMPANY_SCHEMAS_IDX  |      1 |      1 |      1 |00:00:00.01 |      1 |      0 ||*185 |    VIEW                                |                                |      1 |      1 |      1 |00:00:00.01 |      0 |      0 || 186 |    FAST DUAL                          |                                |      1 |      1 |      1 |00:00:00.01 |      0 |      0 || 187 |  FAST DUAL                            |                                |      1 |      1 |      1 |00:00:00.01 |      0 |      0 |--------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------  2 - access("ID"=:B1)  3 - filter(TO_CHAR("ID")=LTRIM(:B1,'!'))  4 - access("FLOW_ID"=:B1)  6 - access("ID"=ABS(:B1))  8 - access("ID"=:B1)  10 - access("ID"=:B1)  12 - access("FLOW_ID"=:B1 AND "PAGE_ID"=:B2)  14 - access("FLOW_ID"=:B1 AND "FLOW_STEP_ID"=:B2)  16 - access("FLOW_ID"=:B1 AND "FLOW_STEP_ID"=:B2)  18 - access("FLOW_ID"=:B1 AND "FLOW_STEP_ID"=:B2)  20 - access("FLOW_ID"=:B1 AND "FLOW_STEP_ID"=:B2)  22 - access("FLOW_ID"=:B1 AND "FLOW_STEP_ID"=:B2)  24 - access("FLOW_ID"=:B1 AND "FLOW_STEP_ID"=:B2)  29 - access("FLOW_ID"=:B1 AND "PAGE_ID"=:B2)  30 - access("REGION_ID"="ID")  31 - filter("FLOW_ID"=:B1)  32 - filter("FLOW_ID"=:B1)  33 - access("ID"=:B1)  34 - filter(:B1 IS NOT NULL)  37 - filter('EXISTS'=:B1)  39 - filter('NOT_EXISTS'=:B1)  41 - filter('SQL_EXPRESSION'=:B1)  43 - filter('PLSQL_EXPRESSION'=:B1)  45 - filter('FUNCTION_BODY'=:B1)  47 - filter('REQUEST_EQUALS_CONDITION'=:B1)  49 - filter('REQUEST_NOT_EQUAL_CONDITION'=:B1)  51 - filter('REQUEST_IN_CONDITION'=:B1)  53 - filter('REQUEST_NOT_IN_CONDITION'=:B1)  55 - filter('VAL_OF_ITEM_IN_COND_EQ_COND2'=:B1)  57 - filter('VAL_OF_ITEM_IN_COND_NOT_EQ_COND2'=:B1)  59 - filter('ITEM_IS_NULL'=:B1)  61 - filter('ITEM_IS_NOT_NULL'=:B1)  63 - filter('ITEM_IS_ZERO'=:B1)  65 - filter('ITEM_IS_NOT_ZERO'=:B1)  67 - filter('ITEM_IS_NULL_OR_ZERO'=:B1)  69 - filter('ITEM_NOT_NULL_OR_ZERO'=:B1)  71 - filter('ITEM_CONTAINS_NO_SPACES'=:B1)  73 - filter('ITEM_IS_NUMERIC'=:B1)  75 - filter('ITEM_IS_NOT_NUMERIC'=:B1)  77 - filter('ITEM_IS_ALPHANUMERIC'=:B1)  79 - filter('VALUE_OF_ITEM_IN_CONDITION_IN_COLON_DELIMITED_LIST'=:B1)  81 - filter('VALUE_OF_ITEM_IN_CONDITION_NOT_IN_COLON_DELIMITED_LIST'=:B1)  83 - filter('USER_PREF_IN_COND_EQ_COND2'=:B1)  85 - filter('USER_PREF_IN_COND_NOT_EQ_COND2'=:B1)  87 - filter('CURRENT_PAGE_EQUALS_CONDITION'=:B1)  89 - filter('CURRENT_PAGE_NOT_EQUAL_CONDITION'=:B1)  91 - filter('CURRENT_PAGE_IN_CONDITION'=:B1)  93 - filter('CURRENT_PAGE_NOT_IN_CONDITION'=:B1)  95 - filter('WHEN_THIS_PAGE_SUBMITTED'=:B1)  97 - filter('WHEN_THIS_PAGE_NOT_SUBMITTED'=:B1)  99 - filter('PAGE_IS_IN_PRINTER_FRIENDLY_MODE'=:B1)101 - filter('PAGE_IS_NOT_IN_PRINTER_FRIENDLY_MODE'=:B1)103 - filter('CONDITION1_IN_VALUE_OF_ITEM_IN_CONDITION2'=:B1)105 - filter('DISPLAY_COND_IN_COND_TEXT'=:B1)107 - filter('DISPLAY_COND_NOT_IN_COND_TEXT'=:B1)109 - filter('DISPLAY_COND_EQUAL_COND_TEXT'=:B1)111 - filter('DISP_COND_NOT_EQUAL_COND_TEXT'=:B1)113 - filter('IS_READ_ONLY'=:B1)115 - filter('IS_NOT_READ_ONLY'=:B1)117 - filter('USER_IS_NOT_PUBLIC_USER'=:B1)119 - filter('USER_IS_PUBLIC_USER'=:B1)121 - filter('DISPLAYING_INLINE_VALIDATION_ERRORS'=:B1)123 - filter('NOT_DISPLAYING_INLINE_VALIDATION_ERRORS'=:B1)125 - filter('MAX_ROWS_LT_ROWS_FETCHED'=:B1)127 - filter('MIN_ROW_GT_THAN_ONE'=:B1)129 - filter('BROWSER_IS_NSCP'=:B1)131 - filter('BROWSER_IS_MSIE'=:B1)133 - filter('BROWSER_IS_MSIE_OR_NSCP'=:B1)135 - filter('BROWSER_IS_OTHER'=:B1)137 - filter('CURRENT_LANG_IN_COND1'=:B1)139 - filter('CURRENT_LANG_NOT_IN_COND1'=:B1)141 - filter('CURRENT_LANG_NOT_EQ_COND1'=:B1)143 - filter('CURRENT_LANG_EQ_COND1'=:B1)145 - filter('DAD_NAME_EQ_CONDITION'=:B1)147 - filter('DAD_NAME_NOT_EQ_CONDITION'=:B1)149 - filter('SERVER_NAME_EQ_CONDITION'=:B1)151 - filter('SERVER_NAME_NOT_EQ_CONDITION'=:B1)153 - filter('HTTP_HOST_EQ_CONDITION'=:B1)155 - filter('HTTP_HOST_NOT_EQ_CONDITION'=:B1)157 - filter('NEVER'=:B1)159 - filter('ALWAYS'=:B1)162 - filter("PLUG_CACHING"<>'NOCACHE')163 - access("FLOW_ID"=:B1 AND "PAGE_ID"=:B2)165 - access("ID"=:B1)166 - filter(TO_CHAR("ID")=LTRIM(:B1,'!'))167 - access("FLOW_ID"=:B1)168 - filter(TO_CHAR("ID")=LTRIM(:B1,'!'))169 - access("FLOW_ID"=:B1)171 - access("ID"=ABS(:B1))172 - filter(("S"."SCHEMA"="CU" OR ='Y' OR "D"."SGID"="W"."PROVISIONING_COMPANY_ID"))178 - filter("F"."SECURITY_GROUP_ID"<>0)179 - access("F"."ID"=101)181 - access("P"."ID"=1 AND "P"."FLOW_ID"=101 AND "F"."SECURITY_GROUP_ID"="P"."SECURITY_GROUP_ID")      filter("P"."SECURITY_GROUP_ID"<>0)183 - access("F"."SECURITY_GROUP_ID"="W"."PROVISIONING_COMPANY_ID")      filter("W"."PROVISIONING_COMPANY_ID"<>0)184 - access("S"."SECURITY_GROUP_ID"="W"."PROVISIONING_COMPANY_ID" AND "S"."SCHEMA"="F"."OWNER")      filter("S"."SECURITY_GROUP_ID"<>0)185 - filter(("D"."SGID"<>0 OR NVL("F"."BUILD_STATUS",'x')<>'RUN_AND_HIDDEN'))

AndrewSayer · May 17, 2018 3:30AM

I suggest use dbms_monitor to enable extended sql trace on a particular page that suffers from the slowness. You could set up a job to enable and disable it at a certain time of day (when you experience the slowdowns). Once you have a trace of a page load, run it through tkprof and see what it thinks is going on.

Id be very cautious about running parts of code manually to see its speed, there is going to be lots of dependencies you have little control over. Comparing a few thousand executions might make a change seem faster but in reality unless a user call is doing a few thousand executions of that line of code, they’re not going to notice the millisecond difference of one execution.

Remember there are many caches in play, it may be impossible to replicate the real performance problem, the only execution that matters is the one the user complained about.

After creation of your MV, did the actual response time of the page decrease around the problem time? That’s the only metric that matters

Neil Clare · May 17, 2018 4:18AM

Isn't result cache an enterprise only feature?

Forum Stats

Quick Links

Categories

On snapshotting APEX dictionary views

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

On snapshotting APEX dictionary views

Answers