remote dba support
More
    HomeOracle TuningHow to Tune Full Table Scan?

    How to Tune Full Table Scan?

    Eliminating DB File Scattered Reads of Oracle Wait Event

    full table scanIn Oracle, a DB file scattered read normally indicates a full table scan. A DB file scattered read is the same type of wait event as DB file sequential read, except that Oracle is reading multiple data blocks. Depending on the setting for the DB_FILE_MULTIBLOCK_READ_COUNT parameter in your parameter file, multi-block reads are typically used on full table scans. The name “scattered read” is somewhat misleading, but it refers to the fact that multiple blocks are read into the database block buffers “scattered” throughout Oracle’s buffer memory.

    Some data structures are well-designed by database designers and the database runs very smoothly. But during daily operations, there are chances to add more tables in the database without indexes or useless indexes. Due to this reason, a full table scan is performed.

    - Advertisement -
    dbametrix

    Sometimes, the table doesn’t have indexes and doesn’t give any performance drawback, but while you are adding more and more data during daily operations those tables become problematic due to full table scans. It happens due to a lack of accurate indexes. If a full table scan is performed on a small table then it is not problematic, but when it occurs on a large table then obviously performance becomes degraded.

    The scattered read information is kept in the Oracle dynamic data dictionary view v$system_event, and you can easily interrogate scattered reads by selecting all values from this view.

    Whereas full table scans are acceptable and even desirable, in cases where Oracle is scanning a small table or retrieving most of the rows in a table in a traditional Oracle environment. If a small table contains a primary key index, then Oracle might degrade performance because of scanning more than one segment.

    If the table contains an index but doesn’t use then it is a problem with either poor table structure or SQL query writing. In both cases, you need to understand the data structure first and rectify the issue.

    - Advertisement -
    dbametrix

    When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.

    This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

    - Advertisement -
    dbametrix
    - Advertisment -
    remote dba services

    Most Popular