查看sql语句执行计划并重建索引

晚上cpu报警显示当前cpu使用率达到90%以上，不到10%的空闲
先查询当前sql：

#（ORACLE）
SQL >
set line 200 pages 1000
col event for a30
col program for a30
col username for a12
col sql_id for a15
col INST_ID for 9999
col machine for a15
select inst_id,sid,serial#,username,sql_id,event,program,machine,last_call_et,status from gv$session 
where wait_class<>'Idle'
order by last_call_et;

可以看到当前Oracle 等待事件为 resmgr:cpu quantum
Oracle事件resmgr:cpu quantum是由Oracle数据库资源管理器提供的一种事件类型，用于控制数据库会话中的CPU使用情况。它以一个若干微秒的操作数量（quantum）统一计算使用时间。如果在这个时间量内完成操作，则会话可以继续使用，如果超时，则会被暂停以给其他会话分给时间使用。
再通过sql_id查看当前执行的sql语句

#（ORACLE）
SQL >
select sql_text from gv$sql where sql_id='ctyksdujp9njn';

由上图可见当前等待的事件为select的查询语句
该语句为什么会长时间占用cpu造成等待呢？之前测试环境明明就没有出现这种问题！
我们先看一下这个语句的执行计划

#（ORACLE）
explain plan for select ....语句; 
select * from table(dbms_xplan.display); 

通过查看执行计划发现这个sql语句中出现了索引跳跃式扫描
Oracle中的索引跳跃式扫描仅仅适用于那些目标索引前导列的distinct值数量较少、后续非前导列的可选择性又非常好的情形，因为索引跳跃式扫描的执行效率一定会随着目标索引前导列的distinct值数量的递增而递减。
先查看该分区表所创建的索引

#（ORACLE）
SQL >
col index_owner format a18 
select index_owner, 
       index_name,
       column_name,
       column_position 
from dba_ind_columns 
where table_name = upper('&table_name')
order by 1,2,4;

该分区表一共有创建了两个索引，执行计划走的是SYS_C009231这个组合索引，其中包含WRITE DATE，MSG TPC，MSG KEY三个字段
刚刚等待的查询sql里面的条件指向的是MSG_TPC，MSG_TAG，MSG KEY这三个列

查看该表字段选择度

#（ORACLE）
SQL >
select owner,column_name,
       num_distinct,
       histogram,num_distinct,
       num_nulls,
       to_char(last_analyzed,'yyyymmdd hh24:mi') as analyzed 
from dba_tab_col_statistics 
where table_name = upper('&table_name' )

distinct值越接近表的总行数，字段选择度越高，通过这个字段选择度可以看出在执行上面的查询sql时，MSG KEY这个字段的选择度最高
之和和业务说明原因后又新建包含MSG_TPC，MSG_TAG，MSG KEY这三个列的索引
创建索引

#(ORACLE)
SQL >
create index ind_scs_mn_msg_dtls on PUSDEP.scs_mn_msg_dtls(msg_tpc,msg_tag,msg_key) global
online paraller 16

生产创建索引，在业务不繁忙的时间，加上online并使用16个并行，在索引创建完毕后要关闭并行
关闭并行

#(ORACLE)
SQL >
alter index ind_scs_mn_msg_dtls noparallel;