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);
