Wednesday, 29 January 2020

Unpivoting Oracle APEX meta-data

There are APEX dictionary views for most of the data that represents the 'source' that is your APEX application meta-data.

Today I found one place where I really wanted to run a query to find references to potential data - application substitution strings.

APEX Application Substitution Strings

And yes, I have a CSS rule to right-align those names, to make it easier to read.

I couldn't find any references in the APEX dictionary, so I looked in the Oracle data dictionary to find where it may live.
select * from all_tab_columns 
where column_name like 'SUB%03';
So not only are these stored in a place inaccessible to us mere-mortal developers, they are also stored in 20 sets of name/value columns - not rows.

This means if you have a dozen applications, with references in slightly different locations for each application, then string searches might be a pain.

Unpivot to the rescue!
select * from (
  select id app_id, alias, name -- key facts
      -- all the substitution stringz!
      ,substitution_string_01, substitution_value_01
      ,substitution_string_02, substitution_value_02
      ,substitution_string_03, substitution_value_03
      ,substitution_string_04, substitution_value_04
      ,substitution_string_05, substitution_value_05
      ,substitution_string_06, substitution_value_06
      ,substitution_string_07, substitution_value_07
      ,substitution_string_08, substitution_value_08
      ,substitution_string_09, substitution_value_09
      ,substitution_string_10, substitution_value_10
      ,substitution_string_11, substitution_value_11
      ,substitution_string_12, substitution_value_12
      ,substitution_string_13, substitution_value_13
      ,substitution_string_14, substitution_value_14
      ,substitution_string_15, substitution_value_15
      ,substitution_string_16, substitution_value_16
      ,substitution_string_17, substitution_value_17
      ,substitution_string_18, substitution_value_18
      ,substitution_string_19, substitution_value_19
      ,substitution_string_20, substitution_value_20
  from apex_190200.WWV_FLOWS -- direct from underlying view
)
unpivot (
(str, val) -- new columns
for rec in -- denoted by 
  ((substitution_string_01, substitution_value_01) as '01'
  ,(substitution_string_02, substitution_value_02) as '02'
  ,(substitution_string_03, substitution_value_03) as '03'
  ,(substitution_string_04, substitution_value_04) as '04'
  ,(substitution_string_05, substitution_value_05) as '05'
  ,(substitution_string_06, substitution_value_06) as '06'
  ,(substitution_string_07, substitution_value_07) as '07'
  ,(substitution_string_08, substitution_value_08) as '08'
  ,(substitution_string_09, substitution_value_09) as '09'
  ,(substitution_string_10, substitution_value_10) as '10'
  ,(substitution_string_11, substitution_value_11) as '11'
  ,(substitution_string_12, substitution_value_12) as '12'
  ,(substitution_string_13, substitution_value_13) as '13'
  ,(substitution_string_14, substitution_value_14) as '14'
  ,(substitution_string_15, substitution_value_15) as '15'
  ,(substitution_string_16, substitution_value_16) as '16'
  ,(substitution_string_17, substitution_value_17) as '17'
  ,(substitution_string_18, substitution_value_18) as '18'
  ,(substitution_string_19, substitution_value_19) as '19'
  ,(substitution_string_20, substitution_value_20) as '20'
  )
)
order by app_id, str 
This query transposes all the string/value columns into rows, each denonimated by the new "REC" column.

Note, this query can only be executed by those with the APEX Administrator Role, and if you want to have it within a view that could be executed by other schemas, then select access on wwv_flows is needed with grant option.

Columns unpivoted into rows

By placing the query in a view, I can now make queries like this to find any substitution strings in any applications that have date references.
select * from apx_app_sub_strings
where val like 'APP_DATE%';
Pretty neat, well at least as far as what I was trying to do.

This query may break in future versions, as it's based on an underlying, undocumented view.
I also think that once upon a time, there were fewer pairs.

Thursday, 16 January 2020

Performance on Classic Reports with LOVs

Just an observation made yesterday that I thougt was worth everone's consideration.

I created a classic report on a reference table with a touch over 1000 rows, with pagination set at 150 rows per page.

That’s all I had on the page.

The table had it's own set of lookups for a couple of columns, so I assigned two LOVs I already defined in Shared Components.

Classic Report LOV

Next minute, I had a simple reference page taking 10 seconds to load.


