The most useful feature of exadata-storage Indexes

The most useful feature of exadata-storage Indexes

Unknown storage indexes are the most useful functions in exadata. They are not traditional indexes stored in databases like Oracle B-tree indexes or Bitmap indexes. In fact, in the traditional sense, it is not even an index. We cannot use storage indexes to locate a series of records with specific values on a given column. Instead, storage indexes are software on the storage server and are designed to eliminate disk I/O. Because the storage index can be used to locate a location that does not have the required record, rather than an existing location, we sometimes call it a "reverse Index ". It stores the minimum and maximum values of fields stored in each disk storage unit (1 MB by default). When smart scanning is used, SQL predicates are passed to the storage server, therefore, the storage software can compare the predicates and store the index metadata (maximum and minimum values) before the real request I/O. Any storage interval that cannot contain matching records will be skipped, in many cases, this will greatly reduce the amount of I/O to be executed. Note that the storage software requires a predicate to compare the maximum and minimum values stored in the storage index. Therefore, this optimization takes effect only during intelligent scanning.

There is no documented Algorithm for maintaining and optimizing storage indexes for storage software (although several implicit parameters can be set on the storage server before cellsrv is started). In fact, there is no way to monitor storage indexes. For example, when the storage index is read or updated, there is no waiting event to record the time consumed. Even if no command can be used to manipulate the storage index, it is very important to know how it works because it is a very powerful feature that can significantly improve performance.

Kevin said: To deepen my mind's image of storing indexes, I usually outline this optimization method to greatly improve the efficiency of searching for a silver needle in the straw, instead of looking for a straw in the straw.

Structure

The storage index contains the minimum and maximum values of up to 8 columns. This structure is maintained for each 1 MB Storage Unit (storage interval). The storage index is saved only in the memory and never written to the disk.

Kevin said: storage indexes are stored in the cellsrv heap, so technically, in some extreme scenarios, they may be stored on disks (SWAP areas )...... So, it cannot be said that it will never ......

Figure 4-1 shows the concept of data contained in the storage index.

 

As shown in 4-1, the maximum value of the cust_age field in the first storage interval of the customer table is 77, which indicates that it may contain records that meet the query conditions (cust_age
> 35), and the maximum value of other storage ranges in the figure is not enough to meet the query conditions, so they are not read from the disk.

In addition to the minimum and maximum values, there is also an identifier indicating whether there are records containing null values in the storage range. It is surprising that there are null values in the index, because in traditional Oracle indexes, null values are not saved, and the ability to track null values such as indexes is stored, in fact, it may be affected by the design method and implementation method. Some systems do not need null values at all. For example, SAP uses a space to replace null, SAP does this to ensure that records can be queried through the B-tree index (such indexes do not save null values. In any case, the storage Index provides a function similar to the bitmap index on the null value, which makes it very efficient to search for null values (assuming that null values only occupy a small part ).

Monitoring storage Index

The function of monitoring and storing indexes is very limited. The optimizer does not know whether to store indexes in an SQL statement, AWR and ash cannot capture any information about whether to store indexes in SQL. Only a statistical value at the database level can be used to track storage indexes, there is also an undisclosed tracking mechanism.

Database statistical value

Only one database statistical value is related to the storage index, that is, "cell physical Io bytes saved by storage Index", which records the I/O accumulative values that are avoided because of the storage index. This statistical value can be found in views such as V $ sesstat and V $ sysstat. Although this statistical value is strange and inaccurate, however, this is the only metric that can be easily obtained to show whether the storage index is used. Unfortunately, because the statistical value is accumulated, it is the same as all other statistical values in V $ sesstat, you must check the value before and after SQL statement execution to determine whether the storage index is used. The following is an example:

Sys @ exdb1> set echo on

Sys @ exdb1> @ Si

Sys @ exdb1> Col name for a70

Sys @ exdb1> Col value for 99999999999999

Sys @ exdb1> select name, Value

2 from V $ mystat S, V $ statname n

3 where N. Statistic # = S. Statistic #

4and name like '% storage % ';

 

 

NAME value

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

Cell physical Io bytes saved by storageindex 0

 

Sys @ exdb1> select AVG (pk_col) fromkso. skew2 where col1 is null;

 

AVG (pk_col)

-----------

32000001

 

Sys @ exdb1> set echo off

Sys @ exdb1> @ Si

 

NAME value

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

Cell physical Io bytes saved by storageindex 3984949248

 

Sys @ exdb1> select AVG (pk_col) fromkso. skew2 where col1 is null;

 

AVG (pk_col)

-----------

32000001

Sys @ exdb1> @ Si

 

NAME value

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

Cell physical Io bytes saved by storageindex 7969898496

As you can see, Si. the SQL script queries the statistical value containing the word "Storage" in the V $ mystat view. The statistical value is 0 unless there are SQL statements in the current session that use the storage index. In our example, the query that uses the storage index reduces disk I/O by about 4 billion bytes, which are the additional I/O that has to be performed without the storage index. Because the V $ mystat view displays the cumulative statistical value of your current session, if you execute this statement again, this value will be doubled after the first execution, of course, disconnecting sessions (for example, exiting SQL * Plus) will clear most of the statistical values in the V $ mystat vieW including this statistical value.

 

 

This article is excerpted from an in-depth understanding of Oracle exadata.

Kerry Osborne (Kyrie Osborne)

Randy Johnson)

Tanel P indder

Huang kaiyao, Zhang leyi, translated by Zhang Rui

Published by Electronic Industry Publishing House

Book details: http://blog.csdn.net/broadview2006/article/details/7844209