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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

Read Full Post »