一、背景:对于Oracle数据库来说,性能的问题往往出现在系统部署一段时间后,大量用户开始使用该系统,系统的数据量和各种计算的复杂性增加。为了避免的性能调整,最好在编码阶段就编写高效的SQL语句,称为主动性能调整。而一旦系统部署好,应用系统本身无法改变,我们只能通过对文件存储做合理分布以减少I/O,对表创建合理的索引、建立分区表等,这样的系统性能优化称为被动的性能调整,而这就是作为DBA大多数的工作内容。
CBO优化包括以下3部分:
1、统计信息:CBO使用统计信息确定最优的执行计划;
2、系统资源:考虑系统资源的使用情况,SQL_TRACE主要用于检查数据库的异常情况,通过跟踪数据库的活动,找到有问题的语句;
3、SQL语句的复杂度:EXPLAIN PLAN用于分析SQL语句的执行计划,便于优化SQL语句。
二、实际操作之统计信息
这节我们先来介绍下统计信息:
统计信息是优化器选择最优执行计划的重要依据之一,统计信息包括表的行数、列的数据分布、索引的高度等等。当Oracle执行SQL语句时,优化器会根据统计信息来选择最优的执行计划。如果统计信息不准确,那么优化器选择的执行计划就可能不是最优的。
举个例子,如果一个表有1000万行数据,但是统计信息中显示该表只有1000行数据,那么优化器就会认为全表扫描比使用索引更快。这样就会导致性能问题。
oracle提供了三种级别上的CBO优化:实例级、会话级、语句级,优化方式分别为
1) ALL_ROWS(默认):满足最大吞吐量
2) FIRST_ROWS_n:输出查询的前n行数据,满足最快响应的查询需求
3) FIRST_ROWS:输出查询的前几行数据,满足最快响应时间的查询需求
针对自己的需求,可以修改不同的优化方式
查看当前数据库的CBO优化方式:(system用户)
SQL> show parameter optimizer_mode;
在实例级设置优化方式
SQL> alter system set optimizer_mode=first_rows_10 scope=both;
在会话级设置优化方式
SQL> alter session set optimizer_mode=first_rows;
1、自动统计信息
查看gather_stats_job的当前运行状态
SQL> select job_name,state,owner from dba_scheduler_jobs;
通过数据字典DBA_TABLES查询某一用户下表的统计情况
SQL> select last_analyzed,table_name,owner,num_rows,sample_size from dba_tables where owner='QUANT';
分别表示为统计时间、表名、用户、总行数,统计的行数
2、手工统计信息
使用DBMS_STATS包手工收集统计信息
1)gather_database_statistics为全库的表统计信息
2)gather_schema_statistics为某个模式统计信息(和自动统计信息方式类似)
3)gather_table_statistics为某个特定的表统计信息
4)gather_index_statistics为某个索引表统计信息
1)、为某用户下的所有表统计信息
SQL> execute dbms_stats.gather_schema_stats(ownname=>'QUANT');
验证是否统计成功
SQL>select last_analyzed,table_name,owner,num_rows,sample_size from dba_tables where owner='QUANT';
2)、特定表的统计数据
SQL> execute dbms_stats.gather_table_stats('QUANT','TRADE_CAL');
3)、特定索引的统计数据
查询索引
SQL> select index_name,table_name from dba_indexes where owner='QUANT';
为表TRADE_CAL的索引统计数据
SQL> execute dbms_stats.gather_index_stats('QUANT','SYS_C007469');
4)、收集数据库级别的统计数据
需要对初始化参数job_queue_processes设置一个非0值,才能保证gather_database_statistics正常运行
查询参数job_queue_processes的值
SQL> show parameter job_queue_processes;
说明job_queue_processes值是400,不需要修改
如果值为0,修改方式如下:
SQL> alter system set job_queue_processes=20,scope=both;
收集数据库的统计信息
SQL> begin
2 dbms_stats.gather_database_stats(estimate_percent=>null);
3 end;
4 /
查询某表的统计数据
SQL> select num_rows,avg_space,avg_row_len,num_freelist_blocks,last_analyzed from dba_tab_statistics where table_name='TRADE_CAL';
查询某表每列的统计数据
SQL> select column_name,num_distinct,low_value,high_value,sample_size,avg_col_len from dba_tab_col_statistics where table_name='TRADE_CAL';
分析了不同值的列数量,某列中的最大值和最小值,采样数以及列的长度。
5)、收集操作系统的统计数据
使用gather_system_stats过程来统计操作系统数据
在负载模式下收集10分钟的系统统计数据
SQL> execute dbms_stats.gather_system_stats('NOWORKLOAD',10);
收集系统统计数据(自定义间隔时间)
SQL> execute dbms_stats.gather_system_stats('start');
SQL> execute dbms_stats.gather_system_stats('stop');
上述两个指令间隔3分钟执行,采用sys.aux_stats$查询统计结果信息
SQL> select * from sys.aux_stats$;
显示了:有负载模式下的平均CPU周期数、I/O传销速度等字段,想知道的读者可以具体查一下
6)、收集字典的统计数据
收集动态性能视图字典的统计数据
SQL> execute dbms_stats.gather_fixed_objects_stats;
收集数据字典的统计数据
SQL> execute dbms_stats.gather_dictionary_stats;
也可以使用gather_schema_stats统计数据字典数据
SQL> execute dbms_stats.gather_schema_stats('sys');