This is information from apex_workspace_activity_logs, showing results where no LOV was applied, two LOVs, and just one. I later found the number of rows shown in the pagination set also varied the result.

As I removed one of the LOVs, I quickly realised this was the problem.

I ran the page in debug mode, to see if something crazy was happening as it constructed the query.
When I used debug = YES, it pinned all the effort onto the one line item - but not the query itself.
rows loop: 150 row(s)

Looking again using LEVEL9, I could see that every row took a bit of work, not just the query.
In fact each row had three debug line items referencing my LOV lookup SQL.
begin begin SELECT name display_value, id return_value  
bulk collect into wwv_flow_utilities.g_display,wwv_flow_utilities.g_value 
FROM  my_secondary_ref 
WHERE SYSDATE BETWEEN eff_start AND COALESCE(eff_end, SYSDATE)
ORDER by 1
; end;
end;
Debug chart

Apparently these are only melded into the SQL for IR/IG, not Classic Reports. Context switching kills the page instead - well, at least 149 extra executions of the one statement.

I ran another test so I could check v$sqlarea, and sure enough, there are far more executions of this lookup tha necessary - unnecessarily churning the CPU.

v$sql_area status

I’ve tended to embed these in my queries anyway, often as some form of scalar subqery.
This is a habit I started as a Oracle Forms developer, since Post-Query lookups made repeated network calls that just slowed the application down.
select sm.*
  ,(select name
    from my_lookup m
    where sm.id = m.id) my_desc
from some_model sm
This makes me really wonder how much of our existing page generation time is spent on this work?
Even on small classic report regions. This all adds up.
If only there was a way I could find all occurences of LOVs used in classic reports... wait a minute! I can query the APEX dictionary! ;p
select application_id, pagE_id, page_name, region_name
  ,(select maximum_rows_to_query 
    from apex_application_page_regions r
    where r.region_id = c.region_id) nbr_rows
  , column_alias, heading 
  , named_list_of_values, inline_list_of_values
  , column_is_hidden, condition_type
  ,build_option, column_comment
from apex_application_page_rpt_cols c
where application_id = 102
and display_as_code = 'TEXT_FROM_LOV'
order by nbr_rows desc
It's a shame such a nify declarative feature impacts performance this way.

Wednesday, 15 January 2020

So you've submitted an abstract

You've thought hard about your conference talk ideas; you then fleshed out your idea and worked hard on an abstract; and finally you plucked up the courage to make the submission to that big conference.

What next?

Keep momentum.

#physics

It really depends on the style of your talk, but in most cases, just keep whatever momentum you have (or had back in December), and work on it in some form every week.

Wait, abstracts closed. Public voting complete. Aren't I just waiting for a March notification before I bother starting?

No.

There are a few reasons you could be working on your talks each week. For instance:
  1. Rome wasn't built in a day - it depends, but a reasonable talk should take at least 20 hours to develop, plus the time needed to gather the experience the talk represents, past, present, or future. Presentations left to the last minute can look & sound like it. Presentations started early will have time to cultivate, allow your ideas to progress as you write up progress so
  2. Experience - even if your talk isn't accepted, you have gained the experience necessary for a topic you've considered worthy enough to talk about it. Maybe you've already gained the experience, but compiling your thoughts will help you gain a deeper understanding of the topic.
    Maybe you gathered a few important insights along the way.
  3. Aim high - OK, maybe you end up getting a "sorry, not this time" email for the big conference. So what's happening locally? Do you have a regular meetup nearby? Is there another conference where this talk would fit well? Would the team at your current workplace benefit from listening to your talk for an hour? If you write it, people will come.
And as an extra tip 
  • Transform your speaker notes into search engine fodder
    I need to do this more often. I've seen more organised folk post their notes on GitHub, or as some sort of blog post. As a developer, sometimes I stumble across a great set of slides, but yearn for a little more context or content. It will always be useful for somebody, won't hurt your SEO, and you'll thank yourself immediately, and again in 6 months time when you google your own post.
How am I tackling my own submissions?

Slow at first, it's been a busy summer, but now I'm back at work building things, I feel like getting back into regular time aside.

I've submitted four ideas, to help my chances of being accepted. Maybe one idea is really good, but every other Tom, Dick, and Henrietta has submitted some variation of that idea. Perhaps your secondary submission fits rather well instead, among all the rest?

And given the thoughts above, I could make progress on all these ideas, knowing that they'll be useful somewhere along the line. Our local user group always seem happy to have me yak on about APEX.

