Here’s a little secret APEX performance hack if you are running on the Autonomous database.
As background, I’m running an Autonomous database with six CPUs and a terabyte of storage.
Let me grab a good sized chunk of that terabyte with a single table. I just kept duplicating copies of DBA_OBJECTS until my table got to around half a terabyte.
At 430 gigabytes in size it’s not going to be blazingly fast to query that table (I’m not using any flash or in-memory here) but with six CPUs I can connect to one of the higher powered services which opens up parallelism and it should run fairly quickly. Let’s see how long a typical kind of aggregation query takes which I connect to the HIGH service.
and you can see that’s not too shabby – half a terabyte scanned in just over 30 seconds is pretty impressive because we have plenty of CPU and parallelism available on this Autonomous database. Of course, typically end users aren’t using command line SQL tools to query the database. Let’s assume I built the same functionality in a more user friendly tool such as APEX (which comes for free on all Oracle databases including Autonomous). I built a little simple classical report sitting on top of that same query and added the PARALLEL hint to it.
But when I ran this, my APEX report took five minutes(!) which is a far cry from 30 seconds.
That doesn’t seem right – is there some fundamental issue in APEX that’s causing a slow down? No, but you do need to be aware of how the Autonomous database allocates resources.
By default, APEX runs in the LOW service consumer group on Autonomous. That is strictly non-parallel even if you add PARALLEL hints, so my APEX region is getting single-threaded performance for that query. Similarly, if I ran that query in SQL*Plus in the LOW service it would also take around 5 minutes. APEX must run in the LOW service because its a multi-user transactional system. See this post for why just about every transactional style application on Autonomous database should be running in the LOW or TP service.
But that raises a problem. APEX is a fantastic tool for data warehouse style query facilities so obviously I’d like to get access to all the power of parallel query from APEX as well. Thankfully it’s very easy to do – we made a change to APEX to allow you to request what consumer group you get access to when populating regions inside your applications. All that you have to do is go to the optimizer hint attribute (which obviously is normally for standard SQL optimizer hints) and then you can nominate service that you require for the region query using a custom APEX hint. For example, I’m can go for the high service
Note this is not a true database hint, so you can’t use it in standard SQL. This is a directive to APEX that when I run the query that populates this particular region, APEX will switch into the HIGH service to get full access to the CPUs and parallelism I have available at my disposal in my Autonomous database before executing the region query. It will then switch back to LOW at the conclusion to ensure APEX keeps working normally.
You can use apex-adb-medium or apex-adb-high. I suppose you could also use apex-adb-low but that’s the default anyway.
You won’t see these hints in the help text for “Optimizer Hint” in the APEX builder (I’ll try get that corrected), but if you’re on Autonomous, now you know how to give your large scale queries a boost in performance, even when they are running from within APEX.
Pro Tip: If you’re running your own APEX/ORDS instance against Autonomous, do not be tempted to run APEX entirely in the MEDIUM or HIGH instances. See the previously mentioned posts for details – you’ll probably end up with a broken APEX environment.
Leave a Reply