jump to navigation

Index Monitoring and Index Statistics (The Great Gig In The Sky) September 16, 2008

Posted by Richard Foote in 11g, Concatenated Indexes, Extended Statistics, Index Monitoring, Index statistics, Oracle Cost Based Optimizer, Oracle Indexes.

I write this post whilst listening to Pink Floyd’s masterpiece “The Dark Side Of The Moon” while sadly lamenting the passing away of Richard Wright. RIP and thank you for all the great musical gifts you’ve given me over the years.

In my last post I highlighted an example of where Index Monitoring doesn’t show how indeed Oracle does indeed use an index when checking for the existence of Foreign Key values. Thought I might discuss yet another example of where Oracle does indeed use an index but it’s again not picked up by index monitoring, this time with a slight 11g flavour.

This specific example involves using the statistics associated with the indexes to provide the CBO with useful additional information, although the index itself is not used directly within the execution plan. Dropping the index means losing this information which could possibly result in a different, non optimal execution plans.

Prior to 11g, Oracle can have a hard time of accurately determining the correct selectively where there is a correlation between two (or more) columns in a table. By default, Oracle assumes the selectivity of two distinct columns to be the density of both columns multiplied together. So for example, if one column (say “A”) had 10 distinct values and the other column (say “B” also had 10 distinct values, Oracle assumes the selectivity of both columns combined to be 10 x 10 = 100 distinct values. A predicate such as:

WHERE A = 5 and B = 2

would assume 1% of data would be retrieved if both columns A and B both had 10 distinct possible values.

However, what if there’s a special relationship between the columns and the actual number of distinct combinations was somewhat different ? What if B always equals 2 when A equals 5, what if instead of the theoretical 100 different combinations there were only 10 combinations (or some such) because most of the other possible combination don’t actually exist …

This 9i and 10g demo shows how there is indeed only 10 distinct values for each of two different columns, however there is a direct relationship between these columns such that there is actually only 10 distinct combinations of both these columns (and not the 100 combinations which are possible and which Oracle assumes in it’s selectivity calculations).

Instead of the actual 10,000 rows (or 10% of all data) being selected, Oracle is incorrectly assuming only 1000 rows (or 1%) will be selected. This is a significant error by a order of magnitude which in many cases can result in a less efficient execution plan.

With 11g, Oracle can use the statistics associated with an index to give Oracle some vital extra information. Because if there’s an index based on the two columns, then the number of distinct key values recorded for the index can provide Oracle with a much more accurate estimation of the true selectivity based on the two columns. If a concatenated index based on the two columns only has say 10 distinct values, then Oracle can assume that a specific combination of the two columns is likely to also retrieve 1/10 of all the values and not the 1/100 that are theoretically possible.

This identical 11g demo to the one above shows by having an index on the two columns that have a correlation, Oracle is using the DISTINCT_KEYS statistic for the index to determine the correct selectivity and associated cardinality for the query.

However, the demo clearly shows index monitoring is still not showing the index as being “USED”. If you were to hence drop the index, the CBO loses potentially vital information and the cardinality estimates revert back to being the product of the two column densities as with pre 11g.

By dropping the index which appears to not be used, we can potentially impact other execution plans, even though they don’t directly use the index within the execution plan. The correct cardinality estimates of a table can for example potential drive the order in which the table is subsequently joined or the manner in which it’s joined.

This demo on the possible impact of dropping an “unused” index shows how an execution plan can change to be sub-optimal, even though neither execution actually directly uses the associated index. It’s not a particularly “clever” example, but it does illustrate the potential impact of dropping these so called unused indexes.

Of course, with 11g, we now have the capability of collecting extended statistics. We can potentially determine these same level of statistics by generating statistics on both columns combined. Oracle can determine the actual distinct combinations of columns that are somehow correlated and produce more accurate and detailed statistics with hence make the CBO determine more accurate and reliable cardinality estimates.

This final demo on extended statistics shows how we can recreate the more efficient execution plan and provide the CBO with more detailed extended statistics so that it can accurately determine the correct cardinality estimates without the need to recreate the “unused” index.

Extended statistics can be extremely useful in determining correct cardinality values for column combinations that exist however it still falls somewhat short when it attempts to estimate the expected cardinality for combinations that don’t actually exist, even with histograms.

But that’s a tale for another day.

Now it’s time for “Wish You Were Here” …

Index Monitoring and Foreign Keys – Caution (Kid A) September 12, 2008

Posted by Richard Foote in 11g, Constraints, Foreign Keys, Index Monitoring.

One of the questions asked of indexes is are they actually being used or are they sitting there looking pretty not being used, wasting valuable resources and storage.

One possible method of determining whether an index is being used is to set the MONITORING USAGE attribute of the index. This will place the index in the v$object_usage view and will flag the index as being used when indeed the Cost Based Optimizer decides to “use” the index in an execution plan.

For example:

SQL> create table daddy (id number constraint daddy_pk primary key, name varchar2(20));

Table created.

SQL> insert into daddy values (1, ‘BOWIE’);

1 row created.

SQL> insert into daddy values (2, ‘ZIGGY’);

1 row created.

SQL> insert into daddy values (3, ‘THIN WHITE DUKE’);

1 row created.

SQL> commit;

Commit complete.

SQL> alter index daddy_pk monitoring usage;

Index altered.

If we look at the v$object_usage view, we’ll now see an entry for this index, with the USED flag currently set to NO:

SQL> select index_name, monitoring, used from v$object_usage where index_name = ‘DADDY_PK’;

------------ --- ---

If we however make the CBO “use” this index:

SQL> select * from daddy where id = 1;

        ID NAME
---------- --------------------
         1 BOWIE

|Id| Operation                   | Name    |
| 0| SELECT STATEMENT            |         |
|*2|   INDEX UNIQUE SCAN         | DADDY_PK|

We now notice that the index is now marked as having been used:

SQL> select index_name, monitoring, used from v$object_usage where index_name = ‘DADDY_PK’;

------------ --- ---

Although it doesn’t give any indication on just how often the index is actually used, if an index is still marked as unused for a period of time, it does provide some indication that the index has not been used during that time and is a candidate for being dropped.

Or does it ?

Unfortunately, the are some cases when an index is being used but monitoring the index doesn’t give any such indication. However dropping such indexes can prove disastrous …

For example, let’s create another table that has a Foreign Key which references this DADDY table:

SQL> create table kiddie (id number, name varchar2(20), fk number, constraint kiddie_fk foreign key(fk) references daddy(id));

Table created.

Let’s now populate it with a number of rows:

SQL> insert into kiddie select rownum, ‘MAJOR TOM’, 1 from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

Let’s now create an index on the Foreign Key column and collect some stats:

SQL> create index kiddie_fk_i on kiddie(fk);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’DADDY’, estimate_percent=>null, cascade=>true, method_opt=> ‘FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’KIDDIE’, estimate_percent=>null, cascade=>true, method_opt=> ‘FOR ALL COLUMNS SIZE 1’);

