风哥教程

培训 . 交流 . 分享
Make progress together!

转自老师的实际ORACLE优化案例

[复制链接]
内容发布:dafufeng| 发布时间:2015-4-19 21:08:46
1.    概述
从2013年9月份开始跟踪及分析,发现浙江的ITSM数据库在环境、设计及SQL三方面,都存在不少问题。在SQL类优化中,本地化代码编写和设计不良,是比较明显的问题。下面将分成环境、设计、SQL优化三类进行持续分析,并给出相关建议、整改方案、整改进度,从第5点的整改计划中来看,目前来看已经有效缓解高峰期CPU使用用率100%的情况,但IO、内存资源使用率依旧挺高,比较可疑的SQL语句已经罗列出来,将按整改计划表继续改造,应会有所缓解。
随着需求不断增加,特别是复杂逻辑的需求,一旦出现高并发量时,也将可能导致数据库主机无法承载,因此数据库主机硬件扩容也亟待解决。
在跟进过程中出现了5次前台无法访问报错的故障,故障分析报告一并整理其中,详见第8点中的附件《故障分析.zip》,近段时间故障概况汇总如下:

  
故障时间
  
故障原因
是否解决
备注
2013.10.17
  
(两次)
由于节点1系统被重启,业务访问高峰期时,节点2无法承载压力导致,待节点1恢复正常后,故障消失,当业务量上来之后,后台应用由于之前的故障导致session积压,再次产生性能问题。
故障所涉问题SQL都已汇集到本文
2013.10.22
由于SQL性能问题引起,当问题sql达到一定并发量后将引发主机资源耗尽,导致前台无法访问问题。
2013.10.25
同上
2013.11.07
同上
2.    环境类优化
2.1 统计信息收集被关闭
浙江系统统计信息未自动收集,非常奇怪(看了集团、安徽、黑龙江等其他工程点,都是正常有收集,ENABLEED为TRUE)!这个关闭统计信息,将会对系统的性能造成巨大的影响。
2.2BOSSWG_ZJ用户需删除了
浙江V3已经升级1年多了,BOSSWG_ZJBASEDB_ZJ以删除了。
2.3部分大表要考虑建分区和分区清理
AWR报表看出,物理内存是30G                     
SGA10GPGA3G

ADDM报告中看到如下说明:
  
  
     
  
     
  

结合有30G大小物理内存的主机,只跑数据库没有跑其他应用,因此可以考虑将SGA和PGA都增大,减少以SGA增大到15G,PGA增大到5G
2.6 PGA参数改小到3G
10月22日,单个节点session数达到700个,造成了数据库内存消耗严重,PGA参数改小后,能够提升一些数据库session数容量,每个节点能够多容纳大约100个session,达到550个左右,降低一些因连接数超载带来的风险。
  
alter system set pga_aggregate_target=3G scope=both  sid='*'
  

3.    设计类优化
2      
3      
3.1      表的字段设计有问题
     ZJ_KZH_DATEZJ_CRM_S_ORDER_GATHER等本地表,为什么设计了大量的V1V2….,请开发人员和我们探讨一下业务的需求。这类设计对性能有影响。
3.2      函数索引设计有疑问
ITSM_EVL_RESULT_INTF表的如下索引:
  
createindex  IDX_CALL_TIME on ITSM_EVL_RESULT_INTF (TO_CHAR(CAL_TIME,'yyyymmddhh24mi'))
  

ZJ_CRM_S_ORDER_GATHER表的如下索引:
  
create  index GATHER_DATE_FUNC_INDEX on ZJ_CRM_S_ORDER_GATHER  (TO_DATE(GATHER_DATE,'yyyy-mm-dd hh24:mi:ss'))
  
create  index INDEX_V13 on ZJ_CRM_S_ORDER_GATHER (TO_DATE(V13,'yyyy-mm-dd  hh24:mi:ss'))
  
create  index INDEX_V18 on ZJ_CRM_S_ORDER_GATHER (TO_DATE(V18,'yyyy-mm-dd  hh24:mi:ss'))
  
create  index INDEX_V2 on ZJ_CRM_S_ORDER_GATHER (TO_DATE(V2,'yyyy-mm-dd hh24:mi:ss'))
  
create  index INDEX_V3 on ZJ_CRM_S_ORDER_GATHER (TO_DATE(V3,'yyyy-mm-dd hh24:mi:ss'))
  
create  index INDEX_V8 on ZJ_CRM_S_ORDER_GATHER (TO_DATE(V8,'yyyy-mm-dd hh24:mi:ss'))
  
create  index OPERATION_DT on ZJ_CRM_S_ORDER_GATHER (TO_DATE(OPERATION_DT,'yyyy-mm-dd  hh24:mi:ss'))
  
CTNBC_TRAN_DATA_008表的如下索引
  
create  index IDX_CTNBC_TRANS_DATETIME on CTNBC_TRAN_DATA_008  (TO_CHAR(TRANS_DATETIME,'YYYYMMDD'))
  
ACT_RU_EXECUTION的如下索引(开源就算了)
  
create  unique index ACT_UNIQ_RU_BUS_KEY on ACT_RU_EXECUTION (CASE  WHEN BUSINESS_KEY_ IS NULL THEN NULL ELSE  PROC_DEF_ID_ END, CASE  WHEN  BUSINESS_KEY_ IS NULL THEN NULL ELSE BUSINESS_KEY_ END)
  
ACT_UNIQ_HI_BUS_KEY的如下索引
  
create  unique index ACT_UNIQ_HI_BUS_KEY on ACT_HI_PROCINST (CASE  WHEN BUSINESS_KEY_ IS NULL THEN NULL ELSE  PROC_DEF_ID_ END, CASE  WHEN  BUSINESS_KEY_ IS NULL THEN NULL ELSE BUSINESS_KEY_ END)
  
3.3      定时审核任务SQL优化(0542kf3ywcd6a)
这个语句单次执行需要9秒,大概1分钟执行一次,由于对logic_analyze_task进行全表扫描,对I/O影响也很大。
  
SELECT  A.LOGIC_ANALYZE_RULE_ID,
  
       A.GATHER_BATCH_ID,
  
       TO_CHAR(B.SOURCE_BEGIN_DATE,  'YYYYMMDDHH24MISS'),
  
       TO_CHAR(B.SOURCE_END_DATE,  'YYYYMMDDHH24MISS'),
  
       C.ANALYZE_PLSQL,
  
       C.REGION_ID,
  
       C.NE_ID,
  
       NVL(C.KPI_ID, 0),
  
       C.Subject || '--产生数据'
  
  FROM LOGIC_ANALYZE_TASK   A,
  
       TA_GATHER_TASK_BATCH B,
  
       LOGIC_ANALYZE_RULE   C,
  
       LOGIC_DATA_GATHER    D
  
