Here’s an interesting post and test case from Gregory Guillou (WeDoStreams blog). It features an SQL statement that is re-optimised the second time you run it.
Since it’s running on 220.127.116.11 your first thought is likely to be “SQL Plan Management”, or “Adaptive Cursor Sharing” – except the first feature wasn’t enabled, and the statement doesn’t include any bind variables.
Gregory emailed me about this one, and it was the thing that finally persuaded me to tear down a laptop and install 64-bit OEL with 11.2 – and I ran his test case and got the same results. (If you build his sample schema, you’ll need about 1GB of free space).
- Just run the query from SQL*Plus, then hit “/” to re-run it.
- Check v$sql and you’ll find two child cursors, with different execution plans
- Use dbms_xplan.display_cursor() to check v$sql_plan and you’ll see the second plan report that it’s using “cardinality feedback” (shades of Wolfgang Breitling)
- Flush the shared_pool and repeat the exercise with event 10053 (optimizer trace) enabled and you’ll see that the second execution is optimised with a few opt_estimate() hints embedded.
- Flush the shared_pool and repeat the exercise with event 10046 enabled and there’s no clue about how Oracle is getting the estimates it uses in the hints – it’s clearly not running any dynamic sampling code, and the “actuals” of v$sql_plan_statistics_all are not populated.
A word of warning – the test is not guaranteeably reproducible. Gregory discovered it on 32-bit Oracle, I’ve reproduced it on 64-bit Oracle. We were both using small SGAs and PGAs to make the database work; but Gregory found that he could not reproduce the effect on some other systems.