Hotsos PRO training Day 3/3

Today we learnt how to read raw trace files and manually calculate what HotSoS profiler does programmatically.

Metalink Note 39817.1 explains the same. In Oracle 10g we have a new package DBMS_MONITOR through which trace should be done.

We learnt to analyze trace files and did various exercises with profiler reports.

Ric said, he has stopped working with tkprof ever since he got introduced to HotSos profiler and that if he ever were to go independent, he would sell his soul for keeping the HotSos profiler in his bag of tools.

HotSoS profiler is the best. I have compared the outputs of other profilers like Oracle's trace analyzer and OraSRP. However none of the alternatives provides the depth of HotSoS profiler.

Hotsos PRO training Day 2/3

Today we learnt how to collect trace data with proper instrumentation, Hotsos free package ILO for implementing instrumentation in PL/SQL code was shown. We learnt how to read trace files.

One of the students, Rob had brought a production trace file. Our instructor, Ric, generated the profiler report for that trace file. There were 3 statements:

1. UPDATE 30% time
2. INSERT 25% time
3. UPDATE 27% time

Ric said that the first statement looked optimized as it was doing 1 LIO per execution which is very good. However, it was doing this thousands of time. In HotSos World, the best way to optimize something is not to do it. Rob explained that they were updating all the rows with very same data everytime. Ric told Rob to explore MERGE command which will combine INSERT and UPDATE and avoid the unnecessary UPDATE of very same data again.

Hotsos PRO training Day 1/3

I am attending the world famous 3 day training on Oracle Performance from Hotsos. Our instructor is Ric Van Dyke. We have 6 students in the class. Ric mentioned that he was our instructor and not a teacher as an instructor presents the information, but a teacher makes sure that the student learns whatever is taught. 3 days is a short period to teach.

7 years back when I was in New Delhi, India, I had read Cary Milsap's book Optimizing Oracle Performance from cover to cover. It had enlightened me about Oracle performance.

Here are some of the things we learnt today apart from the regular curriculum:

1. For each row returned you should touch 10 or fewer blocks

2. No. of Logical IOs = No. of tables in a query x No. of rows x 10

3. Nested loops work great with small data sets.

One of the students asked Ric "How do we find out what a query is doing if it is taking a dirt nap" and Rick said that he had never heard of the term "dirt nap". I also never heard it.

Apparently "dirt nap" is a colloquial term which means dead. A dirt nap is when you die, you take the big dirt bath to be buried underground. similar to death.