If you don’t know that by now, OTN (Oracle Technology Netowrk) has changed its name to ODC (Oracle Developer Community), so OTN Appreciation Day becomes ODC Appreciation Day.
In last year post I wrote about RAC. This year I want to choose a much smaller feature, but one that helped me A LOT over the years.
When tuning SQL, one of the most important thing is to know if the optimizer calculated the cardinality wrong. This is the number 1 reason for bad plan. I think that this feature was introduced in 10g Oracle and now we can get important cardinality information using the /*+ GATHER_PLAN_STATISTICS */ hint and the DBMS_XPLAN package.
I won’t explain the entire usage, as you can read about it in Maria Colgan’s post, but the bottom line is that the output will provide information about the optimizer cardinality estimation vs. the real number of rows generated. Using this comparison, you can easily find where the optimizer calculation was wrong and it’s a huge step towards fixing the performance problem.