Anyhoo, these were the titles for my Kscope20 submissions.
  • Oracle Reports to AOP Case Study
  • Visualising APEX Performance Monitoring
  • Navigating APEX Version Upgrades
  • A Practical Guide to APEX Authorisation Schemes
I was involved in the public voting exercise, and I not only saw some similar submissions, but a whole bunch of submissions on ideas I've considered, had, or wished that I had. Even if only a third of those submissions ever get produced, there's going to be some amazing content out there.

My titles are a little utilitarian, and lack some awesome word play I saw when reviewing abstracts, but I'm pretty excited about the building the content.

What am I doing to prepare these? I haven't got to this yet, have I?
  • AOP - I strategically chose this topic as I'm learning this as part of business as usual at work. My aim is to present a cheat-sheet style session to help new AOP developers hit the ground running. All I need to do is show up to work each day to prepare, though I need bed down the session structure.
  • APEX Performance monitoring - I've been writing charts & reports on these log tables for years, and I have the confidence I can piece together this presentation at will. I've offered different formats, so it may even be hands-on.
  • Version upgrades - I've done a few of these over time, and I've been making notes. I need to start playing on an empty canvas to help cultivate exactly how it will play out.
    And by this I mean probably both a simple text file that slowly fleshes out a list of key items I want to cover, and some sort of scratching/drawing the represents the story of my talk.
  • Authorisation Schemes - this one's been a real slow burn, something I've been wanting to do for a long time, but I hope to turn this into more than just a presentation. This is what I've been hinting about in this sites left menu bar, some form of publication.
    After piecing together a fairly clear breakdown of content, I may have procrastinated a little.
Regardless of where my experience is coming from to formulate the presentation, I typically start with a simple text file, flesh about my ideas, reorder as necessary, then start my slides.

Once the slides are done, I tend to go through them a few times, with a text file ready to note down simple modifications I want to make, without disrupting my flow too much.

Rinse and repeat, until your practice in front of a mirror/camera/friend comes out smooth, and on time.

Don't worry if you get nervous. We all get nervous.

PS - some of us have been living under metaphorical rocks, so if you've missed a bunch of posts by Martin Widlake on presenting, I recommend you check them out.

Friday, 13 December 2019

Friday Funny - Festival of serious yahoos

I don't care what you all think, this is my blog, and I continue to laugh at this SQL joke.

select column_value
from apex_string.split('YAHOO~SERIOUS~FESTIVAL', '~')
order by dbms_random.value

If you're a Simpsons fan, you may recall this clip

Yahoo Serious Festival - The Simpsons
If you're not aware, Yahoo Serious is an Australian actor from the movie of the same name. He made it onto Time magazine, then nothing.

But how nifty is that apex_string.split function? And it's cousin join?
Although recently I saw Connor use what appears to be a native option, one that doesn't require an APEX package. On the blog topic list...

Tuesday, 10 December 2019

Where would we be if we just believe?

As a science aficionado, there are certain phrases that ... catch the eye.

Recently on twitter there was an interesting thread that continued from Michelle Skamene's post on Top 15 Tuning Tips for APEX.  Michelle provided a wonderful follow-up post summarising the outcomes of the thread.

Point 9 suggests we avoid HTML in our queries, and use HTML expressions. This is undeniably good practice, but there was a question regarding how much performance is gained. Patrick Wolf summarises it well (sorting, XSS, context switching).

I've been curious about this for a while myself, and what better way to verify the truth than do some science ;p

I thought about the recent experimentation I did with interpreted code, and recent client work I've done with bind variables, so I thought I'd not only compare timings with a simple test harness I use, but see what v$sqlarea had to say.

When considering what SQL to compare, I decided to use the simple bit of SQL I used for my AskTOM Office Hours demo app. I saw how quickly the embedded HTML expanded to fit repeated business rules, and why not use something simple. If any difference was to be seen, let's see if it shows up with something basic, just like Juergen suggested.

The first query is just a simple query on scott.emp, with an extra expression to determine if the row is 'special'. This information is used declaratively within APEX, so these calculations are absent from this testing. The complexity has been transferred, but simplified. That's the reason it's best practice.
cursor c_1 is
select /*+ qb_name(c1) */ empno, ename, job, hiredate
  ,case
   when hiredate < date '2000-01-01'
     then 'special'
   end last_cent
