6 Replies Latest reply: May 28, 2010 3:17 AM by Lubiez Jean-Valentin RSS

    excessive flashback log generates with select statement

    755676
      Hi everyone;
      We have some extractions taken from a "flashback on" database.
      Extractions are just select statements but when they are run, database produces excessive flashback logs.
      What may be the reason database produce flashback logs with just select statements?
      (It's certain that there are no insert-update-delete operations)

      Version: 10.2.0.4.3

      Thanks...
        • 1. Re: excessive flashback log generates with select statement
          Lubiez Jean-Valentin
          Hello,


          If you enable Flashback Database then Flashback Logs must be able to flashback your entire database including UNDO Tablespace.

          May be during the query execution some UNDO Segments are growing so as to guarantee the Read Consistency in your Database.

          How is your UNDO Tablespace ?


          Best regards,
          Jean-Valentin
          • 2. Re: excessive flashback log generates with select statement
            g777
            hi

            perhaps there are some background activities that generate entires into logs.
            I don't know how, but would be helpful to read/decode some of the logs content and find the "author".
            Maybe other flashback technics like flb transaction quey, flb query, ... can help to identify those hidden transactions...
            • 3. Re: excessive flashback log generates with select statement
              566761
              Do you do heavy update/delete before you select the statements ?

              I am not very sure if delayed block cleanout also have the same effect on flashback logs but the output below is leading me to think that way
              HR@ORACOS> select * from v$flashback_database_stat;
              
              BEGIN_TIME        END_TIME          FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
              ----------------- ----------------- -------------- ---------- ---------- ------------------------
              20100527 15:32:53 20100527 15:50:16      875266048 1207132160 2038729728                        0
              20100527 14:32:50 20100527 15:32:53      248160256  127295488  450139648               1.3215E+10
              20100527 13:32:48 20100527 14:32:50       10452992   15646720    4400640               1.5549E+10
              20100527 12:32:43 20100527 13:32:48      745693184  948461568 1311620608               2.2789E+10
              20100527 11:25:56 20100527 12:32:43     1262026752 1984741376 2358546432               2.7212E+10
              
              HR@ORACOS> set autotrace traceonly statistics
              HR@ORACOS>  update base_table_np set y='INVALID';
               commit;
              
              4021808 rows updated.
              
              
              Statistics
              ----------------------------------------------------------
                     2512  recursive calls
                  8341430  db block gets
                  4069140  consistent gets
                   120569  physical reads
               1908471980  redo size
                      848  bytes sent via SQL*Net to client
                      793  bytes received via SQL*Net from client
                        3  SQL*Net roundtrips to/from client
                        1  sorts (memory)
                        0  sorts (disk)
                  4021808  rows processed
              
              HR@ORACOS> set autotrace off;
              HR@ORACOS> select * from v$flashback_database_stat;  
              
              HR@ORACOS> 
              BEGIN_TIME        END_TIME          FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
              ----------------- ----------------- -------------- ---------- ---------- ------------------------
              20100527 15:32:53 20100527 16:00:36     1236664320 2021974016 4019910656                        0
              20100527 14:32:50 20100527 15:32:53      248160256  127295488  450139648               1.3215E+10
              20100527 13:32:48 20100527 14:32:50       10452992   15646720    4400640               1.5549E+10
              20100527 12:32:43 20100527 13:32:48      745693184  948461568 1311620608               2.2789E+10
              20100527 11:25:56 20100527 12:32:43     1262026752 1984741376 2358546432               2.7212E+10
              
              HR@ORACOS> set autotrace traceonly statistics
              HR@ORACOS> select * from base_table_np;
              4021808 rows selected.
              
              
              Statistics
              ----------------------------------------------------------
                      139  recursive calls
                        0  db block gets
                    53908  consistent gets
                     4404  physical reads
                  1652384  redo size                                                  ------->delayed block cleanout effect
                175008833  bytes sent via SQL*Net to client
                    88996  bytes received via SQL*Net from client
                     8045  SQL*Net roundtrips to/from client
                        4  sorts (memory)
                        0  sorts (disk)
                  4021808  rows processed
              
              HR@ORACOS> set autotrace off 
              HR@ORACOS> select * from v$flashback_database_stat;    ----flashback data size increases
              HR@ORACOS> 
              BEGIN_TIME        END_TIME          FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
              ----------------- ----------------- -------------- ---------- ---------- ------------------------
              20100527 15:32:53 20100527 16:01:11     1305264128 2054594560 4021728256                        0
              20100527 14:32:50 20100527 15:32:53      248160256  127295488  450139648               1.3215E+10
              20100527 13:32:48 20100527 14:32:50       10452992   15646720    4400640               1.5549E+10
              20100527 12:32:43 20100527 13:32:48      745693184  948461568 1311620608               2.2789E+10
              20100527 11:25:56 20100527 12:32:43     1262026752 1984741376 2358546432               2.7212E+10
              Basically what I do is I update a 4 million table big redo generated with flashback logs
              When I do select after the update I still see the redo generated because of delayed block cleanout but what I also see is the slight increase in flashback data size (check the first row of flashback_database_stat) which suits what you asking for. Select statement generates flashback log

              Tested on 11.2.0.1 with single active session on the db


              ---------
              Coskan Gundogar

              Blog: http://coskan.wordpress.com
              Twitter: http://www.twitter.com/coskan
              Linkedin: http://uk.linkedin.com/in/coskan
              ---------
              • 4. Re: excessive flashback log generates with select statement
                gary myers
                "May be during the query execution some UNDO Segments are growing so as to guarantee the Read Consistency in your Database."

                Undo may grow because a large transaction is consuming it and so it needs more space to write the stuff that it may need to rollback.
                Undo may grow because lots of small transactions are writing, and committing, changes but for which the undo may be required for read-consistency. However this is governed by undo_retention (eg I will kep undo for 90 minutes).

                Either way, the undo grows because of inserts/updates/deletes/merges activity. If that was happening at the same time, that activity would be responsible for the flashback logs growing, not the select.
                • 5. Re: excessive flashback log generates with select statement
                  755676
                  Hi everyone, thanks for your replies.
                  I checked the undo segments during the extractions and couldn't see them growing. I'll keep looking if something related with undo.

                  Coskan, your delayed block cleanout suggestion opened a new window for me. Actually, there are no huge update/delete before select but i'll try to check if delayed block cleanout is working during the select statements. Jonattan's "Cleane it up" post which i missed to read earlier helped me a lot on understanding the idea. (By the way, I'm Emre, i was signed in with this account and opened the thread in a hurry so i came up with this account. I hope you're doing well :))

                  Thanks
                  Emre Baransel
                  • 6. Re: excessive flashback log generates with select statement
                    Lubiez Jean-Valentin
                    Hello,


                    I know that you are in 10.2 but, do you have any old-fashioned Temporary DMT (Dictionary Managed Tablespace) with Datafile, instead of Temporary LMT with Tempfile ?

                    Do you use for this long lasting Query any Temporary Tables in a Permanent Tablespace ?


                    Hope this help.
                    Best regards,
                    Jean-Valentin