WHERE A.Gather_Batch_ID = B.Gather_Batch_ID
  
   AND A.State = '1'
  
   AND A.LOGIC_ANALYZE_RULE_ID =  C.LOGIC_ANALYZE_RULE_ID
  
   AND C.LOGIC_DATA_GATHER_ID =  D.LOGIC_DATA_GATHER_ID
  
   AND C.State = '0SA'
  
ORDER BY A.State_Date;
  
分析排查:
state是logic_analyze_task表处理的状态位,值为'1'的记录占比极少,在state上建索引SQL执行时间降低到1秒以下,对I/O消耗减小。
  
create index idx_analyze_task_stat on  logic_analyze_task (state) tablespace tbs_Bosswg_index;
  

3.4      URL权限代码改造(重点)

  
SELECT '<privilege_url id="' || A.PRIVILEGE_ID ||  '" name="' || PKP_STRING_UTIL.XMLENCODE(B.PRIVILEGE_NAME) ||  '">' || PKP_PRIVILEGE.GETPRIVILEGERULE(A.PRIVILEGE_URL_ID) ||  '</privilege_url>' FROM PRIVILEGE_URL A, PRIVILEGE B WHERE  A.PRIVILEGE_ID = B.PRIVILEGE_ID AND A.PRIVILEGE_URL = :B1 AND B.STATE = '0SA'  ORDER BY A.PRIVILEGE_URL_ID
  

分析排查:
URL权限验证的SQL是weblogic执行频率最高的SQL,SQL性能以及业务逻辑对数据库性能影响很大。将部分数据缓存到weblogic内存后,能够减少大量数据库查询,降低数据库性能消耗。(修改后28日上午awr报告里没再出现这个SQL。)

4.    SQL类优化
3      
4      
4.1      SQL_ID=bdcfdz26x5hm9(本地化)
语句情况:
该语句仅节点1在7天内就执行255130 次,平均每次2.2 秒,两节点合计7天执行50多万次。

  
select  KEY_WORD
  
  from cust_zj_declaration a
  
where REASON_SUB_TYPE is not null
  
and  IS_RECOMMEND_CLERK = 1
  
   and rownum <= 10
  
order by CLERK_SORT;
  

这个表是重点关注对象,如下:

分析排查:
  
1.        CUST_ZJ_DECLARATION表记录有100多万条。
  
2.        IS_RECOMMEND_CLERK的类型为VARCHAR2类型
  
select  data_type
  
  from user_tab_columns
  
where table_name = 'CUST_ZJ_DECLARATION'
  
   and column_name = 'IS_RECOMMEND_CLERK';
  
DATA_TYPE
  
----------------
  
VARCHAR2
  
3.  IS_RECOMMEND_CLERK列有索引:
  
select  t.table_name,t.index_name, t.column_name,  t.column_position, t.DESCEND
  
from  user_ind_columns t
  
where table_name ='CUST_ZJ_DECLARATION'
  
order  by table_name,index_name, column_position;
  
  
TABLE_NAME                INDEX_NAME                  COLUMN_NAME           COLUMN_POSITION DESCEND
  
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
CUST_ZJ_DECLARATION        IDX_CUST_ZJ_SUB_STAFF_ID       SUBMIT_STAFF_ID                    1 ASC
  
CUST_ZJ_DECLARATION        IDX_DECLARE_CLERK             IS_RECOMMEND_CLERK     1 ASC
  
CUST_ZJ_DECLARATION        IDX_SUBMIT_TIME_DESC          SUBMIT_TIME                        1 ASC
  
CUST_ZJ_DECLARATION        INDEX_CUST_ZJ_DECLARATION   FLOW_ID                      1 ASC
  
CUST_ZJ_DECLARATION        INDEX_SERIAL                   SERIAL                            1 ASC
  
修改建议:
将IS_RECOMMEND_CLERK列的VARCHAR2类型修改为NUMBER型,因为里面只放0和1两个取值,其中=1仅返回10条以内,0几乎返回所有记录。适合用索引!IS_RECOMMEND_CLERK列有索引,只是因为是VARCHAR2类型的,产生了类型转换,用不到索引。
如果实在无法修改类型,则只有将  and IS_RECOMMEND_CLERK = 1改为andIS_RECOMMEND_CLERK = '1'  。
4.2      SQL_ID=45ksbh7qdf71t(本地化)
语句情况:
该语句单节点1小时内就执行174次,平均每次3秒。
-修改建议:在分析SQL_ID=bdcfdz26x5hm9的案例时已经查了CUST_ZJ_DECLARATION列的索引情况,该表在REQUEST_ID列无索引。
在request_id 列增加索引,当前来看,是没索引!
4.3      SQL_ID=bn5w1gv1d8jgk(统一版本)
语句情况:
该语句单节点7天内就执行364次,平均每次507秒。
修改建议:
  
SQL>  select  t.table_name,t.index_name,  t.column_name, t.column_position, t.DESCEND
  
  2     from user_ind_columns t
  
  3     where table_name in ('MAINTANCE_JOB',
  
  4                         'MAINT_JOB_ITEM',
  
  5                         'MAINT_JOB_INSTANCE',
  
  6                          'JOB_ITEM_INSTANCE')  
  
  7    order by table_name,index_name, column_position;
  
  
TABLE_NAME        INDEX_NAME     COLUMN_NAME      COLUMN_POSITION DESCEND
  
--------------------------  ------------------------------ ------------------------- ---------------  ----------------------------------------------
  
JOB_ITEM_INSTANCE  IDX_INST_JOB_ITEM_ID   MAINT_JOB_ITEM_ID          1 ASC
  
JOB_ITEM_INSTANCE   IDX_JOB_INST_ID      MAINT_JOB_INSTANCE_ID    1 ASC
  
JOB_ITEM_INSTANCE  PK_JOB_ITEM_INSTANCE    JOB_ITEM_INSTANCE_ID      1 ASC
  
MAINTANCE_JOB   PK_MAINTANCE_JOB            MAINTANCE_JOB_ID     1 ASC
  
MAINT_JOB_INSTANCE   IDX_PLAN_EXEC_DATE     PLAN_EXEC_DATE           1 ASC
  
MAINT_JOB_INSTANCE  PK_MAINT_JOB_INSTANCE    MAINT_JOB_INSTANCE_ID     1 ASC
  
MAINT_JOB_ITEM       PK_MAINT_JOB_ITEM    MAINT_JOB_ITEM_ID         1 ASC
  
7 rows selected.
  
  
SQL> select  count(0) from MAINTANCE_JOB      ;
  
  COUNT(0)
  
--------------
  
       768
  
SQL> select  count(0) from MAINT_JOB_ITEM     ;
  
  COUNT(0)
  
-------------
  
      1772
  
SQL> select  count(0) from MAINT_JOB_INSTANCE ;
  
  COUNT(0)
  
