Hi,
I have some performance issues due to UNDO logs.
I am working under Oracle DB 12.2 and OLE 7.4
I have a UNDO tablespace at the moment of 9 GB it is increasing all the time as it is managed by Oracle.
The tablespace for data where the tables are saved is set to NOLOGING.
The big inserts are used with the hint /*+ APPEND */ on GTT although I do not deactivate and reactivate the Indexes. does this have an impact on the UNDO or only on performance ?
I have a lot of INSERT of 1 row only but it should not be generate a huge Undo
I have modified the DELETE statement by TRUNCATE
I have UPDATE that updates between 10 and 200 rows on tables that have between 500 and 4000 rows. In my opinion this should not require a UNDO tablespace of 9 GB.
As I was getting a lot of expired undo I have increased the default value of undo retention from 900s (15 mins) to 20027s (5.5 hours)
alter system set "undo_retention"=20027 scope=both sid='*';
- How can I identify the SQL statements that are creating so big UNDOs ?
- Is is possible to deactivate this functionality as I do not need to recover the DB all the data is calculated and loaded at any time I needed in case of failure ?
- If it is not possible to deactivate it what other modifications can I do in my code to avoid creating UNDO ?