Re: ORA-1555 - 12.2.0.1 Exa
Date: Wed, 30 Jan 2019 15:15:01 +1100
Message-ID: <CAFeFPA9OKp8tE8uT7ONp8M6XyV0-YUN_EXfYaWCnS7dEh5EBOA_at_mail.gmail.com>
Hi Daniel
It is just one select from a view. No commits involved This database is used for scheduling, and billing purposes so there is a lot of changes happening throughout the day
PRD605(4):ORA-01555 caused by SQL statement below (SQL ID: f6sy741v9xsja,
Query Duration=6650 sec, SCN:
PRD605(4):0x00000821a2b5d8b5
PRD605(4):):
2019-01-29T17:00:09.334185+11:00
PRD605(4):select ROWNUM, x.* from SWM_P8_VOD_CONTENT_EXTRACT x
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 10800 undo_tablespace string UNDOTBS1
SQL> show parameter undo
NAME TYPE VALUESQL> SELECT dbms_undo_adv.best_possible_retention FROM dual; 2
------------------------------------ -----------
------------------------------
temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 10800 undo_tablespace string UNDOTBS1
BEST_POSSIBLE_RETENTION
5604881
SQL> select dbms_undo_adv.best_possible_retention(7262,7264) from dual; <-- Period where we had ORA-01555
DBMS_UNDO_ADV.BEST_POSSIBLE_RETENTION(7262,7264)
1573945
SQL> SQL> SELECT dbms_undo_adv.required_retention(7262,7264) from dual;
DBMS_UNDO_ADV.REQUIRED_RETENTION(7262,7264)
1497
SQL> SELECT dbms_undo_adv.required_retention(7262,7264) from dual;
DBMS_UNDO_ADV.REQUIRED_RETENTION(7262,7264)
1497
SQL> SELECT dbms_undo_adv.required_undo_size(10800,7262,7264) from dual;
DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(10800,7262,7264)
225
SQL> SELECT dbms_undo_adv.required_undo_size(10800) from dual;
DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(10800)
200
SQL>
Current undo tablespace : PRD605_UNDO2 Current undo tablespace size (datafile size now) : 122880M Current undo tablespace size (consider autoextend) : 409600M AUTOEXTEND for undo tablespace is : ON Current undo retention : 10800 UNDO GUARANTEE is set to : FALSE
_at_Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> a) Do the undo stats report a non-zero "unexpired steal count" Count is Zero for the period where we had 2 Ora-01555 b) What do the "undo records applied" system statistics suggest?
"Data blocks consistent reads - undo records applied 51,547,216/total
4,105.23/s"
"rollback changes - undo records applied 189,571,880/Total 15,097.54/s"
"transaction tables consistent reads - undo records applied
18,953,990/Total 1,509.50/s"
Jack van Zanen
This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation
On Wed, Jan 30, 2019 at 9:15 AM Daniel Fink <daniel.fink_at_returnpath.com> wrote:
> Is the extract a single statement or a collection of statements? > The alert log should contain the specific statement getting the ORA-1555. > > Is the extract doing any data modifications? For example, logging, > updating a row to indicate it was extracted, etc. If so, look into a > possible 'fetch across commits'. > > What other processes are modifying data during the extract? If so, you may > need to increase the undo retention to provide Oracle more 'incentive' to > use new undo segments instead of re-using existing ones. > > > > On Mon, Jan 28, 2019 at 9:12 PM Jack van Zanen <jack_at_vanzanen.com> wrote: > >> Hi All >> >> >> We are getting ORA-1555 on one of our daily extracts and I need to >> explain to the business why it is happening. >> >> ERROR at line 1: >> ORA-01555: snapshot too old: rollback segment number 92 with name >> "_SYSSMU92_3807070151$" too small >> ORA-06512: at line 43 >> ORA-06512: at "SYS.DBMS_SQL", line 1726 >> ORA-06512: at line 24 >> >> >> >> I have an undo retention of 3 hours (query fails before that generally) >> I have an undo tablespace that can grow to 400G and currently only at >> 120G >> >> so it does not appear to be the usual suspects so I am thinking of >> setting an event to capture more information to explain it better >> >> would below give me more information why this is happening or is there a >> better solution? >> >> *alter system set events '1555 trace name errorstack level 3';* >> >> My guess is that the same blocks are simply changed too many times during >> the time of query so the rollback record required for CR is simply no >> longer available. >> >> >> Jack van Zanen >> >> >> ------------------------- >> This e-mail and any attachments may contain confidential material for the >> sole use of the intended recipient. If you are not the intended recipient, >> please be aware that any disclosure, copying, distribution or use of this >> e-mail or any attachment is prohibited. If you have received this e-mail in >> error, please contact the sender and delete all copies. >> Thank you for your cooperation >> > > > -- > *Daniel Fink* > Sr. Database Administrator | *Return Path* > m | (303) 808 3282 > daniel.fink_at_returnpath.com > > [image: Lifecycle Metrics Benchmark] > <http://signatures.returnpath.com/uc/5b731f4d558a8600a895089c> > [image: Powered by Sigstr] > <http://signatures.returnpath.com/uc/5b731f4d558a8600a895089c/watermark> >
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 30 2019 - 05:15:01 CET