Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 159 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 471 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
On snapshotting APEX dictionary views

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
Answers
-
So what do you get with:
select /*+ gather_plan_statistics */ ...
followed by:
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))
-
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'))
-
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
-
Isn't result cache an enterprise only feature?