APEX Survey Results: Addressing Performance

Yep, I'm still doing this. A bunch of questions to come, many worth the visit. Just a few weeks between drinks, so to speak. My annual review is a little late, too. Anyway...

Time for the performance questions in my 2015 survey. A favourite topic of mine, and my boss, Penny Cookson, lives for tuning.

Q9. How do you proactively address performance? (tick all that apply)

Tune SQL - I would be surprised if this wasn't the top result. Plenty of SQL used in APEX applications, and why not tune them?

Limit Interpreted PL/SQL - this is good practice in general, but in the world of APEX, this means moving inline code from application into packages. You can get pretty quick wins doing this with any plugins. Read here for details.

Materialised Views - a database construct that can aggregate complex information at regular intervals, to be queried many times with simpler SQL over fewer rows. I've seen interesting examples that obfuscate layers to external information using pipelined functions. Or you could just use it for your menu.

Care using v() - Particularly important when referencing page items, since this function would execute a query on the session state table. While values such as APP_ID come from a persistent package variable, it's still context switching between SQL and PL/SQL, so it would be even better to use bind variable.
where id = (select v('APP_ID') from dual)
I think any use of v() should be questioned. SQL queries should use bind variables (not substitution strings) and packages should be parameterised.

Page Workflow/Design - why refresh the entire page when a partial refresh would do? A well designed application will limit network traffic and the amount of queries necessary to serve the data. Refreshing regions on demand with dynamic actions is one of the most regular things I do. Declarative page modals in APEX 5.x have also made APEX life a lot more comfortable.

Global Page Modularisation - Performance also about the developer not repeating tasks over and over, causing future maintenance headaches.

JSS/CSS File Management - minifying code can reduce network traffic, as can well designed libraries; CDNs; and declarative options. I understand APEX Front End Boost can help.

Care with jQuery selectors - selectors can be abused just like table indexes. I have some commentary on this here.

Region Caching - possibly underutilised, but the ability to cache region on the global page in APEX 5.0 opens options, as do some new APIs.

data-attributes - jQuery related, what I meant by this was to offer more information during report generation, which can help interactivity and reduce AJAX activity. Though read consistency issues should be considered. See a basic example here.

Other - a few people suggested a well designed data model. Touche. Even more got stuck into it in the next question on performance return on investment.

The User Interface attributes are also an area worth being familiar with, particularly in regard to application level file management.

I don't need to worry about performance - yeah, right.

APEX Survey Results: Instrumentation Thoughts

This question in my 2015 survey is related to question 7 regarding instrumentation.

Q8: Do you have any thoughts you'd like to share on instrumentation?

I left this open for people to add a free-text response. Here are some worth highlighting, though it was hard to choose since so many people had something to add.

a) How would it help me?

Have a read of this post to get an idea of how the debug output can help solve problems.

b) It's not optional

I agree, it should be baked into your code.

c) An application is not just building, but also managing after deployment. To reduce the cost of the second fase (sic) instrumentation is a must

Another perspective on what instrumentation is all about.

d) Nobody instruments enough ....

Well, I think you could probably have too many logs.

e) Not enough planning goes into instrumentation in general.

Planning is an interesting point. While we could plaster our code with debug logs, this isn't necessarily going to help debugging your code, particularly when you need to further sort the wheat from the chaff.

Once you've decided on a framework, start thinking about standard patterns such as noting start/end of procedures; incoming/outgoing parameters; the varied level of detail you may need; how you can turn it on/off; how errors are highlighted and treated.

Any other factors you think are worth mentioning in regard to planning?

f) Please make Debugging more smoother and documented. An idea can be to create a packaged application to showcase debugging.

Something for the APEX development team to consider.

g) Instrumentation should point out the exact place where error is occured (sic). It should not be overhead for the APEX application and there should be a switch to turn it on/off.

Back in APEX 3.x, debugging was displayed where relevant, within the rendered page itself. It looked awkward and was hard to decipher. Any code is overhead, but the risk/benefit equation outweighs the cost. APEX debugging can be turned on/off, and some logging libraries provide options for granular logging.

h) ... Also debugging apex_collections is somehow hard today ...

Constructs relating specifically to a particular session (such as collections) can be tricky to detail, but with careful planning you should be OK. It may be useful to output a collection count at key locations. You could have a little library that looped and logged key columns from the collection. And the Session link in the Developer Toolbar allows you to see collection contents naturally.

i) I wish I'd known about or used some of the instrumentation methods above long ago when I built my first Apex apps.

I think that comment right there should be a warning to all those who haven't started yet.

j) The CREATE ANY CONTEXT privilege requirement in Logger is a pain.

Sounds worth airing, but I'm pretty sure the benefits outweigh whatever pain that might be.

k) asdfasdfasfd

It seems even cats have opinions on the topic.

l) do it! use whatever tool fits your needs, but use something!

I'll finish with that one.

