Intorduction to the CBO Optimizer1. CBO :Jonathan Lewis <<Cost-Based Oracle Fundamentals>>;2. :

1. SQL ;2. ;3. ;4. ;

3. :1. DML(Data Manipulation Language):INSERT, UPDATE, DELETE, MERGE, SELECT;2. DDL(Data Definition Language):CREATE, DROP, ALTER, RENAME, TRUNCATE, GRANT, REVOKE,

AUDIT, NOAUDIT, COMMENT;

3. ESS(Environmental Sustainability Statement):DECLARE, CONNECT, OPEN, CLOSE, DESCRIBLE, WHENEVER, PREPARE, EXECUTE, FETCH;

4. TCS(Transactoin Control Statement):COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION;5. SystemCS(System Control Statement):ALTER SYSTEM;6. SessionCS(Session Control Statement):ALTER SESSSION, SET ROLE;

4. SQL : SQL ;

5. Cursor1. PL/SQL cursor: rowid, ;2. sql cursor: SHARED POOL ;

6. SQL :1. Create a cursor:

1. Cursor private SQL area ;2. ;3. Cursor SQL ;

2. Parse the statement:1. SQL Oracle ;2. PGA , , private SQL area, Library Cache

, , sql Shared SQL area , ;3. SQL ;

3. Describe query results:1. SELECT , SQL ;2. 9i DBMS_SQL , EXECUTE IMMEDIATE SQL;

4. Define query output: , , ;5. Bind variables:

1. ;2. sql;

6. Parallelize the statement:1. :SELECT, INSERT, UPDATE, MERGE, DELETE, CREATE, ALTER;2. 11g ;

7. Execute the statement: SQL , ;8. Fetch rows of a query: ;9. Close the cursor: ,PGA cursor , , ;

10. DBMS_SQL SQL ;7. SQL ;

8. : ;9. :

10. Transformer1. , ;2. , SQL ;3. OR UNION ALL ;

4. IN (11g);

5. IN exists;6. NOT IN +IS NULL,11g ,10g ;7. IN +IS NOT NULL;8. : , , ;

9. : , .CBO , ;10. Predicate Pushing: ;

11. :employees department_id ,department department_id ,;

11. Cost-Based Optimizer1. Estimator Plan Generator ;2. Estimator ;

1. , ;2. ;

3. Plan Generator:1. ;2. Estimator ;3. ;4. ;

4. OPTIMIZER_MODE :ALL_ROWS, FIRST_ROWS_n:1. FIRST_ROWS_n:

1. CBO N , ;2. BBS :SELECT /*+ first_rows(10) */ FROM tbname;3. SQL , N ;4. , CBO FIRST_ROWS(n), ALL_ROWS;

2. ALL_ROWS:1. CBO , FIRST_ROWS_n ;2. OLAP , ;

12. Estimator1. Selectivity: ;

1. Selectivity is the estimated proportion of a row set retrieved by a particular predicate or combination of predicates;

;2. :Selectivity= / ;3. 0.0-1.0 :

1. High Selectivity: ;2. Low Selectivity: ;

4. Selectivity:1. (Dynamic Sampling);2. ;

5. :1. dba_tables;2. dba_tab_statistics(NUM_ROWS, BLOCKS, AVG_ROW_LEN);3. dba_tab_col_statistics(NUM_DISTINCT, DENSITY, HIGH/LOW_VALUE);

2. Cardinality: ;1. ;2. :Cardinality=Selectivity* ;3. join, filters sort ;

3. :SELECT * FROM hr.employees WHERE job_id = 'SH_CLERK';1. employees job_id :19;

2. employees :107;

3. Selectivity=1/19=0.0526315789473684, DENSITY ;4. Cardinality=(1/19)*107=5.63, 6;

4. Cost:1. Cost I/Os ;2. Cost :1 cost unit = 1 SRds(Standardized Random Reads);3. Cost(%CPU): IO IO ;4. Cost ;

