See what I did there? (Oracle)

[Ashenfury]

After a year of being the sole DBA here I learned something incredibly useful that I wish I had understood long ago. Almost all of my posts that involve me fighting performance issues are resolved by this one little parameter.

*.optimizer_index_cost_adj=50

Many years ago when I first started exploring parameters I was told not to mess with this one because it was already setup exactly as needed. Many of our parameters are like that. Set them once and deploy them like that 100x without any problems. This little bastard though...

What does this do? It's a parameter that controls the optimizer's preference of Index vs Full Table Scan. It has a range of 1-10000 and defaults to 100. Apparently setting it to 100 causes the optimizer to think that both explain plans would have equal fetch times... I'm not sure which engineer decided that should be the default but I am sure that he was hitting himself in the head with a hammer when he considered it a good idea.

We have 1-10 indexes for each data set that we import and nearly an infinite number of data sets. After setting this parameter to 50 my optimizer started using the indexes exclusively and now my longest running SQL isn't even considered problematic anymore. Yipee!

[Greebo]

Intriguing. So 101-10000 is the mirror of 1-99? If I may ask, why 50 and not, say, 2?

You use this when hand-tuning queries? Or is it for the built-in-always-on-cached-query-optimizer?

[Ashenfury]

Actually I was incorrect that it's a percentage scale. Apparently 1-10,000 is the 'cost' differential. So when the optimizer looks at explain plan of FTS vs Index they each have a cost represented as a numeric value 0-10000. This parameter is a baseline cost.

Playing with the optimizer is not something I'm very familiar with but that's my understanding as of right now :)

It's the Oracle Cost Based Optimizer that applies to any SQL running in the database. The hand tuning would be to rewrite the SQL to duplicate the most effective explain plan. I've never heard of disabling the CBO but I'm sure it can be done.

[Cristok]

I refuse to understand this.

[Deminthus]

I refuse to understand your avatar.

[Greebo]

Man has a right to eat turkey, dammit.

[Malstrom]

Actually I was incorrect that it's a percentage scale. Apparently 1-10,000 is the 'cost' differential. So when the optimizer looks at explain plan of FTS vs Index they each have a cost represented as a numeric value 0-10000. This parameter is a baseline cost.

Playing with the optimizer is not something I'm very familiar with but that's my understanding as of right now :)

It's the Oracle Cost Based Optimizer that applies to any SQL running in the database. The hand tuning would be to rewrite the SQL to duplicate the most effective explain plan. I've never heard of disabling the CBO but I'm sure it can be done.

The other problem with trying to match the most effective explain plan, is that it is nearly impossible for some complex queries.

In some cases, you might have a WHERE clause that restricts the rows involved in a table to 1 row. If you issued this as the first example of a certain query, it will cache an execution plan that optimizes for 1 row from that one particular table.

If the next time you issue the same query (but with different matching parameters) you might be returning a million rows from the same table. Suddenly your cached execution plan is no longer optimal.

Generally you want to try and issue the biggest query first, as it will have less impact on the optimizer when it conducts smaller queries. Doing this is also nearly impossible as it would need to be done on every server/instance reset (as the cache gets cleared on server/instance reset). Cached execution plans are always a tough nut to crack unless your queries are really vanilla (ie. always return one row, or always involves a similar number of rows from each table).

The parameter *.optimizer_index_cost_adj is used to tell the optimizer what threshold to use indexes vs full table scans. Since it tends to favor full table scans (because with memory today, and typical table sizes, the entire table can often be loaded into memory making full table scans quite fast and loading the index will require additional disk io which is often a bottleneck on current high volume database systems). In many cases the index scan will lead to faster results, but since it produces more IO, Oracle favors full table scans as it has to load the table anyway (to retrieve the data).

I believe that if your IO subsytem is not suffering and is not your bottleneck, or if you are using many covering indexes, you can modify this parameter to favor index scans and you will see a performance increase. If you have a high level of IO throttling, it will likely produce the opposite result and make queries slower as it loads the index from disk (IO), finds what it needs and then loads the table (moar IO) to get the data.

Mind you, it's been a couple of years since I worked with Oracle so things may have changed, but the above is true for most DBMS systems.

Mal

[Canaie]

I refuse to understand your avatar.

Oh, it's just Cristok eating a deep fried turkey leg at a Ren Fare.

[Malstrom]

I refuse to understand your avatar.

Oh, it's just Cristok eating a deep fried turkey leg at a Ren Fare.

I am somewhere in the background also eating a turkey leg.

Cause they were good. Damn good.

Mal

[Ashenfury]

The statement that is giving us the most headache actually does have a where clause that returns only 1 row. I would paste it here but the whole query ends up being about a page and half long once I populate the bind variables.

I really wish I understood how the where clause is effecting my optimizer/performance as well as you do!

[Malstrom]

The statement that is giving us the most headache actually does have a where clause that returns only 1 row. I would paste it here but the whole query ends up being about a page and half long once I populate the bind variables.

I really wish I understood how the where clause is effecting my optimizer/performance as well as you do!

Warning signs:

- COALESCE(X,Y)
- (WHERE FIELD1=X or FIELD1 is NULL)

Both of these often optimize for NULL. I know for a fact they do in SQL Server and query optimizers are often quite similar, cause hey! they're damn complicated and often based on PhD research papers.

Bind variables can also cause some issues for the optimizer. (EDIT: Actually, BIND variables may not be much of an issue. I realize now that I am generalizing something from the way SQL Server handles certain variables but I am not at all sure that Oracle does the same. Unlike the optimizer I suspect this part is quite different from DBMS to DBMS.)

In SQL Server (which I am currently cursed to work with) they have the RECOMPILE query hint which bypasses the execution plan and is pretty simple to use. I don't know offhand whether Oracle has something similar (a cursory google check got nada).

Mal

[Greebo]

Have fun Ash.
http://feeds.arstechnica.com/~r/arstech ... elease.ars

[Ashenfury]

Actually I count myself as extremely lucky. We don't use encryption, secure authentication, safe data transport, nada. There's a stipulation in the contract with our end users that states that all of the security needs will be addressed at the network level. My favorite little caveat.

We don't even secure our OS very well! I've only seen 1 hacking attempt in the 7 years I've been here and it was a brute force attack on one our boxes in the Caribbeans (if I remember correctly). We informed the end users of the breach in their network and it was promptly shut down.

Some of our systems (not the database but the process and equipment that provides me data) are actually network elements for telecommunications. It's a fancy way of saying that if you take down our server that our end user's, end users lose dial tone for their telephone networks. They realize this more than anybody and usually have extremely beefy network security (even the little guys).

[Cristok]

I refuse to understand your avatar.

Oh, it's just Cristok eating a deep fried turkey leg at a Ren Fare.

I am somewhere in the background also eating a turkey leg.

Cause they were good. Damn good.

Mal

Remember the bawdy wenches? Did I actually bury my post turkey leg face in a bosom or am I just hoping thats what happened?

[Kailei]

Those turkey legs at Ren Faire's are awesome...I have had a few of them in my years as well...but I will never post the evidence of that...I'm far too lady like LOL