oracle数据库优化事例
1、 某项目，在pl/sql中运行top sql脚本：
--使用频率
select sql_text, executions,sysdate
from (select sql_text,
executions,
rank() over(order
by executions desc) exec_rank
from v$sql)
where exec_rank <= 10;
建crontab定时运行上面脚本，求出其当前时间段使用频率较高的sql，找到其第一条（就是执行最频繁的语句），拷贝出来，
粘贴到一个新窗口，代码为：
SELECT grp_addr
FROM Table_1
WHERE ID = :B1
AND grp_ID = (SELECT MAX(grp_ID)
FROM Table_1
WHERE ID = :B1);
按f5，求其执行计划为：
SELECT STATEMENT, GOAL = ALL_ROWS Cost=4 Cardinality=1 Bytes=21
TABLE ACCESS BY INDEX ROWID Object owner=*** Object name=*** Cost=2 Cardinality=1 Bytes=21
INDEX RANGE SCAN Object owner=*** Object name=*** Cost=2 Cardinality=1
可见其cost为4，试着用分析函数改写之：
select first_value(grp_addr) over (partition by ID order by grp_ID)
from Table_1 a
where a.ID = :B1;
再按f5，执行计划为：
SELECT STATEMENT, GOAL = ALL_ROWS Cost=3 Cardinality=1 Bytes=21
WINDOW SORT Cost=3 Cardinality=1 Bytes=21
TABLE ACCESS BY INDEX ROWID Object owner=*** Object name=*** Cost=2 Cardinality=1 Bytes=21
可见cost下降为3，同时执行时间下降了0.011秒，别看不起这一点点的优化，该语句每天要运行次数百万计，聚沙成塔，效果
还是有的。除去执行最频繁的sql，还要优化的就是那些真正性能低下的sql了，这些语句都可以用top sql脚本找到，然后就要
一一对其进行解决。
2、 通过等待事件判断问题，运行一下语句，求系统非空闲等待事件：
select sid,
p1,
p1raw,
p2,
p2raw,
p3,
p3raw,
wait_time,
seconds_in_wait,
state,
event,
sysdate
from v$session_wait
where event not in
('AQ Proxy Cleanup Wait', 'ASM background timer', 'DIAG idle wait',
'EMON idle wait', 'KSV master wait', 'LNS ASYNC archive log',
'LNS ASYNC dest activation', 'LNS ASYNC end of log',
'LogMiner: client waiting for transaction',
'LogMiner: slave waiting for activate message',
'LogMiner: wakeup event for builder',
'LogMiner: wakeup event for preparer',
'LogMiner: wakeup event for reader', 'Null event',
'PX Deq Credit: need buffer', 'PX Deq Credit: send blkd',
'PX Deq: Execute Reply', 'PX Deq: Execution Msg',
'PX Deq: Par Recov Execute', 'PX Deq: Signal ACK',
'PX Deq: Table Q Normal', 'PX Deq: Table Q Sample', 'PX Deque wait',
'PX Idle Wait', 'Queue Monitor Shutdown Wait',
'Queue Monitor Slave Wait', 'Queue Monitor Wait',
'SQL*Net message from client', 'SQL*Net message to client',
'SQL*Net more data from client',
'STREAMS apply coord waiting for slave message',
'STREAMS apply slave idle wait',
'STREAMS apply slave waiting for coord message',
'STREAMS capture process filter callback wait for ruleset',
'STREAMS fetch slave waiting for txns',
'STREAMS waiting for subscribers to catch up',
'Streams AQ: RAC qmn coordinator idle wait',
'Streams AQ: deallocate messages from Streams Pool',
'Streams AQ: delete acknowledged messages',
'Streams AQ: qmn coordinator idle wait',
'Streams AQ: qmn slave idle wait',
'Streams AQ: waiting for messages in the queue',
'Streams AQ: waiting for time management or cleanup tasks',
'Streams fetch slave: waiting for txns', 'class slave wait',
'client message', 'dispatcher timer', 'gcs for action',
'gcs remote message', 'ges remote message', 'i/o slave wait',
'jobq slave wait', 'knlqdeq', 'lock manager wait for remote message',
'master wait', 'null event', 'parallel query dequeue', 'pipe get',
'pmon timer', 'queue messages', 'rdbms ipc message', 'slave wait',
'smon timer', 'virtual circuit status', 'wait for activate message',
'wait for unread message on broadcast channel',
'wakeup event for builder', 'wakeup event for preparer',
'wakeup event for reader', 'wakeup time manager');
发现大量db file sequential read事件，说明sql在硬盘io上有优化的可能，建crontab定时运行上面脚本，找到某连续等待（就是
老有它在那里讨厌着~~~）的记录的sid,运行以下代码求其对应sql：
select sql_text
from v$sqltext_with_newlines st, v$session se
where st.address = se.sql_address
and st.hash_value = se.sql_hash_value
and se.sid = :SID
order by piece;
找到其sql为：
select * from TABLE_2 where acct_nbr = :B;
（这里要说一点，session和wait event都是动态的，而几次对应操作都找到该sid则从另一侧面说明其很不“动态”，赖在那里不走）
语句简单，判断为缺失索引（其实在addm和statspack里可以得同样结论），建立相关索引，速度大幅提高，客户又提出该表有一定量
的ddl会受影响，连续监控并询问开发方该表每天插入数据不足万条，而且不要求实时性，所以建议模仿c语言的copy on write策略，
在业务低谷，删除索引--批量插入--重建索引。
3、通过addm。10g开始可以使用，本来有web oem用就简单多了的，但客户不让起相应监听（emctl start dbconsole），无奈。用命
令行的。
先生成一张db快照，参数'TYPICAL'的意思是以典型采集等级生成快照，还可以用ALL参数，则多了os相关信息：
begin
dbms_workload_repository.create_snapshot('TYPICAL');
end;
/
等待一段时间（大约40分钟，必须大于30分钟，不然报间隔太短），再次运行上面的代码生成第二张快照。
运行：select * from dba_hist_snapshot a order by a.snap_id desc;找到最后的两张快照（就是我自己生成的两张），记录其snap_id
字段的值，运行以下脚本：
DECLARE task_name VARCHAR2(30) := 'turning02';
task_desc VARCHAR2(30) := 'turning02';
task_id NUMBER;
BEGIN
dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
dbms_advisor.set_task_parameter(task_name, 'START_SNAPSHOT', 5209);
dbms_advisor.set_task_parameter(task_name, 'END_SNAPSHOT', 5212);
dbms_advisor.set_task_parameter(task_name, 'INSTANCE', 1);
dbms_advisor.set_task_parameter(task_name, 'DB_ID', ********);
dbms_advisor.execute_task(task_name);
END;
/
其中的*******是你数据库的db_id全球唯一标识，在v$database里记录，然后查看生成的报告：
SELECT dbms_advisor.get_task_report('turning02', 'TEXT', 'ALL') FROM DUAL;
屏幕上会有一大堆的英文，复制之到ue啊什么的，这个报告其实就是这段时间db里的即时状态的分析，有什么不好的sql，少什么索引，
有没有物理热块什么的。我们先搜索‘index’关键字（因为一直是用oem的，按个按钮就ok了，命令行的不知道怎么导成文件~~~惭愧），看
看有没有建议建什么索引，然后往下看看有没有提示一些有问题的sql，总之addm是很方便的，不仅能找到问题，连解决的脚本都会给你写
好，问题sql也会提出修改建议，各位自己复制出来看看执行时间、执行计划什么的就行了。
总结下上面的，首先掌握10/90原则，就是90%的性能问题是10%的原因造成的，而db的性能问题90%是在sql语句上的，所以先从sql下手，上
面3条路其实是“条条大路通罗马”的，找出的问题代码大同小异，大家可以用下面的top sql脚本找出来，或者从操作系统里用top看cup占用
较高的带ora的进程，记录其pid然后：
SELECT p.pid,p.spid,s.sid,p.username,s.TYPE,
s.SERIAL#,s.SCHEMANAME,s.OSUSER,s.MACHINE,
s.PROCESS,p.PROGRAM,s.MODULE,s.STATUS,
s.terminal,logon_time
FROM v$process p, v$session s
WHERE p.addr=s.paddr
and spid ='*****';
看看是哪个session占用cpu多，然后给这个session做个sql trace 定位问题sql，这些都是极其简单的。
sql优化的原则是1、让语句尽量少执行 2、让语句少占用系统资源。第一点需要对业务流程很了解，就好像我一开始提到的那个例子，
系统大量资源是在“空跑”。第二点可以通过sql语句的优化解决。上面写的只是大致的思路，而每个结论都不是一蹴而就的，每个脚本都要定时、
长期执行，并把结果插入一张表（或log），我在脚本里加了sysdate就是要插表，然后按时间来看的。而找到的问题sql也不是一条，要逐个
解决，而几条“路”又是一个互相印证的关系，比如先看等待事件再出addm，给出问题最大的sql是基本相同的，而在第二点里建立了索引后
addm里这个问题语句也就消失了。
db方面的优化，sql优化基本上能解决大部分问题了，db侧动的可以少些（毕竟要顾虑客户方dba的面子啊~~~），主要是一些频繁ddl的表建
索引有困难，就要考虑从逻辑上修改业务，或者是表分区来尽量减少access full的代价。
举个例子，客户经常要查一张大表里某时间段的数据（做环比报告），而该表插入数据频繁，建索引影响明显，而做时间轴的范围分区能
较好的平衡这两方面的问题。在分区时还遇到了一个问题，我想把不同分区放在不同的文件上，而不同的文件再放在不同的io通道上（一组raid
算一条io通道），这样可以尽量分散物理io的压力到不同的io通道及硬盘上，让硬盘做到“有活大家干”，但客户lvm这一层屏蔽了底层的物理实现
最后还是费了些周折找到清楚该系统物理架构的人员解决的问题。
在优化工作中我遇到过一个sql只是通过“谓词后推”语句速度就快了30%多，就是说这些优化动作就是要细心的逐个寻找并解决。逐步累积最后效
果就是显著的了。