13. :1. CURSOR_SHARING:SIMILAR|EXACT(default)|FORCE, Cursor SQL ;2. DB_FILE_MULTIBLOCK_READ_COUNT: IO , IO

;( OLTP 4-16, DW );3. PGA_AGGREGATE_TARGET:PGA server processes PGA ;4. STAR_TRANSFORMATION_ENABLED: TRUE CBO ,

;5. RESULT_CACHE_MODE:MANUAL,FORCE,11g6. RESULT_CACHE_MAX_SIZE:11g;7. RESULT_CACHE_MAX_RESULT:11g;8. RESULT_CACHE_REMOTE_EXPIRATION:11g;9. OPTIMIZER_INDEX_CACHING: Buffer Cache , 0;

10. OPTIMIZER_INDEX_COST_ADJ: / , 100%, ;; ;

11. OPTIMIZER_FEATURES_ENABLE: CBO;12. OPTIMIZER_MODE:ALL_ROWS|FIRST_ROWS|FIRST_ROWS_n

1. all_rows: ;2. first_rows_n:n 1|10|100|1000, , ;

13. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES;14. OPTIMIZER_USE_SQL_PLAN_BASELINES;15. OPTIMIZER_DYNAMIC_SAMPLING: ,10g 2;16. OPTIMIZER_USE_INVISIBLE_INDEXES;17. OPTIMIZER_USE_PENDING_STATISTICS;

14. OPTIMIZER_INDEX_COST_ADJ :1. , , ;

2. , , 100%, , ;

3. 50, 1/2, ;

15. Selectivity :1. CBO , Selectivity ;2. 1200 , 1-12, 1-12 ;

3. , , 1/12, 100 ,CBO;

4. , , ;

16. 10053 :1. 10053 , sql ;

2. session_id process_id udump ;

3. udump , ;

4. 10053 ;

-- Estimator ;SELECT * FROM hr.employees WHERE job_id = 'SH_CLERK';SELECT COUNT(DISTINCT job_id) FROM hr.employees;SELECT owner, table_name, column_name, num_distinct, density FROM dba_tab_col_statisticsWHERE owner = 'HR' AND table_name = 'EMPLOYEES' AND column_name = 'JOB_ID';SELECT owner, table_name, num_rows, blocks, avg_row_len FROM dba_tab_statisticsWHERE owner = 'HR' AND table_name = 'EMPLOYEES';

-- OPTIMIZER_INDEX_COST_ADJ ;CREATE TABLE t1 ASSELECT MOD(ROWNUM, 200) n1, MOD(ROWNUM, 200) n2FROM dba_objects WHERE ROWNUM <= 3000;CREATE INDEX t_i1 ON t1(n1);EXEC dbms_stats.gather_table_stats(USER, 't1', CASCADE=>TRUE);SET autotrace traceonly exp;SELECT * FROM t1 WHERE n1 = 50;ALTER SESSION SET optimizer_index_cost_adj = 50;SELECT * FROM t1 WHERE n1 = 50;

-- Selectivity ;CREATE TABLE t2(ID, month_id) ASSELECT ROWNUM, trunc(dbms_random.value(1, 13))FROM dba_objects WHERE ROWNUM <= 1200;EXEC dbms_stats.gather_table_stats(USER, 't2', CASCADE => TRUE);SET autotrace traceonly exp;SELECT * FROM t2 WHERE month_id = 5;SELECT COUNT(*) FROM t2 WHERE month_id = 5;

-- 10053 ;ALTER SYSTEM/SESSION SET EVENTS '10053 trace name context forever, level 8';SELECT * FROM employees WHERE employee_id = 100;ALTER SYSTEM/SESSION SET EVENTS '10053 trace name context off';SELECT s.sid, p.spid FROM v$session sINNER JOIN v$process p ON s.paddr = p.addr AND s.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1);