关于LOB的优化问题

gdpr-dba

数据库有几个很大的LOB字段，有几百G，根据AWR的报告，LOB段上有存在大量的物理读取和写入，不知道LOB段的IO可以如何优化？

Maclean Liu(刘相兵

ODM FINDING:

LOBS - Storage, Redo and Performance Issues  

Introduction
~~~~~~~~~~~~
  This is a short note on the internal storage of LOBs. The information
  here is intended to supplement the documentation and other notes
  which describe how to use LOBS. The focus is on the storage characteristics
  and configuration issues which can affect performance.

  There are 4 types of LOB:
           CLOB, BLOB, NCLOB        stored internally to Oracle
           BFILE                stored externally

  The note mainly discusses the first 3 types of LOB which as stored INTERNALLY
  within the Oracle DBMS. BFILE's are pointers to external files and
  are only mentioned briefly.  
  Examples of handling LOBs can be found in Note:47740.1


Attributes
~~~~~~~~~~
  There are many attributes associated with LOB columns. The aim here
  is to cover the fundamental points about each of the main attributes.
  The attributes for each LOB column are specified using the
  "LOB (lobcolname) STORE AS ..." syntax.

  A table containing LOBs (CLOB, NCLOB and BLOB) creates 2 additional
  disk segments per LOB column - a LOBINDEX and a LOBSEGMENT. These
  can be viewed, along with the LOB attributes, using the dictionary views:

        DBA_LOBS, ALL_LOBS or USER_LOBS

  which give the columns:

        OWNER              Table Owner
        TABLE_NAME         Table name
        COLUMN_NAME        Column name in the table
        SEGMENT_NAME       Segment name of the LOBSEGMENT
        INDEX_NAME         Segment name of the LOBINDEX
        CHUNK              Chunk size (bytes)
        PCTVERSION         PctVersion
        CACHE              Cache option of the LOB Segment        (yes/no)
        LOGGING            Logging mode of the LOB segment        (yes/no)
        IN_ROW             Whether storage in row is allowed         (yes/no)


Storage Parameters
~~~~~~~~~~~~~~~~~~

  The system determines which tablespace to use for LOB data and LOB index
  depending on your specification in the LOB storage clause:

    *If you do not specify a tablespace for the LOB data, then the tablespace
     of the table is used for the LOB data and index.
    *If you specify a tablespace for the LOB data, then both the LOB data and
     index use the tablespace that was specified.

  By default LOB segments are created in the same tablespace as the
  base table using the tablespaces default storage details. You can
  specify the storage attributes of the LOB segments thus:

  Eg: Create table DemoLob ( A number, B clob )
       LOB(b)
        STORE AS lobsegname (
          TABLESPACE lobsegts
          STORAGE (lobsegment storage clause)
          INDEX lobindexname (
                TABLESPACE lobidxts
                STORAGE ( lobindex storage clause )
          )
        )
        TABLESPACE tables_ts
        STORAGE( tables storage clause )
     ;

   The LOB INDEX could be stored separately from the lob segment in versions earlier than Oracle 8i.

    Tablespace for LOB Index in Non-Partitioned Table:

    When creating a table, if you specify a tablespace for the LOB index
    for a non-partitioned table, then your specification of the tablespace will
    be ignored and the LOB index will be co-located with the LOB data.
    Partitioned LOBs do not include the LOB index syntax.

  Unless you specify names for the LOB segments system generated names
  are used.


In ROW Versus Out of ROW
~~~~~~~~~~~~~~~~~~~~~~~~
  LOB columns can be allowed to store data within the row or not as detailed
  below. Whether in-line storage is allowed or not can ONLY be specified
  at creation time.

  "STORE AS ( enable storage in row )"
        Allows LOB data to be stored in the TABLE segment provided
        it is less than about 4000 bytes.  

        The actual maximum in-line LOB is 3964 bytes.

        If the lob value is greater than 3964 bytes then the LOB data is
        stored in the LOB SEGMENT (ie: out of line). An out of line
        LOB behaves as described under 'disable storage in row' except that
        if its size shrinks to 3964 or less the LOB can again be stored
        inline.

        When a LOB is stored out-of-line in an 'enable storage in row'
        LOB column between 36 and 84 bytes of control data remain in-line
        in the row piece.

        In-line LOBS are subject to normal chaining and row migration
        rules within Oracle. Ie: If you store a 3900 byte LOB in a row
        with a 2K block size then the row piece will be chained across
        two or more blocks.

        Both REDO and UNDO are written for in-line LOBS as they are part
        of the normal row data.



  "STORE AS ( disable storage in row )"
        This option prevents any size of LOB from being stored in-line.

        Instead a 20 byte LOB locator is stored in the ROW which gives
        a unique identifier for a LOB in the LOB segment for this column.

        The Lob Locator actually gives a key into the LOB INDEX which
        contains a list of all blocks (or pages) that make up the LOB.


        The minimum storage allocation for an out of line LOB is 1 Database
        BLOCK per LOB ITEM and may be more if CHUNK is larger than a
        single block.

        UNDO is only written for the column locator and LOB INDEX changes.

        No UNDO is generated for pages in the LOB SEGMENT.
        Consistent Read is achieved by using page versions.
        Ie: When you update a page of a LOB the OLD page remains and a
            new page is created. This can appear to waste space but
            old pages can be reclaimed and reused.


CHUNK size
~~~~~~~~~~
  "STORE AS ( CHUNK bytes ) "
        Can ONLY be specified at creation time.

        Values of CHUNK are in bytes and are rounded to the next
        highest multiple of DB_BLOCK_SIZE without erroring.
        Eg: If you specify a CHUNK of 3000 with a block size of 2K then
            CHUNK is set to 4096 bytes.

        "bytes" / DB_BLOCK_SIZE determines the unit of allocation of
        blocks to an 'out of line' LOB in the LOB segment.
        Eg: if CHUNK is 32K and the LOB is 'disable storage in row'  
            then even if the LOB is only 10 bytes long 32K will be
            allocated in the LOB SEGMENT.

        CHUNK does NOT affect in-line LOBS.



PCTVERSION
~~~~~~~~~~
  "STORE AS ( PCTVERSION n )"
        PCTVERSION can be changed after creation using:
                ALTER TABLE tabname MODIFY LOB (lobname) ( PCTVERSION n );

        PCTVERSION affects the reclamation of old copies of LOB data.
        This affects the ability to perform consistent read.

        If a session is attempting to use an OLD version of a LOB
        and that version gets overwritten (because PCTVERSION is too small)
        then the user will typically see the errors:
                ORA-01555: snapshot too old:
                                rollback segment number  with name "" too small
                ORA-22924: snapshot too old

        PCTVERSION can prevent OLD pages being used and force the segment
        to extend instead.

        Do not expect PCTVERSION to be an exact percentage of space as there
        is an internal fudge factor applied.


CACHE
~~~~~
  "STORE AS ( CACHE )" or "STORE AS ( NOCACHE )"
        This option can be changed after creation using:
                ALTER TABLE tabname MODIFY LOB (lobname) ( CACHE );
        or
                ALTER TABLE tabname MODIFY LOB (lobname) ( NOCACHE );

        With NOCACHE set (the default) reads from and writes to the
        LOB SEGMENT occur using direct reads and writes. This means that
        the blocks are never cached in the buffer cache and the the Oracle
        shadow process performs the reads/writes itself.
        The reads / writes show up under the wait events "direct path read"
        and "direct path write" and multiple blocks can be read/written at
        a time (provided the caller is using a large enough buffer size).

        When set the CACHE option causes the LOB SEGMENT blocks to
        be read / written via the buffer cache . Reads show up as
        "db file sequential read" but unlike a table scan the blocks are
        placed at the most-recently-used end of the LRU chain.

        The CACHE options for LOB columns is different to the CACHE
        option for tables as CACHE_SIZE_THRESHOLD does not limit the
        size of LOB read into the buffer cache. This means that extreme
        caution is required otherwise the read of a long LOB can effectively
        flush the cache.

        In-line LOBS are not affected by the CACHE option as they reside
        in the actual table block (which is typically accessed via the buffer
        cache any way).

        The cache option can affect the amount of REDO generated for
        out of line LOBS. With NOCACHE blocks are direct loaded and
        so entire block images are written to the REDO stream. If CHUNK
        is also set then enough blocks to cover CHUNK are written to REDO.
        If CACHE is set then the block changes are written to REDO.
        Eg: In the extreme case  'DISABLE STORAGE IN ROW  NOCACHE  CHUNK 32K'
            would write redo for the whole 32K even if the LOB was only
            5 characters long. CACHE would write a redo record describing the
            5 byte change (taking about 100-200 bytes).


LOGGING
~~~~~~~
   "STORE AS ( NOCACHE LOGGING )" or "STORE AS ( NOCACHE NOLOGGING )"
        This option can be changed after creation but the LOGGING / NOLOGGING
        attribute must be prefixed by the NOCACHE option. The CACHE option
          implicitly enables LOGGING.

        The default for this option is LOGGING.

        If a LOB is set to NOCACHE NOLOGGING then updates to the LOB SEGMENT
        are not logged to the redo logs. However, updates to in-line LOBS
        are still logged as normal. As NOCACHE operations use direct
        block updates then all LOB segment operations are affected.
        NOLOGGING of the LOB segment means that if you have to recover the
        database then sections of the LOB segment will be marked as corrupt
        during recovery.



Space required for updates
~~~~~~~~~~~~~~~~~~~~~~~~~~
  If a LOB is out-of-line then updates to pages in the LOB cause new
  versions of those pages to be created. Rollback is achieved by reverting
  back to the pre-updated page versions. This has implications on the
  amount of space required when a LOB is being updated as the LOB SEGMENT
  needs enough space to hold both the OLD and NEW pages concurrently in case
  your transaction rolls back.
  Eg: Consider the following:
        INSERT a large LOB                LOB SEGMENT extends take the new pages
        COMMIT;
        DELETE the above LOB                The LOB pages are not yet free as
                                        they will be needed in case of
                                        rollback.
        INSERT a new LOB                Hence this insert may require more
                                        space in the LOB SEGMENT
        COMMIT;                                Only after this point could the
                                        deleted pages be used.

Performance Issues
~~~~~~~~~~~~~~~~~~~
  Working with LOBs generally requires more than one round trip to the database.
  The application first has to obtain the locator and only then can perform
  operations against that locator. This is true for inline or out of line
  LOBS.

  The buffer size used to read / write the LOB can have a significant
  impact on performance, as can the SQL*Net packet sizes.
  Eg: With OCILobRead() a buffer size is specified for handling the LOB.
      If this is small (say 2K) then there can be a round trip to the database
      for each 2K chunk of the LOB. To make the issue worse the server will
      only fetch the blocks needed to satisfy the current request so may
      perform single block reads against the LOB SEGMENT. If however a larger
      chunk size is used (say 32K) then the server can perform multiblock
      operations and pass the data back in larger chunks.

  There is a LOB buffering subsystem which can be used to help improve
  the transfer of LOBs between the client and server processes. See the
  documentation for details of this.


BFILEs
~~~~~~
  BFILEs are quite different to internal LOBS as the only real storage
  issue is the space required for the inline locator. This is about 20 bytes
  PLUS the length of the directory and filename elements of the BFILENAME.

  The performance implications of the buffer size are the same as for internal
  LOBS.

Maclean Liu(刘相兵

也可以参考以下2个文档：

http://t.askmaclean.com/thread-785-1-1.html  LARGE ISSUES WITH LARGE OBJECTS
http://t.askmaclean.com/thread-786-1-1.html LOB performance Guidelines