Sample Table Scans in Oracle

•A sample table scan retrieves a random sample of data from a simple table or a complex SELECT statement, such as a statement involving joins and views.

•This access path is used when a statement’s FROM clause includes the SAMPLE clause or the SAMPLE BLOCK clause.

•To perform a sample table scan when sampling by rows with the SAMPLE clause, Oracle reads a specified percentage of rows in the table.

•To perform a sample table scan when sampling by blocks with the SAMPLE BLOCK clause, Oracle reads a specified percentage of table blocks.

Example:
——————-

SQL> select * from test_skip_scan sample(.2);
20 rows selected.

Execution Plan
———————————————————-
Plan hash value: 571935661

————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————–
| 0 | SELECT STATEMENT | | 20 | 180 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS SAMPLE| TEST_SKIP_SCAN | 20 | 180 | 6 (0)| 00:00:01 |

Expert are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2011/01/04/oversize-of-datatype-varchar2-causes-performance-issue/

https://samadhandba.wordpress.com/2011/02/16/what-and-when-index-scans-is-used/

https://samadhandba.wordpress.com/2011/02/16/index-skip-full-fast-full-index-index-joins-bitmap-indexes-scan/

https://samadhandba.wordpress.com/2011/02/14/sample-table-scans-in-oracle/

https://samadhandba.wordpress.com/2011/02/14/when-you-would-make-index-and-when-not/

https://samadhandba.wordpress.com/2011/02/14/optimize-data-access-path-in-oracle-2/

https://samadhandba.wordpress.com/2011/02/13/troubleshoot-unusable-index-in-oracle/

https://samadhandba.wordpress.com/2011/02/13/the-possible-causes-for-excessive-undo-generation-2/

https://samadhandba.wordpress.com/2011/02/13/three-basic-steps-of-sql-tuning/

https://samadhandba.wordpress.com/2011/02/13/goals-for-tuning-2/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cannot-set-the-on-commit-refresh-attribute-for-the-materialized-view/

Read Full Post »

Troubleshoot unusable Index in Oracle

If a procedural object, such as a stored PL/SQL function or a view, becomes invalid, the DBA does not necessarily have to do anything: the first time it is accessed, Oracle will attempt to recompile it, and this may well succeed.

But if an index becomes unusable for any reason, it must always be repaired explicitly before it can be used.

This is because an index consists of the index key values, sorted into order, each with the relevant
rowid. The rowid is the physical pointer to the location of the row to which the index key refers.

If the rowids of the table are changed, then the index will be marked as unusable. This could occur for a number of reasons. Perhaps the most common is that the table has been moved, with the ALTER TABLE…MOVE command. This will change the physical placement of all the rows, and therefore the index entries will be pointing to the wrong place. Oracle will be aware of this and will therefore not permit use of the index.

Identifying Unusable Indexes
———————————
In earlier releases of the Oracle database, when executing SQL statements, if the session attempted to use an unusable index it would immediately return an error, and the statement would fail. Release 10g of the database changes this behavior. If a statement attempts to use an unusable index, the statement will revert to an execution plan that does not require the index.

Thus, statements will always succeed—but perhaps at the cost of greatly reduced performance. This behavior is controlled by the instance parameter SKIP_UNUSABLE_INDEXES, which defaults to TRUE. The exception to this is if the index is necessary to enforce a constraint: if the index on a primary key column becomes unusable, the table will be locked for DML.

To detect indexes which have become unusable, query the DBA_INDEXES view:
SQL> select owner, index_name from dba_indexes where status=’UNUSABLE’;

Repairing Unusable Indexes
—————————————-

To repair the index, it must be re-created with the ALTER INDEX…REBUILD command.

This will make a pass through the table, generating a new index with correct rowid pointers
for each index key. When the new index is completed, the original unusable index is dropped.

The syntax of the REBUILD command has several options. The more important ones are TABLESPACE, ONLINE, and NOLOGGING. By default, the index will be rebuilt within its current tablespace, but by specifying a table space with the TABLESPACE keyword, it can be moved to a different one.

Also by default, during the course of the rebuild the table will be locked for DML. This can be avoided by using the ONLINE keyword.

1)Create Table and insert row in it:
—————————————-
SQL> create table test ( a number primary key);
Table created.

SQL> insert into test values(1);
1 row created.

SQL> commit;
Commit complete.

2)Check the Index Status
————————–
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
—————————— ——–
SYS_C0044513 VALID

3)Move the Table and Check Status:
————————————
SQL> alter table test move;
Table altered.

SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
—————————— ——–
SYS_C0044513 UNUSABLE

4)Rebuild The Index:
———————–
SQL> alter index SYS_C0044514 rebuild online;
Index altered.

SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
—————————— ——–
SYS_C0044514 VALID

Expert are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2011/01/04/oversize-of-datatype-varchar2-causes-performance-issue/

https://samadhandba.wordpress.com/2011/02/16/what-and-when-index-scans-is-used/

https://samadhandba.wordpress.com/2011/02/16/index-skip-full-fast-full-index-index-joins-bitmap-indexes-scan/

