基于成本的优化--CBO

选择CBO的优化方式
		默认条件下，CBO将sql语句的吞吐量作为优化目标
		
		三种不同的优化方式
			ALL_ROWS	:该优化方式是oracle的默认模式，优化目标是实现查询的最大吞吐量

			FIRST_ROWS_n:该优化方式使用CBO的成本优化输出查询的前n行数据，目标是以满足快速相应的查询需求，

			FIRST_ROWS  :该方式是FIRST_ROWS_n优化方式的老版本，作用是使用CBO的成本优化尽快输出查询的前几行数据，满足最小相应时间的需求

	查询当前数据库的CBO优化方式
		show parameter optimizer_mode

	在实例级设置优化方式
		alter system set optimizer_mode = FIRST_ROWS_10 scope=spfile

	在会话级设置优化方式
		alter session set optimizer_mode=ALL_ROWS

	会话级上设置优化方式必须使用hint提示
		select  ename,sal,mgr
		from scott.emp

	优化器工作过程
		步骤
			1.SQL转换
				在CBO优化中，一个SQL语句往往被转换成另一种表达形式，这个转换的基础是CBO认为转换后的查询会更有效

			2.确定访问路径
				一个SQL查询中对数据的访问的路径要根据访问这些数据消耗的资源来判断，在多个查询路径中选择计算成本最小的一个。

			3.确定联结方式
				在SQL语句中涉及多个表时，CBO会根据统计数据以及表的键的信息来选择连接方式，在多个连接方法中选择计算成本最低的一个作为最佳连接方法

			4.确定联结次序
				CBO会对不同的连接次序中进行计算以选择最好的执行计划。

		自动统计数据
			查看GATHER_STATS_JOB状态
				select job_name,state,owner
				from dba_scheduler_jobs;

			通过数据字典DBA_TABLES查询用户SCOTT拥有表的统计分析情况
				select last_analyzed,table_name,owner,num_rows,sample_size
				from dba_tables
				where owner='SCOTT'

		手动统计数据库数据
			DBMS_STATS
				存储过程
					GATHER_DATABASE_STATS	        为全库中的表统计数据
					GATHER_SCHEMA_STATS		为某个模式统计数据
					GATHER_TABLE_STATS		为某个特定的表统计数据
					GATHER_INDEX_STATS		为某个索引表统计数据

				上述统计数据保存在 DBA_TAB_STATISTICS 和 DBA_TAB_COL_STATISTICS 

			为模式SCOTT的所有表统计数据
				execute DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT');

			验证模式SCOTT的数据统计成功
				select last_analyzed,table_name,owner,num_rows,sample_size
				from dba_tables
				where owner='SCOTT'

			为模式SCOTT用户的表EMP统计数据
				execute DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP');
			
			为DEPT的索引统计数据
				execute DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_DEPT')

			手工收集数据库级别的统计数据-----需要对初始化参数JOB_QUEUE_PROCESSES设置一个非0值
				execute DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent=>null)

				查询表的统计数据 		DBA_TAB_STATISTICS
				查询表的列的统计数据	        DBA_TAB_COL_STATISTICS

		统计OS数据
			DBMS_STATS.GATHER_SYSTEM_STATS			SYS.AUX_STAST$

			无负载方式下收集10分钟的系统统计数据	
				execute DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD',10)

			收集系统统计数据
				execute DBMS_STATS.GATHER_SYSTEM_STATS('start')

				execute DBMS_STATS.GATHER_SYSTEM_STATS('stop')
				每三分钟执行一次

			查询统计的系统数据
				select * from SYS.AUX_STAST$;

		手工统计字典数据---具备SYSDBA权限
			收集固定字典表的统计数据
				execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

			收集数据字典表的统计数据
				execute DBMS_STATS.GATHER_DIRECTORY_STATS;
                        /
			使用过程GATHER_SCHEMA_STATS统计数据字典数据
				execute DBMS_STATS.GATHER_SCHEMA_STATS('sys')
				
	主动优化SQL语句
		SQL语句优化工具
			1.使用EXPLAN FOR 指令
				utlxplan.sql
				执行脚本---生成PLAN_TABLE表
					@?/rdbms/admin/utlxplan.sql

				通过EXPLaiN PLAN FOR 指令分析SQL语句的执行计划
					explain plan for select count(*) from scott.emp;

				查看表 PLAN_TABLE 中SQL语句执行计划信息

					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
				设置参数 SQL_TRACE 启动SQL语句追踪
					alter system set SQL_TRACE = TRUE;
					



                                使用AUTOTRACE追踪SQL语句执行计划
					set autotrace traceonly 
					select count(*) from scott.emp

					recursive calls                       递归调用的次数
					db block gets                         读数据块的数量
					consistent gets                       总的逻辑I/O 
					physical reads                        物理I/O 
					redo size                             重做数量
					bytes sent via SQL*Net to client      SQL*Net通信
					bytes received via SQL*Net from client
					SQL*Net roundtrips to/from client     
					sorts (memory)                        内存排序统计
					sorts (disk)                          磁盘排序统计
					rows processed                        被检索的行数
				关闭AUTOTRACE
					set autotrace OFF
					
					
			启动 SQL Trace的前提
			   1.statistics_level: TYPICAL / ALL
			   						BASE 

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

			   3.user_dump_dest:	该参数存储SQL语句的追踪文件。

			   (max_dump_file_size)

			启动SQL Trace追踪
				实例级启动SQL Trace追踪
					alter system set SQL_TRACE=TRUE

				会话级启动SQL Trace追踪
					alter session set SQL_TRACE=TRUE
					/
					begin
					sys.dbms_session.set_sql_trace(TRUE);
					end;
					
			使用 TKPPOF 解释 SQL Trace文件
				
				执行sql查询
				使用TKPPOF工具格式化SQL追踪文件
					TKPPOF  xxxxxxxx.trc xxxx.txt sys=no
					
				格式化参数的含义
				count	:不同执行阶段所读取的数据块数量
				cpu     :不同执行阶段锁消耗的CPU时间，单位是秒
				elapsed :执行用掉的时间
				disk    :物理磁盘数据读操作数目
				query   :一致的缓冲区读取数量
				current :数据库块读取的数量
				call    :该参数说明SQL语句的不同执行阶段 										

