Oracle of dating series

23 Dec

You can copy/paste these into your SQL and the exact explain plan will be produced.

I find this technique useful for those occasional problem SQLs that won’t stay tuned.

Now, I haven’t looked in Oracle12 yet but Oracle11r2…the RULE hint was still visible in 10046 SQL Trace files.

The RULE hint tells the CBO to use the old optimizer that generated explain plans based on a set of rules that was mostly centered around the existence or lack of available indexes and tended to drive off the last table in the from clause.

oracle of dating series-26oracle of dating series-60oracle of dating series-57oracle of dating series-15

This is why the rule of thumb back in the day was to put the largest table last.

This feature enables caching of query result sets in client memory.

The cached result set data is transparently kept consistent with any changes done on the server side.

It plays back much like the old magnetic tape recorders (I know, I’m really dating myself). This testing tecnique will quickly give you this answer.

I find this replay an excellent way to test these SQL and application changes/changes in general. I learned about database replay in Robert Freeman’s book Oracle 11g Database New Features.