https://samadhandba.wordpress.com/2011/02/14/sample-table-scans-in-oracle/

https://samadhandba.wordpress.com/2011/02/14/when-you-would-make-index-and-when-not/

https://samadhandba.wordpress.com/2011/02/14/optimize-data-access-path-in-oracle-2/

https://samadhandba.wordpress.com/2011/02/13/troubleshoot-unusable-index-in-oracle/

https://samadhandba.wordpress.com/2011/02/13/the-possible-causes-for-excessive-undo-generation-2/

https://samadhandba.wordpress.com/2011/02/13/three-basic-steps-of-sql-tuning/

https://samadhandba.wordpress.com/2011/02/13/goals-for-tuning-2/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cannot-set-the-on-commit-refresh-attribute-for-the-materialized-view/

Read Full Post »

Optimizer Operations while executing SQL statement

Whenever you write an sql query, the query can be executed in many different ways, such as full table scans, index scans, nested loops, and hash joins. The query optimizer checks the various ways and determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query. This determination is an important step in the processing of any SQL statement and can greatly affect execution time.

The output from the optimizer is a plan that describes an optimum method of execution. The Oracle server provides query optimization.

Let’s have a look at the operations that optimizer performs while processing SQL operations.

1)Evaluation of expressions and conditions: The optimizer first evaluates expressions and conditions containing constants as fully as possible.

2)Statement transformation: For complex statements involving, for example, correlated subqueries or views, the optimizer might transform the original statement into an equivalent join statement.

3)Choice of optimizer goals: The optimizer determines the goal of optimization. This topic is discussed later.

4)Choice of access paths: For each table accessed by the statement, the optimizer chooses one or more of the available access paths to obtain table data.

5)Choice of join orders: For a join statement that joins more than two tables, the optimizer chooses which pair of tables is joined first, and then which table is joined to the result, and so on.

Besides optimizer determination, the application designer can use hints in SQL statements to instruct the optimizer about how a statement should be executed.

Note: Before oracle 10g there was available CBO and RBO in case of optimizer optimization.

CBO-Cost Based Optimizer: Execution plan is calculated by taking into account the distribution of data. Starting from 10g optimizer only use CBO while optimization.

RBO-Rule-based optimizer: Chooses an execution plan for SQL statements based on the access paths available and the ranks of these access paths. If there is more than one way, then the RBO uses the operation with the lowest rank. This feature has been desupported.

Read Full Post »

How missing statistics is handled in Oracle

When Oracle encounters a table with missing statistics, Oracle dynamically gathers the necessary statistics needed by the optimizer. Based on the settings of OPTIMIZER_DYNAMIC_SAMPLING parameter which is discussed in my previous topic optimizer gathers statistics and generate execution plan for the table with missing statistics.

Though in case of remote tables and external tables oracle does not perform dynamic sampling.

Whether dynamic sampling enabled or disabled the optimizer uses default values for its statistics.

The values are listed below.

A)Default Table Values When Statistics Are Missing
—————————————————————–
1)Cardinality=num_of_blocks * (block_size – cache_layer) / avg_row_len
2)Average row length=100 bytes
3)Number of blocks=100 or actual value based on the extent map
4)Remote cardinality=2000 rows
5)Remote average row length=100 bytes

B)Default Index Values When Statistics Are Missing
——————————————————————
1)Levels=1
2)Leaf blocks=25
3)Leaf blocks/key=1
4)Data blocks/key=1
5)Distinct keys=100
6)Clustering factor=800

Read Full Post »

How does one prevent Oracle from using an Index?

Autotrace Option:
————————
SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY: Like SET AUTOTRACE ON, but suppresses the printing of the user’s query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.

Now look explain plan of a query which will use index.
SQL> SELECT COUNT(*) FROM TEST WHERE A=1;

Execution Plan
———————————————————-
Plan hash value: 2811351645

———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 3 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX UNIQUE SCAN| SYS_C005994 | 1 | 3 | 0 (0)| 00:00:01 |
———————————————————————————-

Now we can prevent oracle from using index in several methods.

Method 1:Adding an expression to the indexed column:
————————————————————————
SQL> SELECT COUNT(*) FROM TEST WHERE A+0=1;
Execution Plan
———————————————————-
Plan hash value: 160014765

——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FULL SCAN| SYS_C005994 | 1 | 3 | 1 (0)| 00:00:01 |
——————————————————————————–

Method 2:Specifying the FULL hint to force full table scan:
————————————————————————————–
SQL> select /*+ FULL(TEST)*/ * from TEST WHERE A=1;
Execution Plan
———————————————————-
Plan hash value: 1357081020

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 8 | 3 (0)| 00:00:01 |
————————————————————————–

Method 3:Specifying NO_INDEX hint
——————————————————————
SQL> select /*+ NO_INDEX(TEST) */ count(*) from test where A=1;
Execution Plan
———————————————————-
Plan hash value: 1950795681

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| TEST | 1 | 3 | 3 (0)| 00:00:01 |
—————————————————————————

