migudb3 ORA-01555错误分析处理

#################################################alert日志中的告警内容
Sat Jul 08 11:12:56 2017
ORA-01555 caused by SQL statement below (SQL ID: 42wj2jgjxyaz1, Query Duration=36503 sec, SCN: 0x0000.51258770):
select
        UPAY_TRANS_ID,UPAY_DATE_TIME,CRM_REQ_SELEDATE,
        CRM_BIP_CODE,CRM_ACTIVITY_CODE,CRM_SESSION_ID,CRM_TRANS_IDO,
        CRM_TRANS_IDO_TIME,HOME_PROV,RSP_CODE,RSP_DESC,ACTIVITY_CODE,RESULT_CODE,
        RESULT_DESC,TRADE_SEQNO,ORI_REQ_SYS,ORI_REQ_DATE,TRADE_SESSION,PAY_TYPE
        from TXN_REVOKE_NOTICE_RESULT
         WHERE  ORI_REQ_SYS = :1
                and ORI_REQ_DATE = :2
                and TRADE_SESSION=:3
##################################################查看执行计划，确认sql内容以及执行计划走的是全表扫描
SQL> select * from table(dbms_xplan.display_awr('42wj2jgjxyaz1')); 
PLAN_TABLE_OUTPUT
--------------------
SQL_ID 42wj2jgjxyaz1
--------------------
select         UPAY_TRANS_ID,UPAY_DATE_TIME,CRM_REQ_SELEDATE,
CRM_BIP_CODE,CRM_ACTIVITY_CODE,CRM_SESSION_ID,CRM_TRANS_IDO,
CRM_TRANS_IDO_TIME,HOME_PROV,RSP_CODE,RSP_DESC,ACTIVITY_CODE,RESULT_CODE
,         RESULT_DESC,TRADE_SEQNO,ORI_REQ_SYS,ORI_REQ_DATE,TRADE_SESSION
,PAY_TYPE         from TXN_REVOKE_NOTICE_RESULT          WHERE
ORI_REQ_SYS = :1                                            and
ORI_REQ_DATE = :2                                            and
TRADE_SESSION=:3


Plan hash value: 2597457415


----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                          |       |       |   885 (100)|          |       |       |
|   1 |  PARTITION RANGE ALL|                          |     1 |   218 |   885   (1)| 00:00:11 |     1 |   115 |
|   2 |   PARTITION LIST ALL|                          |     1 |   218 |   885   (1)| 00:00:11 |     1 |    31 |
|   3 |    TABLE ACCESS FULL| TXN_REVOKE_NOTICE_RESULT |     1 |   218 |   885   (1)| 00:00:11 |     1 |  3565 |
----------------------------------------------------------------------------------------------------------------


#################################################确认表的用户，为OCDBA.TXN_REVOKE_NOTICE_RESULT
SQL> select distinct OWNER from dba_objects where OBJECT_NAME='TXN_REVOKE_NOTICE_RESULT';


OWNER
------------------------------
YJOCDBA
OCDBA
HLWOCHIS


SQL> select distinct user_id from dba_hist_active_sess_history where sql_id='42wj2jgjxyaz1';
  USER_ID
----------
        93
        
SQL> select username from dba_users where user_id=93;


USERNAME
------------------------------
OCDBA






#################################################查看该表的索引，确认无索引
SQL> select t.*,i.index_type from dba_ind_columns t,dba_indexes i where t.index_name=i.index_name and t.table_name='TXN_REVOKE_NOTICE_RESULT' and t.table_owner='OCDBA';


no rows selected




################################################查看该表的建表语句，确认是按照CRM_REQ_SELEDATE做范围分区，按照HOME_PROV做列表子分区。
SQL> select dbms_metadata.get_ddl('TABLE','TXN_REVOKE_NOTICE_RESULT','OCDBA') from dual;


