Execution plan is nothing but sequence of steps carried out by the optimizer to successfully execute the SQL statements.
There are 2 types of optimizers
1.rule bases optimizer
2.cost based optimizer
Rule based optimizer has become obsolete and it totally depends on predefined rules given by oracle.
From 9i, the default optimizer is cost based. The parameter which determines the optimizer is optimizer_mode = rule/choose/first_rows_n/all_rows
-->first_rows_n and all_rows-->cost based.
-->choose-->cost based or rule based.
first_rows_n means cost based optimizer. Generally, we set this for better response time.
If we are looking for better throughput, set the parameter all_rows.
Choose means oracle considers cost based if statistics are available in the data dictionary otherwise considers rule based.
Cost based optimizer totally depends on the availability of statistics.
Prior to 10g, as a DBA we need to gather optimizer statistics during off peek hours either by using "analyse" command or "dbms_stats" package.
Note : From 10g, by default oracle gathers optimizer statistics during the maintainance window i.e 10pm to 6am during week days and all day during week ends.
Note: oracle by default gathers statistics during mainatinance window provided if more than 10% of records are modified in an object.
Note : Even though oracle is gathering the statistics, depending on the requirement we need to gather the statistics manually.
Recommanded to gather statistics with the help of "dbms_stats" package.
Statistics are nothing but upto date accurate information about the database objects.
From 10g, by default oracle gathers statistics with the help of the sheduled job i.e gather_stats_job.
We can also disable automatic statistics gathering by executing the following package.
SQL>execute dbms_schedular.disable('SYS','GATHER_STATS_JOB');
SQL>select * from emp where job='Y';
SQL>select * from emp where job='N';
Histograms is a nice feature which helps the optimizer in determining an efficient execution plan.
There are 2 types of histograms
1.height balanced histograms
2.frequency histograms
In height balanced histograms column values are divided into buckets where each bucket contains equal number of values.
In frequency histograms each column value corresponds to a single bucket. Each bucket contains the number of occurances of this value.
Database automatically creates frequency histograms if the number of distinct values less than or equal to the number of histogram buckets specified(upto 254).
oracle is gathering statistics on columns with the help of histogrmas.
SQL>exec DBMS.Gather_table_stats(
ownname=>'SCOTT',tabname=>'EMP',method_opt=>For columns size 10 job_status
(or)
'for all columns size repeat');
views :
dba_tab_histograms
user_column_statistics
Note : we can specify repeat if already histograms are available. In this case oracle drops the existing histograms and creates.
