“Optimizing For Performance” with Jože Senegačnik
Last week I attended a four day seminar held by Jože Senegačnik. The first three days covered theoretical part and if you plan to attend the course be prepared for a hefty amount of information (720+ slides). Considering the amount of material and in-depth knowledge of Jože about Oracle performance tuning and internals, I’m positive you’ll learn something new, no matter how experienced you’re (or you might think you’re;-).
The fourth day was reserved for practical workshop. Jože prepared a test Oracle XE database instance for each participant with sample database and PL/SQL package that we needed to tune during the day. Test scenario resembled TPC-C benchmark.
Since the elapsed time could vary from PC to PC due to the different HW configuration, he told us that we should concentrate on logical I/O (LIO), so we all started with approx. 1,1 million LIO per execution of PL/SQL package, with the goal to lower the LIO under 300K (which we did at the end of the day). And here comes a shameless advert for a profiler from my side. Instead of using tkprof for a trace file analysis we used profiler.
Jože is developing and enhancing his proprietary trace file analyzer called profiler since 2002. This is a command line tool written in C that runs on Windows (I’m not sure about Unix/Linux ports) that can be used instead of tkprof. Output is a html file with correct sql hierarchy and all the necessary statistics and timings. Profiler can be purchased by course attendees for 600€ (a fair offer if you ask me, considering the amount of work put in the development of this tool by Jože). Hopefully, I’ll publish some screenshots as soon as I get my copy of the profiler.
Anyway, if you have a chance to attend this course I’m sure you’ll not regret.
ORASRP – free trace file profiler
I found alternative free command line profiler, written in D language (speed of C) called ORASRP – Oracle Session Resource Profiler, written by Egor Starostin. Hard to tell which is better since I (still) don’t have my copy of profiler written by Joze Senegacnik and it’s not likely we’ll be spending any amount of money for new SW licenses this year…:-(