ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一次优化的全过程

一次优化的全过程

原创 Linux操作系统 作者:crpp0902 时间:2019-02-19 09:21:04 0 删除 编辑

最近发现公司的主数据库CPU负载严重,通过TOP工具发现占用CPU严重的SPID,
通过如下语句得到该SPID具体执行的SQL:
select ps.spid,se.sid,se.USERNAME,sql_text from
v$sql sql,
v$session se,
v$process ps
where
se.SQL_ADDRESS=sql.ADDRESS and ps.ADDR=se.PADDR and ps.SPID='&id';

该SQL语句如下:
SELECT snd_phone, title, trade_code, price, rcv_phone,        
         TO_CHAR (send_time, 'yyyy-mm-dd hh24:mi:ss'),        
         TO_CHAR (sended_time, 'yyyy-mm-dd hh24:mi:ss'), state
    FROM tbl_send_histroy                                     
   WHERE snd_phone LIKE '%139XXXXXXXX'                        
     AND send_time BETWEEN TO_DATE ('2004-05-01', 'YYYY-MM-DD')
                       AND TO_DATE ('2005-05-26', 'YYYY-MM-DD')
ORDER BY send_time DESC   


tbl_send_histroy表是一个1000多万条记录的日志表,并且做了分区,并且在send_time+snd_phone上建立了一个复合索引,
跟踪该sql语句的执行计划如下:
SQL> SELECT snd_phone, title, trade_code, price, rcv_phone,
  2           TO_CHAR (send_time, 'yyyy-mm-dd hh24:mi:ss'),
  3           TO_CHAR (sended_time, 'yyyy-mm-dd hh24:mi:ss'), state
  4      FROM tbl_send_histroy
  5     WHERE snd_phone LIKE '%13910613104'
  6       AND send_time BETWEEN TO_DATE ('2004-05-01', 'YYYY-MM-DD')
  7                         AND TO_DATE ('2005-05-26', 'YYYY-MM-DD')
  8  ORDER BY send_time DESC
  9  /

已选择2496行。

已用时间:  00: 00: 09.07

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=994 Bytes=179         
          914)                                                                 
                                                                               
   1    0   TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TBL_SEND_HISTROY'         
           (Cost=3 Card=994 Bytes=179914)                                      
                                                                               
   2    1     INDEX (RANGE SCAN DESCENDING) OF 'IDX_ST_SP' (NON-UNIQUE         
          ) (Cost=2 Card=1789)                                                 
                                                                               


Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                     
      30304  consistent gets                                                   
      12232  physical reads                                                    
          0  redo size                                                         
     174572  bytes sent via SQL*Net to client                                  
       2322  bytes received via SQL*Net from client                            
        168  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
       2496  rows processed                                                    

SQL> /

已选择2496行。

已用时间:  00: 00: 08.00

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=994 Bytes=179         
          914)                                                                 
                                                                               
   1    0   TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TBL_SEND_HISTROY'         
           (Cost=3 Card=994 Bytes=179914)                                      
                                                                               
   2    1     INDEX (RANGE SCAN DESCENDING) OF 'IDX_ST_SP' (NON-UNIQUE         
          ) (Cost=2 Card=1789)                                                 
                                                                               


Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                     
      30304  consistent gets                                                   
       2437  physical reads                                                    
          0  redo size                                                         
     174572  bytes sent via SQL*Net to client                                  
       2322  bytes received via SQL*Net from client                            
        168  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
       2496  rows processed                                                    

通过跟程序员了解,得到信息,对于SND_PHONE字段的值,除了具体的“手机号码”,还有一部分为“前缀+手机号码”,为了得到结果,
程序员写成了snd_phone like '%13XXXXXX'的形式。
通过查询分析该字段的值,确认该字段除了手机号码外,都是86+手机号码的形式,发现通过另一种方法也可以得到正确的值,更改SQL
语句如下:
SELECT   snd_phone, title, trade_code, price, rcv_phone,        
         TO_CHAR (send_time, 'yyyy-mm-dd hh24:mi:ss'),          
         TO_CHAR (sended_time, 'yyyy-mm-dd hh24:mi:ss'), state  
    FROM tbl_send_histroy                                       
   WHERE (snd_phone ='138XXXXX' OR snd_phone ='86138XXXXXXXXX')
     AND send_time BETWEEN TO_DATE ('2004-05-01', 'YYYY-MM-DD') 
                       AND TO_DATE ('2005-05-26', 'YYYY-MM-DD') 
ORDER BY send_time DESC        

并在snd_phone+send_time上建立了一个复合索引,删除开始send_time+snd_phone的复合索引。


跟踪执行计划:
SQL> SELECT   snd_phone, title, trade_code, price, rcv_phone,
  2           TO_CHAR (send_time, 'yyyy-mm-dd hh24:mi:ss'),
  3           TO_CHAR (sended_time, 'yyyy-mm-dd hh24:mi:ss'), state
  4      FROM tbl_send_histroy
  5     WHERE (snd_phone ='13910613104' OR snd_phone ='8613910613104')
  6       AND send_time BETWEEN TO_DATE ('2004-05-01', 'YYYY-MM-DD')
  7                         AND TO_DATE ('2005-05-26', 'YYYY-MM-DD')
  8  ORDER BY send_time DESC 
  9  /

已选择2496行。

已用时间:  00: 00: 08.02

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=199 Bytes=36         
          019)                                                                 
                                                                               
   1    0   SORT (ORDER BY) (Cost=11 Card=199 Bytes=36019)                     
   2    1     INLIST ITERATOR                                                  
   3    2       TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TBL_SEND_HIST         
          ROY' (Cost=3 Card=199 Bytes=36019)                                   
                                                                               
   4    3         INDEX (RANGE SCAN) OF 'IDX_ST_SS' (NON-UNIQUE) (Cost         
          =2 Card=143)                                                         
                                                                               


Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                     
       1767  consistent gets                                                   
         18  physical reads                                                    
          0  redo size                                                         
     174646  bytes sent via SQL*Net to client                                  
       2322  bytes received via SQL*Net from client                            
        168  SQL*Net roundtrips to/from client                                 
          1  sorts (memory)                                                    
          0  sorts (disk)                                                      
       2496  rows processed                                                    

SQL> /

已选择2496行。

已用时间:  00: 00: 02.07

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=199 Bytes=36         
          019)                                                                 
                                                                               
   1    0   SORT (ORDER BY) (Cost=11 Card=199 Bytes=36019)                     
   2    1     INLIST ITERATOR                                                  
   3    2       TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TBL_SEND_HIST         
          ROY' (Cost=3 Card=199 Bytes=36019)                                   
                                                                               
   4    3         INDEX (RANGE SCAN) OF 'IDX_ST_SS' (NON-UNIQUE) (Cost         
          =2 Card=143)                                                         
                                                                               


Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                     
       1767  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
     174646  bytes sent via SQL*Net to client                                  
       2322  bytes received via SQL*Net from client                            
        168  SQL*Net roundtrips to/from client                                 
          1  sorts (memory)                                                    
          0  sorts (disk)                                                      
       2496  rows processed                                                    

SQL> /

已选择2496行。         

可以看到使用了刚建立的索引 IDX_ST_SS,并且consistent gets大大建立,执行时间控制在3秒之内。CPU更是一下降了N多

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

上一篇: 记录
请登录后发表评论 登录
全部评论

注册时间:2002-11-14

  • 博文量
    59
  • 访问量
    44212