Friday, August 14, 2020
    Home Oracle Tuning How 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 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 full table scan is performed. 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 then 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.

    - Advertisement -

    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 -
    - Advertisment -

    Most Popular

    ORA-01940: Cannot drop a user that is currently connected

    Detail explanation and solution of ORA-01940: Cannot drop a user that is currently connected error.

    Ubuntu and Oracle SQL Developer

    Blog post explains how to install and use of Oracle SQL Developer on Ubuntu linux server

    Create Schema in Oracle 18c XE

    Article explain how to create new user in Oracle 18c XE and grant some system privileges with connect as new schema user in database.

    Oracle Autonomous JSON database

    The blog article explains about What is the Oracle autonomous JSON database, usage and how to get it from Oracle Cloud.

    Recent Comments

    Margareta Combes on nosql Security Vulnerabilities
    Gordon Coleclough on Oracle Tuning Basics Part-1
    Gordon Coleclough on ASM Disk creation issue