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

On the PL/SQL you don't write when using APEX.

Fancy joining in on a discussion with PL/SQL and Oracle APEX community members from around the world?

I'm honoured join Karen Cannell and Scott Spendolini, to be hosted by Steven Feuerstein in the next AskTom PL/SQL Office Hours on December 3, 2019.

It seems a few people haven't heard of these "office hours" sessions, but they're worth a go - more than just your average webinar. And they're all recorded for later viewing.

Topic

This is just the PL/SQL sessions! Every month they have one for Oracle APEX, SQL, Spatial, JavaScript, JSON, Database Security, and even more.

Product segments

It's probably one of the richest learning resources Oracle currently produces, and I really need to tap into more of it.

If the timezone gods allow it, I recommend live participation, as you really do get your questions answered. The produce managers handle the chatline, ensuring questions get followed through.

I think Steven's ensuring we have a good 15 minutes for questions, because they always come through. They keep the chat transcripts, too. The one for Oracle Forms Modernisation was all about the questions!

And it's free - you only need to sacrifice your email address, though it's probably the best email feed I have. Concise, infrequent, except my forum feed, of course.

On December 3, in my 15 minutes of fame, I plan to (spark debate?) about "the PL/SQL you don't need to write". I delivered this as an APEX application, instead of whipping out the ol' powerpoint.

Join in next week, and Karen will start with some decent habits, and the other Scott will no doubt enlighten us about security, or some such.

Free training - from Oracle... well, in this case, delegated to some passionate volunteers. ;p

APEX Low code expressions

The following where clause expression would normally return false, therefore the query would deliver no rows.

select * from dual where null != 'X'

no rows returned

A null can never equal a value, and hence cannot be determined to be disimilar to another value. False is the expected condition.

Turns out when using it as what appears to be the  declarative low code alternative, it returns true – and renders.

So if you want to display something based on a field that may have a value you don’t want, eg:

This will display the component if P10_NULL is anything but "yuck", including a null value.

In my business case, there was an occasional value of "D" that I need to hide things for.

Instead of using (slower) PL/SQL expression
nvl(:P42_CAT,'x') != 'D'

or SQL expression (anything but = D)
lnnvl(:P42_CAT='D')

As Kim suggests, our brain is seeing something SQL-ish, when really it's a low code translation that probably nails most scenarios - and maybe it would be better re-worded slightly as "not equals"

Our low code alternative is not only faster, but simpler. It turns it into just another if statement, as opposed to a snippet of PL/SQL that's interpreted on the fly, which is why dozens of global page conditions using PL/SQL expressions will slow your application.

Tom Kyte's mantra was to aim to reduce to just a single SQL statement (or no SQL at all).
Perhaps APEX developers should advocate for declarative attributes where possible, only then maybe try an expression or query.

So you want to submit a conference abstract?

It used to be our annual Perth conference that got me thinking about abstract ideas, but now it's the Kscope abstract submission deadlines that get my boat rocking.

But every day is a good day to think about the topics I'd like to might like to share with to your local community.

I've also been trying to convince a collegue to do their first presentation, and the questions in the ODTUG submission page got me thinking, so I thought I'd share some of those thoughts.

So here are some of the questions in the Kscope20 abstract submission form.

This is arguably the best conference for Oracle developers, and we only constitute a portion of delegates! I selected the APEX Track.

But the Session Title is obsiouly a good place to start. It needs to be catchy, concise, yet inform the reader as to what to expect - even without reading the abstract itself.
What session headline would grab you?

I like the descriptive distinction between HOLs and Workshops

The title can lead to focussing your content, which is necessary. Scope creep is something that's been on my back these last few years, and a good title can nail a good portion of this down. The Perth conference form asked for 3 objectives, which is an awesome way to focus scope. (I don't think this should be confused with Martin's concept of the evil threes)

Searching for my first presentation topic took some time. Something I felt experienced enough to talk about, something that felt relevant, useful to others. Now I have such a large list of ideas splattered on a Trello board, if anyone is thinking about doing their first presentation but can't think of a topic, I'm sure we can eke one out, even without necessarily knowing your skillset.

The Session Type can also be a prompt for ideas. Presentations can often be categorised by style and format, something Penny & I explored one year in a session of our own.

Maybe you have a collection of related tips, or you have a case-study to share where successfully utilised a nifty feature? Or some feature people haven't been talking about enough?

There is also a Sub-categorisation that varies by track, and helps align the content to relevant product sub-sets. There's an impressive amount of detail on these here.