---------------
  
    215117
  
SQL>  select count(0) from JOB_ITEM_INSTANCE  ;
  
  COUNT(0)
  
----------------
  
    825303
  
1.建议COMPLETE_TIME_LIMIT列有索引
  2.确认COMPLETE_TIME_LIMIT列会有前后空格吗,如果不会,则不必加TRIM,直接写成D.COMPLETE_TIME_LIMIT>= TO_CHAR(SYSDATE, 'hh24:mi:ss')
  3.说明一下为什么COMPLETE_TIME_LIMIT这个时间列用VARCHAR2类型
  4.此处PKP_MAINT_JOB.GETHOLIDAYNUM的包产生很多递归调用,可以考虑改写,改写如下,之前我们系统组已经改写过这个语句了,由黄锏发出,请开发人员看看,是否出了补丁了。
  
with  tmp_maintjob as
  
(select a.job_name,
  
         b.duty_id,
  
         b.execute_staff,
  
         b.item_name,
  
         b.if_mobile,
  
         b.if_voice,
  
         b.if_mail,
  
         b.if_phs,
  
         d.job_item_instance_id,
  
         a.weekend_opr,
  
         a.holiday_opr,
  
         b.complete_day_limit,
  
         d.complete_time_limit,
  
         c.plan_exec_date
  
    from maintance_job      a,
  
         maint_job_item     b,
  
         maint_job_instance c,
  
         job_item_instance  d
  
   where b.maint_job_item_id =  d.maint_job_item_id
  
     and c.maint_job_instance_id =  d.maint_job_instance_id
  
     and a.maintance_job_id =  b.maintance_job_id
  
     and a.state = '0SA'
  
     and a.ENABLED_DATE <= sysdate
  
     and nvl(a.IF_DEL, '0BF') = '0BF'
  
     and b.state = '0SA'
  
     and nvl(b.IF_DEL, '0BF') = '0BF'
  
     and nvl(b.execute_staff, b.duty_id) is not  null
  
     and b.if_hasten = '0BT'
  
     and c.JOB_INSTANCE_STATE != '2'
  
     and c.JOB_INSTANCE_STATE != '3'
  
     and d.JOB_ITEM_STS != '2'
  
     and d.COMPLETE_TIME_LIMIT is not NULL
  
     and trim(d.COMPLETE_TIME_LIMIT) >=  to_char(sysdate, 'hh24:mi:ss')
  
     and nvl(d.main_next_notify_time,
  
             to_date(to_char(sysdate,  'yyyy-mm-dd') || d.complete_time_limit,
  
                     'yyyy-mm-dd hh24:mi:ss')  + b.hasten_time / (24 * 60)) <=
  
         sysdate)
  
select  s.job_name,
  
       s.duty_id,
  
       s.execute_staff,
  
       s.item_name,
  
       s.if_mobile,
  
       s.if_voice,
  
       s.if_mail,
  
       s.if_phs,
  
       s.job_item_instance_id,
  
       s.weekend_opr,
  
       s.holiday_opr,
  
       s.complete_day_limit,
  
       s.complete_time_limit,
  
       s.plan_exec_date,
  
       sum(delay_count) delay_count
  
  from (select s.job_name,
  
               s.duty_id,
  
               s.execute_staff,
  
               s.item_name,
  
               s.if_mobile,
  
               s.if_voice,
  
               s.if_mail,
  
               s.if_phs,
  
               s.job_item_instance_id,
  
               s.weekend_opr,
  
               s.holiday_opr,
  
               s.complete_day_limit,
  
               s.complete_time_limit,
  
               s.plan_exec_date,
  
               count(t.holiday_day)  delay_count
  
          from tmp_maintjob s, holiday_cfg t
  
         where s.holiday_opr = 'DELAY_EXEC'
  
           and t.holiday_day >=  s.plan_exec_date
  
           and t.holiday_day < sysdate
  
           and t.holiday_day > sysdate -  730
  
         group by s.job_name,
  
                  s.duty_id,
  
                  s.execute_staff,
  
                  s.item_name,
  
                  s.if_mobile,
  
                  s.if_voice,
  
                  s.if_mail,
  
                  s.if_phs,
  
                  s.job_item_instance_id,
  
                  s.weekend_opr,
  
                  s.holiday_opr,
  
                  s.complete_day_limit,
  
                  s.complete_time_limit,
  
                  s.plan_exec_date
  
        union all
  
        select s.job_name,
  
               s.duty_id,
  
               s.execute_staff,
  
               s.item_name,
  
               s.if_mobile,
  
               s.if_voice,
  
               s.if_mail,
  
               s.if_phs,
  
               s.job_item_instance_id,
  
               s.weekend_opr,
  
               s.holiday_opr,
  
               s.complete_day_limit,
  
               s.complete_time_limit,
  
               s.plan_exec_date,
  
               count(t.weekend) delay_cnt
  
          from tmp_maintjob s,
  
               (select thedate as weekend
  
                  from (select sysdate - 730  + (level - 1) thedate
  
                          from dual
  
                        connect by level  <= sysdate - trunc(sysdate - 730))
  
                 where to_char(thedate, 'd')  = '7'
  
                    or to_char(thedate, 'd') =  '1') t
  
         where s.weekend_opr = 'DELAY_EXEC'
  
           and t.weekend >=  s.plan_exec_date
  
         group by s.job_name,
  
                  s.duty_id,
  
                  s.execute_staff,
  
                  s.item_name,
  
                  s.if_mobile,
  
                  s.if_voice,
  
                  s.if_mail,
  
                  s.if_phs,
  
                  s.job_item_instance_id,
  
                  s.weekend_opr,
  
                  s.holiday_opr,
  
                  s.complete_day_limit,
  
                  s.complete_time_limit,
  
                  s.plan_exec_date
  
        union all
  
        select s.job_name,
  
               s.duty_id,
  
               s.execute_staff,
  
               s.item_name,
  
               s.if_mobile,
  
               s.if_voice,
  
               s.if_mail,
  
               s.if_phs,
  
               s.job_item_instance_id,
  
               s.weekend_opr,
  
               s.holiday_opr,
  
               s.complete_day_limit,
  
               s.complete_time_limit,
  
               s.plan_exec_date,
  
               0 delay_count
  
          from tmp_maintjob s
  
         where weekend_opr != 'DELAY_EXEC'
  
           and holiday_opr != 'DELAY_EXEC') s
  
group by s.job_name,
  
          s.duty_id,
  
          s.execute_staff,
  
          s.item_name,
  
          s.if_mobile,
  
          s.if_voice,
  
          s.if_mail,
  
          s.if_phs,
  
          s.job_item_instance_id,
  
          s.weekend_opr,
  
          s.holiday_opr,
  
          s.complete_day_limit,
  
          s.complete_time_limit,
  
          s.plan_exec_date
  
