Gwen Shapira has written a nice summary of a problem case where the classic wait interface based troubleshooting method is not always enough for troubleshooting low-level issues.
Jagjeet Singh mentioned that he has built a V2 of his cool SQL Dashboard sqlplus-based monitoring script, which now also shows a nice colored ASH graph output. Yes, right inside sqlplus!
Hello all fellow Oracle geeks and technology enthusiasts! Long time no see ;-)
Here’s the list of public events where I’ll be speaking this year:
Enkitec’s Extreme Exadata Expo in Dallas, TX (13-14. August 2012):
Additionally I’m going to be around to participate at Q&A sessions, panels, random Exadata hacking and just for fun! :)
It’s a well known fact that the cursor_sharing parameter can be set to FORCE for making Oracle replace any literals with system-generated bind variable placeholders and then calculating the SQL hash value for looking up an existing cursor in library cache. This should reduce the amount of hard parsing and shared pool garbage.
Back in the (really) old days, systemstate dumps had to be used for diagnosing hangs and finding blockers of hung databases. Basically you just identified which resource your waiting sessions were waiting for and then scanned through the rest of the system state dump to see which session held that particular resource (note that over time the instrumentation and systemstate dumps have evolved to resolve and dump the blocker information right at the waiting session section in the dump).
When you troubleshoot an Oracle (performance) problem you usually want to find out what are the problem sessions doing (wrong) and then why are they doing it.
The “what” question is usually answered using some wait interface based method – like ASH or SQL*Trace, which both add plenty of extra details to the plain wait event data.
My normal session troubleshooting data collection sequence consists of only three steps: