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.