having  trunc (trunc(plan_exec_date) + nvl(complete_day_limit, 0) + sum(delay_count))  = trunc (sysdate)
  

4.4      SQL_ID=cwzwax00wmm7q(本地化)
语句情况:
该语句单节点7天内就执行89次,平均每次995秒。
  
INSERT  INTO  BOSSWG.HIGH_USAGE_ALARM_MR_RESPONSE
  
   ( ALARM_ID ,
  
     EXTERNAL_ID ,
  
     PRODUCT_INSTANCE_CODE ,
  
     EXTERNAL_ID_TYPE ,
  
     CUSTOMER_NAME ,
  
     STATUS ,
  
     STATUS_DATE )
  
   SELECT   A2.ALARM_ID ,
  
           A2.EXTERNAL_ID ,
  
           A2.PRODUCT_INSTANCE_CODE ,
  
           A2.EXTERNAL_ID_TYPE ,
  
           A2.CUSTOMER_NAME ,
  
           1,
  
           SYSDATE                    
  
     FROM HIGH_USAGE_ALARM_MR_READ  A2
  
    WHERE A2.CITY_CODE   = :B1
  
      AND   A2.ALARM_ID  <> ALL
  
    (SELECT   A3.ALARM_ID  FROM  BOSSWG.HIGH_USAGE_ALARM_MR_RESPONSE A3 );
  
分析排查:
1.      这个语句应该是MERGE的需求,可以用MERGE来改写
2.      CITI_CODE的取值只有11个,表示11个地市。
修改建议:
1.      建议HIGH_USAGE_ALARM_MR_READ表的CITI_CODE建分区
2.      SQL建议改写为如下:
  
MERGE INTO  BOSSWG.HIGH_USAGE_ALARM_MR_RESPONSE A1
  
      USING (select * from BOSSWG.HIGH_USAGE_ALARM_MR_READ where  CITY_CODE=:B1) A2
  
     ON (A1.ALARM_ID=A2.ALARM_ID)
  
      WHEN NOT MATCHED THEN
  
      INSERT   (ALARM_ID,EXTERNAL_ID,PRODUCT_INSTANCE_CODE ,EXTERNAL_ID_TYPE  ,CUSTOMER_NAME ,STATUS ,STATUS_DATE)
  
      VALUES  (A2.ALARM_ID  ,A2.EXTERNAL_ID ,A2.PRODUCT_INSTANCE_CODE , A2.EXTERNAL_ID_TYPE  ,A2.CUSTOMER_NAME ,1 ,sysdate );
  