Analytics / APEX / Database / Modern App Dev / ...

Naturally, at some point we need to write the abstract. The few paragraphs that inform the delegate about the story you're delivering. I've been stewing on mine for a while. Write a draft one night, review it another. What's being taught? Is it new? Is it compelling?

But the abstract is also helping define your scope. A simple aim, an interesting insight, and an awesome outcome.
What were your three main objectives? That's 10+10+10 minutes, with a few on each side - bang, you've got your talk. Now to divide & conquer. But remember, 10 minutes can disappear real fast.

Make sure there's no boasting or selling going on, that can be left for the notes for the review committee. I like this distinction. The Session Summary educates the delegates what you're talking about, the Notes educate the committee as to why you're gone with this topic, any maybe do some selling (of yourself) there. I also included my 3 session objectives, for a concise perspective.

For everyone, then for the selection committee

And don't forget to check grammar and spelling!

Who's on first? What's on second?

Clearly someone needs to be written as the primary presenter, but do you have a friend?

Seriously, you may have a colleague that contributed just as much to the topic, why not present with them? Well, there's probably a hoard of reasons, including the fact they may never have presented before - but if you're up for it, mentoring is rewarding for all parties. And starting to present can change your career trajectory.

Is this good or bad?

This first question about having presented to a live audience before gives the reviewers some context regarding your experience, but this year, after just over a decade of presenting, I got a different perspective on the second question.

"Over a decade" sounds a little too grandiose. I pick one, sometimes two topics a year, and present them at our local conference. Sometimes I might do one twice, perhaps in a second city, or at a local meetup. I don't get much re-use.

However, some conference regulars will use & refine the same session numerous times a year. I had the opportunity to do my APEX for Forms Developers session 3 times, and it wasn't until the third that I really felt I nailed it. I had trimmed enough chaff, reordered the odd slide, integrated feedback, and neatened the story. And this is even with practice sessions under the belt, but Martin's recent post on controlling the presentation monster was a good kick in the behind for some aspects of my preparation that has been lacking in recent years. Perhaps I've become a little complacent.

As for the "Have you presented this session before" question, initially I thought it was a virtue to consider my presentation as 'fresh' for Kscope, something other people haven't had the opportunity to see yet. Perhaps I'd forgotten, because I certainly valued doing my inaugural Kscope session in Perth first.

(There is a 90 minute option)

Session Length is typically one hour. If this seems long, don't worry - it can be filled. And as it turns out, trimming a 60 minute presentation that was already a tad long into the Australian 45 minutes took a lot of cutting. But good cutting, like cutting superfluous code.

And it reminded me how good it is to get a targeted, concise presentation topic.

I realised my second abstract could also work as a 30 minute Workshop, so I added to the review committee notes, and maybe improved my chances a notch. My fourth submission could also work as a fully fledged hands on lab, so ... why not?

There are a heap of regular speakers at these gigs, but as Opal points out, there are a good percentage of new speakers every year - even at a high demand conference like Kscope. .

And it's not just Kscope - there are a wealth of conferences around the world, ready to listen to what you have to say. Check out the call for paper schedule at Clocwise (built with love using Oracle APEX).

There are a few other questions on the form, and plenty of other considerations when it comes to submitting abstracts, but these few stood out to be and I felt compelled to share.

I'd also add that there are so many quiet achievers out there that have expert knowledge of various features, they just don't blabber on blogs about it. We've got to find those pearls.

Also consider taking part in the public voting for Kscope abstracts. It's an interesting way to get a feel for what a good and bad abstract looks like, and may inspire a new range of ideas.

So bada bing bada boom, once you have your slides nearly done - practice practice practice, and have a listen to this sound advice from Connor - it's a great help <ahem>.

• Honesty - I've heard this from Connor for years, it's so darn true.
• Expert - we're all subject matter experts on something.
• Love all the things - love the topic. love being there to share.
• Practice - matters.

I was lucky enough to have as an instrumental guide on my path to speaking. I've heard these messages in various forms over the years, and he really hits the nail on the head.
And now he's being an instrumental advocate, providing various levels of support for Oracle developers around the world - not bad for just a tech guy.

Seriously though, he will help you out, and you won't feel like you need to write a 400 slide monster.

"Connor, your deck is 90 slides."

"Yeah"

"But you've only got 5 minutes"

"Yeah"

"..."

Things I learned at a user group event

