8 Replies Latest reply: May 21, 2012 2:26 PM by sb92075 RSS

    High Undo_retention

    878451
      Hi Fellows,

      I am using Oracle 10g 10.2.0.5 with RAC 10G, using Solaris.

      I am having some troubles with a specific querie, and it is taking a lot of time to finish with ORA-01555:
      ORA-01555 caused by SQL statement below (SQL ID: 9h6zbrufuk2p7, Query Duration=63680 sec, SCN: 0x0002.6a1d8fef):
      Thu May 17 05:14:04 BRT 2012

      * I know that i suppose to increase the undo_retention to at least duration+1;
      * my undo tablespaces have a lot of free space. The disks too.
      * I know that i should analyze the querie and see the cost, but i want to explain to developer the reason i can´t set undo_retention too high.

      What would happen if i set the undo_retention parameter to a high value as 64000 ?

      Thanks in advance.
        • 1. Re: High Undo_retention
          sb92075
          BrunoSales wrote:
          Hi Fellows,

          I am using Oracle 10g 10.2.0.5 with RAC 10G, using Solaris.

          I am having some troubles with a specific querie, and it is taking a lot of time to finish with ORA-01555:
          ORA-01555 caused by SQL statement below (SQL ID: 9h6zbrufuk2p7, Query Duration=63680 sec, SCN: 0x0002.6a1d8fef):
          Thu May 17 05:14:04 BRT 2012

          * I know that i suppose to increase the undo_retention to at least duration+1;
          * my undo tablespaces have a lot of free space. The disks too.
          * I know that i should analyze the querie and see the cost, but i want to explain to developer the reason i can´t set undo_retention too high.

          What would happen if i set the undo_retention parameter to a high value as 64000 ?

          Thanks in advance.
          ORA-01555 can occur for reasons other than "low" undo_retention

          visit http://asktom.oracle.com & do KEYWORD search on "ORA-01555"
          • 2. Re: High Undo_retention
            JustinCave
            There isn't much of a downside to having an undo_retention that is too large.

            undo_retention is just a request to Oracle that it try to keep the UNDO data for a particular period of time. If there is no more space in the UNDO tablespace, Oracle is free to simply expire some UNDO data early (unless you happen to have specified RETENTION GUARANTEE when you created your UNDO tablespace but I've never heard of anyone that actually did that). So, in general, the risk is that if your UNDO tablespace is set to autoextend and some process generates a ton of UNDO, your UNDO tablespace may autoextend and consume more space on disk than you're comfortable with.

            Since you're on 10.2, Oracle will be using tuned_undoretention anyway which makes setting undo_retention even less important.

            Justin
            • 3. Re: High Undo_retention
              415289
              ORA-01555 caused by SQL statement below (SQL ID: 9h6zbrufuk2p7, Query Duration=*63680* sec, SCN: 0x0002.6a1d8fef):
              Appear issue with your query,fine tune it as its running from last 17 hrs and got failed.
              • 4. Re: High Undo_retention
                428027
                Publish the query, and we may find out a better aproach.

                Regards
                Helio Dias
                http://heliodias.wordpress.com
                • 5. Re: High Undo_retention
                  878451
                  Ok, i will put a part of trace with tkprof:

                  TKPROF: Release 10.2.0.5.0 - Production on Fri May 18 09:51:41 2012

                  Copyright (c) 1982, 2007, Oracle. All rights reserved.

                  Trace file: cmspw2_ora_1459_trace_mepcs602.trc
                  Sort options: prsela exeela fchela
                  ********************************************************************************
                  count = number of times OCI procedure was executed
                  cpu = cpu time in seconds executing
                  elapsed = elapsed time in seconds executing
                  disk = number of physical reads of buffers from disk
                  query = number of buffers gotten for consistent read
                  current = number of buffers gotten in current mode (usually for update)
                  rows = number of rows processed by the fetch or execute call
                  ********************************************************************************

                  select NVL(sum((IMPORTE_ML* (1-(2* CODIGO_OPERACION)))),0) ,
                  NVL(sum((IMPORTE_MR* (1-(2* CODIGO_OPERACION)))),0) into :b1,:b2
                  from
                  MOVTOS_CUENTAS where ((((EMISOR=:b3 and SUCURSAL_EMISOR=:b4) and PRODUCTO=
                  :b5) and NUMERO_CUENTA=:b6) and PERIODO_CIERRE=:b7)


                  call count cpu elapsed disk query current rows
                  ------- ------ -------- ---------- ---------- ---------- ---------- ----------
                  Parse 1 0.00 0.00 0 0 0 0
                  Execute 130000 135.75 135.51 0 0 0 0
                  Fetch 130000 2467.64 28115.43 14994471 22931122 0 130000
                  ------- ------ -------- ---------- ---------- ---------- ---------- ----------
                  total 260001 2603.39 28250.95 14994471 22931122 0 130000

                  Misses in library cache during parse: 1
                  Optimizer mode: ALL_ROWS
                  Parsing user id: 49


                  I don´t got the values of bind variables, another person do this statement, i am responsible just to check why this statement is taking so long.

                  Thanks yall.
                  • 6. Re: High Undo_retention
                    JustinCave
                    That's not the SQL statement that generated the ORA-01555 error, right?

                    The problem with this statement is that it's getting called 130,000 times. Each execution takes 0.2 seconds which seems at least vaguely reasonable (though you would know better than us if you'd expect a single execution to run in more like 0.02 seconds). About 90% of that time appears to be spent doing something other than using the CPU which almost certainly means that it's waiting on I/O.

                    In descending order of the probability that they'll help (and the benefit you get)

                    - If this is getting called in a loop where someone is calling it 130000 times with different bind variables, it's likely going to be much more efficient to do a join so that you don't have to do 130,000 single-row lookups.
                    - If you look at the query plan for a single execution, there may be opportunities to improve a single execution.
                    - If you're doing a lot of physical I/O, it may be possible to improve performance by increasing your SGA so that more blocks are cached.


                    Justin
                    • 7. Re: High Undo_retention
                      878451
                      Hi Justin, thanks for answering.

                      That's not the SQL statement that generated the ORA-01555 error, right?

                      The thing is: the developer said that he was doing a process, and it took about 17hs to finish with ORA-1555. So i asked him to do the process again, for me getting the trace.

                      When the trace was ready, i passed tkprof in it.

                      What i posted here, is one of many queries that have high consumption.

                      Just remembering that i am not the one who constructed the query, neither know the bind variables.

                      I´ve sent the tkprof to them, and they might develop a better query, but i am interested to know how can i solve the problem if i was the responsible for this.

                      I will check out your hints,

                      Thanks
                      • 8. Re: High Undo_retention
                        sb92075
                        In most cases some long running SELECT throws the ORA-01555 error.
                        SELECT is the victim; not the culprit.
                        The culprit is a session that does DML against the same table as the SELECT AND does "frequent" COMMIT;

                        Two alternatives exist to reduce or eliminate the ORA-01555.
                        1) speed up the SELECT so it completes in less time.
                        2) change the code doing the DML so that only a single COMMIT at the every end of the transaction

                        either of the two can prevent the ORA-01555 from being thrown.