Ron,
My apologies for being so slow in reconstituting my website since migrating to WordPress. I will get everything re-posted for download soon, and I would be glad to share the white paper with you via email, with one huge caveat…
…and that caveat is that the white paper was written for Oracle8.0 and Oracle8.1 only, and it has been effectively obsolete since Oracle9i, for almost 10 years now. So please bear in mind that using the OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ parameters for any version of Oracle greater than Oracle8i is dangerous and will cause more problems than it resolves. Please consider using the GATHER_SYSTEM_STATS procedure within the DBMS_STATS package to set appropriate values within the SYS.AUX_STATS$ table for the CPU model of the cost-based optimizer. I strongly recommend reading up on gathering statistics using information posted by Maria Colgan at “http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/1354477.pdf” and Kimberly Floss at “http://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15tech-tuning-090612.html”.
If this is acceptable, then please let me know and I’ll send the white paper.
This white-paper is hopelessly obsolete; it was written for Oracle8i and has been largely obsolete since Oracle9i and the advent of the GATHER_SYSTEM_STATS procedure in the DBMS_STATS package.
I strongly urge everybody *NOT* to play with the parameters OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ, but rather to focus on gathering statistics properly to provide the cost-based optimizer all the information that it needs, because providing good cardinality estimates to the optimizer is much better than fiddling with parameters that severely skew the workings of a complicated mathematical engine. Gathering good statistics is not that hard; the default stats-gathering job created within the database since Oracle10g v10.1 accomplishes most of that, with a few small exceptions.
I have kept this away off my website purposefully, for these reasons.
Because I believe that I caused a significant amount of damage keeping that paper posted too long, I’m not going to post it again, or allow it to be posted anywhere, if I can. I still frequently see those two parameters in use, and I always recommend strongly to cease doing so. Where ever I find that paper re-posted, I always have (and always will) request that it be taken down.
If you really want me to share this ancient white paper with you, then I’ll email it to you, as long as you promise never to use those two parameters (i.e. OPTIMIZER_INDEX_*) for anything other than testing?
Can I get a copy of your paper “The Search for Intelligent Life in Oracle’s cost-based optimizer.
Thanks
Ron,
My apologies for being so slow in reconstituting my website since migrating to WordPress. I will get everything re-posted for download soon, and I would be glad to share the white paper with you via email, with one huge caveat…
…and that caveat is that the white paper was written for Oracle8.0 and Oracle8.1 only, and it has been effectively obsolete since Oracle9i, for almost 10 years now. So please bear in mind that using the OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ parameters for any version of Oracle greater than Oracle8i is dangerous and will cause more problems than it resolves. Please consider using the GATHER_SYSTEM_STATS procedure within the DBMS_STATS package to set appropriate values within the SYS.AUX_STATS$ table for the CPU model of the cost-based optimizer. I strongly recommend reading up on gathering statistics using information posted by Maria Colgan at “http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/1354477.pdf” and Kimberly Floss at “http://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15tech-tuning-090612.html”.
If this is acceptable, then please let me know and I’ll send the white paper.
Thanks!
-Tim
Hi Tim,
Can you please send the “The Search for Intelligent Life in Oracle’s cost-based optimizer” whitepaper to my email address . Thanks in advance.
Sahil,
This white-paper is hopelessly obsolete; it was written for Oracle8i and has been largely obsolete since Oracle9i and the advent of the GATHER_SYSTEM_STATS procedure in the DBMS_STATS package.
I strongly urge everybody *NOT* to play with the parameters OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ, but rather to focus on gathering statistics properly to provide the cost-based optimizer all the information that it needs, because providing good cardinality estimates to the optimizer is much better than fiddling with parameters that severely skew the workings of a complicated mathematical engine. Gathering good statistics is not that hard; the default stats-gathering job created within the database since Oracle10g v10.1 accomplishes most of that, with a few small exceptions.
I have kept this away off my website purposefully, for these reasons.
Because I believe that I caused a significant amount of damage keeping that paper posted too long, I’m not going to post it again, or allow it to be posted anywhere, if I can. I still frequently see those two parameters in use, and I always recommend strongly to cease doing so. Where ever I find that paper re-posted, I always have (and always will) request that it be taken down.
If you really want me to share this ancient white paper with you, then I’ll email it to you, as long as you promise never to use those two parameters (i.e. OPTIMIZER_INDEX_*) for anything other than testing?
Thanks!
-Tim