Forum Stats

  • 3,750,344 Users
  • 2,250,158 Discussions
  • 7,866,940 Comments

Discussions

How to deactivate the UNDO and REDO ?

Filipe RB
Filipe RB Member Posts: 121 Blue Ribbon
edited Mar 9, 2018 1:05PM in SQL & PL/SQL

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 ?
Tagged:
BEDE

Best Answer

  • Filipe RB
    Filipe RB Member Posts: 121 Blue Ribbon
    edited Mar 9, 2018 1:05PM Accepted Answer

    I fixed the issue increasing the UNDO tablespace from 9GB to 16GB so Oracle does not have to increase it.

    Insert /*+ append */ will not work for single row some types of inserts. The hint makes only sense if more than 1 row is inserted. Make sure to do bulk inserts, they are WAY faster than single or little row inserts. You can test if a direct path insert is done, by doing a select directly after the insert in the same session. You should get an error like this one when trying to select

    "ORA-12838: cannot read/modify an object after modifying it in parallel"

    Indeed I was getting the above error and had to commit the data in order to do a select on the table.

Answers

  • GregV
    GregV Member Posts: 3,056 Gold Crown
    edited Mar 9, 2018 3:32AM

    Hi,

    Why are you concern with the undo size? Is it giving you some trouble? Having gigabytes of undo is not a problem. If I'm not wrong you can even set local undo in PDBs now with 12.2, which is your version.

    You would need to change your undo_retention parameter if you want a larger flashback capacity. And it would make sense if you want to guarantee it in case of flashback usage.

    You need to be concerned with undo only if you get errors regarding undo.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Mar 9, 2018 3:31AM
    user12087924 wrote:Hi,I have some performance issues due to UNDO logs.I am working under Oracle DB 12.2 and OLE 7.4I 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 UndoI have modified the DELETE statement by TRUNCATEI 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 ?

    Are you sure?

    How have you identified your performance problem is due to undo when you can’t even see where the undo is being generated? Are you guessing? (it’s okay to admit this, it’s the first step) Have you seen errors that mention undo/rollback and so you think thats where the time is going? (Hint: it’s usually not)

    A few comments, GTTs are generally unnecessary, appending one row at a time is just playing with fire - you’re asking Oracle to create an extent per row, that‘s a ridiculous amount of wasted space.

    Append on a GTT won’t create undo or redo for the table blocks but it will for indexes

    Whats wrong with expired undo? It’s just undo that Oracle can overwrite.

    If you dont need undo that you don’t need consistency, you might as well be using a shared json document

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,570 Red Diamond
    edited Mar 9, 2018 4:38AM

    How to deactivate undo and redo?

    That is like asking how to deactivate a car's steering wheel, as the road being driven on is straight, and there is no need to turn the car, left, or right.

    BEDE
  • Filipe RB
    Filipe RB Member Posts: 121 Blue Ribbon
    edited Mar 9, 2018 5:15AM

    How have you identified your performance problem is due to undo when you can’t even see where the undo is being generated?

    I have identified it when I saw some statement to increase the UNDO tablespace. So yes every time Oracle increases the Undo tablespace it is affecting my SQL execution.

    A few comments, GTTs are generally unnecessary

    when should we use and not use GTT ?

    I used it because I read somewhere that it does not creates UNDO and therefore it is faster than using normal tables.

    and according to some analysis done on this url: https://recurrentnull.wordpress.com/2013/06/30/direct-path-nologging-ctas-and-gtt-a-comparison-of-undo-and-redo-generate…

    direct pathnologgingredo (bytes)undo (bytes)blocks
    insert into <table> select * fromnono928,535,94835,028,304113117
    insert into <table> select * from nologgingnoyes928,664,19235,027,486113117
    insert /*+ append */ into <table> select * fromyesno937,730,61276,952113789
    insert /*+ append */ into <table> select * from nologgingyesyes772,91676,644113789
    create table <table> as select * fromyesno938,164,896157,080113789
    create table <table> as select * from nologgingyesyes962,696121,376113789
    insert into <GTT> select * fromnoyes49,459,28434,941,516112702
    insert into <GTT> select * from nologgingyesyes42080112702

    The solution that use less UNDO and REDO is the one with insert into GTT.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Mar 9, 2018 5:42AM
    user12087924 wrote:How have you identified your performance problem is due to undo when you can’t even see where the undo is being generated?I have identified it when I saw some statement to increase the UNDO tablespace. So yes every time Oracle increases the Undo tablespace it is affecting my SQL execution.A few comments, GTTs are generally unnecessarywhen should we use and not use GTT ? I used it because I read somewhere that it does not creates UNDO and therefore it is faster than using normal tables.and according to some analysis done on this url: https://recurrentnull.wordpress.com/2013/06/30/direct-path-nologging-ctas-and-gtt-a-comparison-of-undo-and-redo-generate… direct pathnologgingredo (bytes)undo (bytes)blocksinsert into <table> select * fromnono928,535,94835,028,304113117insert into <table> select * from nologgingnoyes928,664,19235,027,486113117insert /*+ append */ into <table> select * fromyesno937,730,61276,952113789insert /*+ append */ into <table> select * from nologgingyesyes772,91676,644113789create table <table> as select * fromyesno938,164,896157,080113789create table <table> as select * from nologgingyesyes962,696121,376113789insert into <GTT> select * fromnoyes49,459,28434,941,516112702insert into <GTT> select * from nologgingyesyes42080112702The solution that use less UNDO and REDO is the one with insert into GTT.

    Just because UNDO is being generated, doesn't mean it's the cause of any problem. What is the problem? Performance? Of what?

    How much undo and redo gets generated when you use a subquery in the final query rather than populating the data into a GTT?

  • GregV
    GregV Member Posts: 3,056 Gold Crown
    edited Mar 9, 2018 6:00AM

    Undo will increase for sure, no matter what. Are you actually experiencing errors due to undo? If not, then leave it alone.

    Now about using GTTs. I use them when I have to store temporary calculations that will be used for final results. The great thing about GTTs is that their contents pertains to the session, so 2 sessions using the same GTT will see only their own contents. This is great when using the same GTT in DIY parallelism.

    It's true that GTT will generate quite less redo, almost none if using insert /*+ append */, but you'll still need to somehow read/transfer their contents into a permanent table or an output of some kind.

  • Sven W.
    Sven W. Member Posts: 10,518 Gold Crown
    edited Mar 9, 2018 6:31AM

    Some comments.

    If the tablespace, where your table AND your index segements are, is set to NOLOGGING, then all inserts will only create minimal undo/redo when inserting into that tablespace. So check your tables and indexes where they are and if the tablespace is really set to nologging.

    So you never need to write NOLOGGING in a select statement (which will probabaly not work anyways).

    Insert /*+ append */ will not work for single row some types of inserts. The hint makes only sense if more than 1 row is inserted. Make sure to do bulk inserts, they are WAY faster than single or little row inserts. You can test if a direct path insert is done, by doing a select directly after the insert in the same session. You should get an error like this one when trying to select
    "ORA-12838: cannot read/modify an object after modifying it in parallel"

    There is a decent chance that UNDO is not your main problem. So far you failed to show any measurements that indicate such an issue.

    Maybe you just have a poorly written select, that does a merge join cartesian and spills some sort operation onto disk.

  • Filipe RB
    Filipe RB Member Posts: 121 Blue Ribbon
    edited Mar 9, 2018 1:05PM Accepted Answer

    I fixed the issue increasing the UNDO tablespace from 9GB to 16GB so Oracle does not have to increase it.

    Insert /*+ append */ will not work for single row some types of inserts. The hint makes only sense if more than 1 row is inserted. Make sure to do bulk inserts, they are WAY faster than single or little row inserts. You can test if a direct path insert is done, by doing a select directly after the insert in the same session. You should get an error like this one when trying to select

    "ORA-12838: cannot read/modify an object after modifying it in parallel"

    Indeed I was getting the above error and had to commit the data in order to do a select on the table.

This discussion has been closed.