1 2 Previous Next 26 Replies Latest reply: Jun 12, 2008 11:14 PM by 639907 Go to original post RSS
      • 15. Re: append and parallel hint
        591186
        From the post.
        nologging/logging
        • 16. Re: append and parallel hint
          582889
          Using append hint writes directly to the database files I think no log generated, because we bypass the buffer cache
          Therefore there will be no diffrence if we use nologging option with append hint

          Right?
          • 17. Re: append and parallel hint
            mbobak
            Um. no.

            APPEND or PARALLEL hints invoke direct path load. This means blocks are allocated from above the HWM. That is, blocks that do not, and never have had any rows in them. For that reason, Oracle does not generate UNDO. (There's no need for a 'before image', since the 'before image is that the block didn't exist in the segment.) Redo is still generated for direct path load, unless NOLOGGING is also set.

            If nologging is set, then no REDO will be generated. The exception there is recursive space transactions MUST generate redo to guarantee that the database can be recovered during instance recovery, if there's a crash during nologging direct path load, and block invalidation records are written to the redo log (instead of the actual redo for the blocks), to cover the case of media recovery following a nologging direct path load from a backup taken before the nologging load. If you do a recovery, there's no data in those blocks, since the changes were not logged to redo in the first place, but the block invalidation records were logged, and when applied, will mark the blocks as logically corrupt. That's all Oracle can do, since the data is not available in the redo stream. Those blocks being marked logically corrupt means, any access to them would raise an ORA-26040.

            So, it looks like this:
            direct path load + logging = no undo, but full redo
            direct path load + nologging = no undo, almost no redo (exceptions are space transactions and block invalidation record)
            conventional path load + loggging = full undo, full redo
            conventional path load + nologging = full undo, full redo (i.e. nologging has no effect if direct path is not used)
            • 18. Re: append and parallel hint
              247514
              3. No all DML operations can make use of
              NOLOGGING,regular DML (update/insert/delete) will
              generate redo disregard NOLOGGING setting.

              So even the table is in nologging mode,,regular DML
              (update/insert/delete) will still generate
              redo.Correct?

              sing append hint writes directly to the database
              files I think no logging needed, because we bypass
              the buffer cache right?
              What's your think is wrong. Please check Oracle concept, if it by pass the buffer cache or not have nothing to do with redo generation.
              Don't just think, read Oracle document, understand the concept and find approve.
              • 19. Re: append and parallel hint
                582889
                Without makıng alter table nologgıng
                can I use nologgıng like this?

                insert /*+ APPEND NOLOGGING */
                • 20. Re: append and parallel hint
                  26741
                  no, there is no such valid hint as "NOLOGGING". Oracle will silently ignore
                  the "NOLOGGING" although it will use the "APPEND".
                  • 21. Re: append and parallel hint
                    582889
                    http://www.oracle.com/technology/oramag/oracle/06-sep/o56partition.html

                    this link says opposite

                    insert /*+ APPEND NOLOGGING */
                    into trans_summary
                    select product_code, sum(trans_amount)
                    from trans
                    group by product_code;
                    • 22. Re: append and parallel hint
                      26741
                      I'm not sure if the NOLOGGING hint appears in 11g -- I can't find it.

                      However, see MetaLink Note#285285.1

                      Removed suggestion that the note (OTN article) referred
                      to 11g. It seems to be a version independent article
                      about Partitioning.
                      I've sent emails to OTN and the author of that article asking him to confirm if the NOLOGGING does work as he
                      suggests.
                      T
                      • 23. Re: append and parallel hint
                        SomeoneElse
                        It doesn't appear to have any effect on redo logging:
                        SQL> insert /*+ APPEND */ into t select * from all_objects;

                        53759 rows created.


                        Statistics
                        ----------------------------------------------------------
                              53046  recursive calls
                               1413  db block gets
                             342777  consistent gets
                                  2  physical reads
                        60052 redo size
                                903  bytes sent via SQL*Net to client
                               1018  bytes received via SQL*Net from client
                                  4  SQL*Net roundtrips to/from client
                               3531  sorts (memory)
                                  0  sorts (disk)
                              53759  rows processed

                        SQL> truncate table t;

                        Table truncated.

                        SQL> insert /*+ APPEND NOLOGGING */ into t select * from all_objects;

                        53759 rows created.


                        Statistics
                        ----------------------------------------------------------
                              53046  recursive calls
                               1412  db block gets
                             342777  consistent gets
                                  2  physical reads
                        60004 redo size
                                903  bytes sent via SQL*Net to client
                               1028  bytes received via SQL*Net from client
                                  4  SQL*Net roundtrips to/from client
                               3531  sorts (memory)
                                  0  sorts (disk)
                              53759  rows processed
                        This is 11.1.0.6

                        Message was edited by: SomeoneElse

                        (forgot the Oracle version)
                        • 24. Re: append and parallel hint
                          SomeoneElse
                          And here is the phantom NOLOGGING hint vs. no hint at all:
                          SQL> insert /*+ NOLOGGING */ into t select * from all_objects;

                          53759 rows created.


                          Statistics
                          ----------------------------------------------------------
                                53043  recursive calls
                                 7930  db block gets
                               344235  consistent gets
                                    2  physical reads
                          6613492 redo size
                                  919  bytes sent via SQL*Net to client
                                 1021  bytes received via SQL*Net from client
                                    4  SQL*Net roundtrips to/from client
                                 3531  sorts (memory)
                                    0  sorts (disk)
                                53759  rows processed

                          SQL> truncate table t;

                          Table truncated.

                          SQL> insert into t select * from all_objects;

                          53759 rows created.


                          Statistics
                          ----------------------------------------------------------
                                53032  recursive calls
                                 7904  db block gets
                               344217  consistent gets
                                    2  physical reads
                          6612788 redo size
                                  919  bytes sent via SQL*Net to client
                                 1004  bytes received via SQL*Net from client
                                    4  SQL*Net roundtrips to/from client
                                 3531  sorts (memory)
                                    0  sorts (disk)
                                53759  rows processed
                          This is 11.1.0.6
                          • 25. Re: append and parallel hint
                            247514
                            http://www.oracle.com/technology/oramag/oracle/06-sep/
                            o56partition.html

                            this link says opposite

                            insert /*+ APPEND NOLOGGING */
                            into trans_summary
                            select product_code, sum(trans_amount)
                            from trans
                            group by product_code;
                            There's no such nologging hint in 10g as well, Oracle will simply ignore it.

                            http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements006.htm#SQLRF00219
                            • 26. Re: append and parallel hint
                              639907
                              Dear Ricardinho,

                              Please take a look in this link:

                              http://oraclenz.com/__oneclick_uploads/2008/06/redo_reduction_v_15.pdf

                              Regards,

                              Francisco Munoz Alvarez
                              1 2 Previous Next