Clear cache for query optimizing

[lawlerpat]

IN SQL Server I am aware of two statements that drop the caching of a query plan. I am looking for the equivalent in Oracle to use when optimizing a query. The SQL Server statements are.

dbcc dropcleanbuffers
dbcc freeproccache

 

[Scunningham99]

you can use optimizer settings by either setting the initialization parameter optimizer_mode=<value>, issue at the session level by issuing alter session set optimizer_mode=<value> or use hints in sql statements to overide the default optimizer setting.

Optimizer CBO (Cost Based Optimizer) is based on table statistics gatheres using the DBMS_STATS package. This can be done at database, schema or table/index level.

heres some info:-
OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.

Values:

rule

The optimizer chooses a rule-based approach for all SQL statements regardless of the presence of statistics.

choose

The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available.

If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput. If the data dictionary contains only some statistics, then the cost-based approach is used, and the optimizer must guess the statistics for the subjects without any statistics. This can result in sub-optimal execution plans. If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.

first_rows_n

The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).

first_rows

The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

all_rows

The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

hth

Sy UK

 

[stefanhei]

If you created the statistics using the ANALYZE command (or the dbms_utility.analyze_xxx procedure) you drop statistics with:

analyze table delete statistics

If you gather statistics using the DBMS_STATS package you'll find the procedures to drop in there - and procedures to export/import old ones.

Stefan