PL/SQL procedure successfully completed.

Let’s now monitor this index on the Foreign Key and see where it may or may not get used:

SQL> alter index kiddie_fk_i monitoring usage;

Index altered.

SQL> select index_name, monitoring, used from v$object_usage where index_name = ‘KIDDIE_FK_I’;

------------ --- ---

OK, let’s now delete a row from the parent table and look at the statistics to see how many consistent reads were performed:

SQL> delete daddy where id = 2;

1 row deleted.

          8 db block gets
         26 consistent gets
          1 physical reads
          1  rows processed

Now, behind the scenes, Oracle indeed used the index on the Foreign Key to determine whether or not the parent row could be safely deleted or whether there were child records that were still referencing the parent row. Note we used just 26 consistent reads.

Let’s see if the monitoring of the index has picked this up …

SQL> select index_name, monitoring, used from v$object_usage where index_name = ‘KIDDIE_FK_I’;

------------ --- ---

Ooops, this is not good. Is this index really not being used, is it really perhaps safe to drop.

Let’s find out:

SQL> drop index kiddie_fk_i;

Index dropped.

Let’s now delete another parent row and see if we notice a difference in performance:

SQL> delete daddy where id = 3;

1 row deleted.

          7  db block gets
       3194  consistent gets
       1599  physical reads
          1  rows processed

