Thursday, June 21, 2007
I was recently invited to become an Oracle Fusion Middleware Regional Director. While pleased by the recognition, I'm really enthused about the opportunity this will give me to meet with Oracle development and product management. The Regional Directors are invited to regular con-calls and occasional live events. I will be sure to pass along points of interest on this blog. If you have anything that you would like addressed, add a comment and I will bring it up.
Friday, June 01, 2007
A co-worker recently approached me with an Apex 2.2.1 problem. He had a report that was running extremely slowly. The report used a database link. He suspected the dblink was the problem.
We spent some time looking over it directly in sql*plus (always a good way to start) and confirmed that the query ran slowly accross the link. Next step, we ran the query directly in the source database--still slow. There were a lot of rows in the view (which was based upon two other views, which were both based upon views). Only one index, on the primary key. I recommended some specific indexes that would help his situation.
A week later he returned. Now the query ran great in sql*plus, but the report was still slow in Apex with certain query criteria. I ran the page, submitting different criteria and confirmed that with some criteria it ran very slowly. Turning on debug indicated that the query region was running fast. I suspected that the debug mode timing somehow did not include the time spent in the linked database (boy was I wrong).
After some hair pulling, I looked at the page that I was submitting (the page with the query criteria) and saw several validations on that page. Aha! Perhaps a validation was the culprit. The report was branched via a "Branch to Page or URL" branch, so debug mode was not showing anything that happened during the submit portion--only the render portion of the report page. I created a new branch with sequence=1. This branch was a Direct Branch (see image). By making it a direct branch I was able to see the time spent in the validation:
0.03: Branch point: BEFORE_VALIDATION
0.03: Perform validations:
0.03: ...PL/SQL "function body returning error text"...
As you can see, the validation was taking over 5 minutes. Using the direct branch made all difference, allowing me to look at the submit processing. The validation also used the dblink, but did a select count(*) from that same view (with 50 million rows)--not using any indexes. Cleaning that up did the trick.