1 2 Previous Next 28 Replies Latest reply on Sep 7, 2018 4:18 PM by 933257

    UNDO Read Issue

    933257

      Hi we are using version 11.2.0.4 of oracle Exadata. Many times , we are seeing high UNDO reads due to "delayed block cleanout" effect on certain queries and those were giving uneven response time, sometimes they run in few minutes and some other time the same query takes Hrs due to the firstly visiting those blocks. And the stats captured for the session were showing "transaction tables consistent reads - undo records applied" and "clean outs only - consistent read gets" as the top two for that session during the slowness period, which is giving evidence of delayed block clean out effect.

       

      We are doing array based insert to the base transaction tables through out the day 24/7, and we are seeing the major UNDO reads for the INDEX reads as index blocks were updated and we have the table not getting Updated/deleted but getting inserted 24/7. And sometimes the big reporting read queries face the UNDO read issue which appears to be delayed block cleanout effect. So we were thinking of any approach which will make the cleanout process finished before the long queries triggered. We were thinking as we gather stats once in 3hrs so the stats gather should help in cleaning the blocks, but as currently we have stats gather happening in parallel degree, so the cleanout must not be happening due to direct path read(as its happening in Parallel Degree) and as per my understanding it has to be happen in serial mode to do the block cleanout. But we have the base table daily range partitioned and each of these partitions are holding ~200million records and having size ~30GB+ each, so gathering stats(with cascade=>true, so that index block will also be visited) in serial will take significant amount of time. So need experts view , what would be the ideal way to get the index and table blocks cleaned frequently with minimal DB resource usage, so that it wont cause the reporting query slowed down which first touches those blocks?

       

      I was thinking,  if we collect stats on just one column to make it happen in lesser Db resource , that should also make it visit all the rows/tables blocks, but will that visit all the index blocks too?

        • 1. Re: UNDO Read Issue
          John Thorton

          933257 wrote:

          I was thinking, if we collect stats on just one column to make it happen in lesser Db resource , that should also make it visit all the rows/tables blocks, but will that visit all the index blocks too?

          THINKING? I doubt that.

          Oracle only deals with disk data at the BLOCK level. Oracle can only read & write whole data blocks.

           

          Please read & respond to URL below

          How to improve the performance of my query? / My query is running slow.

           

           

          SELECT will only read UNDO for those blocks that have changed since the start of the SELECT

           

          Current session can NEVER see uncommitted DML rows from different session.

           

          Do the "big reporting queries" utilize BIND variables?

           

          Realize & consider that a table can have more than 1 index associated with the table.

           

          > but will that visit all the index blocks too?

          It depends upon if the column in question is indexed.

          • 2. Re: UNDO Read Issue
            jgarry

            I'm not sure there is a general answer to this question, since Oracle is so clever these days. https://jonathanlewis.wordpress.com/2013/12/23/plan-changes/#comment-61829 

             

            Also consider there might be more than one thing going on, for example, the delayed block cleanout plus more standard concurrency issues, which could be helped by looking closely at what order and timing your processes require the same blocks and associated undo. If there is a lot of the latter, adding more access to the same blocks with some scheme to cleanout may make it worse.

            1 person found this helpful
            • 3. Re: UNDO Read Issue
              JohnWatson2

              Can you adjust your code such that the DMLs commit more frequently? So that the blocks are cleaned out in cache on commit?

              1 person found this helpful
              • 4. Re: UNDO Read Issue
                933257

                I will check if we can increase the frequency of COMMIT, so that the cleanout can happen along with Commit. But i think we need to be careful so that it wont result into any other issue like "log file sync" wait issue etc.

                 

                I don't remember exactly but somewhere i read , if by gathering stats in below way(auto_sample_size and gather for atleast one column) will also make the cleanout happen for all data/index block? Is this correct?

                 

                begin

                    dbms_stats.gather_table_stats('USER1',

                                                  'TAB1',

                                                   METHOD_OPT => 'FOR COLUMNS C1 size 254');

                end;

                /

                • 5. Re: UNDO Read Issue
                  933257

                  Actually every time we kill and rerun the same query finishing in BAU time(~10minutes), which exceeds ~4hrs+ sometimes during UNDO read. So we are thinking its mainly UNDO which is causing the delay in first/initial execution of the query. And the session statistics suggest the delayed block cleanout effect. Below is sample statistics for one of the long execution.

                   

                   

                  transaction tables consistent reads - undo records applied     23179196

                  cleanouts only - consistent read gets    24545

                  transaction tables consistent read rollbacks    275

                  cleanouts and rollbacks - consistent read gets     0

                  rollback changes - undo records applied    0

                  data blocks consistent reads - undo records applied    0

                  • 6. Re: UNDO Read Issue
                    933257

                    Just corrected the initial post , its version 11.2.0.4 Oracle Exadata.

                    • 7. Re: UNDO Read Issue
                      John Thorton

                      933257 wrote:

                       

                      Just corrected the initial post , its version 11.2.0.4 Oracle Exadata.

                      I'll ask again

                       

                      Do the "big reporting queries" utilize BIND variables?

                      • 8. Re: UNDO Read Issue
                        933257

                        No, the queries doesn't contain any bind variable. All literals. And i have validated there is no change in execution plan happened for these queries.

                        • 9. Re: UNDO Read Issue
                          Jonathan Lewis

                          What does BAU stand for ?

                           

                          Do you have any periods of the day (particularly around the time the randomly performing report runs) when you see a large number of very short transactions taking place in a very short (few minutes) interval ?

                           

                          Regards

                          Jonathan Lewis

                          • 10. Re: UNDO Read Issue
                            933257

                            Hi Jonathan, I mean to say "business as usual" or BAU time i.e the expected time(e.g.~10minutes for one of the query) in which the query used to finish. when you say "large number of very short transactions" are you asking for the DML/INSERT only on the base table? I see the hourly data load pattern into that table, not seeing any spike in that during the report slowness period. If you are asking about the significant change in COMMIT frequency of those insert query, i am not sure at this moment , i need to check on that. Not sure if any easy way to get this related stats from AWR history. We are doing batch insert into the base table from Java with an arraysize of ~1000. Mostly i see the issue occurs in the morning 2AM to 5AM, but again the issue also appeared yesterday for one of the report which ran at ~7AM too. Just wondering, if making the data fetch lesser would make this situation better, say ~15million rather ~25million?

                             

                            Another odd thing we saw a report failed with ora-01555 while it was fetching data from a partition which is 2days old, and we are no way writing to that partition in current day. Found one related bug, not sure if we are hittin that anyway.

                             

                            Delayed Block Cleanout is Not Working as Expected (Doc ID 1925688.1)

                            • 11. Re: UNDO Read Issue
                              933257

                              I executed below query in my database : seeing many "transaction tables consistent reads - undo records applied", is this normal?

                               

                              SELECT sn.name,
                                       ss.username,
                                       se.SID,
                                       VALUE
                                  FROM gv$session ss, gv$sesstat se, gv$statname sn
                                 WHERE     se.STATISTIC# = sn.STATISTIC# and ss.inst_id=se.inst_id and se.inst_id=sn.inst_id
                                       AND NAME IN
                                              ('cleanouts only - consistent read gets',
                                               'cleanouts and rollbacks - consistent read gets',
                                               'transaction tables consistent reads - undo records applied',
                                               'data blocks consistent reads - undo records applied',
                                               'rollback changes - undo records applied',
                                               'transaction tables consistent read rollbacks')
                                       AND se.SID = ss.SID 
                                       AND ss.status = 'ACTIVE'
                                       AND ss.username IS NOT NULL
                              ORDER BY VALUE DESC;
                              
                              
                              
                              rollback changes - undo records applied    SYS    1825    3373611
                              transaction tables consistent reads - undo records applied    TPD    2182    2575357
                              transaction tables consistent reads - undo records applied    TRP    966    240522
                              cleanouts only - consistent read gets    TRP    583    42596
                              cleanouts only - consistent read gets    SYS    1825    37272
                              cleanouts only - consistent read gets    TDRP    456    35376
                              cleanouts only - consistent read gets    TGG    1837    28646
                              cleanouts only - consistent read gets    TDRP    898    24816
                              cleanouts only - consistent read gets    TRP    966    19109
                              cleanouts only - consistent read gets    TDRP    1442    17257
                              data blocks consistent reads - undo records applied    TDRP    1222    17197
                              cleanouts only - consistent read gets    TDRP    1222    16733
                              data blocks consistent reads - undo records applied    TDRP    1442    15641
                              transaction tables consistent reads - undo records applied    TDRP    1762    13644
                              cleanouts only - consistent read gets    TDRP    1762    13500
                              data blocks consistent reads - undo records applied    TDRP    1762    11956
                              cleanouts only - consistent read gets    TDRP    231    9297
                              transaction tables consistent reads - undo records applied    TDRP    231    7226
                              transaction tables consistent reads - undo records applied    TDRP    456    4494
                              transaction tables consistent reads - undo records applied    TDRP    898    3622
                              transaction tables consistent reads - undo records applied    TDRP    1222    3208
                              transaction tables consistent reads - undo records applied    TPM    1422    3129
                              cleanouts only - consistent read gets    TRP    1411    2834
                              cleanouts only - consistent read gets    TDRP    1091    2764
                              cleanouts only - consistent read gets    TRP    1891    2448
                              data blocks consistent reads - undo records applied    TDRP    898    2388
                              transaction tables consistent reads - undo records applied    TDRP    1442    2198
                              data blocks consistent reads - undo records applied    SYS    1825    2134
                              
                              • 12. Re: UNDO Read Issue
                                933257

                                Few of blog says making the table FTS or index full  scan will help in cleanout for those table/index blocks, however there is chances that the same table FTS or index full scan may suffer slowness due to the block cleanout and also Oracle may do the direct path operation for these , then it will endup doing no cleanup.

                                 

                                Regarding stats collection with AUTO_SAMPLE_SIZE, as it will update the "Commit SCN" so the query should not traverse the transaction slot of the undo segment, is this correct assumption? Then will it be a good idea to trigger stats before running big reporting queries, just to avoid the block cleanout effect?

                                 

                                Increasing the frequency of COMMIT for the data load, can cause this issue more severe if the cleanout doesn't happen with each commit.

                                • 13. Re: UNDO Read Issue
                                  933257

                                  I captured the stats for two of the reporting query again within first 15minutes of their run. Additionally i captured the "Redo Size" also. This shows the symptom of delayed block cleanout too.

                                   

                                   

                                   

                                  redo size    USER1    1834    12681584

                                  transaction tables consistent reads - undo records applied    USER1    1834    50004

                                  cleanouts only - consistent read gets    USER1    1834    3518

                                  transaction tables consistent read rollbacks    USER1    1834    21

                                  cleanouts and rollbacks - consistent read gets    USER1    1834    0

                                  rollback changes - undo records applied    USER1    1834    0

                                  data blocks consistent reads - undo records applied    USER1    1834    0

                                   

                                   

                                   

                                  redo size    USER1    2189    23423180

                                  transaction tables consistent reads - undo records applied    USER1    2189    167663

                                  cleanouts only - consistent read gets    USER1    2189    15966

                                  transaction tables consistent read rollbacks    USER1    2189    34

                                  cleanouts and rollbacks - consistent read gets    USER1    2189    0

                                  rollback changes - undo records applied    USER1    2189    0

                                  data blocks consistent reads - undo records applied    USER1    2189    0

                                  • 14. Re: UNDO Read Issue
                                    933257

                                    Hi Jonathan, Can you guide me here, what should be the correct strategy to fix or have an work around for this? or is it that , this is not normal and we are possibly hitting a bug which we need to raise to Oracle support?

                                     

                                    I think reducing the volume of data fetch at one shot may help, so the block visit will be minimum and so the cleanout work and more chances are that the query will  finish and will not stuck(doing high amount of work to see the commit SCN) or fail with ora-01555.but i was thinking if we have any other option available to fix this?

                                     

                                    We are mainly seeing this for index blocks i.e. "INDEX RANGE SCAN" part in the plan is crawling with high "cell single block physical read". We have the base table only INSERT only. no update/delete happening on the base table.

                                    1 2 Previous Next