Being re-constituted from the old website; if you can’t find what you’re seeking, please let me know via email and I’ll get it re-posted as soon as possible…
Presentation: Real-Time SQL Tuning
Presented at the Hotsos Symposium 2014 in Irving TX and at the UTOUG "Training Days 2014" in Sandy UT, this presentation discusses the problems of tuning long-running SQL statements in real-time, without having to wait for them to complete. Discusses the problems of obtaining "actuals" information to complement the estimates the Oracle optimizer uses to determine an execution plan, and how the SQL Plan Monitor utility (GV$SQL_PLAN_MONITOR) can be used to provide that real-time monitoring. But SQL Plan Monitor doesn't provide "actual" elapsed time information, so the presentation discusses the possibility of augmenting this information further with Active Session History (ASH) information. The ASH_XPLAN package (downloadable from my scripts page) encapsulates the concepts discussed.
White Paper: RDBMS Forensics: Troubleshooting Using ASH
This is based on three case studies illustrating how Active Session History (ASH) can be used as the next best thing to SQL-trace (event 10046) files when diagnosing complex problems in the Oracle RDBMS, particularly in situations where SQL-trace files are not available, such as long after the error occurred. ASH is not just for performance tuning, it can also provide vital information for the forensic analysis following a specific problem.
Presentation: RDBMS Forensics - Troubleshooting Using ASH
Presentation slides to accompany the white paper.
White Paper: Scaling To Infinity - Partitioning Data Warehouses using Oracle Database
This paper was presented at the Oracle Open World conference in 2011, 2010, 2009 and 2007 in San Francisco CA, and at the RMOUG Training Days conference in 2010, 2009, 2006, and 2005 in Denver CO, Collaborate 2013, and the OTN China/Japan 2013 tour. It has been presented numerous times at other Oracle users group conferences and as a 1-day and 2-day training seminar for Oracle University and Speak-Tech.
This paper explains why using the EXCHANGE PARTITION operation during ETL leads a data warehouse onto the virtuous cycle of effective use of advanced features and easy living. Not using EXCHANGE PARTITION during ETL relegates a data warehouse onto the dreaded death spiral where life becomes progressively more difficult and ROI shrinks to zero.
Presentation: Scaling To Infinity - Partitioning Data Warehouses on Oracle
Presentation slides to accompany the white paper.
White paper: Compression in Oracle Database
This paper was presented at the RMOUG Training Days conference in 2013 in Denver CO in February 2013, at the UTOUG Training Days conference in Salt Lake City UT in March 2013, and at the IOUG forum of Collaborate 2013 in Denver CO in April 2013.
This paper explains the three types of table data compression available in Oracle database: BASIC/OLTP de-duplication, hybrid-columnar compression (HCC), and the often-overlooked "trailing NULL columns" form of compression. The paper reviews all of the features of Oracle that have combined to implement the first two compression features, paying particular attention to reviewing row formats and block formats within Oracle database, using blocks dumps and diagrams as illustration.
Presentation: Compression in Oracle Database
Presentation slides to accompany the white paper.
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
Thanks for reply and detailed answer .
Tim,
Yes, I promised i will never used those two parameters (i.e. OPTIMIZER_INDEX_*) for anything other than testing .
Thanks again .