问题:
Tue Sep 06 08:50:15 2016ORA-01555 caused by SQL statement below (SQL ID: c3w7vx7m9zuw4, Query Duration=46697 sec, SCN: 0x0cef.8558a045):
select * from ( SELECT c.CardCode »áÔ±¿¨ºÅ, case c.CardState when :"SYS_B_000" then :"SYS_B_001" when :"SYS_B_002" then :"SYS_B_003" when :"SYS_B_004" then :"SYS_B_005" when :"SYS_B_006" then :"SYS_B_007" when :"SYS_B_008" then :"SYS_B_009" else :"SYS_B_010" end ¿¨×´Ì¬, m.MemberCode »áÔ±±àÂë, m.MemberName »áÔ±Ãû³Æ, m.CustomerTypeCode ¹Ë¿ÍÀàÐͱàÂë, CT.CustomerTypeName ¹Ë¿ÍÀàÐÍÃû³Æ, tcm.TradeCircleCode ÉÌȦ±àÂë, tc.TradeCircleName ÉÌȦÃû³Æ, case m.Sex when :"SYS_B_011" then :
分析:
1:select dbms_sqltune.report_sql_monitor(sql_id=>'c3w7vx7m9zuw4') monitor_report from dual;
Global Information
------------------------------
Status : DONE (ERROR)
Instance ID : 1
Session : SYSCRM (554:18163)
SQL ID : c3w7vx7m9zuw4
SQL Execution ID : 16777216
Execution Started : 09/05/2016 19:51:57
First Refresh Time : 09/05/2016 19:52:06
Last Refresh Time : 09/06/2016 07:29:34
Duration : 41857s
Service : crm
Program : Server.exe
Fetch Calls : 599187
2:
Select * from table(dbms_workload_repository.awr_sql_report_text(1247385927,1,14173,14174,'c3w7vx7m9zuw4',0));
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 105,592 N/A 2.2
CPU Time (ms) 63,746 N/A 2.2
Executions 0 N/A N/A
Buffer Gets 2.4033E+07 N/A 19.9
Disk Reads 3,405 N/A 0.0
Parse Calls 0 N/A 0.0
Rows 3,386,236 N/A N/A
User I/O Wait Time (ms) 1,902 N/A N/A
Cluster Wait Time (ms) 989 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version Count 1 N/A N/A
Sharable Mem(KB) 419 N/A N/A
看到这里基本上就知道什么问题了。
也可以看看执行计划,查看相关参数,我一般会保留至少8小时和足够的undo空间,主要是防止有误的DML操作。
undo_retention
ALTER DATABASE UNDOTBS01 RETENTION GUARANTEE