Stelios Charalambides

Tracing CBO Decisions, Hints, and Query Transformations Using 10053

The Oracle query optimizer is an amazing piece of code, developed and improved over the years to generate execution plans that are both easy to generate and that run fast. The optimizer uses a number of “tricks” along the way to improve the speed of execution and implements the execution plan in a way that gives the same results as your unmodified SQL. These “tricks” (or heuristics) sometimes include query transformations.

AWR: Your Guide on What to Do Next

In the dim and distant past there used to be a utility called Statspack {XE “Statspack”}. More people now use AWR than Statspack, but Statspack is still useful for those sites where there is no Enterprise edition and for which there is no Diagnostic pack. It also works the same as it did back in 7.3.4. Statspack was replaced long ago by the more sophisticated tool AWR (Automatic Workload Repository). Has AWR now been replaced by SQLT? No. They are different blades on the same Utility toolkit that you always carry with you as a DBA. One for tuning SQL and one for tuning the system. Let me explain what I mean by that last statement.

SQL Monitor Active Reports

In days long past the best tool for tuning SQL was the humble 10046 trace, and it’s still useful today, but other tools have been developed over the years, to give us the high level picture with some deep dives when needed. Not least of which is SQLTXPLAIN. SQL Monitor has been around for a while but in 12c it has been improved and is now especially useful for parallel execution.

Directives and Statistics Feedback

In 11g there was a feature called Cardinality Feedback. This reoptimized (hard parsed) a query if the Cardinality Estimate of the final result was more than a certain factor out from the actual result. This was a nice simple feature that was easy to understand. If you saw this note at the end of an execution plan, you knew that Cardinality Feedback had been used.

How to Work with Adaptive Plans

Imagine you’ve come to conquer the planet SQL but those pesky aliens are constantly changing their bind variable values presenting skewed data all the time, and sometimes not even reporting how many rows there are. The name of the game would be Adapt and Conquer. Another adjective you could use would be Dynamic. Your space fleet’s motto might well be “Victory through Adaptive and Dynamic Plans.” In the world of SQL plans, Adaptive Cursor Sharing has already been introduced in the previous chapter. ACS deals with different combinations of bind variable values and adapts its plan. This allows us to change the plan based on the bind variable values. But what if our plan was so wrong that even during the execution we could see that the ‘wheels were coming off the wagon’? The philosophy, up to 11g has always been to find the best plan and then stick with it. As of 12c and Adaptive Plans the plan can change while executing! This feature ‘Adaptive Plans’ is part of the whole new area ‘Adaptive Query Optimization’. Let’s review the whole area before focusing on Adaptive Plans.

Collecting Good Oracle 12c Statistics

I probably do go on and on about statistics, how it is crucial to collect good statistics, how statistics are to blame for all the tuning ills in the world, and how all tuning problems would be fixed if we just had good statistics. But what exactly are good statistics and why can’t we rely on the default mechanisms and auto-collection mechanism to do the job for us? The answer is that you probably can, most of the time! But when the automation is not ideal then you have to know what is happening, what good practices are, and how to get the show back on the road.

The Cost-Based Optimizer Environment

When I’m solving tricky tuning problems, I’m often reminded of the story of the alien who came to earth to try his hand at driving. He’d read all about it and knew the physics involved in the engine. It sounded like fun. He sat down in the driver’s seat and turned the ignition; the engine ticked over nicely, and the electrics were on. He put his seatbelt on and tentatively pressed the accelerator pedal. Nothing happened. Ah! Maybe the handbrake was on. He released the handbrake and pressed the accelerator again. Nothing happened. Later, standing back from the car and wondering why he couldn’t get it to go anywhere, he wondered why the roof was in contact with the road.

Adaptive Cursor Sharing

There’s no doubt that Adaptive Cursor Sharing is one of the most misunderstood and confusing optimizer areas. It doesn’t help that it is sometimes referred to as Intelligent Cursor Sharing or Bind Aware Peeking. Adaptive Cursor Sharing was introduced in 11g to take care of those pesky bind variables that keep changing. SQLTXTRACT has a section called “Cursor Sharing and Binds,” which can be found at the top of the main report. See Figure 7–1 to remind yourself. The Adaptive Cursor Sharing section is at the bottom left of the screen.

The Final Word

The key to successful tuning with SQLT is to use SQLT regularly and for real-life problems. If you’ve reached the final chapter, you should now consider yourself a card-carrying member of the SQLT supporters club. You’ve learned a lot about what SQLT can do, and along the way you’ve probably learned some things about the cost-based optimizer and the Oracle engine. Let me remind you of some of the features we came across on our journey: The effect of statistics on execution plans The effect of skewness on execution plans How the optimizer transforms SQL during parsing How profiles can help you temporarily freeze an execution plan How adaptive cursor sharing works How dynamic sampling works How cardinality feedback works How you can use SQLT with Data Guard How test cases can be built with SQLT to allow exploration of the execution plans How to use the brute force of XPLORE to look for unexpected effects on the CBO from upgrades and other changes. How we can use the COMPARE method to investigate two SQLs Last but not least we talked about the health check script, which is a good second choice if SQLT is not available.

Running a Health Check

The SQL health check script is not SQLT. It is a completely separate utility downloaded from a different Metalink note and used in a different way. Why is it even mentioned in this book? SQLHC is considered a backup option when SQLT cannot be installed. This does not happen very often, but when it does it is usually because of site restrictions. There are many reasons for this: Not allowed to install anything on production (security reasons). Not allowed to install anything on production (fear of affecting production). Installation and testing procedures take too long and you have a problem on production. Don’t trust SQLT. SQLT is too complicated. Not enough space to install SQLT.