基于CBO的SQL优化之被动优化SQL语句（4）

比如系统运行缓慢，发现了等待事件的SQL语句，此时就需要分析该SQL语句的语法结构，给出合理的优化建议，一般就是建立或删除索引、建立分区表等操作。

1、使用分区表

分区表是将一个大表按照一定的规则，分解成几个逻辑结构相同的表。在查询时，用户使用相同表的名字，但是Oracle会根据相同的查询条件选择分区表的特定分区部分，因为根据分区规则限制了查询的数据量，这样就减少了查询大表的时间。

适用场景：数据库表中的数据快速上升到上千万条的数据，此时的查询性能就受到很大的影响，即使使用索引也很难满足用户的需求，此时就需要考虑根据时间或范围以及组合条件来创建分区表。

2、使用表和索引压缩

原理就是，将表中重复的数据去掉，采用算法来替换这些重复的值，在需要的时候用算法去重建这些重复的数据，从而实现对表的压缩。

适用场景：需要增强SQL语句的查询性能，可以对表、索引以及物化视图进行压缩，压缩后的表会根据算法重建这些重复的数据，从而实现对表的压缩

1）创建压缩表

SQL> create table compress_emp compress tablespace users as select * from quant.TRADE_CAL;

 查看是否成功建成压缩表compress_emp

SQL> SELECT t.table_name,t.tablespace_name,t.compression FROM user_tables t where t.table_name like '%COMPRESS%';

2) 创建压缩索引：原理同压缩表，主要就是去掉索引中的重复值，尤其对于大表，可以减少存储空间并增强查询性能。

SQL> create index compress_emp_ename_idx  on compress_emp(ID) compress;

 我为TRADE_CAL中的列ID创建了压缩索引

3、保持CBO的稳定性（直接略过）

适用场景：在版本升级或者应用程序升级后的CBO的执行策略会发生变化，使得CBO因为环境的变化而改变计划的执行，所以此时就需要某种方式来确保CBO执行计划的稳定性，这种方式就是存储大纲，它维持CBO的计划执行稳定性，只要是相同的查询SQL语句都使用相同的执行计划。

但我查到数据说在Oracle 19c中，CBO（Cost-Based Optimizer）是默认的优化器。CBO是一种基于成本的优化器，它可以根据查询的成本来选择最佳的执行计划。所以说在Oracle 9之后，Oracle存储大纲已经被SQL计划所替代了。

4、创建合适的索引

对于用户经常使用的查询，尤其是数据仓库系统，都是用户的查询业务，应尽量使用索引，只会增加系统的查询性能，而不用考虑对索引的维护费用。如果发现某个SQL语句总是使用全表扫描实现用户的查询，这时就需要建立索引。

而对于OLTP系统来说，有大量的插入或者删除操作，对于这样的系统要尽量少应用索引（只在必要的列上使用索引，如主键上创建索引），因为索引在大量DML（增删改查）操作时会触发自身的维护，并且需要储存空间，索引本身也要消耗系统资源。

截断表清空数据，在查询时减少数据量的输出

SQL> truncate table plan_table;

登录数据库，不使用SYSTEM用户，使用自己创建的用户

使用EXPLAIN PLAN FOR分析某表是否使用了索引

SQL> SQL> explain plan for select is_open,count(*) from TRADE_CAL group by iS_open;

查询 EXPLAIN结果

SQL> select  id,operation,options,object_name,position  from plan_table;

由于OPTIONS的值为FULL，对于 TS_CODE的查询是使用全表扫描

1）、创建基于列的索引

SQL> create index is_open_index on TRADE_CAL (is_open);

查询索引TS_CODE_index的创建信息

SQL> col index_name for a20
SQL> col index_type for a124
SQL> col table_owner for a20
SQL> col table_name for a20
SQL> select index_name,index_type,table_owner,table_name from user_indexes;

 

索引已经创建完成

截断表plan_table，需在system用户下操作

SQL> truncate table plan_table;

 使用EXPLAIN解析SQL语句

SQL> explain plan for select TS_CODE,count(*) from SYMBOL group by TS_CODE;

查询 EXPLAIN结果

SQL> select  id,operation,options,object_name,position  from plan_table;

 2）、基于函数创建索引

SQL>create index quant_emp_open_index on trade_data(open);

查看是否成功创建了索引
SQL> col table_name for a124
SQL> col table_owner for a124
SQL> col index_name for a20
SQL> select index_name,index_type,table_owner,table_name from user_indexes;

截断表并解析SQL语句

SQL> conn system/1234
SQL> truncate table plan_table;
SQL> conn quant/admin

SQL> explain plan for select ts_code,open from trade_data where open>7;

查询 EXPLAIN结果

SQL> select  id,operation,options,object_name,position  from plan_table;

5、使用V$SQL视图

可以查询消耗磁盘I/O最多的语句，缓冲区读取次数最多的SQL语句等。

1）、查询自实例启动以来磁盘IO最多的sql语句 

SQL>  select sql_text，executions,disk_reads from v$sql   where  disk_reads>&number  order by disk_reads desc;

number：自定义磁盘读取的次数

2）、分析缓冲区读取次数最多的SQL语句

• 查询逻辑读取次数最多的SQL语句

SQL> select sql_text，buffer_gets,parse_calls from v$sql  where buffer_gets>&number order by  buffer_gets;

number：自定义逻辑读取次数

• 查询当前系统耗费CPU资源最多的前24个SQL语句

SQL> select sql_text，cpu_time,buffer_gets from (select sql_text,cpu_time,buffer_gets from v$sql order by cpu_time desc)
where rownum<24;