## Wrong Index

Posted July 5, 2013One of the sad things about trying to keep on top of Oracle is that there are so many **little** things that could go wrong and take a long time to identify. In part this is why I try to accumulate test cases for all the oddities and anomalies I come across as I travel around the world – if I’ve spent the time recreating a problem I’ll probably remember it the next time I see the symptoms.

Here’s a little threat that comes into play when a couple of events occur simultaneously, in this case: automatically selected indexes being rebuilt combined with an unfortunate choice of index definitions. Here’s a demonstration (running 11.2.0.3, 1MB uniform extents, 8KB block size, freelist management – first the symptoms, script, followed by results:

## maxthr – 3

Posted July 3, 2013In * part 1* of this mini-series we looked at the effects of costing a tablescan serially and then parallel when the

*and*

**maxthr***statistics had not been set.*

**slavethr**In * part 2* we looked at the effect of setting just the

*- and this can happen if you don’t happen to do any parallel execution while the stats collection is going on.*

**maxthr**In part 3 we’re going to look at the two variations the optimizer displays when both statistics have been set. So here are the starting system stats:

## maxthr – 2

Posted June 27, 2013Actually, there hasn’t been a ** “maxthr – 1″**, I called the first part of this series

*. If you look back at it you’ll see that I set up some system statistics, excluding the*

**“System Stats”***and*

**maxthr***values, and described how the optimizer would calculate the cost of a serial tablescan, then I followed this up with a brief description of how the calculations changed if I hinted the optimizer into a parallel tablescan.*

**slavethr**## System Stats

Posted June 25, 2013Several years ago I wrote the following in “Cost Based Oracle – Fundamentals” (p.47):

*The maxthr and slavethr figures relate to throughput for parallel execution slaves. I believe that the figures somehow control the maximum degree of parallelism that any given query may operate at by recording the maximum rate at which slaves have historically been able to operate—but I have not been able to verify this.*

Browsing the internet recently, I discovered that that no-one else seems to have published anything to very my comment, so I decided it was about time I did so myself. I’m going to work up to it in two blog notes , so if you do happen to know of any document that describes the impact of * maxthr* and

*on the optimizer’s costing algorithms please give me a reference in the comments – that way I might not have to write the second note.*

**slavethr**## Webinar questions

Posted June 14, 2013Following the webinars about 11g stats that I presented on Monday John Goodhue emailed me a few questions that had come through the chat line while I was speaking, but hadn’t appeared on my screen. He’s emailed them to me, so here are the questions and answers.

*1. I’d like to know what parameter to use for faster results on dbms_stats.gather_dictionary_stats*

## v$lock

Posted May 2, 2013The problem of slow queries on * v$lock* just came up again on the

*, so I thought I’d better push out a post that’s been hanging around on my blog for the last few months. This is actually mentioned in MOS in note 1328789.1: “Query Against v$lock Run from OEM Performs Slowly” which points out that it is basically a problem of bad statistics and all you have to do is collect the stats.*

**OTN database forum**## System statistics poll

Posted April 12, 2013Recent thread in the OakTable mailing list prompted me to create a poll and ask about the ways DBAs use system statistics in real systems. If you struggle to understand what system statistics is and what are the available options, here is the suggested reading:

Documentation – System Statistics

Best Practices for Gathering Optimizer Statistics, Oracle whitepaper

System Statistics – Troubleshooting Oracle Performance

## Oracle SQL's MEDIAN Function

Posted April 9, 2013Article #3 in my ongoing series covering SQL statistic functions in Oracle Database is now up. The topic is the median:

MEDIAN: For When You Don't Really Mean It

Median is useful in typifying a data set when the data might be skewed, or in the presence of extreme outliers. For example, the U.S. Census Bureau reports *median household income* for states and counties so as paint a picture unskewed by the presence of, say, Bill Gates or Warren Buffet living just down the street. To learn more, hit the link.

## Standard Deviation and the Mean

Posted March 20, 2013I've just put up the second in an ongoing series (I hope!) of articles on Oracle SQL's build-in statistical functions. The topic is standard deviation. The previous one, my first, is on the mean. Here are links to the two:

2. STDDEV: Standing Sentinel on Your Data

1. AVG: What Does it Mean?

## Fixed stats

Posted February 25, 2013Some time ago I had two questions about fixed objects statistics for which I couldn’t quickly find the answers. Questions are:

- how to gather fixed object stats for a given X$
- should I gather fixed objects stats on both RAC nodes
- why I can’t gather stats on some X$-tables

At the time of asking these question I’ve read CBO development team blog post on the topic, but still I was unable to answer them distinctly. Well, it appears the questions are simple and could probably be deduced after careful reading (first two questions for sure). The core phrase is:

The DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure gathers the same statistics as DBMS_STATS.GATHER_TABLE_STATS except for the number of blocks