Hi Leo,
Dear Sirs, We got low 0% of indexed Queries. I'm very worry
about that. If there is anything that I need to config? Or
if it's normal to get 0% of index Queries ? PS: I got 0% of
index queries from "Database -> Monitor-> DatabaseMonitor".
My Oracle is v9.2.0.4, TOAD is 9.0.1 Thanks a lot !!
0% indexed reads is probably not a good thing. 
There are many problems that will prevent an index from being used, some
of the ones I remember are:
- You don't have enough values in the index - selectivity is low:
Imagine you have a table with 20 million rows, indexed on a gender
column, for example. Now, imagine that the vast majority of those gender
columns are 'FEMALE'. Looking for a FEMALE in that column will most
likely have to use a full scan because there are so many FEMALES.
Looking for MALE or UNDECIDED would most likely use the index, however,
see below for histogram problems.
- The statements that could use the indexes are being prevented due to
implicit or explicit function calls:
A few examples should suffice:
select stuff from table where date_column = '01/02/2010';
select stuff from table where to_char(date_column, 'dd/mm/yyyy') =
'01/02/2010';
select stuff from table where number_column = '123456';
select stuff from table where to_char(number_column) = '123456';
And so on.
If you use a function call on the left side of the comparison, the index
cannot be used.
If you compare dissimilar data types - date with string etc - then the
index can't be used as Oracle implicitly converts the column to match
the data type of the value being compared with.
If you create a function based index on a character column, to convert
it to a number:
create index whatever on table_name (to_number(char_column));
The index cannot be used unless the left side of the comparison is
exactly the same function call, so this should use the index:
select stuff from table where to_number(char_column) = 123456;
But this will not use it, even though the column is converted implicitly
by Oracle, to a number:
select stuff from table where char_column = 123456;
- Oracle 9i bind variable peeking is selecting for the cases where the
use of an index would not be the best plan:
Bind variable peeking was introduced at Oracle 9i. What it does is looks
at the SQL - "select stuff from table where column = :a_bind_value" -
and on each HARD parse of the statement, looks to see what is in
":a_bind_value" and uses that to build a plan.
Using our example above, if the bind held UNDECIDED the it would -
assuming histograms were present - choose to use the index. This is good
but when the next execution binds the value of FEMALE, it still uses the
index.
Equally, if the bind holds FEMALE on the hard parse, the full scan that
results will be used even if all the remaining executions (until the
next hard parse) are MALE or UNDECIDED.
-
CBO Statistics incorrect or not gathered:
Without stats being gathered (and histograms - see below - where
required) the CBO has no idea how good your data spread is and will use
default settings for certain statistics/and gather a sample of stats
each time it accesses the table. -
No Histograms present for skewed data:
Histograms are useful when the data in the index is lop sided (skewed).
Using the MALE/FEMALE/UNDECIDED example from above, then without
histograms, the CBO assumes that there are
total_number_of_rows/number_of_unique_entries for each index entry. With
20 million rows and only three index values, we get 20,000,000/3 rows
assumed for each of MALE, FEMALE and UNDECIDED even if there are 19
million FEMALES, 999,999 MALES and 1 UNDECIDED.
By creating a histogram on the column the statistics gathered will let
the CBO know that using an index for UNDECIDED would be a good idea and
possibly also for MALE. It would know not to bother when looking for
FEMALE.
- Your indices are invalid!
Select status from user_indexes where status <> 'VALID';
Don't worry about indexes with a status of 'N/A';
Hope this helps.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk