基于成本的優(yōu)化--CBO

選擇CBO的優(yōu)化方式
		默認(rèn)條件下，CBO將SQL語句的吞吐量作為優(yōu)化目標(biāo)
		
		三種不同的優(yōu)化方式
			ALL_ROWS	:該優(yōu)化方式是Oracle的默認(rèn)模式，優(yōu)化目標(biāo)是實(shí)現(xiàn)查詢的最大吞吐量

			FIRST_ROWS_n:該優(yōu)化方式使用CBO的成本優(yōu)化輸出查詢的前n行數(shù)據(jù)，目標(biāo)是以滿足快速相應(yīng)的查詢需求，

			FIRST_ROWS  :該方式是FIRST_ROWS_n優(yōu)化方式的老版本，作用是使用CBO的成本優(yōu)化盡快輸出查詢的前幾行數(shù)據(jù)，滿足最小相應(yīng)時(shí)間的需求

	查詢當(dāng)前數(shù)據(jù)庫的CBO優(yōu)化方式
		show parameter optimizer_mode

	在實(shí)例級設(shè)置優(yōu)化方式
		alter system set optimizer_mode = FIRST_ROWS_10 scope=spfile

	在會(huì)話級設(shè)置優(yōu)化方式
		alter session set optimizer_mode=ALL_ROWS

	會(huì)話級上設(shè)置優(yōu)化方式必須使用hint提示
		select /*+first_rows_10*/ ename,sal,mgr
		from scott.emp

	優(yōu)化器工作過程
		步驟
			1.SQL轉(zhuǎn)換
				在CBO優(yōu)化中，一個(gè)SQL語句往往被轉(zhuǎn)換成另一種表達(dá)形式，這個(gè)轉(zhuǎn)換的基礎(chǔ)是CBO認(rèn)為轉(zhuǎn)換后的查詢會(huì)更有效

			2.確定訪問路徑
				一個(gè)SQL查詢中對數(shù)據(jù)的訪問的路徑要根據(jù)訪問這些數(shù)據(jù)消耗的資源來判斷，在多個(gè)查詢路徑中選擇計(jì)算成本最小的一個(gè)。

			3.確定聯(lián)結(jié)方式
				在SQL語句中涉及多個(gè)表時(shí)，CBO會(huì)根據(jù)統(tǒng)計(jì)數(shù)據(jù)以及表的鍵的信息來選擇連接方式，在多個(gè)連接方法中選擇計(jì)算成本最低的一個(gè)作為最佳連接方法

			4.確定聯(lián)結(jié)次序
				CBO會(huì)對不同的連接次序中進(jìn)行計(jì)算以選擇最好的執(zhí)行計(jì)劃。

		自動(dòng)統(tǒng)計(jì)數(shù)據(jù)
			查看GATHER_STATS_JOB狀態(tài)
				select job_name,state,owner
				from dba_scheduler_jobs;

			通過數(shù)據(jù)字典DBA_TABLES查詢用戶SCOTT擁有表的統(tǒng)計(jì)分析情況
				select last_analyzed,table_name,owner,num_rows,sample_size
				from dba_tables
				where owner='SCOTT'

		手動(dòng)統(tǒng)計(jì)數(shù)據(jù)庫數(shù)據(jù)
			DBMS_STATS
				存儲(chǔ)過程
					GATHER_DATABASE_STATS	        為全庫中的表統(tǒng)計(jì)數(shù)據(jù)
					GATHER_SCHEMA_STATS		為某個(gè)模式統(tǒng)計(jì)數(shù)據(jù)
					GATHER_TABLE_STATS		為某個(gè)特定的表統(tǒng)計(jì)數(shù)據(jù)
					GATHER_INDEX_STATS		為某個(gè)索引表統(tǒng)計(jì)數(shù)據(jù)

				上述統(tǒng)計(jì)數(shù)據(jù)保存在 DBA_TAB_STATISTICS 和 DBA_TAB_COL_STATISTICS 

			為模式SCOTT的所有表統(tǒng)計(jì)數(shù)據(jù)
				execute DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT');

			驗(yàn)證模式SCOTT的數(shù)據(jù)統(tǒng)計(jì)成功
				select last_analyzed,table_name,owner,num_rows,sample_size
				from dba_tables
				where owner='SCOTT'

			為模式SCOTT用戶的表EMP統(tǒng)計(jì)數(shù)據(jù)
				execute DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP');
			
			為DEPT的索引統(tǒng)計(jì)數(shù)據(jù)
				execute DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_DEPT')

			手工收集數(shù)據(jù)庫級別的統(tǒng)計(jì)數(shù)據(jù)-----需要對初始化參數(shù)JOB_QUEUE_PROCESSES設(shè)置一個(gè)非0值
				execute DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent=>null)

				查詢表的統(tǒng)計(jì)數(shù)據(jù) 		DBA_TAB_STATISTICS
				查詢表的列的統(tǒng)計(jì)數(shù)據(jù)	        DBA_TAB_COL_STATISTICS

		統(tǒng)計(jì)OS數(shù)據(jù)
			DBMS_STATS.GATHER_SYSTEM_STATS			SYS.AUX_STAST$

			無負(fù)載方式下收集10分鐘的系統(tǒng)統(tǒng)計(jì)數(shù)據(jù)	
				execute DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD',10)

			收集系統(tǒng)統(tǒng)計(jì)數(shù)據(jù)
				execute DBMS_STATS.GATHER_SYSTEM_STATS('start')

				execute DBMS_STATS.GATHER_SYSTEM_STATS('stop')
				每三分鐘執(zhí)行一次

			查詢統(tǒng)計(jì)的系統(tǒng)數(shù)據(jù)
				select * from SYS.AUX_STAST$;

		手工統(tǒng)計(jì)字典數(shù)據(jù)---具備SYSDBA權(quán)限
			收集固定字典表的統(tǒng)計(jì)數(shù)據(jù)
				execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

			收集數(shù)據(jù)字典表的統(tǒng)計(jì)數(shù)據(jù)
				execute DBMS_STATS.GATHER_DIRECTORY_STATS;
                        /
			使用過程GATHER_SCHEMA_STATS統(tǒng)計(jì)數(shù)據(jù)字典數(shù)據(jù)
				execute DBMS_STATS.GATHER_SCHEMA_STATS('sys')
				
	主動(dòng)優(yōu)化SQL語句
		SQL語句優(yōu)化工具
			1.使用EXPLAN FOR 指令
				utlxplan.sql
				執(zhí)行腳本---生成PLAN_TABLE表
					@?/rdbms/admin/utlxplan.sql

				通過EXPLAIN PLAN FOR 指令分析SQL語句的執(zhí)行計(jì)劃
					explain plan for select count(*) from scott.emp;

				查看表 PLAN_TABLE 中SQL語句執(zhí)行計(jì)劃信息

					col if for 999
					col operation for a20
					col options for a20
					col object_name for a20
					select id,operation,options,object_name,position
					from PLAN_TABLE
					OPERATION 	:為TABLEACCESS說明該步驟的行為是訪問表
					OPTIONS 	:為FULL，說明全表掃描訪問表
					OBJECT_NAME :說明行為的對象為表EMP

			使用AUTOTRACE指令------SQL_TRACE=TRUE
				設(shè)置參數(shù) SQL_TRACE 啟動(dòng)SQL語句追蹤
					alter system set SQL_TRACE = TRUE;
					

/*	選項(xiàng)								結(jié)果

SET AUTOTRACE ON 						查詢輸出，解釋計(jì)劃，統(tǒng)計(jì)信息

SET AUTOTRACE OFF 						關(guān)閉 AUTOTRACE

SET AUTOTRACE ON EXPLAIN					查詢輸出，解釋計(jì)劃，沒有統(tǒng)計(jì)信息

SET AUTOTRACE ON EXPLAIN STAT 					查詢輸出，解釋計(jì)劃，統(tǒng)計(jì)信息

SET AUTOTRACE ON STAT 						查詢輸出，解釋計(jì)劃，統(tǒng)計(jì)信息

SET AUTOTRACE TRACE 						解釋計(jì)劃，統(tǒng)計(jì)信息，生成結(jié)果但不顯示

SET AUTOTRACE TRACE EXPLAIN 					只有解釋計(jì)劃，不生成結(jié)果

SET AUTOTRACE TRACE STAT 					只有統(tǒng)計(jì)，生成結(jié)果但不顯示*/

                                使用AUTOTRACE追蹤SQL語句執(zhí)行計(jì)劃
					set autotrace traceonly 
					select count(*) from scott.emp

					recursive calls                       遞歸調(diào)用的次數(shù)
					db block gets                         讀數(shù)據(jù)塊的數(shù)量
					consistent gets                       總的邏輯I/O 
					physical reads                        物理I/O 
					redo size                             重做數(shù)量
					bytes sent via SQL*Net to client      SQL*Net通信
					bytes received via SQL*Net from client
					SQL*Net roundtrips to/from client     
					sorts (memory)                        內(nèi)存排序統(tǒng)計(jì)
					sorts (disk)                          磁盤排序統(tǒng)計(jì)
					rows processed                        被檢索的行數(shù)
				關(guān)閉AUTOTRACE
					set autotrace OFF
					
					
			啟動(dòng) SQL Trace的前提
			   1.statistics_level: TYPICAL / ALL
			   						BASE 

			   2.timed_statistics:	TRUE   -----BASE
			   						False	-----TYPICAL / ALL

			   3.user_dump_dest:	該參數(shù)存儲(chǔ)SQL語句的追蹤文件。

			   (max_dump_file_size)

			啟動(dòng)SQL Trace追蹤
				實(shí)例級啟動(dòng)SQL Trace追蹤
					alter system set SQL_TRACE=TRUE

				會(huì)話級啟動(dòng)SQL Trace追蹤
					alter session set SQL_TRACE=TRUE
					/
					begin
					sys.dbms_session.set_sql_trace(TRUE);
					end;
					
			使用 TKPPOF 解釋 SQL Trace文件
				
				執(zhí)行sql查詢
				使用TKPPOF工具格式化SQL追蹤文件
					TKPPOF  xxxxxxxx.trc xxxx.txt sys=no
					
				格式化參數(shù)的含義
				count	:不同執(zhí)行階段所讀取的數(shù)據(jù)塊數(shù)量
				cpu     :不同執(zhí)行階段鎖消耗的CPU時(shí)間，單位是秒
				elapsed :執(zhí)行用掉的時(shí)間
				disk    :物理磁盤數(shù)據(jù)讀操作數(shù)目
				query   :一致的緩沖區(qū)讀取數(shù)量
				current :數(shù)據(jù)庫塊讀取的數(shù)量
				call    :該參數(shù)說明SQL語句的不同執(zhí)行階段 										

