The day starts with a presentation b6 Tom Kyte about “What else can you do with system and session data”.
Tom starts with reviewing the history of tuning an Oracle database.
The prehistoric era (v5) required writeing debug code as that was the only way to get any information about what the code did.
The dark ages followed (v6) and now Oracle introduces:
- SQL Trace
- The first few (7?) v$-views are introduced
In the renaissance era (v7) introduced a few more things:
- The wait event instrumentation was introduced
- Move from counters to timers
The modern era (v10) introduced the tools we prefer today
- DB-Time tuning
- Multiple scoping levels (AWR)
- Always on, non-intrusive
- Bult into the infrastructure – instrumentation, ASH, AWR, ADDM, EM
DBA_HIST_* views is where the historic data is located.
Tom continued with discussing the metrics and the time model and how to join that to get data about them. However, all his presentations are available for everyone and you’re better off reading them on his site than getting a summary of it here.
As usual, Tom places his presentations and other things under files on asktom.
The next presentation was Apex debugging and tracing with Doug Gault from Sumneva.
Debug mode is turned off on the app level by default. It needs to be enabled there before it can be used.
Debug data is now written to a table, and not dumped on the web page as before. It is viewed by clicking view debug in the developer bar in the APEX interface.
Use conditional display to output things only in debug mode (using v(‘DEBUG’) ).
APEX_DEBUG_MESSAGE is currently undocumented, but the code shows how to use it.
Debugging can be turned on/off on the page programmatically.
Logging from you own code allows you to see what parts of a package call it is that takes the time.
Doug then shows a few different ways to collect and report on the debug data for a report.
I recommend looking at the presentation for ideas on how to use it and what can be done. Unfortunately this presentation does not seem to be available on the sumneva website at this point. If you are interested in it, send an email to them asking if they are willing to share it on their presentation page. You’d of course want to check their site first to make sure it isn’t uploaded between the time I write this and the time you read it.