Does anyone recognise their comment? I didn't want to name anyone without asking.

APEX Survey Results: Workspace Activity Log

This question in my 2015 survey relates to built-in instrumentation.

Q7. Do you utilise apex_workspace_activity_log (for monitoring/reporting application usage)?

Yes (90)  47%
No (102) 53%

This log table reports details of all page rendering and AJAX process calls. I really like this information, though I would like to be able to add to the output things such as
sys_context('userenv', 'server_host')

In addition to the Monitoring pages you'll find in the APEX administration section, I've built a few pages that report on this information in my own particular way. This allows me to monitor behaviour and identify bottlenecks. More information to come on this since it's fuelling a presentation I'm writing on charting in APEX 5.1, so stay tuned.

APEX Survey Results: Instrumentation / Debugging

This question from my 2015 survey may pique curiosity among some APEX developers.

Q6: How do you instrument your code?

Hopefully those in the 22% who do not add instrumentation just didn't understand the question.

Instrumentation is another term for adding debugging information to your code. There is plenty of commentary on this topic and done right it can make tracking down problems a breeze.

It can be as simple as knowing when a procedure was called, and with what parameters. Take the following procedure definition with instrumentation (debug logs) added.

procedure xyz(p_in number) is
begin
  debug('start xyz');
  debug('p_in:'||p_in);

  -- do stuff

  debug('end xyz');
end;

 I think you're essentially coding blindfolded without it. You could only infer this procedure was executed because of whatever it does, and if something goes wrong how do you know what was passed in? Where did it get up to? Did it execute at all?

Imagine using the database without the ALL% dictionary views. They're essentially instrumentation to your database. Tom Kyte has some particularly good talks on the topic, and a few chapters in his various books.

It's very much a PL/SQL thing, but since APEX pages are generated via PL/SQL, there is a natural solution with the supplied apex_debug package. Any process can add information to the APEX debug log, which is only collected while debug is on.

There is no reason why you shouldn't instrument any PL/SQL, and have the granular control to turn on/off at will. Libraries are available to make this job easier.

You may have heard Logger talked about at conferences. It's a community developed library, well, mostly driven by some key players. It's had a few homes (remember google code?) but is now found on GitHub:
github.com/OraOpenSource/Logger

PL/SQL Logger can be found here, if you want to compare solutions or look for ideas, read other people's PL/SQL.
https://sourceforge.net/p/plsqllogger/wiki/Home/

Despite recommending it a few times, I've never actually used Logger on-site (sorry Martin). For quite some time I've had a basic infrastructure of my own that has worked fine, using the same premise. That being said, it's on my list to convert/upgrade when the timing is right. Partially because I've followed it's progress, know who wrote it, and inspected the code. I look forward to benefits some extra features will bring.

The next few questions also relate to instrumentation, so stay tuned.

APEX Survey Results: Editing Tools

Another preference question in my 2015 survey.

Q5: What editing tools do you use for PL/SQL and JavaScript

That Jeff Smith fellow should be pretty happy with the top result, but almost half of the "Other" responses said PL/SQL Developer. I used this many years ago while SQL Developer was still being born. Since SQL Developer was free and portable, it was an easy selection, though I only use if for queries, not PL/SQL development.

A number of editors were suggested in Other, including two takers for Notepad. All I can say is wow, and I hope for the sake of others that tab characters are not being saved in your files.

I've been happily using Textpad almost since I started coding. I'm interested in making the jump to a non OS specific editor like Sublime, or perhaps Atom, but I'm not there yet. Though after watching the Kscope16 deep dive / montage (as a livestream), I want to try soon!

Edit: January 2017

I spotted these results in the 2016 StackOverflow survey, a somewhat more diverse survey than mine.

And I've started to use Atom, but I might have to give Sublime a go for a while to compare.

APEX Survey Results: Which browser for development?

Next question in my 2015 survey, how many different ways can we all type "chrome"?

Q4: Which browser do you use for development?

The first free text question, with a long variety of unique results. I haven't analysed the complete list to get a truer result, but the pattern is clear. 3 of the top 5 are Chrome. APEX developers build in Chrome, the application builder is recommended for Chrome, especially during early adopter season. I find the Chrome Developer Tools so easy to use.

Safari had a few mentions, but I guess all the Apple geeks also use Chrome.
I also wonder if Edge will emerge in future and shake off the weight of IE?

I'm currently at a client where we're lucky enough to only need to test for Chrome as we're building enterprise applications in APEX, run internally to the business.

Attempting to code across browsers is best illustrated with this GIF.

APEX Survey Results: What development resources?

The next question in my 2015 survey was a high level look at what resources developers use to get through your day.

On a slightly side note, there was an interesting discussion on the science of preferred vs effective learning styles in this podcast. It reminded me of my scuba diving course where we learnt the content using 5 different methods, which was a great way to ensure everyone understood how to survive in a pressure environment (pun intended).

