Kreiranje
CBO (Cost Based Optimizer) statistike.
Inicijalni
parametri optimizatora (optimizer)
Od verzije Oracle 8i (CBO) je preporučeni metod optimizacije za Oracle.
Da bi se ovaj metod koristio na odgovarajući način potrebno je krirati statistiku u bazi podataka.
Konande rade za sve verzije baze
ANALYZE TABLE tabela1 COMPUTE STATISTICS;
ANALYZE INDEX index1 COMPUTE STATISTICS;
7.3.4, 8, 9
execute DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE',null,null,'FOR TABLE');
execute DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE',null,null,'FOR ALL INDEXES');
execute DBMS_UTILITY.ANALYZE_SCHEMA ('SCOTT','COMPUTE',null,null,'FOR ALL INDEXED COLUMNS');
10g
DBMS_STATS paket je trenutno preporučeni metod koji može da sakuplja statistiku koristeći
parallel opciju, može da sabira statistiku i za globalne particionisane objekte itd.
execute dbms_stats.gather_schema_stats(ownname=> 'TRP' , cascade=> TRUE);
SQL> show
parameter optimizer
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
optimizer_features_enable
string 9.2.0
optimizer_index_caching
integer 0
optimizer_index_cost_adj
integer 100
optimizer_mode string CHOOSE
SQL> show
parameter optimizer_
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
optimizer_features_enable
string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj
integer 100
optimizer_mode string ALL_ROWS
10R2
Syntaksa OPTIMIZER_MODE =
{ first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows }
Default value all_rows
OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.
Values:
*
first_rows_n
The optimizer uses a cost-based approach 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 and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).