Oracle – High Logical Reads Analysis

oracle

I have a table with 999 rows in 24 blocks.Ran a sql query for the first time which does a full table scan.From the execution statistics it shows 87 LIOS I'm confused why LIOS is high it's like Oracle is reading all blocks close to 3 times

Here arraysize is 500

    SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

      SQL> select num_rows,blocks,empty_blocks from user_tables
      2  where table_name like 'SALE_TAB';

      NUM_ROWS     BLOCKS EMPTY_BLOCKS
    ---------- ---------- ------------
           999         24            0

        SQL> @x
        Display execution plan for last statement for this session from library cache...

        PLAN_TABLE_OUTPUT
        ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        SQL_ID  ap1xwwqvvbfyd, child number 0
        -------------------------------------
        select * from sale_tab

        Plan hash value: 2430023251

        ----------------------------------------------------------------------------------------------------------------------
        | Id  | Operation         | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
        ----------------------------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT  |          |      1 |        |       |     8 (100)|    999 |00:00:00.02 |      25 |     21 |
        |   1 |  TABLE ACCESS FULL| SALE_TAB |      1 |    999 | 91908 |     8   (0)|    999 |00:00:00.02 |      25 |     21 |
        ----------------------------------------------------------------------------------------------------------------------


        13 rows selected.

        SQL> @sql_id ap1xwwqvvbfyd %
        Show SQL text, child cursors and execution stats for SQLID ap1xwwqvvbfyd child %

        HASH_VALUE  CH# SQL_TEXT
        ---------- ---- ------------------------------------------------------------------------------------------------------------------------------------------------------
        3082140621    0 select * from sale_tab


         CH# PARENT_HANDLE    OBJECT_HANDLE     PLAN_HASH     PARSES   H_PARSES EXECUTIONS    FETCHES ROWS_PROCESSED     CPU_MS     ELA_MS       LIOS       PIOS      SORTS USERS_EXECUTING
        ---- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------------
           0 00007FFD911A4238 00007FFD9C847038 2430023251          1          1          1          3            999     15.625     37.842         87         22          0               0

Best Answer

Because the database has to run additional recursive queries the first time it encounters a query, to parse it. It has to check if the given SQL is valid, it has to check the optimizer statistics, etc.

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set statistics_level=all;

Session altered.

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  3ghpkw4yp4dzm, child number 0
-------------------------------------
select count(*) from hr.employees

Plan hash value: 3580537945

-------------------------------------------------------------------------------------------
| Id  | Operation        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |      1 |        |      1 |00:00:00.01 |       1 |
|   1 |  SORT AGGREGATE  |              |      1 |      1 |      1 |00:00:00.01 |       1 |
|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |      1 |    107 |    107 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------


14 rows selected.

SQL> select buffer_gets, executions from v$sql where sql_id = '3ghpkw4yp4dzm';

BUFFER_GETS EXECUTIONS
----------- ----------
        220          1

We can clearly see from the plan statistics that it required a single buffer get, yet V$SQL shows 220. But now the query is parsed, if I run it again, buffer gets are increased only by the amount the query really uses:

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

SQL> select buffer_gets, executions from v$sql where sql_id = '3ghpkw4yp4dzm';

BUFFER_GETS EXECUTIONS
----------- ----------
        222          3

If you are interested what additional SQLs the database executes when parsing the query, you can enable SQL tracing and view the trace file:

SQL> select tracefile from v$process where addr = 
     (select paddr from v$session where sid = sys_context('userenv', 'sid'));

TRACEFILE
-------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ss18/SS18/trace/SS18_ora_14811.trc

SQL> alter system flush shared_pool;

System altered.

SQL> exec dbms_session.session_trace_enable(true, true);

PL/SQL procedure successfully completed.

SQL> -- run your query here, then view the trace file
Related Question