一 问题现象
linux 服务器cpu使用率接近100%
使用vmstat 1 10查看id项接近1
top查看有好几个oracle进程排在前面
使用如下语句查看使用cpu高的sql
占用cpu总时间排序
select * from (select s.sid,s.serial#,s.username,s.program,t.sql_id,t.cpu_time/1000000 as sum_cpu_time_seconds,t.executions,t.sql_text
from v$session s,v$sql_text where s.sql_id=t.sql_id and s.status='ACTIVE' order by sum_cpu_time_seconds desc) where rownum<=10
平均cpu执行时间前10条sql
select * from (select s.sid,s.serial#,s.username,s.program,t.sql_id,t.cpu_time/1000000/t.executions as per_cpu_time_seconds,t.executions,t.sql_text
from v$session s,v$sql_text where s.sql_id=t.sql_id and s.status='ACTIVE' order by per_cpu_time_second desc) where rownum<=10
发现很多sid，serial$指向通一个sql，反复执行上面查询，executions增长很快，sum_cpu_time_seconds也增长很快
确认top中的pid对应的(sid,serial#)
select p.spid,s.sid,s.serial#,s.username,s.osuer from v$session s,v$process p
where s.paddr=p.add and p.spid in ('','','') ;
查看sql执行计划
select * from table(dbms_xplan.display_cursor('&SQL_ID',&child_number));
select * from table(dbms_xplan.display_awr('******'));
查看表的统计信息
select t.OWNER, t.TABLE_NAME, t.NUM_ROWS, t.LAST_ANALYZED, t.PARTITIONED
from dba_tables t where t.TABLE_NAME in ('a','b', 'c')
如果上次收集统计信息时间在1周以前，或者num_rows和当前表中记录数差距较大，应对该表新收集统计信息：
索引的统计信息
select owner,table_name,index_name,last_analyzed from dba_indexes where table_name ='&table_name';
表记录数
select count(PK) from TAB;
表的属性/是否为分区表：
收集表的统计信息
查看表上的索引信息
1 确认该表上的索引状态正常：
SELECT OWNER,INDEX_NAME
FROM dba_indexes
WHERE status<>'VALID'
AND partitioned <> 'YES'
UNION ALL
SELECT INDEX_OWNER,INDEX_NAME
FROM dba_ind_partitions
WHERE status<>'USABLE'
UNION ALL
SELECT INDEX_OWNER,INDEX_NAME
FROM dba_ind_subpartitions
WHERE status<>'USABLE'
不应该返回记录，或返回索引和查询表无关
确认表上有合适的索引可以被利用：
sysdba用户执行：
set lines 132
col column_name for a40
col index_name for a30
select index_name,column_name,COLUMN_POSITION , INDEX_OWNER
from dba_ind_columns where table_name ='&table_name'
order by 1,3 asc;
select index_name,index_type,table_name,uniqueness,status,num_rows from dba_indexes where owner='SCOTT' and table_name='EMP1'
根据sql_id查看历史执行计划
SELECT sql_id,plan_hash_value,
SUM(elapsed_time_total)/SUM(executions_total)/1000000 avg_elapsedt_secs
FROM dba_hist_sqlstat
WHERE sql_id = 'brk7h8vn51wm2'
GROUP BY sql_id,plan_hash_value ;
数据分布情况：
set line 120
col OWNER for a10
col COLUMN_NAME for a18
select OWNER,TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,NUM_NULLS from dba_tab_columns where TABLE_NAME='&table_name';
NUM_DISTINCT任意列不同值的数量
dba_tables 的num_rows可以得到某个表的行数
列的可选择性分析：
SELECT COUNT(*) from BRAS.TJ_ZJJY_5;
SELECT COUNT(distinct entry_id) from BRAS.TJ_ZJJY_5;
SELECT COUNT(distinct ishandle) from BRAS.TJ_ZJJY_5;
SELECT COUNT(distinct bankcode) from BRAS.TJ_ZJJY_5;
--索引定义语句
set pagesize 0
set long 90000
set feedback off
set echo off
select dbms_metadata.get_ddl('INDEX','PK_LD_CAR_MONITOR','LOGISTICS') from dual;
索引建立原则：
主要索引：一般索引，唯一性索引，组合索引，
组合索引：应用最广泛，使用时候选择性最高（唯一值）放在最前面
建立索引考虑事项：结果集，数据量很大，但结果集很小
检查where查询中限定性比较强的条件
表连接方式：hash连接（数据量大的表之间）
NL （循环遍历），适合结果集很小，在第一个表中查询的结果少
---在dept1上创建unique索引
create unique index pk_dept1_index on dept1(deptno);
删除索引
drop index EMP_EMPNO_IDX;
---索引创建示例：
在表TJ10，TJ30.T100 组合索引
create index TJ_ZJJY_ZH_INX_1 on TJ10(entry_id, ishandle, bankcode);
create index TJ_ZJJY_ZH_INX_2 on TJ30(entry_id, ishandle, bankcode);
create index TJ_ZJJY_ZH_INX_3 on TJ100(entry_id, ishandle, bankcode);
create index TJ_ZJJY_ZH_INX_4 on TJ1102(entry_id, ishandle, bankcode);
create index ET_BU_IDX on ET_BUSIFORM_TB(NODE_NO,FORM_TYPE,ACTIVE_FLAG,1) invisible;
count(*) 需要计算总行，索引不记录空行，需要一定的技巧
show parameter invisible 确定参数为false
create index ET_BU_IDX on ET_BUSIFORM_TB(NODE_NO,FORM_TYPE,ACTIVE_FLAG,1) invisible;
(可以建立node_no,from_type,active_flag,1)这样的索引
create index on (node_no,from_type,active_flag,1)
确定需要建立索引的列
1 主键约束，除开主键列的其他列的唯一性约束，都会创建一个索引
2 外键需要手工建立索引
3 适合创建索引的列
where后的列，组合索引，select子句中的列，group by，order by union distinct子句中使用的列
注：优化器忽略索引的原因：
使用不等条件 <>
使用通配符 ‘%lapti’，而使用通配符apti%可以走index range scan
空值：唯一索引的所有列都是null值，这些列不会包含在索引中，即使有唯一索引
查询中包含函数