Providing the Oracle optimizer with accurate statistics on the Apex schema tables can be challenging.
Many of tables tracking Apex session state information see a huge turnover in data such that statistics gathered overnight can quickly become stale resulting in poor choice of execution plan by the optimizer and seriously degraded performance.
There are several ways to tackle the issue e.g. locking the stats on some of the tables to values that are known to perform reliably.
However I believe it’s better to give the optimizer the most accurate information you can, so it can make more informed decisions based on the actual data in the tables.
The problem is that even if you refresh the optimizer statistics often, you need to ensure that an execution plans that are stored in the shared_pool are also cleared or you risk still using a plan that does not reflect those new statistics.
Fortunately you can achieve all this with one relatively straight forward job i.e.
conn / as sysdba
variable v_jobno number;
begin
dbms_job.submit(
:v_jobno,
'dbms_stats.gather_schema_stats(ownname=>''APEX_040200'',options=>''GATHER STALE'',cascade=>true,no_invalidate=>false);',
sysdate+(5/(24*60)),
'sysdate+(5/(24*60))',
TRUE);
commit;
end;
/
This will create a job that runs every five minutes and refresh statistics on any Apex tables that are marked as stale, but also thanks to the no_invalidate option, it will clear any plans from the shared_pool that would be affected by the new statistics gathered.
You should make sure the schema reflects your Apex version, but with this job in place you should have no worries about stale statistics on the Apex session state tables impacting your application performance.
Steve Bamber 
Hi Steve
I’ve been using your job above for several months and it is working fine. Thank you for making it available.
The only drawback is that the job runs with sysdba privileges and due to the auditing settings I have in operation causes lots of info to be written to the Windows event log, making troubleshooting of issues more difficult.
Are there any reasons why I couldn’t grant the relevant permissions to APEX_PUBLIC_USER and submit the job as that user?
LikeLike
Hi Mark
To invoke the GATHER_SCHEMA_STATS procedure you must be the schema owner or else have the Analyze Any privilege granted to you.
I’m not sure I’d use the APEX_PUBLIC_USER account but you could try running the job as whichever Apex schema you’re trying to analyze e.g. APEX_040200 for Apex 4.x
Then just make sure you’ve granted permissions on the dbms_stats package with e.g.
grant execute on dbms_stats to APEX_040200;I think that should work – although I’ve not tried it so test it first !
LikeLike
Thanks
The upgrade to Apex 5.1 leaves the apex_050100 user in an expired state and I wasn’t clear on the effect of setting a new password.
I’ve now done that on a test system and created the job as the apex_050100 user. Doesn’t seem to have broken anything so will make it live in a couple of days time.
Thanks for the advice.
LikeLike