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.
This initiative started by Tim Hall from ORACLE_BASE last year. In this day, every blogger writes something to thank Oracle for. You can read about the concept and “rules” here.
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.
1 thought on “ODC Appreciation Day – Plan Statistics”