Yesterday I attended a friendly, informative workshop by Shakeeb Rahman and Christina Cho, members of the Oracle APEX product team.

Reflecting on the worthiness of attending such an event, I thought I'd list out all the things I learned & observed.

1. Everyone learns.
   It was observed that 'gurus' such as Trent, Lino, and myself were there, and maybe this might influence the level of detail content delivered. Sure, I like the details, and a particular pace, but I'm there to learn a bunch of things as much as the next person. My REST skills need a lot of work, it's always great to get a demonstration of features found in the new version. The APEX feature set has grown so much, I'm struggling to stay across it all. I attend these sessions because I know the value I can get out of them.
2. Watching Shakeeb build.
   The last session involved Shakeeb using Quick SQL to build and populate data in seconds, then transforming the default output to look like a feed like Twitter. He did it in about 30 minutes, only using CSS once, maybe because his perfectionism was showing ;p
   As awesome as it was seeing the showcase that is APEX in action with declarative/low code techniques, I also get a lot out of seeing Shakeeb's thought processes, and how he moves about the IDE.
3. REST Services.
   One of my attractions to this event was the workshop on REST services. I understand the mechanics, but have not had enough practice using the declarative features. Having a hands on lab where I not only receive instructions in person, but get to ask Christina deeper questions as I explore the workshop. This was invaluable to understanding the feature integration. I have a few posts in mind to help bake it in.
4. Data is important.
   Our client would have appreciated Shakeeb's opening remarks. While we all know data is important, it can't be iterated enough to those decision makers that may not have enough understanding of the value of databases.
5. APEX Longevity.
   I knew this one already, but I like the fact this slide has made it into Shakeeb's commentary. APEX has seen so many JavaScript frameworks come and go, with little resistance between versions. I think this is rarely seen in the programming world.
6. Faceted search looks amazing.
   This will a boon for creating interactive applications that people are used to even quicker.
   I'm interested to see how this responds to more complicated queries, but there seems to be options for everything. I look forward to this feature, as we already build similar constructs already.
7. The native Popup LOV looks even more suburb.
   They really have learnt from the two solid plugins that came before them. Based on the attributes I saw demonstrated, this has everything we need, and everything we didn't realise we should have. And this is iteration one. Awesome.
8. I don't need to rely on a CSS overlay to remove toolips
9. We can hide empty display positions in the layout editor
10. The undo/redo tooltip was restored to native HTML in 19.x, different to the rest (thanks to sharing the love on Twitter).
11. I was reminded of an auto-height template option for inline modals.
12. There is a template option for 'icon and text' button templates to hide text for mobile devices.
13. A component setting was introduced to transform application Switch items into the pill format - yet another application level optional upgrade between versions.
14. Syntax in the code editor for SQL Workshop and Quick SQL is now highlighted. I think this improves readability and is a form of inline validation for your syntax.
15. SQL Workshop Object Browser has a few more tables, including suggested sample queries for the tables - the same sort of group by queries that I use to help analyse what's going on in a table. The same queries that utilised in the faceted search analysis. Code & data re-use right there.
16. We can now REST enable tables right from the SQL Workshop, with the flick of a switch.
17. I was reminded Postman was a nifty tool for REST development.
18. We can jump to the create application wizard from a table. I can't remember how, nor find the option, but I can't remember everything from the day... I make these notes to help, but hey.
19. Data dictionary cache is a thing. Again, I can't recall exactly how it worked, but I've made a note to investigate later as I explore the faceted search facilities.
20. The spotlight search has been expanded, and there are other nifty shortcuts that already exist. I use some already, but I now know to keep aware.
21. Paged designer multi edit - I am looking forward to these improvements, which include the ability to change template options for multiple components at once - hallelujah! But I wasn't aware that some attributes were previously hidden during multi-edit mode, so this has eased.
22. The ability to change the application logo has received all the declarative nick-knacks we needed.
23. This application logo is separate from the one defined in the login screen, which as also received a wodge of declarative low code improvements.
24. There is now a pre-built template style for dark mode. While I was aware of this, it's always beneficially for me to see Shakeeb operate the theme roller, since I don't use it enough.
25. Theme roller facilities are more refined
26. The Content Row report template - I'm looking forward to this so much I've retrofitted the template into 18.2, for demonstration
27. All the interactive report dialogs have been re-written, with a fresh look.
28. Menus UI can be tweaked with a 'callout' nature. We'll see how that's adopted in general...
29. Inline regions have been given some upgrades. I've been playing with a page to experiment with these recently (post coming), so this event gave me the opportunity to ask some specific questions.
30. Inline popups can be located near the invoking component, documented in the UT application.
31. Lino showed me how to define inline region size with classes, instead of jQuery (to be included in post).
32. Lino is approachable regarding my growing list of AOP questions.
33. And as the session was beginning, I felt a little inspiration to solve a regular expression problem I was facing last week. Some interesting performance observations noted as I experimented further today, and had a regexp expert to review my work. (post coming)
34. Christina is another solid member of the APEX family. I've had the pleasure of conversing with quite a few members of the team now, and they're a great bunch.
35. Juniper berries are used to make gin - it was the drink'n'learn on the beer cap, in our afternoon wind-down.

