🙎🏼 ♌️ 🆒 15 best Oracle APEX performance tuning tips for developers 👊 👆🏿 👩🏼‍🌾

Hot Habra Hello everyone!

Today our company is 28 years old, and in honor of this pleasant event, we decided to share new material with you.

Thank you for your help with the translation of our regular author Yuri PonomarevOBIEESupport.

The author of the article is Michelle Scamin, the founder and managing partner of the company that provides the Reading Rewards service. Back in 2009, Michelle suffered from the fact that her sons, 8 and 9 years old, read too little. Books simply could not compete with computers and video games, so Michelle and her husband decided to develop a system in which children earned time on computer games by reading books.

As an IT consultant and web application developer, Michelle has developed software that allows children to record the time they read and watch TV, and parents to track this time. This was the beginning of the Reading Rewards service.

And now, in fact, an article.

So, you have chosen an amazing Oracle APEX application for record speed - you won’t have to write a lot. And, as the old saying goes, what to be - that cannot be avoided!

In 2010, I created an application to try to get my two little sons to read. I admit, at that time I did not think about performance, and it had a few juicy “select count (*) from huge table” sort of strategically scattered throughout the code.

Hey, my boys didn’t really read that much, so these tables were pretty small at the time ...

Let's just say that I was pretty naive when I released the app to the public, and I didn’t expect it to load thousands of users daily, generating 150,000 page views per day.

Statistics from Google Analytics

Besides the fact that all this interest was very exciting for me, I was not at all ready for so many clicks. I had performance issues. You just have to admit that I became an avid student of Oracle APEX performance tuning and thought that I would just share some of the things that I learned over the years.

How to identify a bottleneck?

There are many things you will want to pay attention to when evaluating the performance of your application. You will want to consider the problems associated with it, the browser. Network problems. ORDS configuration. Database configuration, including possible missing indexes. Are there any database locks in the game? Expectations?

As soon as you feel that your underlying infrastructure is in good condition and not at fault, it may be time to turn your attention to the application itself, bearing in mind your frontend (Javascript and CSS) and backend (SQL and PL / SQL).

1. Frontend: file order matters

First of all, if you include custom CSS and JS components, make sure your CSS is at the top of the page and JavaScript is at the bottom .

This ensures that your users at least get the user interface components, even if some business logic files are not already loaded.

2. View activity monitor

This is always a great place to get started. If everything seems slow and you don’t know where to look, activity monitor in the APEX development environment can provide valuable information.

Link to the activity monitor from the APEX development console

Every pageview in your workspace and applications is logged, including information about the user, date / time stamp, application, page_id, and most importantly, the time the application was running.

My favorite pageview report is “By Weighted Page Performance.”

View example from APEX activity monitor

Pay close attention to any pages that have a large number of Page Events (meaning frequently visited), and the high value of the average application runtime. I seem to remember how Joel Kalman once said that everything above 0.5 seconds should be reviewed. Of course, this is a rather crude generalization and may (not) relate to your specific case of using APEX.

The activity monitor makes it easy to work with IR, but if you need a little more detail and want to run reports in different workspaces, you can use the following query in SQL Developer to make it as detailed as possible:

select workspace
      , application_name 
      , application_id, page_id
      , count(*) total_page_events
      , avg(elapsed_time) avg_elapsed_time
      , sum(elapsed_time) elapsed_time
from apex_workspace_activity_log
where view_date between to_date('201911190900','RRRRMMDDHH24MISS') and to_date('201911191200','RRRRMMDDHH24MISS')
group by workspace, application_name, application_id, page_id
order by 6, 7 asc

3. # TIMING # lookup variable

So you may have found a slow page. And now what?

Well, you can use the # TIMING # lookup variable in the footer of your report regions if you want to retrieve and display their elapsed time. This can help you identify the slowest areas of the report on the page that you can turn your attention to. This is especially useful on dashboard pages where you can have a lot of work.

Using the substitution line # TIMING # in the footer of the report

Running the report will then give the result:

One thing that I like about this function is not necessarily that it determines which regions take a lot of time to run (I could get this from my debug windows, see below), but it offers information to end users.

Often I find that they will request data that I know will be visualized for ages. At the very least, it gives them some idea of what might happen and why they had to wait a little longer for their page than expected.

4. Run the page in debug mode

Better yet, run it in Debug LEVEL9 to access the execution plan for your report.

The debug window will show you everything that happens on your page, and it will show the runtime for each component. LEVEL9 will generate tons of lines, but you can sort them in descending order of time to show that your page takes the most time to render.

5. Beware of calling v (”)

If you find a poorly performing report, you can check if you used the v (”) notation when you could use the bind variable.

select task_name
from tasks
where assigned_to=:APP_USER

select task_name
from tasks
where assigned_to=v('APP_USER')

In a large table, the difference between the two statements can be huge, because v (”) is actually a function call. This means that you will not take advantage of any indexes and the query will result in a full table scan.

Tip: if you need to reference the state of an APEX session in a view (where you cannot use binding variables), consider using a scalar subquery that can work out almost as well as your binding variable. See:

select task_name
from tasks
where assigned_to = (select v('APP_USER') from dual)

