Struggles With Statistics
Jonathan Lewis, JL Computer Consultancy
The optimizer manages to find reasonable execution plans for a huge fraction of the queries thrown at it, but it's still surprising how a small number of problems, apparently occurring at randomly over time, can have a huge impact on performance at critical moments during the day.You might hope that leaving the optimizer to do its default stats collection would be sufficient to minimise the number of problems you see - but there are many ways in which the optimizer can take the best default statistics it's got and produce bad cardinality estimates that lead to bad execution plans.In this execution plans I'll explain some of the classic patterns that the optimizer has trouble with, and give you strategies for working around the problems that can be most easily addressed. After seeing this session you will be better equipped to predict the cases where performance may be unstable, and more able to identify the underlying more rapidly if you are hit by performance problems created by changes in execution plans.
Jonathan Lewis is a well-known figure in the Oracle world with more than 30 years experience using the software. He has published three books about Oracle - the most recent being "Oracle Core" published by Apress Nov 2011 - and contributed to three others. He has posted more than a 1,200 articles to his blog and contributes fairly regularly to newsgroups, forums, and User Group magazines and events around the world.Jonathan has been self-employed for most of his time in the IT industry. For the last 25 years he has specialised in short-term assignments - typically of a design, review, or trouble-shooting nature - often spending no more than two or three days on a client site to address problems . He has presented seminars about using Oracle all over the world and has visited more than 50 different countries (and more than a dozen US states) to talk about, or trouble-shoot, Oracle systems, but now spends more time in the UK using Internet technology to handle remote trouble-shooting assignments.