ODC Appreciation Day – Plan Statistics

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s