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/