Method 4:Using a function over the indexed column
——————————————————————-
SQL> select count(*) from test where to_number(A)=1;

Execution Plan
———————————————————-
Plan hash value: 160014765

——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FULL SCAN| SYS_C005994 | 1 | 3 | 1 (0)| 00:00:01 |
——————————————————————————–

Read Full Post »

Rebuild unusable Index in Oracle

If a procedural object, such as a stored PL/SQL function or a view, becomes invalid, the DBA does not necessarily have to do anything: the first time it is accessed, Oracle will attempt to recompile it, and this may well succeed.

But if an index becomes unusable for any reason, it must always be repaired explicitly before it can be used.

This is because an index consists of the index key values, sorted into order, each with the relevant
rowid. The rowid is the physical pointer to the location of the row to which the index key refers.

If the rowids of the table are changed, then the index will be marked as unusable. This could occur for a number of reasons. Perhaps the most common is that the table has been moved, with the ALTER TABLE…MOVE command. This will change the physical placement of all the rows, and therefore the index entries will be pointing to the wrong place. Oracle will be aware of this and will therefore not permit use of the index.

Identifying Unusable Indexes
———————————
In earlier releases of the Oracle database, when executing SQL statements, if the session attempted to use an unusable index it would immediately return an error, and the statement would fail. Release 10g of the database changes this behavior. If a statement attempts to use an unusable index, the statement will revert to an execution plan that does not require the index.

Thus, statements will always succeed—but perhaps at the cost of greatly reduced performance. This behavior is controlled by the instance parameter SKIP_UNUSABLE_INDEXES, which defaults to TRUE. The exception to this is if the index is necessary to enforce a constraint: if the index on a primary key column becomes unusable, the table will be locked for DML.

To detect indexes which have become unusable, query the DBA_INDEXES view:
SQL> select owner, index_name from dba_indexes where status=’UNUSABLE’;

Repairing Unusable Indexes
—————————————-

To repair the index, it must be re-created with the ALTER INDEX…REBUILD command.

This will make a pass through the table, generating a new index with correct rowid pointers
for each index key. When the new index is completed, the original unusable index is dropped.

The syntax of the REBUILD command has several options. The more important ones are TABLESPACE, ONLINE, and NOLOGGING. By default, the index will be rebuilt within its current tablespace, but by specifying a table space with the TABLESPACE keyword, it can be moved to a different one.

Also by default, during the course of the rebuild the table will be locked for DML. This can be avoided by using the ONLINE keyword.

1)Create Table and insert row in it:
—————————————-
SQL> create table test ( a number primary key);
Table created.

SQL> insert into test values(1);
1 row created.

SQL> commit;
Commit complete.

2)Check the Index Status
————————–
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
—————————— ——–
SYS_C0044513 VALID

3)Move the Table and Check Status:
————————————
SQL> alter table test move;
Table altered.

SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
—————————— ——–
SYS_C0044513 UNUSABLE

4)Rebuild The Index:
———————–
SQL> alter index SYS_C0044514 rebuild online;
Index altered.

SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
—————————— ——–
SYS_C0044514 VALID

Read Full Post »

Optimize Data Access Path in Oracle

To achieve optimum performance for data-intensive queries, materialized views and indexes are essential when tuning SQL statements. The SQL Access Advisor enables to optimize data access paths of SQL queries by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload.

A materialized view provides access to table data by storing the results of a query in a separate schema object. A materialized view contains the rows resulting from a query against one or more base tables or views.

A materialized view log is a schema object that records changes to a master table’s data, so that a materialized view defined on the master table can be refreshed incrementally.

The SQL Access Advisor also recommends bitmap, function-based, and B-tree indexes. A bitmap index provides a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. A functional index derives the indexed value from the table data. For example, to find character data in mixed cases, a functional index can be used to look for the values as if they were all in uppercase characters.

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

When you would make Index and when not

* Create an index if you frequently want to retrieve less than 15% of the rows in a large table.
* To improve performance on joins of multiple tables, index columns used for joins.

* Small tables do not require indexes.

Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:

* Values are relatively unique in the column.
* There is a wide range of values (good for regular indexes).
* There is a small range of values (good for bitmap indexes).
* The column contains many nulls, but queries often select all rows having a value. In this case, use the following phrase:

WHERE COL_X > -9.99 * power(10,125)

Using the preceding phrase is preferable to:

WHERE COL_X IS NOT NULL

This is because the first uses an index on COL_X (assuming that COL_X is a numeric column).

Columns with the following characteristics are less suitable for indexing:

* There are many nulls in the column and you do not search on the not null values.

The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block.

Other Considerations:

1. The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first.If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries that access just col1, or that access just col1 and col2, are also speeded up. But a query that accessed just col2, just col3, or just col2 and col3 does not use the index.

2. There is a trade-off between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable.

3. Drop Index that are no longer required.

4. Using different tablespaces (on different disks) for a table and its index produces better performance than storing the table and index in the same tablespace. Disk contention is reduced.

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »