In early April, I had the honour to attend Craig Shallahamer’s Oracle Performance Firefighting and Advanced Performance Analysis courses. It was a great opportunity, since he came to Frankfurt which is just a three hours drive away. It was the second time meeting him – and it was great, again. The last two days have been a bit dry by their theoretical nature, and the last exercises on friday are only of limited use for me. But especially the first three days of firefighting have been really useful and most valuable due to their basic technology insights and the teached strategies “how to be sure”.
Before we come to my usual list of thoughts to the event, just have a look at the pictures taken during this week. Most of them are made by Lazar, I think the one above as well.
So, now the technical content! 😉 I won’t repeat things Craig said here – that’s his job, and I think in his book he will describe his thoughts way better that I can. So during a course, event, meeting or conference, I am taking notes – notes about what to do, change or try afterwards. And this is not top secret, so I am constantly publishing it.
Those are my “look at’s” for Craig@Frankfurt2011:
- involve STRACE for diagnosis (gettimeofday, just great)
- wait event “*parallel*” means “multi block access”
- once again, do exercises with the OSM tools in “peacetime” to “be prepared for crisis and wartime”
- check more often how much “SQL Net Message from client” indicates a busy app server
- check the _spin_count parameter on latch bound / CPU bound boxes
- run queue is worth a glance if load is 100%+
- dbms_monitor’s client_id is powerful
- try sampling v$session, v$sqlarea, v$sql,v$sql_plan for a poor man’s ASH
- in-memory undo – what a cool stuff
- Oracle’s MMC snap-in for Windows makes thread analysis way easier (thanks to Rainer Wäschle)
- PGA Aggregated Histogram (to see number of big/small sorts, hashes …)
- your innocent select can easily cause redo beyond consistent read blocks: “Block Clean-Up” for recently modified buffers. But no undo for that.
- check commit_write options
- high performance systems: try to pin LGWn to a dedicated CPU
- dbms_parallel_execute is a great non-partitioning tool to do mass work on objects chunkwise (thanks to Lazar)
Some of the points have not been part of the course. But lots of stuff comes to the collective mind of a bunch of DBAs who are sitting in a room for a week, talking about their job. That’s why I love being there.
So thanks to Craig and all attendees from Russia, Macedonia, Sweden, Czech Republic, Germany …
Martin Klier