Thanks to John Scott for offering this at one of the events I attended.

6. Avoid String Substitution in Queries, if Possible

Beware of lookup strings in queries.

Consider, for example, a situation where you might need a decode or case statement in a query to determine which page you want to branch to:

select case when dept_no=20 then
          'f?p=&APP_ID.:3:&SESSION.::::P3_DEPTNO:'||deptno
       else
          'f?p=&APP_ID.:2:&SESSION.::::P2_DEPTNO:'||deptno
       end as link
       , deptno
       , dname
from dept

Using the bind variable: SESSION, not the & SESSION lookup string. can make a huge difference and save Oracle a lot of parsing time. The bind variable version allows Oracle to reuse the query.

select case when dept_no=20 then
          'f?p=&APP_ID.:3:'|| :SESSION ||'::::P3_DEPTNO:'||deptno
       else
          'f?p=&APP_ID.:2:'|| :SESSION ||'::::P2_DEPTNO:'||deptno
       end as link
       , deptno
       , dname
from dept

If you're interested in learning more about this, check out Jorge Rimblas ’s wonderful video on wildcard strings, binding variables, and APEX links.

7. Use declarative parameters in your conditions when possible

When using conditions on page components, always use declarative parameters whenever possible. They will be much more effective.

8. Use pagination settings rationally

In large reports, the selected pagination options can have a significant effect. Despite the fact that since version 18.1 APEX has significantly improved pagination processing (read this wonderful post by Karsten Charsky), you can disable the parameter “range of lines from X to Y from Z” in one of them if you have performance problems in a very large report.

9. Avoid HTML in queries and use an HTML expression

When possible, use the HTML expression attribute for the report columns to include any HTML / CSS attributes that you might need.

10.

Thus, you have identified a really slow report area that you have configured as best as possible.

Do I need to update the data every time I view the page? Think of dashboard reports, in particular sales data, etc. Even the transaction data received 1 minute ago can be quite complete in many cases.

If so, you can use the region caching option.

Server cache settings in APEX regions.

By default, caching is disabled, but if you enable it, you can select the cache timeout option, starting with just 10 seconds. Even 10 seconds will help the performance of the dashboard, which is used by a large number of users! And it is obvious that increasing this parameter will help even more.

Caution, if you have sensitive data that depends on the user, you can select the "cache by user" or even "cache by session" options.

Available settings when enabling regional caching

If you decide to enable caching, you can inform your users about the latest data update. If so, you can use the APEX_UTIL.CACHE_GET_DATE_OF_REGION_CACHE function .

11. Move PL / SQL to packages

Make sure you move the code to packages. They are already compiled in the database, which means there will be less overhead for dynamic analysis.

Your page processes should be just package calls whenever possible.

Stephen Feuerstein has written a very detailed article on writing PL / SQL for Oracle Application Express , which you might want to read. She is already several years old, but she is still relevant!

12. Launch Advisor!

I rarely see people using this great feature, but this is something we should all do regularly. Every time I wonder what else he finds.

13. Use build options to disable and enable components

Well, you tried ALL THESE THINGS, but still stuck it is not clear on what. If you are going to “redraw your page again,” you should consider using build options for the various components of your page.

Do not place indefinite conditions on the components, otherwise you will lose all your precious conditions! In addition, perpetual components have the unpleasant feature of living in your applications forever ...

Create a new build parameter using Status: Exclude.

Apply it alternately to the various components of your page. Start your page with each change and see if it works better. If your page suddenly runs faster, you may have found the culprits.

14. Understand how various IR settings can affect APEX performance.

With a poorly executed interactive report, various settings, parameters, or filters applied by your users can exacerbate bad work (yes, it really is).

As mentioned earlier, it’s best to start by using LEVEL9 debugging mode and configure a separate real query. Examine the execution plan, examine the indexes, tune where possible. Remember that each view (table, grouping, chart, summary) is a separate query that may require customization. Then it changes again when using the search filter or column header filter!

Your MaxRowCount setting also comes into play, and you should try to make it as small as possible. There is no right answer to this, and you may have to play with different numbers before you get something that works for your users.

Basically, consider deleting or changing some default behavior scenarios. Developers have many controls when it comes to which features are enabled for users. It is worth trying various settings, and do not forget to consult with your users to make sure that you fully understand their requirements.

Finally, if you find that your IR is still too slow, you can consider 2 alternatives:

Pipeline table functions (select * from table (my_rpt_pipelined)) -> they can work much better in complex queries.
Generate Collection Report (APEX_COLLECTION)

Thanks to Karen Cannell for these super useful IR tips.

15. Trace

When all else fails, you can add "& p_trace = YES" to the end of your URL to create a trace file that you can analyze using the TKPROF utility.

More information on SQL tracing can be found here .

Still stuck? I am always amazed at the responsiveness of the Oracle APEX community . Give a helping hand, ask for help on various forums or on Twitter, someone will definitely point you in the right direction. I have received countless responses to calls for help. You will find the answer! Just remember: this is not APEX, it is most often you, you yourself :-)

15 best Oracle APEX performance tuning tips for developers