Friday, October 30, 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

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    oracle 19c new features – Automatic Indexing

    A major Oracle 19c autonomous database new Feature automatic indexing using DBMS_AUTO_INDEX some details and how to article.

    Recent Comments