from scott.emp
So, let's just concentrate on the differences in the SQL we can measure.

The second query has the HTML tags embedded, repeated on columns where I substituted the column into the class attribute in the previous example. The code required expands quickly, hence the argument to keep the SQL and presentation layer separate.
select /*+ qb_name(c2) */empno
  ,'<b>'||ename||'<b>' ename
  ,case when hiredate < date '2000-01-01'
    then '<span style="color:purple;font-weight:bold;">'
     ||'<span title="'||job||'">'||to_char(hiredate,'fmddth Mon YYYY')||'</span>'
   end ||'</span>'
   as hireDate
  ,case when hiredate < date '2000-01-01'
     then '<span style="color:purple;font-weight:bold;">'||job||'</span>'
   end job
from scott.emp
The third query introduces context switching to PL/SQL due to the inclusion of htf.bold and apex_escape.html. This protects the output we'd otherwise have escaped using the relevant property. Or you could mitigate usage by sanitising data on the way in.
cursor c_3 is
select /*+ qb_name(c3) */empno
  ,htf.bold(apex_escape.html(ename)) ename
  ,case when hiredate < date '2000-01-01'
    then '<span style="color:purple;font-weight:bold;">'
     ||'<span title="'||apex_escape.html(job)||'">'||to_char(hiredate,'fmddth Mon YYYY')||'</span>'
   end ||'</span>'
   as hireDate
  ,case when hiredate < date '2000-01-01'
     then '<span style="color:purple;font-weight:bold;">'||apex_escape.html(job)||'</span>'
   end job
from scott.emp;
If we just compare throughput, there is a clear loser. Context switching between SQL & PL/SQL is just too much, though I would like to think in recent versions of the database, those functions could be enhanced with such devices as the UDF pragma?
iterations:50000
     3.98 secs (.0000796 secs per iteration)
     5.16 secs (.0001032 secs per iteration)
    33.09 secs (.0006618 secs per iteration)
I just run these queries lots of times to measure throughput. Tom Kyte wrote a more enhanced test bed called runstats, if you want juicier details.
-- and start timing...
    l_start := dbms_utility.get_time;

    FOR i IN 1 ..c_iterations
    LOOP
     for x in c_1
     loop
        null;
     end loop;

    END LOOP;
    l_end := dbms_utility.get_time-l_start;
    dbms_output.put_line( TO_CHAR(l_end/100, '99990.00') 
                       || ' secs ('||(l_end/c_iterations/100) || ' secs per iteration)' );
What about v$sqlarea?
select substr(sql_text, 1, 22)sql, fetches, parse_calls, buffer_gets, sharable_mem
   ,persistent_mem, runtime_mem, user_io_wait_time
   ,plsql_exec_time, cpu_time, elapsed_time, physical_read_bytes 
from v$sqlarea  
where sql_text like 'SELECT%SCOTT.EMP%'
order by 1

v$sqlarea results

These statistics validate the timings, and I think also validates this as a performance tip that belongs in Michelle's list.
While performance difference with/without HTML may be marginal in this case, the supplementary benefits make it clear best practice.

Tuesday, 3 December 2019

Include new APEX templates in an older APEX instance

Have you seen that super awesome theme in the new APEX version, then wondered how long it will it be before your site upgrades so you can actually use it?

What if I told you that your current version could be retrofitted to use that template?

I really like the look of the Content Row template, I think that's going to serve many developers good purpose.
Sure, I could create something similar now, but if I do as much as I can the same, then it should grease the wheels come upgrade time.

Content Row 18.2 vs 19.2

To make this happen, I
  1. Downloaded APEX 19.2
  2. Copied the /theme_42/1.4 folder to my 18.2 instance
  3. Created the report template, including template options
  4. Added the region component in my app
The first two steps are all about making the relevant CSS available to your instance.

After I had the /1.4 folder in place, I thought I would need to increment the Theme folder, but that just introduced other issues.


Instead, I just referenced the ContentRow.css file in the pages I wanted to use it.
This is the path where you can find it, so it can be placed wherever you like on your older instance.
/i/themes/theme_42/1.4/css/core/ContentRow.css
Alternatively, you could include this for all pages in the User Interface attributes.

Then I needed to create the named column (row template) in my 18.2 application, details of which I just copied from a 19.2 instance.

Content Row syntax