4.5      SQL_ID=ctmv0k8sgf907(外部DBLINK
语句情况:
该语句单节点1小时内就执行12次,平均每次216秒。
  
select  count(*), 10055
  
    from pay.oocp_prepay_log@xzabm
  
   where action = 5
  
     and state = 1
  
and  state_time < sysdate - 1 / 1440
  
      and state_time > sysdate - 5 / 1440
  
这个时段的网络相关等待事件相当严重,如下:

分析排查:
  
1.        该表记录有46694050条,而where state_time < sysdate -  1 / 1440 and state_time > sysdate - 5 / 1440才548条。
  
2.查询一下在这个state_time列有无索引
  
select  t.table_owner,t.index_name, t.column_name,  t.column_position
  
  from dba_ind_columns@xzabm  t
  
  where table_name ='OOCP_PREPAY_LOG'  
  
order by table_name,index_name,  column_position;   
  
  
TABLE_OWNER              INDEX_NAME           COLUMN_NAME    COLUMN_POSITION        
  
-------------------------------------------------------------------------------------------------------------------------------------------
  
PAY           IDX_OOCP_PREPAYLOG_PISID PRODUCT_OFFER_INSTANCE_ID     1     
  
PAY           IDX_OOCP_PREPAY_LOG_ACCCONBRACCT_CODE                 1   
  
PAY           IDX_OOCP_PREPAY_LOG_ACCCONBR     ACC_NBR       2      
  
PAY           IDX_OOCP_PREPAY_LOG_ACCNBR  ACC_NBR         1      
  
PAY          IDX_OOCP_PREPAY_LOG_SERIALNO        SERIAL_NO       1     
  
PAY           IDX_OOCP_PREPAY_LOG_STATE            STATE      1      
  
PAY           IDX_OOCP_PREPAY_PAYID         PAYMENT_ID        1      
  
修改建议:
state_time列建索引,不过这需要和对方沟通,这是对方的表。不过这个语句是通过DBLINK连接的,实际上也会影响对方系统的性能,所以建索引其实对对方也有利!
4.6      SQL_ID=9s06gsx7snk73(本地化)
  
SELECT  COUNT(1)                                                           
  
  FROM (SELECT (CASE                                                      
  
                 WHEN V10 = '97成功' AND V11 IS NULL THEN               
  
                  SYSDATE - TO_DATE(T.V8,  'yyyy-mm-dd hh24:mi:ss')      
  
                 ELSE                                                     
  
                  NVL(TO_DATE(T.V13,  'yyyy-mm-dd hh24:mi:ss'), SYSDATE) -
  
                  NVL(TO_DATE(T.V12,  'yyyy-mm-dd hh24:mi:ss'), SYSDATE)  
  
               END) * 24 * 3600  V_RN_LEN,                                 
  
               T.*                                                        
  
          FROM ZJ_KZH_DATE T                                             
  
         WHERE TO_DATE(T.V12, 'yyyy-mm-dd  hh24:mi:ss') >= :B2            
  
           AND TO_DATE(T.V12, 'yyyy-mm-dd  hh24:mi:ss') < :B1)  
  
ZJ_KZH_DATE T表和CUST_ZJ_DECLARATION一样,也是重点关注对象:

分析排查:
  
1.       ZJ_KZH_DATE表记录有500多万,从条件来看,有时间字段,理应不用全表扫描,这里却用了全表扫描,不过这个语句的TO_DATE写法本来就用不到索引,此外这些V12的列,本身也没有索引,如下:
  
  
select  t.table_name,t.index_name, t.column_name,  t.column_position                                    
  
    from user_ind_columns t                                    
  
    where table_name in='ZJ_KZH_DATE'                                    
  
  order by table_name,index_name,  column_position;                                      
  
  
TABLE_NAME         INDEX_NAME                     COLUMN_NAME    COLUMN_POSITION                                    
  
--------------------------------------------------------------------------------------------------------------------------------------------
  
ZJ_KZH_DATE                IDX_ZJ_KZH_DATE_G              GATHER_DATE               1                                   
  
ZJ_KZH_DATE                IDX_ZJ_KZH_DATE_O              OU_NUM                  1                                    
  
ZJ_KZH_DATE                P_BUSINESS_ID                  BUSINESS_ID                1
  
  
2.       类似这样的语句太多了!
  
SQL_ID=f28942wm5y422   
  
SQL_ID=f83ujj08gw7db
  
SQL_ID=bxf706za5rsj1
  
SQL_ID=22crkv1dz69ny
  
SQL_ID=du3k7qurjfp3x
  
SQL_ID=52c3f75j9cc40
  
SQL_ID=dysqyswvzcusg
  
SQL_ID=apcc9ya8gmqtn
  
  
修改建议:
1.考虑一下,代码是否有加上GATHER_DATE或OU_NUM或BUSINESS_ID的机会。
  2.关于字段V1V2...的设计思路有点不太好,能否再多探讨一下。
4.7      SQL_ID=a5wr8yd24r4wq(本地化)
  
SELECT  COUNT(1)
  
  FROM (SELECT ITEM_ROW_ID
  
          FROM (SELECT T.ITEM_ROW_ID
  
                  FROM ZJ_CRM_S_ORDER_GATHER  T
  
                 WHERE T.V2 IS NOT NULL
  
                   AND T.V8 IS NOT NULL
  
                   AND T.V13 IS NOT NULL
  
                   AND T.V18 IS NOT NULL
  
                ORDER BY T.OPERATION_DT DESC)
  
         WHERE ROWNUM < 2)
  
修改建议:
1.       ZJ_CRM_S_ORDER_GATHER对象和ZJ_KZH_DATE对象类似,关于字段V1,V2...的设计思路有点不太好,能否再多探讨一下。
2.       ZJ_CRM_S_ORDER_GATHER记录有2千多万条,是否有清理机制。
4.8      调和语句大量DELETE可避免
将delete 修正为commit即可,全局临时表中commit就是清除数据,这样可以避免大量无谓的多余动作,也可少产生大量redo。
4.9      SQL_ID=a5wr8yd24r4wq(本地化)
  
insert into INP_DATA_PERF(FILE_ID, NE_ID, NE_TYPE_ID, AUDIT_ID, AREA_CODE, SYSLOC, MSG_SOURCE, KPI_ID, KPI_NAME, KBP, KBP_NAME, MIN, AVG, MAX, MSG_TIME) values (:1, pkp_inp_convert.convert_inp_data_perf(:2, :3), :4, :5, :6, :7, 1, replace(:8, '-', ''), :9, :10, :11, :12, :13, :14, to_date(:15, 'YYYYMMDDHH24MI');
  
分析排查:
其中这个函数如下
  
function convert_inp_data_perf(p_ne_id number,p_areacode varchar2) return number is
      v_ne_id          CI_BASE_ELEMENT.INSTANCE_ID%type;
    begin
      begin
        /*select distinct a.ne_id into v_ne_id
         from net_element a
        where a.ne_type_id = p_ne_id
        and a.region_id = to_number(p_areacode)
        and a.state = '0SA';*/
        SELECT distinct A.INSTANCE_ID INTO v_ne_id FROM CI_BASE_ELEMENT A
         WHERE A.CLASS_ID=p_ne_id
            AND A.REGION_ID = to_number(p_areacode)
            AND A.MARKASDELETED=0
            AND A.DATASET_ID=6;
      exception when others then
        v_ne_id := -1;
      end ;
      return v_ne_id;
    end convert_inp_data_perf;
  
修改建议:
取同一个文件的时候,其实返回的记录pkp_inp_convert.convert_inp_data_perf(:2, :3)都是相同的,所以这里被调用N此是没必要的,只要被调用1次即可。
4.10   SQL_ID=9ghzcqq3y4x49(统一版本)
  
select *
  
  from (select 'event' task_or_event,
  
               a.event_id id,
  
               a.staff_id,
  
               b.src_staff,
  
               nvl(e.staff_name, '...') as  src_staff_name,
  
               b.exec_staff,
  
               nvl(c.staff_name, '...') as  exec_staff_name,
  
               b.event_type type,
  
               DECODE(event_type,
  
                      '2',
  
                      PKP_FLOW_FUNCTION.get_flow_event_text(b.CONTENT_ID),
  
                       f_get_all_state('EVENT_Q', 'EVENT_TYPE', b.event_type)) as type_name,
  
               b.content,
  
               b.grade,
  
               d.grade_name,
  
               b.state,
  
               f_get_all_state('EVENT_Q',  'STATE', b.state) as state_name,
  
               b.content_id,
  
               '' room_name,
  
               '...' dev_name,
  
               to_char(b.state_date,  'yy/mm/dd hh24:mi:ss') as state_date,
  
               IF_ORANGE_ALERT(b.event_id) alert_flag,
  
               DECODE(event_type,
  
                      '3',
  
                       PKP_FLOW.isBindForm(F_GET_FLOW_CUR_TCH_ID(b.content_id)),
  
                      '2',
  
                      PKP_FLOW.isBindForm(b.content_id),
  
                      0) as isBindForm,
  
               replace(replace(f.send_url,  '=0000', '=' || b.content_id),
  
                       'serialnum',
  
                       nvl(b.serial_num,  '-1')) send_url,
  
               '0' sort_id
  
          from staff_event    a,
  
               event_q        b,
  
               grade          d,
  
               staff          c,
  
               staff          e,
  
               workaccept_cfg f
  
         where a.event_id = b.event_id
  
           and b.grade = d.grade
  
           and b.exec_staff = c.staff_id(+)
  
           and b.src_staff = e.staff_id(+)
  
           and a.staff_id = 1
  
           and not exists (select 1
  
                  from sys_config
  
                 where sys_var =  'HIDDEN_EVENT_TYPE'
  
                   and instr(', ' || sys_var_value || ',  ',
  
                             ', ' ||  b.event_type || ', ') > 0)
  
           and b.event_type = f.work_type
  
           and f.queue_type = 'EVENT_Q'
  
           and b.flag is null
  
        union all
  
        select 'task' task_or_event,
  
               a.task_id id,
  
               a.staff_id,
  
               b.src_staff,
  
               nvl(e.staff_name, '...') as  src_staff_name,
  
               b.exec_staff,
  
               nvl(c.staff_name, '...') as  exec_staff_name,
  
               b.task_type type,
  
               f_get_all_state('TASK_Q',  'TASK_TYPE', task_type) as type_name,
  
               b.content,
  
               b.grade,
  
               d.grade_name,
  
               b.state,
  
               f_get_all_state('TASK_Q', 'STATE',  b.state) as state_name,
  
               b.content_id,
  
               nvl(b.room_name, '...') as  room_name,
  
               nvl(b.dev_name, '...') as  dev_name,
  
               to_char(b.state_date,  'yy/mm/dd hh24:mi:ss') as state_date,
  
               0 alert_flag,
  
               0 isBindForm,
  
               replace(f.send_url, '=0000',  '=' || b.content_id) send_url,
  
               '0' sort_id
  
          from staff_task     a,
  
               task_q         b,
  
               grade          d,
  
               staff          c,
  
               staff          e,
  
               workaccept_cfg f
  
         where a.task_id = b.task_id
  
           and b.grade = d.grade
  
           and b.exec_staff = c.staff_id(+)
  
           and b.src_staff = e.staff_id(+)
  
           and a.staff_id = 1
  
           and b.task_type = f.work_type
  
           and f.queue_type = 'TASK_Q'
  
         order by state_date desc)
  
where rownum <= 20
  

分析排查:
由admin帐号历史代办信息过多引起,清理staff_event、event_q相关数据后SQL效率提高,CPU占用降低。

4.11   8p3wxmt13z5xv3kbwc677sm94y(本地化)
  
8p3wxmt13z5xv
  
begin  pkp_itsm_upload_wyl.P_CUST_ZJ_require_yu(sysdate); end;
  
  
3kbwc677sm94y
  
SELECT  A.FLOW_ID,
  
       A.REQUEST_ID,
  
       A.SUBMIT_STAFF_NAME,
  
       A.SUBMIT_TIME,
  
       A.APPLY_DEPT,
  
       A.TITLE,
  
       A.REQUIRE_SOURCE,
  
       A.REQUIRE_URGENT,
  
       A.REQUIRE_INVOLVE_DOMAIN,
  
       A.PRODUCE_REASON,
  
       A.REQUIRE_CONTENT,
  
       A.REQUIRE_INVOLVE_SYSTEM,
  
       A.REQUIRE_TYPE_NAME,
  
       A.REQUIRE_INVOLVE_DOMAIN_NAME,
  
       A.REQUIRE_INVOLVE_SYSTEM_NAME,
  
       A.REQUIRE_TYPE,
  
       A.PATCH_SERIAL,
  
       PKP_ITSM_UPLOAD_WYL.GET_YU(D.STAFF_ID)  BELONG_DOMAIN,
  
       D.STAFF_ID
  
  FROM CUST_ZJ_REQUIRE A, TACHE B, STAFF D
  
WHERE A.FLOW_ID = B.FLOW_ID
  
   AND INSTR(', ' || B.PERSON || ', ', ', '  || D.STAFF_ID || ', ') > 0
  
   AND B.STATE <> 'F'
  
   AND EXISTS (SELECT 1
  
          FROM TACHE C
  
         WHERE C.FLOW_ID = A.FLOW_ID
  
           AND C.TCH_MOD = 11548)
  
   AND NOT EXISTS
  
(SELECT 1
  
          FROM TACHE C
  
         WHERE C.FLOW_ID = A.FLOW_ID
  
           AND C.TCH_MOD = 11595)
  
   AND PKP_ITSM_UPLOAD_WYL.GET_YU(D.STAFF_ID)  IS NOT NULL
  

分析排查:
SQL写法有问题,暂停这两个SQL相关的定时任务,本地优化后再启用,CPU占用降低。
4.12   inp_file_list相关SQL
分析排查:
Inp_file_list改成分区表后,涉及的脚本的没有修改完全,由于脚本没有使用分区字段作为查询条件,造成大量全表扫描。检查晚上的数据库awr报表,可以看出性能消耗排在前面的都是这些脚本,而上班时间这些脚本也有在跑,修改为分区字段作为查询条件后,资源消耗降低不少。
4.13   首页待办的SQL(重点)
  
select  src_staff_name  处理员工,
  
       exec_staff_name 接受员工,
  
       type_name       事务类型,
  
       content         事务标题,
  
       state_date      时间,
  
       id,
  
       content_id,
  
       type,
  
       task_or_event,
  
       isBindForm,
  
       send_url,
  
       '03' thetype,
  
       row_number () over ( order by  state_date desc ) rn
  
  from (select 'event' task_or_event,
  
               a.event_id id,
  
               b.event_type type,
  
               b.content_id,
  
               DECODE(event_type,
  
                      '3',
  
                      PKP_FLOW.isBindForm(F_GET_FLOW_CUR_TCH_ID(b.content_id)),
  
                      '2',
  
                       PKP_FLOW.isBindForm(b.content_id),
  
                      0) as isBindForm,
  
               replace(replace(f.send_url,  '=0000', '=' || b.content_id),
  
                       'serialnum',
  
                       nvl(b.serial_num,  '-1')) send_url,
  
               nvl(e.staff_name, '...') as  src_staff_name,
  
               nvl(c.staff_name, '...') as  exec_staff_name,
  
               DECODE(event_type,
  
                      '2',
  
                       PKP_FLOW_FUNCTION.get_flow_event_text(b.CONTENT_ID),
  
                       f_get_all_state('EVENT_Q', 'EVENT_TYPE', b.event_type)) as type_name,
  
               b.content,
  
               to_char(b.state_date,  'yy/mm/dd hh24:mi:ss') as state_date
  
          from staff_event    a,
  
               event_q        b,
  
               grade          d,
  
               staff          c,
  
               staff          e,
  
               workaccept_cfg f
  
         where a.event_id = b.event_id
  
           and b.grade = d.grade
  
           and b.flag is null
  
           and b.exec_staff = c.staff_id(+)
  
           and b.src_staff = e.staff_id(+)
  
           and a.staff_id =  &CURRENT_STAFF_ID
  
           and b.event_type = f.work_type
  
           and f.queue_type = 'EVENT_Q'
  
        union all
  
        select 'task' task_or_event,
  
               a.task_id id,
  
               b.task_type type,
  
               b.content_id,
  
               0 isBindForm,
  
               replace(f.send_url, '=0000',  '=' || b.content_id) send_url,
  
               nvl(e.staff_name, '...') as  src_staff_name,
  
               nvl(c.staff_name, '...') as  exec_staff_name,
  
               f_get_all_state('TASK_Q',  'TASK_TYPE', task_type) as type_name,
  
               b.content,
  
               to_char(b.state_date, 'yy/mm/dd hh24:mi:ss')  as state_date
  
          from staff_task     a,
  
               task_q         b,
  
               grade          d,
  
               staff          c,
  
               staff          e,
  
               workaccept_cfg f
  
         where a.task_id = b.task_id
  
           and b.grade = d.grade
  
           and b.exec_staff = c.staff_id(+)
  
           and b.src_staff = e.staff_id(+)
  
           and a.staff_id =  &CURRENT_STAFF_ID
  
           and b.task_type = f.work_type
  
           and f.queue_type = 'TASK_Q') a
  
where 1 = 1 and type <> '3' and  type<>'#' and content like :CONTENT
  
and to_date('20'||state_date,'yyyy-MM-dd  hh24:mi:ss') >= to_date(:START_DATE||' 00:00:00','yyyy-MM-dd hh24:mi:ss')
  
and to_date('20'||state_date,'yyyy-MM-dd  hh24:mi:ss') <= to_date(:END_DATE||' 23:59:59','yyyy-MM-dd hh24:mi:ss')
  
)
  
where  rn <= :P_ROWNUM
  
分析排查:
由于select字段列表里有很多函数调用,当代办比较多的时候,SQL性能差,而且这些SQL在用户登录后都会被执行,对整体性能影响比较大。将内层递归调用改到外层,并限制外层返回行数量,降低了对系统资源的消耗。
4.14   需求流程查询报表SQL优化
  
select  re.patch_serial "需求流水号",
  
       re.submit_staff_name "需求发起人",
  
       (select org_name from organization  where org_id = re.apply_dept) "发起人部门",
  
       to_char(t1.create_date, 'yyyy-mm-dd  hh24:mi:ss') "发起时间",
  
       re.title "需求标题",
  
(select va.TCH_NAME
  
          from v_tache va
  
         where va.tch_id =  f_get_flow_cur_tch_id(re.flow_id)) "当前处理环节",
  
       trim(',' from zj_pkp_basic.get_Maxtch_person(re.flow_id))  "当前处理人",
  
       decode(t1.state,
  
              'A',
  
              '未竣工',
  
              'C',
  
              '已接收处理',
  
              'F',
  
              '已竣工',
  
              'X') "流程状态",
  
       t1.flow_mod,
  
       t1.flow_id
  
  from (select r1.*, r2.staff_name
  
          from v_flow r1, staff r2
  
         where r1.staff_id = r2.staff_id) t1,
  
       flow_model t4,
  
       cust_zj_require re
  
where t1.flow_id = re.flow_id
  
   and t4.flow_mod = t1.flow_mod
  
   and t1.flow_mod = 11143
  
   and re.patch_serial like '%' ||  :PATCH_SERIAL || '%'
  
   and re.title like '%' || TITLE || '%'
  
   and t1.create_date >=  trunc(to_date(:BEGIN_DATE, 'yyyy-mm-dd'))
  
   and t1.create_date <  trunc(to_date(:END_DATE, 'yyyy-mm-dd')) + 1
  
   and t1.state = :STATE
  
   and re.submit_staff_name like '%' ||  :APPLY_NAME || '%'
  
   and re.flow_id in (select distinct  (vt.FLOW_ID)
  
                        from v_tache vt,  tache_model tm
  
                       where vt.TCH_MOD =  tm.tch_mod
  
                         and tm.flow_mod =  11143
  
                         and vt.STAFF_ID =  &CURRENT_STAFF_ID)
  
order by t1.create_date desc
  

由于补丁导致默认条件失效,这两个SQL导致两个节点CPU空闲降为0,出现数据库主机hang住的现象,修复默认查询条件后故障恢复。但是SQL存在递归查询,并且是全表扫描,对整体性能危害很大,例如语句一中:分析排查:
(selectva.TCH_NAME
fromv_tache va
         where va.tch_id =f_get_flow_cur_tch_id(re.flow_id)) "当前处理环节",
还需要进一步优化。

4.15   绑定变量SQL优化


分析排查:
部分SQL占用大量共享池内存,并且做了不必要的硬解析。由于SQL比较多且业务比较复杂,需要研发人员修改,目前工程点已经提事件单。具体SQL以及大致修改方法可以看看附件<<以下SQL没有使用绑定变量.pdf>>。

4.16   工作台SQL脚本审核
由于工作台是用户主要入口,访问频率很高,并且会定时刷新。工作台相关的SQL效率低,不但会导致页面访问速度慢,而且会影响到数据库的整体性能(例如10月25日的故障)。因此检查工作台相关SQL,分析潜在风险很有必要。
4.17   SQL_ID=d1w2d9p01b9hmadz3b055vju4s
  
SELECT :B3  BATCH_ID,
  
       B.DR_ID,
  
       B.SYSTEM_ID,
  
       B.AUDIT_ID,
  
       B.PERIOD,
  
       B.INFO_ID,
  
       DECODE(SUBSTR(B.FILE_NAME, 1, 1), 'F',  30, 'E', 20, 10) FILE_TYPE,
  
       B.FILE_DATE,
  
       B.FILE_CNT_EARLY,
  
       B.FILE_CNT_LAST,
  
       B.FILE_NAME,
  
       B.TRANS_DATETIME TRANS_DATE,
  
       B.FILE_BLOCK,
  
       (CASE
  
         WHEN B.FILE_DATE - B.TRANS_DATETIME  > 15 / 1440 THEN
  
          4
  
         WHEN B.FILE_DATE - B.TRANS_DATETIME  + :B2 / 1440 < 0 THEN
  
          2
  
         ELSE
  
          1
  
       END) FLAG
  
  FROM (SELECT 0 DR_ID,
  
               SUBSTR(A.FILE_NAME,  INSTR(A.FILE_NAME, '_', 1, 2) + 1, 4) SYSTEM_ID,
  
               SUBSTR(A.FILE_NAME,  INSTR(A.FILE_NAME, '_', 1, 3) + 1, 4) AUDIT_ID,
  
               A.FILE_NAME,
  
               A.BATCH_TIME + 15 / 1440  FILE_DATE,
  
               TO_NUMBER(SUBSTR(A.FILE_NAME,
  
                                 INSTR(A.FILE_NAME, '_', 1, 8) + 1,
  
                                8)) PERIOD,
  
               TO_NUMBER(SUBSTR(A.FILE_NAME,
  
                                 INSTR(A.FILE_NAME, '_', 1, 4) + 1,
  
                                2)) INFO_ID,
  
               T.PUT_FILE_BLOCK_CNT  FILE_BLOCK,
  
               T.GOODS_SEND_TIME  TRANS_DATETIME,
  
               RANK() OVER(PARTITION BY  A.FILE_NAME ORDER BY A.GENERATE_TIME ASC) FILE_CNT_EARLY,
  
               RANK() OVER(PARTITION BY A.FILE_NAME ORDER  BY A.GENERATE_TIME DESC) FILE_CNT_LAST
  
          FROM INP_FILE_LIST A,  INP_IDEP_DATA_FILES T
  
         WHERE T.GOODS_SEND_TIME >=  SYSDATE - 120 / 1440
  
           AND SUBSTR(A.FILE_NAME,  INSTR(A.FILE_NAME, '_', 1, 5) + 1, 1) = :B1
  
           AND A.STATE IN ('0SB', '0SC',  '0SA')
  
           AND A.FILE_NAME = T.FILE_NAME) B
  
WHERE B.FILE_CNT_EARLY = 1
  
   AND B.PERIOD <= 96
  

adz3b055vju4s来自Audit
  
SELECT  B.DR_ID,
  
       DECODE(B.SYSTEM_ID,
  
              3001,
  
              '综合采集(3001)',
  
              3002,
  
              '离线计费(3002)',
  
              3003,
  
              '在线计费(3003)',
  
              3007,
  
              '统一充值平台(3007)',
  
              3009,
  
              '智能交换平台(3009)',
  
              3011,
  
              '余额管理(3011)',
  
              3031,
  
              '综合结算(3031)') SYSTEM_NAME,
  
       B.AUDIT_ID,
  
       B.PERIOD,
  
       B.INFO_ID,
  
       DECODE(SUBSTR(B.FILE_NAME, 1, 1), 'F',  30, 'E', 20, 10) FILE_TYPE,
  
       B.FILE_DATE,
  
       B.FILE_CNT_EARLY,
  
       B.FILE_CNT_LAST,
  
       B.FILE_NAME,
  
       B.TRANS_DATETIME TRANS_DATE,
  
       B.FILE_BLOCK
  
  FROM (SELECT 0 DR_ID,
  
               SUBSTR(A.FILE_NAME,  INSTR(A.FILE_NAME, '_', 1, 2) + 1, 4) SYSTEM_ID,
  
               SUBSTR(A.FILE_NAME,  INSTR(A.FILE_NAME, '_', 1, 3) + 1, 4) AUDIT_ID,
  
               A.FILE_NAME,
  
               A.BATCH_TIME FILE_DATE,
  
               TO_NUMBER(SUBSTR(A.FILE_NAME,
  
                                 INSTR(A.FILE_NAME, '_', 1, 8) + 1,
  
                                8)) PERIOD,
  
               TO_NUMBER(SUBSTR(A.FILE_NAME,
  
                                 INSTR(A.FILE_NAME, '_', 1, 4) + 1,
  
                                2)) INFO_ID,
  
               A.FILE_SIZE FILE_BLOCK,
  
               C.GOODS_SEND_TIME  TRANS_DATETIME,
  
               RANK() OVER(PARTITION BY  A.FILE_NAME ORDER BY A.GENERATE_TIME ASC) FILE_CNT_EARLY,
  
               RANK() OVER(PARTITION BY  A.FILE_NAME ORDER BY A.GENERATE_TIME DESC) FILE_CNT_LAST
  
          FROM INP_FILE_LIST A,  INP_IDEP_DATA_FILES C
  
         WHERE A.FILE_NAME = C.FILE_NAME
  
           AND C.GOODS_SEND_TIME >=  SYSDATE - 1 / 24
  
           AND SUBSTR(A.FILE_NAME,  INSTR(A.FILE_NAME, '_', 1, 5) + 1, 1) = :B1
  
           AND A.STATE IN ('0SB', '0SC',  '0SA')) B
  
WHERE B.FILE_CNT_EARLY = 1
  
   AND B.PERIOD <= :B2
  
   AND SUBSTR(B.FILE_NAME, 26, 8) || '235959'  -
  
       TO_CHAR(B.TRANS_DATETIME,  'YYYYMMDDHH24MISS') < 0)
  