And I reckon there might be a few I left off. The Q & A included some conjecture about 20.1.

Brisbane & Sydney also get the pleasure enlightment musings of Connor.

Not bad for a user group event, eh? 

It's a shame some people struggle to get support from their employers to attend such events, and more of a shame we don't see more students getting engaged. How do we reach them - the next generation?

I'm glad they came, and I'm glad I went. There are some growing APEX markets around here, and some with lulls. If we don't get the visits, the next round of students will not eventuate. It's not just the people who attend the workshops, but the flow on effect from those individuals who report back and share information with other team members. It's certainly what I did with my colleagues. Who knows who's ears could be listening in on that positive feedback.

Do you want to learn about database technology?

Do you live close enough to Melbourne, Brisbane, Sydney, Seoul, or Tokyo to attend a software development roadshow for a day in November?

Are you interested in how easy it is to build data driven web based applications?
Perhaps you're a student of the programming world?

Your local user group is hosting some visitors from the US that I think are worth listening & engaging with, in person. And if you're not a member of the user group, it's only $50 for the day.

https://blogs.oracle.com/apex/oracle-apex-apac-tour-2019

50 bucks.

For a day of learning, engagement, and networking with industry peers.

I can't emphasise that networking element enough, particularly for those still formulating their career goals & focus.

It's been a while since I was a graduate, but I relished the information I gathered at the local user group events. I was recently privvy to witnessing a member of the next generation of technology professional help shape her career at our conference.

It's worth investing a day in yourself, to help inspire your work tomorrow, to help nurture your love for your job. Whatever the stage of your career.

Warning, tangent ahead.

While I now find myself a regular speaker at such events, I also still value attending them, even with the advent of regular, engaging, recorded online technology seminars with experts and those that build the product.

That photo? That's Oracle product manager David Peake helping out. That's what these communities do - get you within one degree of separation of the product itself.

I can't keep up with all the new gadgets being made available in my development tool of choice.
I certainly need more practice with REST technology, and a hands-on-lab with the development team would really help.
We're also about to level up with our version, so another revision of all the new kit will be super handy.

And Shakeeb is a really slick presenter. It's not just the technology I learn about when watching the sessions by Shakeeb, or Connor & Steven, or many other seasoned (and some new) presenters.
And I'd like to meet Christina, add to the list of the APEX team I've met in person.

Seeing them in person is something else. Nothing beats having a face-to-face conversation with someone about ... anything really. And to then establish that rapport, that human-to-human contact with other people. It can remind us to be humble. A reminder that we all make mistakes; we all continue to learn; and most importantly - none of us know everything.

We see regular personalities on the speaking circuit, or those bloggers out there that pump out their experiences online. These are only a small percentage of all the developers out there, working hard, empowered by education, utilising online resources, leveraging off forum conversations. Sometimes it feels like they seem to know so many things about the product, but really, most of them are just making notes, and thankfully publishing them in a manner for us all to benefit. Don't let the imposter syndrome get you down.

And let's not forget the friendships that are forged at such events. Yes, you're allowed to have friendships at work, it helps keeps smiles on our faces when times are tough.

So. 50 bucks, unless you're already a member - then it's free!

Learn about how to get your FREE Oracle instance.
Learn how to use a low code development tool to build apps.
Learn how to source data from web services.
Learn what's in the latest version of the product.
Have an engaging session where you have the platform to ask anything.
And do it - if you have the chance to ask a burning question, odds are there is at least another person nearby with the same question.

Thank you, Oracle for your growing engagement with the community, and thank you to our local user groups for helping facilitate these visits, and most importantly, thank you to the community - actively producing content, and those who drive up the SEO by quietly read all the content.