Optimizer | Oracle DBA Tutorial pdf

Optimizer

Earlier versions of Oracle used Rule Base Optimizer (RBO). But latest versions use Cost Based Optimizer (CBO) by default.
Statistics must be gathered to benefit from CBO.
=> Gathering statistics
ALTER INDEX index_name COMPUTE STATISTICS
ALTER TABLE table_name COMPUTE STATISTICS
ALTER TABLE table_name ESTIMATE STATISTICS FOR
TABLES/COLUMNS col1, col2/ALL COLUMNS/ALL INDEXES
EXECUTE DBMS_UTILITY.ANALYZE_SCHEMA('MKM','COMPUTE')
Oracle now recommends that you use DBMS_STATS package instead of Analyze.
EXEC DBMS_STATS.gather_schema_stats (ownname => ‘MKM’, cascade
=>true, estimate_percent => dbms_stats.auto_sample_size)
=> Optimizer modes
In initialization file, you can set optimizer mode as shown below
OPTIMIZER_MODE=CHOOSE (this is default) or FIRST_ROWS or
ALL_ROWS or RULE or FIRST_ROWS_n where n = 1/10/100/1000.
You can also change optimizer mode at session level.
ALTER SYSTEM SET OPTIMIZER_MODE=CHOOSE
To change optimizer mode at statement level, you should use hints using /*+ …*/.
SELECT /*+ RULE */ …
Commonly used hints are – FULL, INDEX, REWRITE, and PARALLEL etc.
If statistics exists for any one table or index involved in SQL statement, CBO is used, otherwise RBO is used.
To reuse a saved execution plan, you can use “plan stability” (in the form of stored outline) feature or “materialized view”.
=> Materialized view
Unlike in normal view, materialized view actually stores the data in tables.
To create materialized view, issue
CREATE MATERIALIZED VIEW schema.mview_name
BUILD IMMEDIATE
REFRESH ON DEMAND (or REFRESH COMPLETE ON COMMIT)
ENABLE QUERY REWRITE
AS
SELECT query;
EXEC DBMS_MVIEW.REFRESH('schema.mview_name','C');
EXEC DBMS_MVIEW.REFRESH_ALL_DEPENDENT('table_name');
EXEC DBMS_MVIEW.REFERSH_ALL_MVIEWS;

 Partitioned tables

=> Range partition
It uses range of column values to determine where the record will be inserted.
CREATE TABLE student (…) PARTITION BY RANGE (graduation_year)
(PARTITION p_2000 VALUES LESS THAN 2000 TABLESPACE student_1,
PARTITION p_2002 VALUES LESS THAN 2002 TABLESPACE student_2,
PARTITION p_2004 VALUES LESS THAN 2004 TABLESPACE student_3,
PARTITION p_error VALUES LESS THAN (MAXVALUE) TABLESPACE student_4);
=> List partition
It is based on set of specified value instead of range of values.
CREATE TABLE student (…) PARTITION BY LIST (degree)
(PARTITION p_engg VALUES (‘BTech’,’MTech’) TABLESPACE stu_engg,
PARTITION p_comm VALUES (‘Mcom’,’Bcom’) TABLESPACE stu_comm,
PARTITION p_arts VALUES (‘MA’,’BA’) TABLESPACE stu_arts);
=> Hash partition
It uses a hashing algorithm to assign records in particular partition. It usually keeps almost equal number of records in each partition.
CREATE TABLE student (…) PARTITION BY HASH (roll_no)
PARTITIONS 3 STORE IN (stu_1, stu_2, stu_3);
=> Composite partition
It uses range partition and inside it uses hash sub-partitions. Data is physically stored in sub-partition level.
CREATE TABLE student (…)
PARTITION BY RANGE (graduation_year)
SUBPARTITION BY HASH (roll_no) SUBPARTITIONS 3
STORE IN (stu_1, stu_2, stu_3)
(PARTITION p_2000 VALUES LESS THAN 2000,
PARTITION p_2002 VALUES LESS THAN 2002,
PARTITION p_2004 VALUES LESS THAN 2004,
PARTITION p_error VALUES LESS THAN (MAXVALUE));
Note: Any bitmap indexes created on partitioned table must be local (to the partition).
=> Cluster
A cluster is a group of one or more tables whose data is stored at same place (physically). This helps faster access of data columns, which are often queried as joins because Oracle server needs to read less number of physical data blocks.
CREATE CLUSTER dept_emp
(dno NUMBER) SIZE 1000 TABLESPACE clst
CREATE INDEX dept_emp_idx
ON CLUSTER dept_emp TABLESPACE idx
CREATE TABLE dept (dno NUMBER, dname VARCHAR2(100))
CLUSTER dept_emp(dno);
CREATE TABLE emp (eno NUMBER, ename VARCHAR2(100), dno
NUMBER) CLUSTER dept_emp(dno);
Oracle 11g allows reference partition and interval partition.
Take an example of Transaction and Transaction Detail tables. These two are linked by foreign key TransId. Now, master table has transaction date but detail table do not. We want both tables be partitioned by transaction date. In 11g, we can specify details tables be partitioned based on foreign key reference TransId.
Interval partition is useful when we do not know how many partitions we need beforehand. In above example, we can specify the table such a way whenever new month begins, a new partition will be created automatically.