分析排查:

这个两个语句来自后台进程,对INP_FILE_LIST进行了全表扫描,这个表很大22G。从C.GOODS_SEND_TIME这个条件范围看,记录不是很多,可以考虑改成用FILE_ID来连接。

4.18    SQL_ID=6r6kc73cc4qjs
  
SELECT  *
  
  FROM (SELECT ROWNUM SID, A.*
  
          FROM (SELECT rm.flow_id,
  
                       11303 flow_mod,
  
                       rm.serial 权限编号,
  
                       rm.title 标题,
  
                       rm.apply_name 权限发起人,
  
                       rm.submit_time 发起时间,
  
                       (select mean
  
                          from codelist
  
                         where code_type =  'SWITCH_APPLY_TYPE'
  
                           and code =  rm.APPLY_type) "申请类型",
  
                       decode(rm.LIMIT_TYPE,
  
                              1,
  
                              '应用',
  
                              2,
  
                              'KEY',
  
                              3,
  
                              '网络',
  
                              4,
  
                              '数据库',
  
                              5,
  
                              '主机',
  
                              6,
  
                              '网络设备用户账户') "权限类型",
  
(select va.TCH_NAME
  
                           from v_tache va
  
                          where va.tch_id = f_get_flow_cur_tch_id(rm.flow_id)) "当前停留环节",
  
                        ZJ_PKP_TACHE_CONFIG_YUNTD.GET_PERSONSTAFF_NAME_ZJC(rm.flow_id) "当前停留人",
  
                       (select mr.region_name
  
                          from staff sf,  manage_region mr
  
                         where sf.region_id = mr.region_id
  
                           and sf.staff_id =  rm.submit_staff_id) 地区,
  
                       decode(f.state, 'A', '未竣工', 'F', '已竣工',  '已撤销') 流程状态
  
                  FROM cust_zj_switch rm,
  
                       (select * from v_flow  v where v.FLOW_MOD = 11303) f
  
                 WHERE f.flow_id = rm.flow_id
  
                   AND trunc(rm.submit_time)  >= to_date(:1, 'yyyy-mm-dd')
  
                   AND trunc(rm.submit_time)  <= to_date(:2, 'yyyy-mm-dd')
  
                 order by rm.submit_time desc) A) B
  
