Oracle APEX: Query to generate data for time selection

Needed a way to generate an APEX LOV(List of values) for selecting time of day to schedule a job.
The way the argument is passed to dbms_job.next_date had to deal with decimal values to indicate quarter of an hour.

e.g.
  -- dbms_job.next_date(job_number,TRUNC(NEXT_DAY(SYSDATE,p_day))+p_time/24)
  -- dbms_job.interval(job_number,'SYSDATE+1/24');


After fiddling with for..loop construct for some time, decided to settle for the following assuming sys.all_objects(it could be any table though as long as it has required number of rows) has enough rows, which is going to be typically higher than what is needed for this purpose.

select rownum,
          rownum -1,
         (rownum -1)/4,
         floor((rownum-1)/4) || ':' || substr(mod(((rownum-1)*60/4), 60) || '0', 1, 2)
from sys.all_objects
where rownum <= 96;

(This is not the eventual query put in APEX LOV as it needs only two columns to be returned)

SAP BPC - Catch-me-if-you-can problems during planning cycle

Reports used to run normally and then it would start crawling such as taking 5 to 10 mins. to complete. When one starts to check things out from IT, the problem usually goes away and reports were back to normal runtime. This problem used to aggravate towards the end of the planning cycle, and we concluded that it was due to the load as everyone jammed the system. This is the angle we pursued for some time, but could not nail it.

After a few days, we started learning from IIS logs, that this problem was evident even when one or two users were on the system. This invalidated the theory that the load was the sole reason for the hiccups.

After some more digging, we were able to tie the occurrence of the problem to the "stats" health light SAP BW was displaying for a cube under Manage Action/Performance tab.
With that, we started running "rsvc" transactions at periodic intervals during busy days.
Earlier runs of rsvc were reporting green whenever it was run, so did not think this was the likely cause.

Once we started running it more often, we netted the problem - the "F-Fact" (temporary or new record) table of a cube was having invalid stats when an input template was submitted.
The "E-Fact" (more permanent or old record) table of the same cube was having up-to-date stats, and it was not impacted at all.

Trapping some of the report queries from Oracle Grid Control showed that records from both "E and F" Fact tables were joined with master data dimension tables.
It was guessed that Oracle optimizer could not do well when it ran into a huge table(E-Fact - 20+ M records) with good stats and small tables (F-Fact - 4K records) with invalid stats.

So approached the DBAs to press a re-compute stats job on the affected F-Fact table every 15 mins and the problem is pretty much gone since then.
       execute dbms_stats.gather_table_stats(..., ...);

If it were to happen again, decided to increase the frequency to every 10 mins. or even 5 mins.

Break from Blogging and SAP BPC

Took a break from blogging as much of time was spent towards stabilizing an SAP BPC 7.5 environment.
Weeks of troubleshooting identified a number of performance hogs across the landscape.

We derived some tangible improvements on these fronts:

• Excel report template with more lines than enough had a direct impact on Excel performance due to EV-DRE macros - big saving on Desktops
• Newer Laptops with Intel i7-3740@2.7GHz performed better than Intel i5-3320@2.6GHz because of CPU requirements for BPC reports anyway.
• BW ABAP servers were re-sized to have 4 CPUs and 16GB memory each
• Running reports for YTD measure led to pinning Server CPUs down with the earlier version.
• This problem seemed to have lessened since the upgrade to 7.5 SP15 though
• Computing oracle table stats on an F-Fact table frequently also helped fix catch-me-if-you-can problems during planning cycles - will dedicate a blog for this topic soon.