Q3: What resources do you use to aid development

Personally I find the OTN forums great for most things APEX or database related. StackOverflow is great for JavaScript/jQuery/CSS questions, and most have already been asked for you. The forum format there is superb. Don't forget the revitalised AskTom.

It's great that some people have the opportunity to learn from colleagues. I've had some great mentors in the past that really helped my career. Blogs can be a moderate second, with APEX content aggregated at www.odtug.com/apex.

Documentation is a great source of truth, and I recommend four places. Sense a trend?

• Oracle SQL Reference
• APEX Reference
• jQuery API Reference
• jQuery Selectors

Some of the "Other" responses included Twitter, which is a great tool for picking up information coming out of conferences you can't attend, tech information in general, and amusing parody accounts. Slack is also worth a go.

One interesting response was "Package Specs, Views, Application Code". I often find the package specifications of Oracle Supplied Packages full of useful information not necessarily found in the documentation. I recommend you start with APEX_APPLICATION and APEX_APPLICATION_GLOBAL.
As for views, I've learned a thing or two an how APEX ticks by looking at the SQL for dictionary views, which also explains why many don't return any records when you aren't within an APEX application.
I'm curious what was meant by application code, perhaps the same as another response that said "Packaged Applications". These applications improve every release and are a great demonstrator of APEX capability. Check them out for application design ideas, and if you want to know how something was done, install (and unlock) the application and look under the hood. This is where I learned how to display images in interactive reports.

Webinars was another suggestion, which I guess is a subset of Videos. I recommend paying for membership at ODTUG where they have a brilliant library of webinars, of which I have many yet to catch up on.
These days videos are quite diverse, from quick and simple youtube demos like Connor McDonald's KISS series on analytics; to published screencasts like I did once.

Someone even suggested "Connor McDonald", though I think there was some bias in that one.

My final recommendation would be regardless of preferred source, diversify your content. Don't just read Oracle APEX stuff, but subscribe/buy/view content for related technologies, and why not the competition?

APEX Survey Results: Which versions have you experienced?

Following up from the results on when you started learning APEX, here is question 2 from my 2015 survey.

Note that questions such as this offered the respondent to choose multiple selections, hence a count much higher than 192.

Q2: Which versions have you experienced?

I think it's fair to say that most of the 2.x respondents might also be part of the third that said they started prior to 2007.

I started in 3.x, and still have a client to this day on that version, despite the fact it's no longer supported.

No doubt if we asked the question today, the responses for 5.0 would be far higher. And we could almost ask about 5.1 and the goodies it will bring.

On reflection, I don't miss 4.x. Page Designer in 5.0 has been fantastically more productive. I also think the Universal Theme is a great idea that I haven't sunk my teeth right into yet. The upgrade to 5.1 should really put it's new life cycle to the test. I think this post will come in handy.

APEX Survey Results: When did you start learning APEX?

Early last year I put the call out to #orclapex developers, asking them to fill out some questions in survey (using a packaged APEX application, of course).

The results helped contribute to a presentation I did Kscope15. Thank you to all those who responded. I thought I'd finally post some results, see if it can elicit further discussion, even of a casual nature.

Some of you asked me if I could post these results, so thanks for your patience and stay tuned on the 2015 Survey label in this blog. I'll aim for every Friday.

Now it must be said: this is an internet survey. It's not randomised and it's no doubt highly skewed towards the developer population that either read my blog or use Twitter. And even then it's only those who chose to spend the 10 minutes responding. Based on the free text responses I doubt it was gamed (unlike other polls), no surprises there.

When I closed the survey I had 192 responses, of course far shorter than the number of APEX developers out there, quietly coding away. Everyone, feel free to add your own thoughts to some of these results. It could be insightful and fun!

Q1: When did you start using APEX?

I was surprised there are so many respondents prior to 2007. Maybe this is a reflection of the demographic that chose to respond?

I started learning in 2008, with some experience with mod_plsql and heavy with Forms experience. I found it tough learning session state, and ultimately designing page flow in a web environment.

As others have mentioned, there have been a few features that I would have loved to have found earlier. My real kicker was Region Display Selectors.

What I'd like to know is, what made you start learning APEX? For me, I realised Forms was dying.

APEX Best Practices Survey

Hi All,

I was inspired by Peter Raganitsch to use the Survey Builder packaged application to create a survey that will help with my "Evidence Based APEX" presentation.

I would appreciate 5-10 minutes of your time if you could fill out my survey:

https://apex.oracle.com/pls/apex/f?p=70347:Q::AAC3

How-to Geek

You'll find a small set of questions over five major topics.

1. Instrumentation (debugging)
2. Performance
3. Security
4. PL/SQL
5. JavaScript

In time I'll discuss the results, I look forward to reading your responses.

Edit
Check out this label for detail on the responses
http://www.grassroots-oracle.com/search/label/2015%20Survey

Scott