ORA-01555 caused by SQL statement below

352262

　　Sun Jul 27 15:59:34 2014

　　ORA-01555 caused by SQL statement below (SQL>　　INSERT INTO RP_RETAIL001 (ID, AD_CLIENT_ID, AD_ORG_ID, OWNERID, ISACTIVE, BILLDATE, DOCNO, RETAILBILLTYPE, CREATER, C_STORE_ID, DESCRIPTION, C_VIP_ID, SALESREP_ID, M_PRODUCT_ID, M_ATTRIBUTESETINSTANCE_ID, QTY, PRICELIST, TOT_AMT_LIST, PRICEACTUAL, TOT_AMT_ACTUAL, DISCOUNT, TYPE, REFNO, PERCOST, M_PRODUCT_ALIAS_ID, MARKDISAMT, ITEM_DESCRIPTION, C_MARKBALTYPE_ID, STATUSTIME, AD_PI_ID, C_RETAILTYPE_ID) SELECT B.ID, B.AD_CLIENT_ID, B.AD_ORG_ID, B.OWNERID, B.ISACTIVE, A.BILLDATE, A.DOCNO, A.RETAILBILLTYPE, A.OWNERID AS CREATER, A.C_STORE_ID, A.DESCRIPTION, A.C_VIP_ID, B.SALESREP_ID, B.M_PRODUCT_ID, B.M_ATTRIBUTESETINSTANCE_ID, B.QTY, B.PRICELIST, B.TOT_AMT_LIST, B.PRICEACTUAL, B.TOT_AMT_ACTUAL, B.DISCOUNT, B.TYPE, A.REFNO, (SELECT K.PERCOST FROM FA_PRODUCT_CUSCOST K WHERE K.C_CUSTOMER_ID = A.C_CUSTOMER_ID AND K.M_PRODUCT_ID = B.M_PRODUCT_ID) AS PERCOST, B.M_PRODUCTALIAS_ID AS M_PRODUCT_ALIAS_ID, B.TOT_AMT_ACTUAL * 0, B.DESCRIPTION ITEM_DESCRIPTION, B.C_MARKBALTYPE_ID, A.STATUSTIME, :B5 , A.C_RETAILTYPE_ID FROM M_R
　　Sun Jul 27 16:12:03 2014
　　今天是周末，难得可以休息一下，晚上8点来到办公室查看下库的情况，发现有条日志报错，这条语句很熟悉了，也就是这几天一直和开发那边沟通的一条报表的存储过程，该语句近一段时间以来一直雄踞ADDM 性能分析中占Top SQL Statements第一位，在7月18号时该语句把undo表空间占满了，日志中出现ORA-01555 caused by SQL statement below 提示，所以马上查看UNDO表空间，
　　20:25:00 sys@PORTALDB> select sum(maxbytes)/1024/1024/1024,SUM(USER_BYTES)/1024/1024/1024 FROM dba_data_files where tablespace_NAME='UNDOTBS1';
　　SUM(MAXBYTES)/1024/1024/1024 SUM(USER_BYTES)/1024/1024/1024
　　---------------------------- ------------------------------
　　63.9999695                     63.9989624
　　Elapsed: 00:00:00.02
　　20:25:02 sys@PORTALDB> select sum(maxbytes)/1024/1024/1024,SUM(USER_BYTES)/1024/1024/1024 FROM dba_data_files where tablespace_NAME='UNDOTBS2';
　　SUM(MAXBYTES)/1024/1024/1024 SUM(USER_BYTES)/1024/1024/1024
　　---------------------------- ------------------------------
　　63.9999695                     63.9989624
　　20:10:46 sys@PORTALDB> show parameter undo
　　NAME                                 TYPE        VALUE
　　------------------------------------ ----------- ------------------------------
　　undo_management                      string      AUTO
　　undo_retention                       integer     900
　　undo_tablespace                      string      UNDOTBS1
　　这次表空间几乎没有怎么使用；实际上看表空间的大小我会通过OEM查看比较简单明了；
　　20:29:02 sys@PORTALDB> SELECT DISTINCT STATUS  ,COUNT(*)  , SUM(BYTES) / 1024 / 1024 / 1024   FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
　　STATUS      COUNT(*) SUM(BYTES)/1024/1024/1024
　　--------- ---------- -------------------------
　　EXPIRED         2739                11.5249634
　　UNEXPIRED        957                1.08874512
　　ACTIVE             5                 .01171875
　　网上看到http://www.xifenfei.com/2296.html 博文，我的情况与之非常的类似；转载下"
　　通过undo_retention保留时间为900秒，而该sql执行了1699秒，在这1699秒钟，TAB_XN_COMPANY表中的数据被修改，而且被修改的undo数据在900秒后被覆盖导致，导致原查询语句不能获取到scn小于或者等于查询时候的数据块内容(在undo中)，所以出现ORA-01555。从这里也可以看出来，在undo空间还剩余的情况下，如果超过了undo_retention限制，undo内容还是有可能被覆盖，而不是使用未使用的undo
　　1.出现ORA-1555原因
　　The ORA-1555 errors can happen when a query is unable to access enough undo to build a copy of the data at the time the query started. Committed “versions” of blocks are maintained along with newer uncommitted “versions” of those blocks so that queries can access data as it existed in the database at the time of the query. These are referred to as “consistent read” blocks and are maintained using Oracle undo management.
　　就是一个查询要访问某个数据块，而这个数据块在这个查询执行过程中修改过，那么该查询需要查询undo中数据块，而undo中该数据块已经不存在，从而出现ORA-1555
　　2.ORA-1555解决方法
　　Case 1 – Rollback Overwritten
　　1.缩短sql运行时间
　　2.增加undo_retention，这个同时需要考虑undo空间大小
　　3.减少commit(rollback)次数
　　4.在一条sql中尽量使数据块访问一次
　　4.1)Using a full table scan rather than an index lookup
　　4.2)Introducing a dummy sort so that we retrieve all the data, sort it and then sequentially visit these data blocks.
　　Case 2 – Rollback Transaction Slot Overwritten
　　这种问题，主要是延迟块清理导致，一般建议在进行大批量的dml操作后，使用全表(全index)扫描执行一遍，或者收集全部统计信息
　　今天观察了一天无ora-1555事件发生，先不改保留时间，在观察一段时间试试;