10 Replies Latest reply on Sep 29, 2010 2:42 PM by 618702

    db file scattered read

    baskar.l
      Hi All,

      DB version is 10.2.0.4. When reading one of the thread latch: shared pool , latch: library cache i was studying the http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#i18202

      In the above guide under 10.3.3.5 Finding the Object Requiring I/O

      Found a few objects which comes again as a object requiring I/O. Those objects are frequently acessed. How can i avoid db file scattered read on these objects??

      baskar.l
        • 1. Re: db file scattered read
          You want to avoid db file scattered reads?

          Simple: walk to the wall and pull the plug from the database server. Problem solved.

          Sorry, but your question is just way too vague. In Oracle IO is inevitable. Also there are no silver bullets in Oracle, and those people who think there are silver bullets end up selling memory or Solid State Disks.

          -------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: db file scattered read
            680020
            Scattered read is caused by full table scan or full index scan and it can be reduced by
            1. optimize the SQL to reduce read operations
            2. tune DB_FILE_MULTIBLOCK_READ_COUNT parameter
            3. Improve I/O system
            • 3. Re: db file scattered read
              618702
              Dear baskar.l,

              Please read the following information that has been taken from the online Oracle documentation;

              "
              *db file scattered read*

              This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A db file scattered read issues a scattered read to read the data into multiple discontinuous memory locations. A scattered read is usually a multiblock read. It can occur for a fast full scan (of an index) in addition to a full table scan.

              The db file scattered read wait event identifies that a full scan is occurring. When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other. Such reads are called scattered read calls, because the blocks are scattered throughout memory. This is why the corresponding wait event is called 'db file scattered read'. Multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full scans into the buffer cache show up as waits for 'db file scattered read'.

              Check the following V$SESSION_WAIT parameter columns:

              * P1 - The absolute file number
              * P2 - The block being read
              * P3 - The number of blocks (should be greater than 1)

              Actions

              _On a healthy system, physical read waits should be the biggest waits after the idle waits._ However, also consider whether there are direct read waits (signifying full table scans with parallel query) or db file scattered read waits on an operational (OLTP) system that should be doing small indexed accesses.

              Other things that could indicate excessive I/O load on the system include the following:

              * Poor buffer cache hit ratio
              * These wait events accruing most of the wait time for a user experiencing poor response time

              +Managing Excessive I/O+

              There are several ways to handle excessive I/O waits. In the order of effectiveness, these are as follows:

              *1. Reduce the I/O activity by SQL tuning*
              *2. Reduce the need to do I/O by managing the workload*
              *3. Gather system statistics with DBMS_STATS package, allowing the query optimizer to accurately cost possible access paths that use full scans*
              *4. Use Automatic Storage Management*
              *5. Add more disks to reduce the number of I/Os for each disk*
              *6. Alleviate I/O hot spots by redistributing I/O across existing disks*

              See Also:

              Chapter 8, "I/O Configuration and Design"

              The first course of action should be to find opportunities to reduce I/O. Examine the SQL statements being run by sessions waiting for these events, as well as statements causing high physical I/Os from V$SQLAREA. Factors that can adversely affect the execution plans causing excessive I/O include the following:

              * Improperly optimized SQL
              * Missing indexes
              * High degree of parallelism for the table (skewing the optimizer toward scans)
              * Lack of accurate statistics for the optimizer
              * Setting the value for DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter too high which favors full scans

              Inadequate I/O Distribution

              Besides reducing I/O, also examine the I/O distribution of files across the disks. Is I/O distributed uniformly across the disks, or are there hot spots on some disks? Are the number of disks sufficient to meet the I/O needs of the database?

              See the total I/O operations (reads and writes) by the database, and compare those with the number of disks used. Remember to include the I/O activity of LGWR and ARCH processes.
              Finding the SQL Statement executed by Sessions Waiting for I/O

              Use the following query to determine, at a point in time, which sessions are waiting for I/O:

              SELECT SQL_ADDRESS, SQL_HASH_VALUE
              FROM V$SESSION
              WHERE EVENT LIKE 'db file%read';

              Finding the Object Requiring I/O

              To determine the possible causes, first query V$SESSION to identify the value of ROW_WAIT_OBJ# when the session waits for db file scattered read. For example:

              SELECT row_wait_obj#
              FROM V$SESSION
              WHERE EVENT = 'db file scattered read';

              To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION. For example:

              SELECT owner, object_name, subobject_name, object_type
              FROM DBA_OBJECTS
              WHERE data_object_id = &row_wait_obj;

              *db file sequential read*

              This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read.

              Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache. These waits would also show up as 'db file sequential read'.

              Check the following V$SESSION_WAIT parameter columns:

              * P1 - The absolute file number
              * P2 - The block being read
              * P3 - The number of blocks (should be 1)

              See Also:

              "db file scattered read" for information on managing excessive I/O, inadequate I/O distribution, and finding the SQL causing the I/O and the segment the I/O is performed on

              Actions

              On a healthy system, physical read waits should be the biggest waits after the idle waits. However, also consider whether there are db file sequential reads on a large data warehouse that should be seeing mostly full table scans with parallel query.

              * db file sequential read (single block read into one SGA buffer)
              * db file scattered read (multiblock read into many discontinuous SGA buffers)
              * direct read (single or multiblock read into the PGA, bypassing the SGA)

              When a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event. If the I/O subsystem does not support asynchronous I/Os, then each wait corresponds to a physical read request.

              If the I/O subsystem supports asynchronous I/O, then the process is able to overlap issuing read requests with processing the blocks already existing in the PGA. When the process attempts to access a block in the PGA that has not yet been read from disk, it then issues a wait call and updates the statistics for this event. Hence, the number of waits is not necessarily the same as the number of read requests (unlike db file scattered read and db file sequential read).

              Check the following V$SESSION_WAIT parameter columns:

              * P1 - File_id for the read call
              * P2 - Start block_id for the read call
              * P3 - Number of blocks in the read call

              Causes

              This happens in the following situations:

              * The sorts are too large to fit in memory and some of the sort data is written out directly to disk. This data is later read back in, using direct reads.
              * Parallel slaves are used for scanning data.
              * The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system.

              Actions

              The file_id shows if the reads are for an object in TEMP tablespace (sorts to disk) or full table scans by parallel slaves. This is the biggest wait for large data warehouse sites. However, if the workload is not a DSS workload, then examine why this is happening.
              Sorts to Disk

              Examine the SQL statement currently being run by the session experiencing waits to see what is causing the sorts. Query V$TEMPSEG_USAGE to find the SQL statement that is generating the sort. Also query the statistics from V$SESSTAT for the session to determine the size of the sort. See if it is possible to reduce the sorting by tuning the SQL statement. If WORKAREA_SIZE_POLICY is MANUAL, then consider increasing the SORT_AREA_SIZE for the system (if the sorts are not too big) or for individual processes. If WORKAREA_SIZE_POLICY is AUTO, then investigate whether to increase PGA_AGGREGATE_TARGET. See "PGA Memory Management".
              Full Table Scans

              If tables are defined with a high degree of parallelism, then this could skew the optimizer to use full table scans with parallel slaves. Check the object being read into using the direct path reads. If the full table scans are a valid part of the workload, then ensure that the I/O subsystem is configured adequately for the degree of parallelism. Consider using disk striping if you are not already using it or Automatic Storage Management (ASM).
              Hash Area Size

              For query plans that call for a hash join, excessive I/O could result from having HASH_AREA_SIZE too small. If WORKAREA_SIZE_POLICY is MANUAL, then consider increasing the HASH_AREA_SIZE for the system or for individual processes. If WORKAREA_SIZE_POLICY is AUTO, then investigate whether to increase PGA_AGGREGATE_TARGET.
              See Also:

              * "Managing Excessive I/O"
              * "PGA Memory Management"
              "

              http://download.oracle.com/docs/cd/B14117_01/server.101/b10752/instance_tune.htm

              Also;

              "
              *db file scattered read*

              Similar to db file sequential read, except that the session is reading multiple data blocks.

              Wait Time: The wait time is the actual time it takes to do all of the I/Os
              Parameter      Description
              file#      See "file#"
              block#      See "block#"
              blocks      The number of blocks that the session is trying to read from the file# starting at block#

              *db file sequential read*

              The session waits while a sequential read from the database is performed. This event is also used for rebuilding the control file, dumping datafile headers, and getting the database file headers.

              Wait Time: The wait time is the actual time it takes to do the I/O
              Parameter      Description
              file#      See "file#"
              block#      See "block#"
              blocks      This is the number of blocks that the session is trying to read (should be 1)
              "

              http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm

              Please see the Managing Excessive I/O header for resolving your issue.

              Hope That Helps.

              Ogan
              • 4. Re: db file scattered read
                Pavan Kumar
                Hi,

                If you have read the documentation about the "db file scattered read " - it continuous process from technical point of view - with respect to Business which run's 24*7. Yes, their would be some things which you need to take into account - like the transaction tables - indexes and efficient plans - workload management and hardware - scalability - considering above one's it not totally dealt with "db file scattered read " - which is actualy work going on with respect to customers accesing my DB- perhaps reducing can be carried out.

                HTH

                - Pavan Kumar N
                ORACLe 9i/10g - OCP
                RHCE - Certified Enterprize Linux 5.4
                • 5. Re: db file scattered read
                  baskar.l
                  hi,

                  If these frequently accessed objects and its blocks are read from file often, it will surely increase the execution of programs which uses these objects. Is that wise to keep those objects in the cache, so that db file scattered read on these objects can be avoided.

                  baskar.l
                  • 6. Re: db file scattered read
                    618702
                    Dear baskar.l,

                    I really don't know if the following links can answer your concerns or not but i think it will definately help you;

                    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1590999000346302363
                    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:253415112676

                    Ogan
                    • 7. Re: db file scattered read
                      Pavan Kumar
                      Hi,

                      Pinning objects - then try to check the touch count at segment level -accessing and you can get things/information required and you can decide - yes it can suffice - perhaps you can look into the changes DML - OLTP Businesss 24*7 which works - all through the day you can't pin the objects - try to check the requirement and peek level of Business and check the segments which are required so.


                      - Pavan Kumar N
                      ORACLe 9i/10g - OCP
                      RHCE - Certified Enterprize Linux 5.4
                      1 person found this helpful
                      • 8. Re: db file scattered read
                        baskar.l
                        Hi Ogan,

                        Thanks a lot for those links and suggestions..

                        baskar.l
                        • 9. Re: db file scattered read
                          baskar.l
                          Hi Pavan,

                          Thanks for your suggestions.

                          baskar.l
                          • 10. Re: db file scattered read
                            618702
                            Dear baskar.l,

                            You are welcome.

                            Regards.

                            Ogan