消除子查询优化SQL语句
	对查询用户scott的emp表进行嵌套子查询
		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语句的执行
		select * 
		from scott.emp e1
		where e1.sal>
		(select avg(sal)
			from scott.emp e2
			where e2.deptno=e1.deptno)

	跟踪改写的SQL语句
	  使用联机视图改写子查询
		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

	被动优化SQL语句
		使用分区表
		    使用表和索引压缩
		            创建压缩表
					create table compress_emp 
					compress 
					tablespace users 
					as select * from scott.emp

				查询是否成功创建压缩表	compress_emp
					select table_name,tablespace_name,compression
					from user_tables
					where table_name like 'COMPRESS%';

				创建压缩索引
					create index compress_emp_ename_idx
					on compress_emp(ename)
					compress;
					
		保持CBO的稳定性
			1.创建存储大纲的前提
				初始化参数
					QUERY_REWRITE_ENABLED = TRUE
					STAR_TRANSFORMATION_ENABLED = TRUE

				验证系统师傅具备创建存储大纲的前提
					show paameter QUERY_REWRITE_ENABLED;
					show paramter STAR_TRANSFORMATION_ENABLED;
					show parameter optimizer_features_enable;

			2.创建存储大纲
					创建数据库级的存储大纲
						alter system set create_stored_outlines = TRUE

					创建会话级的存储大纲
						alter session set create_stored_outlines = TRUE

						为特定SQL语句创建存储大纲
							create outline emp_outline
							on
							select * 
							from scott.emp
							tablespace oltbs;

						查询EMP_OUTLINE创建信息
							select ol_name,sql_text,creator,timestamp
							from ol$
							where ol_name like 'EMP%'

						查询Oracle自动生产的存储大纲的名字
							set lines 120
							select ol_name,sql_text
							from ol$

			3.删除存储大纲
					删除存储大纲-----sysdba
						drop outline emp_outline
						
			4.启用存储大纲
					修改参数 USE_STORED_OUTLINES 为TRUE
						alter system set USE_STORED_OUTLINES= TRUE