Ooops, the consistent gets have jumped up dramatically from 26 to a massive 3194 !! Without the index on the Foreign Key, the only way now for Oracle to check whether it’s OK to delete a parent row is to perform a Full Table Scan on the child table. Not only is this potentially very costly but there could be some nasty locking implications as well.

Index Monitoring only tells you if an index has been used or if it hasn’t been used by the CBO.

It doesn’t however tell you whether an index has actually been used or whether it’s safe to drop the index.

More examples of when it may not be safe to drop a so called unused index to come …

Index Create and Rebuild Locking Improvements in 11g (Ch Ch Ch Changes) February 11, 2008

Posted by Richard Foote in 11g, Index Rebuild, Locking Issues, Oracle General, Oracle Indexes.

Although the CREATE INDEX … ONLINE and ALTER INDEX … REBUILD ONLINE options have been available for a long while, they can still introduce locking issues in highly active databases.

Oracle requires a table lock on the index base table at the start of the CREATE or REBUILD process (to guarantee DD information) and a lock at the end of the process (to merge index changes made during the rebuild into the final index structure).

These locks have two implications. Firstly, if there’s an active transaction on the base table of the index being created or rebuilt at the time one of these locks is required, the indexing process will hang. This will of course impact the time it takes to complete the indexing process. However the second far more serious issue is that any other active transactions on the base table starting after the indexing process hangs will likewise be locked and be prevented from continuing, until the indexing process obtains and releases its locks. In highly concurrent environments with many transactions, this can cause serious disruptions to the response times of these impacted transactions. Of course, depending on the time the initial locking transactions take to commit or rollback, this backlog of locked transactions can be quite significant.

Oracle11g has made some improvements in the locking implications regarding creating or rebuilding indexes online.

During the creation or rebuilding of an index online, Oracle still requires two associated table locks on the base table at the start and end of indexing process. If there’s an active transaction on the base table at the time one of these locks is required, the indexing process will still hang as its done previously until all these prior active transactions have completed. No change so far.

However, if the indexing process has been locked out and subsequent transactions relating to the base table start afterwards, these transactions will no longer in turn be locked out by the indexing table locks and are able to complete successfully. The indexing process no longer impacts other concurrent transactions on the base table, it will be the only process potentially left hanging while waiting to acquire its associated lock resource.

This means it may not be quite so “risky” to urgently introduce that new index or rebuild that troublesome index during core business hours due to the reduced locking implications introduced in 11g.

See this demo for Index Rebuild Locking Issues in 10g and the 11g Improvements.

Does this means we can now simply rebuild all our indexes, whenever ? Ummmm, no 😉

Invisible Indexes December 11, 2007

Posted by Richard Foote in 11g, Index Access Path, Invisible Indexes, Oracle Cost Based Optimizer, Oracle Indexes, Performance Tuning.

New in 11g are “Invisible Indexes”, which are basically indexes that exist and are maintained by Oracle but are “invisible” to the CBO. Specific sessions can be set to see these invisible indexes as necessary.

Potentially useful if one has a problematic (and very large) index causing performance issues that you want to make invisible until the specific issue is addressed without the expensive of having to drop and latter recreate the index. Also useful if you want to introduce a new index but want it to be invisible until it’s been given a workout first in a specific “test” session.

Here’s a bit of a demo: Invisible Indexes