Performance Tuning

Category

In part 3 of this series we used a simple hash join to introduce the a simple guideline for reading execution plans – the “first child first, recursive descent” method. This allowed us to work out the order in which Oracle produced rowsources and (implicitly) the order in which it visited the different physical objects in the query. At the… Continue Reading →

All Things Oracle Full Articles, Oracle Database, Performance Tuning

In the first two articles in this series we listed a couple of methods for generating or retrieving execution plans and learned a few extra steps that could increase our confidence that we were using the right environment to investigate any problems we might have with a plan. In this article we’re going to become acquainted with a basic (though, as… Continue Reading →

All Things Oracle Full Articles, Oracle Database, Performance Tuning

After providing a baseline for Parallel Execution working as expected in the introduction of this series, in this part I’ll demonstrate how things can go wrong with the work distribution. A Simple Example Of Parallel Execution Skew One common source of Parallel Execution Skew is a skewed foreign key, which means that the value distribution of the foreign key column… Continue Reading →

10g, 11g, 12c, All Things Oracle Full Articles, Oracle Database, Performance Tuning, Troubleshooting

It is often easy to forget this, but in many ways it is after we hit the execute button that the really exciting stuff starts with our code. A number of engines silently spring into action; including the optimizer. The optimizer analyses your SQL statement and decides the most efficient way to execute it based on the objects involved in… Continue Reading →

All Things Oracle Full Articles, Performance Tuning

In part 2 we looked at the way that Oracle collects and uses height –balanced histograms, and raised the problem of how sensitive they could be to small changes in the sampling, or in the data. We ended the article with a suggestion on how you could generate data for an “approximate” frequency histogram  that could be “good enough” to… Continue Reading →

All Things Oracle Full Articles, Database Administration, Database Development, Performance Tuning, Troubleshooting

In part 1 of this series we discussed the reasons why we might want to create some histograms to help the optimizer deal with skewed data distribution. We used an example of a simple status column in an orders table to demonstrate principles, talked about frequency histograms in particular, and highlighted a couple of problems associated with histograms. In part… Continue Reading →

All Things Oracle Full Articles, Database Administration, Database Development, Performance Tuning, Troubleshooting

In this short series on histograms we will be looking at the reasons why we need histograms and the methods Oracle has to create them. We will examine the costs of creating them and the potential they introduce for undesirable overheads and then we will review their potential for giving you stability problems in your execution plans. This overview will… Continue Reading →

All Things Oracle Full Articles, Database Administration, Database Development, Performance Tuning, Troubleshooting

The forest is thick with menace. The sunlight barely breaks through the leaves and is sludgy like curdled milk; the shadows of the branches look like gnarly fingers reaching to grasp at the pair of frightened small children. As they wind down the path, Hansel leaves a trail of pebbles. “It will help us find our way home,” he says… Continue Reading →

All Things Oracle Full Articles, Database Development, Performance Tuning, Troubleshooting

  Watch video The video above is accompanied by the following resources: Analysing and Troubleshooting Parallel Execution presentation slides (.pdf) Further reading: Calculating Parameter Values for Parallelism Auto DOP and Concurrency Auto DOP and Queuing Parameter Settings Auto DOP and Parallel Statement Queuing Workload Management – Statement Queuing Workload Management – A Simple (but real) Example Parallel_degree_limit hierarchy – CPU,… Continue Reading →

Database Development, Oracle Database, Performance Tuning, Troubleshooting, Webinar

This video is a follow up to the webinar broadcast by Red Gate on 27th February 2012. Please leave a response and ask questions at the bottom of the page. View the presentation slides Watch video This webinar features a demonstration of Source Control for Oracle. You can find out more about this new tool here: Source Control for Oracle…. Continue Reading →

Database Administration, Performance Tuning, Troubleshooting, Webinar