DBMS_METADATA.GET_DDL('TABLE','TXN_REVOKE_NOTICE_RESULT','OCDBA')
--------------------------------------------------------------------------------


  CREATE TABLE "OCDBA"."TXN_REVOKE_NOTICE_RESULT"
   (    "UPAY_TRANS_ID" VARCHAR2(32) NOT NULL ENABLE,
        "UPAY_DATE_TIME" CHAR(17) NOT NULL ENABLE,
        "CRM_REQ_SELEDATE" CHAR(8),
        "CRM_BIP_CODE" CHAR(8),
        "CRM_ACTIVITY_CODE" CHAR(8),
        "CRM_SESSION_ID" VARCHAR2(32),
        "CRM_TRANS_IDO" VARCHAR2(32) NOT NULL ENABLE,
        "CRM_TRANS_IDO_TIME" VARCHAR2(14),
        "HOME_PROV" CHAR(4),
        "RSP_CODE" CHAR(6),
        "RSP_DESC" VARCHAR2(128),
        "ACTIVITY_CODE" VARCHAR2(8),
        "RESULT_CODE" CHAR(6),
        "RESULT_DESC" VARCHAR2(128),
        "TRADE_SEQNO" VARCHAR2(32),
        "ORI_REQ_SYS" CHAR(4),
        "ORI_REQ_DATE" CHAR(8),
        "TRADE_SESSION" VARCHAR2(32),
        "PAY_TYPE" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "UPAY_QINGFEN_DAT02"
  PARTITION BY RANGE ("CRM_REQ_SELEDATE")
  SUBPARTITION BY LIST ("HOME_PROV")
  SUBPARTITION TEMPLATE (
    SUBPARTITION "8711" VALUES ( '8711' ),
    SUBPARTITION "5511" VALUES ( '5511' ),
    SUBPARTITION "7911" VALUES ( '7911' ),
    SUBPARTITION "3511" VALUES ( '3511' ),
    SUBPARTITION "7711" VALUES ( '7711' ),
    SUBPARTITION "2801" VALUES ( '2801' ),
    SUBPARTITION "2301" VALUES ( '2301' ),
    SUBPARTITION "5711" VALUES ( '5711' ),
    SUBPARTITION "2101" VALUES ( '2101' ),
    SUBPARTITION "2001" VALUES ( '2001' ),
    SUBPARTITION "1001" VALUES ( '1001' ),
    SUBPARTITION "2201" VALUES ( '2201' ),
    SUBPARTITION "2501" VALUES ( '2501' ),
    SUBPARTITION "5311" VALUES ( '5311' ),
    SUBPARTITION "5911" VALUES ( '5911' ),
    SUBPARTITION "8511" VALUES ( '8511' ),
    SUBPARTITION "2701" VALUES ( '2701' ),
    SUBPARTITION "7311" VALUES ( '7311' ),
    SUBPARTITION "8981" VALUES ( '8981' ),
    SUBPARTITION "2901" VALUES ( '2901' ),
    SUBPARTITION "3111" VALUES ( '3111' ),
    SUBPARTITION "9711" VALUES ( '9711' ),
    SUBPARTITION "9311" VALUES ( '9311' ),
    SUBPARTITION "4511" VALUES ( '4511' ),
    SUBPARTITION "2401" VALUES ( '2401' ),
    SUBPARTITION "4311" VALUES ( '4311' ),
    SUBPARTITION "3711" VALUES ( '3711' ),
    SUBPARTITION "9511" VALUES ( '9511' ),
    SUBPARTITION "9911" VALUES ( '9911' ),
    SUBPARTITION "4711" VALUES ( '4711' ),
    SUBPARTITION "8911" VALUES ( '8911' ) )
 (PARTITION "P_20160701"  VALUES LESS THAN ('20160702')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "UPAY_QINGFEN_DAT02" ,
 PARTITION "P_20160702"  VALUES LESS THAN ('20160703')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "UPAY_QINGFEN_DAT02" ,
 。。。
  
##############################################总结：
若该sql是经常需要执行的语句，数据库可做如下优化：
由于sql的where条件是ORI_REQ_SYS，ORI_REQ_DATE，TRADE_SESSION，而该表的分区条件是CRM_REQ_SELEDATE，HOME_PROV。所以sql无法按照分区条件去某个单独分区查询数据，需要建全局复合索引。
语句参考如下：create index idx_sys_date_session on OCDBA.TXN_REVOKE_NOTICE_RESULT(ORI_REQ_SYS,ORI_REQ_DATE,TRADE_SESSION);

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

转载于:http://blog.itpub.net/31441616/viewspace-2141806/