Quick: If you had to pick the least-expensive-cost query you could think of, what would that query look like?
…(hums Jeopardy theme)….
Ding! Time’s up!!
Most people respond with something “select from a one-block table.”
Let’s give it a shot:
create table drop_me
as
select *
from dual;
explain plan for
select *
from drop_me;
select *
from table(dbms_xplan.display(format => 'basic +cost'));Plan hash value: 745012572
--------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
--------------------------------------------------
| 0 | SELECT STATEMENT | | 3 (0)|
| 1 | TABLE ACCESS FULL| DROP_ME | 3 (0)|
--------------------------------------------------Cost of 3! (on my system) Hey, not bad!!
There’s no possible way that’d we’d be able to reduce the cost of a one-block query, right?
….actually, we could.
Check it out, if I add an index to our query:
create index drop_me_idx on drop_me (dummy);…and then add a predicate on the query that allows me to use the index…
explain plan for
select *
from drop_me
where dummy = 'X';
select *
from table(dbms_xplan.display(format => 'basic +cost'));Plan hash value: 720537393
-----------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 (0)|
| 1 | INDEX RANGE SCAN| DROP_ME_IDX | 1 (0)|
-----------------------------------------------------Boom! I’ve dropped my cost from 3….all the way down to 1.
Awesome!
But wait…why?
Why is Oracle suddenly taking two off of our cost?
The answer: Whenever you do a full table scan, you have to first read the segment header, and then read the extent map…and then when you finally *DO* get to the block that you have to read, you have to read the block header, and “decode” the data that’s in the block.
Whenever you read an index:
- You don’t have to read the segment header or the extent map. You can just instantly start reading the root block of the index. So Oracle always knows immediately where to start–right at the index block. No having to read the segment header or extent map to try to “make sense” of everything.
- When you do subsequently TABLE ACCESS BY INDEX ROWID, Oracle doesn’t have to try to read the entire block the way it has to in a full table scan….the ROWID gives it enough information where it can simply jump directly to the position within the block where the row is located, and start reading it immediately. So a consistent get (logical IO) that happens using an index will likely take less time than a consistent get by table scan.
SO! This means that NO TABLE IS TOO SMALL to benefit from indexing.
Literally, even a 1 block table can benefit from adding an index to it.
Cool stuff, huh?
So…there’s absolutely no way we could improve on a query that looks like this…right?
A query with a cost of 1…we’re able to immediately jump to where the row is….we don’t have to read the segment header, we don’t have to read the extent map, we can scan the root block of the index and immediately know where to start scanning the block using the index rowid.
Impossible to improve upon, right?
Well…actually, there’s quite a bit of room for improvement. 😉
Suppose we dropped the “regular” index, and instead added a unique index:
drop index drop_me_idx;
create unique index drop_me_idx on drop_me (dummy);
explain plan for
select *
from drop_me
where dummy = 'X';
select *
from table(dbms_xplan.display(format => 'basic +cost'));Plan hash value: 3178888967
------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 (0)|
| 1 | INDEX UNIQUE SCAN| DROP_ME_IDX | 0 (0)|
------------------------------------------------------BOOM!
You can see we have a cost of ZERO on the query.
How awesome is that??!?
But wait…HOW were we able to reduce the cost from just ONE??!?
Answer: A unique index is more efficient than a non-unique index.
A non-unique index has to treat a ROWID as it’s own column.
Essentially, this means that there’s no such thing as a “non” unique index…because the ROWID gets added to the end of the column within the index to make it unique, so that Oracle can differentiate between rows in a table.
This comes in handy, for example, when you’re issuing a DELETE statement…When you delete a record from a table, you must also delete any corresponding index rows that point to our table. Well, if there are multiple index entries that match on the indexed columns values, you can’t just delete “any ol’ index entry” that matches on all of the columns…you have to delete the one that pertains exactly to the row (thus, the rowid) you’re deleting….
So picture this…if I have an index that has a billion entries that all look alike…and I go and I delete one of the records from the table that matches one of these billion entries….Oracle will not want to scan through all one-billion entries to try to figure out which row to delete. It will want to navigate immediately to the pertinent record and delete it.
Thus, for non-unique indexes, ROWID is treated like one of the “indexed” columns. So allowing for the possibility of a DELETE command, this means that Oracle must include the ROWID as one of the “indexable columns” on the index, so that Oracle can quickly remove index entries.
For a unique index, though, you’ve already told Oracle that all the index entries will be unique.
Therefore, Oracle DOES NOT have to treat the ROWID like “another index column”…it can safely just index the values you’ve specified knowing that each index entry will be unique, even if it doesn’t add the ROWID.
Also! Here’s another advantage of an index unique scan: Oracle knows that it can take advantage of certain optimizations for an INDEX UNIQUE SCAN.
For example: Whenever Oracle does an INDEX UNIQUE SCAN, it knows that this operation will take a very small amount of time–the operation is scanning a unique index while specifying a unique set of index entries; therefore, the query will either return 1 row….or 0 rows….(there’s no other alternative with a unique scan…if there’s the possibility that more rows will be returned, Oracle will do an INDEX RANGE SCAN instead).
Previously, Oracle was doing an index range scan….meaning it could return 0 rows, 1 rows, 2 rows or even a hundred billion rows (hopefully not) or anything in between.
With an INDEX UNIQUE SCAN, Oracle is basically *guaranteed* to only encounter either zero or one rows.
This means that Oracle knows it will spend a very small amount of time with a table access, so it can do something sort of devious:
You see, normally when Oracle gets a row from a table using an index, it requires getting a latch…twice.
The block that it’s trying to read is (probably) going to be from the buffer cache (even if the block hasn’t already been loaded to the buffer cache before hand, odds are high that it will be loaded there eventually), meaning Oracle must first acquire the Cache-Buffer-Chains latch just one time, so that Oracle can “pin” the block
(pinning the block means Oracle is saying “Hey! Keep your hands off of this block, I’m using it!!! Don’t try to remove this block from the buffer cache…don’t try to write it out to disk, don’t try to replace this block with another one….I’m using this block currently.”)
Then, once it’s done with the block, it has to “unpin” the block.
This means that for a “regular” index scan, Oracle has to grab the CACHE BUFFER CHAINS latch….twice!
- Once to pin the block
- Once to “unpin” the block
Well…with an INDEX UNIQUE SCAN…Oracle is basically GUARANTEED to spend a very small amount of time reading the block.
So Oracle can shortcut things!
Instead of “acquiring the CBC latch once” to pin the block…
….and then “acquiring the CBC latch again” to un-pin the block…..
…Oracle goes “yeah, I know for a fact that I’m going to spend very little time on this INDEX UNIQUE SCAN operation…since I’m going to return (at very most) one ROW.”
So what Oracle does is…it grabs the latch and “holds on” to it, for the entire duration of the operation. (See the statistic listed in v$mystat/v$statname join called “consistent gets examination”).
Oracle doesn’t relinquish the latch…it goes “I know I’m only going to spend a very limited amount of time on this block…and each time we get a latch (such as the CBC latch) it consumes a significant amount of time and CPU (it takes roughly 150-200 machine instructions for Oracle to acquire a latch…so multiply that x2 when talking about pinning/unpinning a buffer).
When Oracle grabs a buffer during an INDEX UNIQUE SCAN, it says “well, since I’m GUARANTEED to spend such little time on an index unique scan (either 1 or 0 rows) I can just grab the CBC latch….and keep hold of it until the operation is done! Rather than going through the trouble of pinning/unpinning the block again.”
This means that instead of Oracle doing 2 latch-gets (up to 200 machine instructions each) Oracle is just going to do ONE latch get…and hold it while Oracle gets the row from the INDEX UNIQUE SCAN.
Normally, for a “regular” index scan, Oracle wouldn’t want to perform this type operation, because it would be holding on to a latch…possibly longer than normal…that other processes might want access to.
The whole point of “getting the latch” is to prevent others from modifying the block while you’re reading it, but if you have a long-lasting operation, it could cause database contention!
So! It’s really only safe to do with a short-lived operation, such as an INDEX UNIQUE SCAN (there are other times when Oracle will use this too, such as reading undo segments, but it’s only when Oracle knows that it will be a very short-lived operation).
So! I’ve literally shown you how to get a cost of ZERO.
There is NO. FREAKING. WAY. that you can improve upon a cost of zero.
That would be I-N-S-A-N-E to think about….right?!?!?
…actually…we can still get a lower cost. (wuuuuuttt?!?!?)
Cost is broken up into two different components:
- How long is the physical IO component of the query supposed to take?
- How long is the CPU component of the query supposed to take?
AND!
Every time you do an EXPLAIN PLAN, and then pull the plan back using DBMS_XPLAN.DISPLAY….
….what you’re doing is you’re either putting records into a global temp table (SYS.PLAN_TABLE) when you issue an EXPLAIN…
OR!
…you’re reading the records in the global temp table when you’re using the DBMS_XPLAN.DISPLAY function.
So if you want to know even MORE about the query, you can select directly from the plan table.
delete from plan_table;
explain plan for
select *
from drop_me
where dummy = 'X';
select cpu_cost, io_cost, cost
from plan_table; CPU_COST IO_COST COST
---------- ---------- ----------
1050 0 0
1050 0 0So we can see that there are two components to figuring up cost. Oracle finds out “how much time do I think I’m going to burn CPU?” and figures that up into the CPU_COST column…then it figures up “how much time do I think I’m going to burn IO?” and figures that up into the IO_COST column.
Then, it takes the CPU_COST unit, and converts it to IO_COST…and it actually takes a SIGNIFICANT amount of CPU cost to equal just 1 IO cost. It then takes the CPU_COST that’s been converted to IO_COST units, and adds that to the IO_COST of the query…and that gives us the cost of the plan.
So the total plan cost is:
CONVERT_TO_IO_COST_UNITS(CPU_COST) + IO_COST
So when Oracle gives us our cost of 0 in our execution plan, it’s actually taking the CPU_COST of our query, which is 1050…and converting that to fractional IO_COSTS…and then adding our converted CPU cost to our IO cost.
So you can see that Oracle says “oh yeah, for this query, we’re going to burn 1050 CPU units of time (I would hazard a guess that this is roughly 1050 CPU instructions…possibly allowing for “average CPU throughput” based on things such as “CPU Pipeline Bubbles” etc).
Oracle says “when you convert 1050 CPU units to IO Cost units, it turns out to be a very small decimal that rounds to zero….it’s not even a whole IO COST unit of time”
But still, you and I know from looking at the underlying table that we’re dealing with approximately 1050 CPU cost.
What if I told you…we could do better still?
Crazy talk, right?
But check it out:
drop table drop_me;
create cluster drop_me_clust (x number) size 128 single table hashkeys 1;
create table drop_me
(
x
)
cluster drop_me_clust (x)
as
select 1
from dual;
delete from sys.plan_table$
explain plan for
select x
from drop_me
where x = 1;
select cpu_cost, io_cost, cost
from sys.plan_table$;Survey says…
CPU_COST IO_COST COST
---------- ---------- ----------
0 0 0
An *actual* cost of 100% zero….not just coincidentally showing zero because of a rounding error due to minimal amount of CPU, but actually zeros all the way around.
This is because Oracle goes “I don’t even have to traverse through an index to get my answer…I don’t have to touch index blocks, I don’t have to read segment headers, I can hash the value that I’m looking for, and immediately know exactly where to look in a table.
As a clarification, when Oracle is saying “this is an absolute cost of zero” Oracle is not saying “this requires zero work…” this is actually Oracle saying “this is the absolute fastest way to access this information…why bother assigning it a cost if we know that this method will always be the cheapest,” since the objective of cost is to find the fastest path of a query, not get an accurate assessment for amount of work the database is doing.
Free Oracle SQL Tuning Guide
Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!
Get it here: tuningsql.com/secrets
