SQL Tuning02-Intorduction to the CBO Optimizer
0 likes265 views
This document provides an introduction to the Cost-Based Oracle Optimizer (CBO). It describes the main components of the CBO including the estimator, plan generator, and OPTIMIZER_MODE. It also discusses important CBO concepts like selectivity, cardinality, cost and how they are estimated. The document provides examples of how to view CBO statistics and use 10053 tracing to analyze plans.
More Related Content
Similar to SQL Tuning02-Intorduction to the CBO Optimizer (20)
More from Zhaoyang Wang (20)
Recently uploaded (20)
SQL Tuning02-Intorduction to the CBO Optimizer
- 1. Intorduction to the CBO Optimizer 1. 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. Cursor 1. 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. Transformer 1. , ; 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 Optimizer 1. 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. Estimator 1. 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,11g 6. 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_statistics WHERE owner = 'HR' AND table_name = 'EMPLOYEES' AND column_name = 'JOB_ID'; SELECT owner, table_name, num_rows, blocks, avg_row_len FROM dba_tab_statistics WHERE owner = 'HR' AND table_name = 'EMPLOYEES'; -- OPTIMIZER_INDEX_COST_ADJ ; CREATE TABLE t1 AS SELECT MOD(ROWNUM, 200) n1, MOD(ROWNUM, 200) n2 FROM 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) AS SELECT 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 s INNER JOIN v$process p ON s.paddr = p.addr AND s.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1);