分享彼此的优化经验

 最近从朋友那看了一个某咨询公司给一家企业做的一个优化项目的总结报告书，其历时两个月，10万费用，4个人。
最终结果是性能和相应提升了30%，总共修改了3行代码和配置，共修改了3个单词，不到20个字母~~~~。

    朋友总结了一句话，就是“代码质量越烂的项目，优化起来越容易！”。像上面这个项目，外行会认为做优化的人一定是
超一流的高手，有点石成金之能力。其实不然，朋友说，“我们其实只是改了他们系统里一个明显的漏洞，这个漏洞让整个
数据库50%的时间都在空转，仅此而已”。
   
    之后他又给我看了这个项目db的statspack、addm、STA、SAA、以及几个session的trace文件，几个性能试图的定时统计
值，几个java的thread dump文件，jvm的统计信息文件，项目的架构...等等，想考考我还有多大的提升空间。我用一天的时
间仔细看了一下，得出的结论是--如果深度优化，这个项目保守估计还有300%的性能提升空间，也就是说按这个硬件的水平，
客户响应时间可以缩短到三分之一。我把这一结果告诉朋友，他说我还是比较保守的，他们估计这个项目的优化空间可以达到
1000%！

    说了这许多的废话，其实就是给下文做个铺垫，立此贴的目的是想让大家把平时工作中积累的关于代码优化的经验拿出来
分享一下，也希望各位高人能积极响应之，共同完善这个东西。
    
     自己开头，先拿我还算凑合的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 wner=***    Object name=***    Cost=2    Cardinality=1    Bytes=21
  INDEX RANGE SCAN    Object wner=***    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 wner=***    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%多，就是说这些优化动作就是要细心的逐个寻找并解决。逐步累积最后效
果就是显著的了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20577218/viewspace-703825/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20577218/viewspace-703825/