WHERE B.SID > :3
  
   AND B.SID <= :4
  

分析排查:
  
(select va.TCH_NAME
  
                           from v_tache va
  
                          where va.tch_id = f_get_flow_cur_tch_id(rm.flow_id)) "当前停留环节",
  

这个地方改成v_tache改成tache后,能够减少全表扫描风险。修改后的CPU对比


4.19    SGA修改为11G
11 月7日下午系统出现严重pagein/pageout,数据库hang住10分钟左右。在生产当前压力下,系统内存资源略显不足,故将SGA缩小为11G大小,以降低数据库hang住的风险。(11月10日已实施)。
4.20    SQL_ID=dkrydgt7bdx4u
执行计划:

修改后SQL_ID:
原来对tache、tache_his的全表扫描,变成了索引范围扫描,效率提高了几十倍。执行计划:
4.21   SQL_ID= 6fvj6jgj3ttvp
分析排查:
这个JOB已经没有用,可以停止。



上一篇:占用CPU严重的SQL语句
下一篇:一键获取库总体情况(oracle自动创建awr与addm报告的脚本)
回复

使用道具 举报

内容发布:sstr| 发布时间:2021-5-26 02:44:59
很详细的分析过程,学习了
回复 支持 反对

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

热门文章教程

  • 实战PHP与MySQL权威指南PDF电子书下载
  • 大数据技术与应用入门培训教程(电子版下载
  • Oracle 12cR2 九大新功能全面曝光_详解云数
  • Oracle OCP认证考试IZ0-053题库共712题数据
  • MySQL5权威指南(第3版)PDF电子版下载
  • 风哥Oracle数据库巡检工具V1.0(附2.6网页
快速回复 返回顶部 返回列表