消除子查詢優(yōu)化SQL語句
	對查詢用戶scott的emp表進(jìn)行嵌套子查詢
		select * 
		from scott.emp e1
		where e1.sal>
		(select avg(sal)
			from scott.emp e2
			where e2.deptno=e1.deptno)
	開啟AUTOTRACE功能
		set autotrace traceonly
	跟蹤SQL語句的執(zhí)行
		select * 
		from scott.emp e1
		where e1.sal>
		(select avg(sal)
			from scott.emp e2
			where e2.deptno=e1.deptno)

	跟蹤改寫的SQL語句
	  使用聯(lián)機(jī)視圖改寫子查詢
		select * from scott.emp e1,(select e2.deptno deptno ,avg(e2.sal) avg_sal 
		from scott.emp e2 group by deptno ) dept_avg_sal
		where e1.deptno = dept_avg_sal.deptno
		and e1.sal > dept_avg_sal.avg_sal

	被動(dòng)優(yōu)化SQL語句
		使用分區(qū)表
		    使用表和索引壓縮
		            創(chuàng)建壓縮表
					create table compress_emp 
					compress 
					tablespace users 
					as select * from scott.emp

				查詢是否成功創(chuàng)建壓縮表	compress_emp
					select table_name,tablespace_name,compression
					from user_tables
					where table_name like 'COMPRESS%';

				創(chuàng)建壓縮索引
					create index compress_emp_ename_idx
					on compress_emp(ename)
					compress;
					
		保持CBO的穩(wěn)定性
			1.創(chuàng)建存儲(chǔ)大綱的前提
				初始化參數(shù)
					QUERY_REWRITE_ENABLED = TRUE
					STAR_TRANSFORMATION_ENABLED = TRUE

				驗(yàn)證系統(tǒng)師傅具備創(chuàng)建存儲(chǔ)大綱的前提
					show paameter QUERY_REWRITE_ENABLED;
					show paramter STAR_TRANSFORMATION_ENABLED;
					show parameter optimizer_features_enable;

			2.創(chuàng)建存儲(chǔ)大綱
					創(chuàng)建數(shù)據(jù)庫級的存儲(chǔ)大綱
						alter system set create_stored_outlines = TRUE

					創(chuàng)建會(huì)話級的存儲(chǔ)大綱
						alter session set create_stored_outlines = TRUE

						為特定SQL語句創(chuàng)建存儲(chǔ)大綱
							create outline emp_outline
							on
							select * 
							from scott.emp
							tablespace oltbs;

						查詢EMP_OUTLINE創(chuàng)建信息
							select ol_name,sql_text,creator,timestamp
							from ol$
							where ol_name like 'EMP%'

						查詢Oracle自動(dòng)生產(chǎn)的存儲(chǔ)大綱的名字
							set lines 120
							select ol_name,sql_text
							from ol$

			3.刪除存儲(chǔ)大綱
					刪除存儲(chǔ)大綱-----sysdba
						drop outline emp_outline
						
			4.啟用存儲(chǔ)大綱
					修改參數(shù) USE_STORED_OUTLINES 為TRUE
						alter system set USE_STORED_OUTLINES= TRUE