Home » RDBMS Server » Performance Tuning » Undo tablespace(monitoring and avoiding ORA-01555 (oracle 11grw on linux)
Undo tablespace(monitoring and avoiding ORA-01555 [message #666325] Mon, 30 October 2017 01:35 Go to next message
dba4oracle
Messages: 9
Registered: June 2010
Junior Member
Hi Guys,

Please share best approaches for Undo tablespace(monitoring and avoiding ORA-01555
like dyanamically monitoring undo usage(script,sql) and if possible alerting if reaching threshold
also with latest version can snap shot too old be prevented by init parameters

like if large batch job is running which can take many days to finish,how to ensure undo tablespace not reached it max value and impact failing of batch job
(apart from increasing undo size and retention),how reliable are estimates given by views like undostats for required size of undo table space

Thanks

Re: Undo tablespace(monitoring and avoiding ORA-01555 [message #666328 is a reply to message #666325] Mon, 30 October 2017 02:26 Go to previous messageGo to next message
John Watson
Messages: 7150
Registered: January 2010
Location: Global Village
Senior Member
When you say Quote:
estimates given by views like undostats
are you referring to the V$UNDOSTAT view? It doesn't give estimates, it gives actuals. How are you getting estimates?
In general, there is no way to reduce the chance of errors other than increasing the tablespace size and the retention parameter. If that isn't good enough you will have to look at tuning the SQL and PL/SQL you are running, or adjusting the business process.
Re: Undo tablespace(monitoring and avoiding ORA-01555 [message #666331 is a reply to message #666328] Mon, 30 October 2017 07:04 Go to previous messageGo to next message
BlackSwan
Messages: 25719
Registered: January 2009
Location: SoCal
Senior Member
Post SQL & results that is used to ensure TEMP tablespace not reached it max value and impact failing of batch job
Re: Undo tablespace(monitoring and avoiding ORA-01555 [message #666342 is a reply to message #666331] Mon, 30 October 2017 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 25719
Registered: January 2009
Location: SoCal
Senior Member
>with latest version can snap shot too old be prevented by init parameters

Rarely is ORA-01555 result of init parameter values.
ORA-01555 most frequently cause by COMMIT inside LOOP.
Re: Undo tablespace(monitoring and avoiding ORA-01555 [message #666346 is a reply to message #666325] Mon, 30 October 2017 10:29 Go to previous messageGo to next message
gazzag
Messages: 905
Registered: November 2010
Location: Bristol, UK
Senior Member
Also, there is an article on ORA-01555 on this site here.
Re: Undo tablespace(monitoring and avoiding ORA-01555 [message #666446 is a reply to message #666325] Sat, 04 November 2017 23:11 Go to previous messageGo to next message
trantuananh24hg
Messages: 704
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
dba4oracle wrote on Mon, 30 October 2017 06:35
Hi Guys,

Please share best approaches for Undo tablespace(monitoring and avoiding ORA-01555
like dyanamically monitoring undo usage(script,sql) and if possible alerting if reaching threshold
also with latest version can snap shot too old be prevented by init parameters
How do you know the threshold of Undo? Said JW, did you estimate through v$_undostat? What was long SQL? What was undo_segment corrupt/offline/online? Many, many reasons caused the 1555, some time, it walked belong to ORA-0600 [kkpamRGet],[PTI 8.1].

Quote:

like if large batch job is running which can take many days to finish,how to ensure undo tablespace not reached it max value and impact failing of batch job
(apart from increasing undo size and retention),how reliable are estimates given by views like undostats for required size of undo table space

Thanks

@BlackSwan answered, and I believe, it's better to find the root cause, in case, almost the bad SQL, Oracle is often a victim.

A large batch job ran many days, it's a bad SQL, PL/SQL, even bad design. I really do not accept not well solution, such as -increase undo_retention, increase cursor open, increase cached_cursor, ..etc. Remember, limitation of resource, but unlimited of human abilities.

Example:

I have a batch job execute loading many rows from large table into clone one, I wrote PL/SQL, bulk collect
CREATE OR REPLACE PROCEDURE stpgview_proc (p_array_size IN PLS_INTEGER DEFAULT 1000)
IS
TYPE ARRAY IS TABLE OF STATS_PAGE_DAILY%ROWTYPE;
l_data ARRAY;

CURSOR c IS SELECT * FROM STATS_PAGE_DAILY where log_date is not null;

BEGIN
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO STATS_PAGE_DAILY1 VALUES l_data(i);

    EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
END stpgview_proc;

Now, I must investigate some thing:
Logical objects:
Source Table
- How is big the source table?
- Is the big table heap or partitioned?
- Does the source table used to be corresponding many DMLs?
- Has the source table got many contentions in time?
- How bad or good design constraint, index, trigger, the dependencies was in?

Target table
- What is the target table cloning from source? Is it created by select, using dpdump, golden gate, mview, ...etc?
- What is the purpose of target clone table? Does it only contain the rows from source and no impact to? Does it contain the rows and use for report? Has the target table got same constraint, index, trigger, ..etc as the source?

Logical batch job
- What is the best time to run batch job? The answer is above from result of logical objects investigation
- How many rows should I push to array? The answer is above from result of logical objects investigation. In my case, I push only 1000 rows because of large table, none partitioned, many contention usually impacts to.

Physical considering
- The target table will be used to reporting collection from many source, and I do not push it into OLTP database but DSS
- The target table did not need fast DML, so that, it was created on hold RAID 5 LUN but not RAID 1 or SSD.
- Sometime, I suggest to append the hint "insert append" into insert statement.

In my period tuning database time, I had got both of bad logical/physical design case, the accident caused broken database, the root cause was the bad design. ORA-0155 can push the database hang, it was recoverable but sometime cannot. The broken undo_segment was corrupt can be recoverable and should be in UNDO but not SYSTEM.

Good luck.

[Updated on: Sat, 04 November 2017 23:24]

Report message to a moderator

Re: Undo tablespace(monitoring and avoiding ORA-01555 [message #666527 is a reply to message #666342] Sat, 11 November 2017 03:49 Go to previous messageGo to next message
dba4oracle
Messages: 9
Registered: June 2010
Junior Member
Thanks
i want to explore,Set the UNDO tablespace in GUARANTEE mode.

what is issues if set like i feel dml can fail,but will it prevent undo error
Re: Undo tablespace(monitoring and avoiding ORA-01555 [message #666529 is a reply to message #666446] Sat, 11 November 2017 07:52 Go to previous messageGo to next message
BlackSwan
Messages: 25719
Registered: January 2009
Location: SoCal
Senior Member
[quote title=trantuananh24hg wrote on Sat, 04 November 2017 21:11]dba4oracle wrote on Mon, 30 October 2017 06:35


I have a batch job execute loading many rows from large table into clone one, I wrote PL/SQL, bulk collect
CREATE OR REPLACE PROCEDURE stpgview_proc (p_array_size IN PLS_INTEGER DEFAULT 1000)
IS
TYPE ARRAY IS TABLE OF STATS_PAGE_DAILY%ROWTYPE;
l_data ARRAY;

CURSOR c IS SELECT * FROM STATS_PAGE_DAILY where log_date is not null;

BEGIN
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO STATS_PAGE_DAILY1 VALUES l_data(i);

    EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
END stpgview_proc;

do NOT do in PL/SQL that which can be done in plain SQL.
Do as below
INSERT INTO stats_page_daily1 
SELECT * 
FROM   stats_page_daily 
WHERE  log_date IS NOT NULL; 

UNDO is a shared resource; just like TEMP.
UNDO needs to be sized such that no error get thrown for it being too small.
ORA-01555 error has NOTHING to do with the size of UNDO tablespace.
Re: Undo tablespace(monitoring and avoiding ORA-01555 [message #666530 is a reply to message #666529] Sat, 11 November 2017 08:02 Go to previous message
trantuananh24hg
Messages: 704
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, BlackSwan.
Swan, swan, the most famous ballet Swan Lake in the world, and I love this, so to you. Very Happy
Previous Topic: Hints
Next Topic: Performance Issue
Goto Forum:
  


Current Time: Wed Nov 22 21:50:45 CST 2017

Total time taken to generate the page: 0.02326 seconds