I can even transfer the template options, so I started transferring from details on the page
You can also use this query to find instances within my 19.2 application.
select name, display_name, display_sequence, css_classes, group_id
  ,(select display_name
    from APEX_APPL_TEMPLATE_OPT_GROUPS
    where template_opt_group_id = group_id) group_name
  , help_text
from APEX_APPL_TEMPLATE_OPTIONS 
where application_id = 32532
and report_template = 'Content Row'
order by display_sequence
Then I created the template options in 18.2.

Add Template Option

And sure enough, they become available on my 18.2 region component.

Page Designer outcomes

Due to the differences in availability of template option groups, some manifest a little differently, but I personally prefer a one click checkbox to the more fiddly select list.

All that said, I'm not sure I'm entirely sold on the small screen format, though maybe thing will change when I get more realistic data in there.

Content Row - mobile

Now I'm sure when I change over to use the actual template when I'm in 19.2, I'll need to re-apply the template options - but Shakeeb likes the idea of retaining shared template options, so you never know.

This was all done in 18.2. If you're on an earlier version, your mileage may vary.

If you're not using the Universal Theme, then you're probably not going to get very far.

Thursday, 28 November 2019

Interpreted code in APEX

A few years ago I posted a comparison between plugin code left in the source attribute, vs code that has been transferred to a PL/SQL package.

In the interests of good science, and I wanted to chat about it at next week's Office Hours, I wanted to repeat this test.

I had a little difficulty working out how I got the metrics, I think APEX debugging has changed a little since I ran the test. Instead I considered looking at v$sqlarea to assess performance.

Turns out I quickly found the relevant queries using the following SQL, which a case statement to help me identify the difference between rows each time
select
  case
  when sql_text like 'begin declare  begin wwv_flow_plugin_api.%' then 'API'
  when sql_text like 'begin declare function x return varchar2 is begin return null; %' then 'call dynamic'
  when sql_text like 'begin declare FUNCTION enkitec_sparkline_render%' then 'parse dynamic'
  when module = 'SQL Developer' then ' me'
  end which
  ,executions
  ,loads
  ,parse_calls
  ,disk_reads
  ,buffer_gets
  ,user_io_wait_time
  ,plsql_exec_time
  ,rows_processed
  ,cpu_time
  ,elapsed_time
  ,physical_read_requests
  ,physical_read_bytes
  ,lockeD_total
  ,pinned_total
  ,sql_text
from v$sqlarea
where sql_text like '%sparkline%'
order by which
When APEX invoked the code using an API call, it looked like

begin declare begin wwv_flow_plugin_api.g_dynamic_action_render_result := apx_plug_sparkline.render (p_dynamic_action => wwv_flow_plugin_api.g_dynamic_action,p_plugin => wwv_flow_plugin_api.g_plugin );end; end;

When APEX needed to parse the entire function, it looked like

begin declare FUNCTION enkitec_sparkline_render ( p_dynamic_action IN APEX_PLUGIN.T_DYNAMIC_ACTION, p_plugin IN APEX_PLUGIN.T_PLUGIN ) RETURN APEX_PLUGIN.T_DYNAMIC_ACTION_RENDER_RESULT IS

To do this test, all I needed to do was paste the PL/SQL back into the source attribute; I didn’t bother changing what was invoked in the callback fields.

After 50 page refreshes each in dev – parsing the code was at least twice as slow, based on CPU time.

I suspect the 'call dynamic' was the builder validating the code.

Basic glimpse

In an environment with more activity, I was able to compare the standard API call with a few hundred that included parsing the code.

Click/tap to embiggen

If I take 141312 CPU time, divide by 301, then multiply by 17778, I get parsing around 1.8x the amount time as using the API.
The same goes for elapsed time, while the PLSQL Execution time remains the same.

Plus there’s disk reads, an obscene amount of buffer gets, considering the execution ratio.

I tried a second plugin (nested reports), and while the CPU ratio seemed the same, the physical reads were over twice as high.

Remembering this is just placing the code in the box – I haven’t even referenced it.

Too many words and numbers? How about a graph.

If this seems a fair reason to reduce the amount of interpreted code you have…

Twice the work, for nothing.

... then how does this make you feel?

What's a buffer, and why do we want to get it?

It seems by removing the code from the source attribute of the plugin, we measurably reduce the amount of work the database does. Imagine if we reduce our PL/SQL usage throughout the application?

So I conclude

  1. Use bind variables
  2. Put your code in packages