Oracle Database - Example of query Tuning (to decrease the logical I/O)

Card Puncher Data Processing

About

To decrease the Oracle Database - Buffer IO (Logical IO), you have several possibilities :

Example

Creation of the table

gerardnico@orcl>create table i1 (n number primary key, v varchar2(10));

Table created.

gerardnico@orcl>create table i2 (n number primary key, v varchar2(10));

Table created.

gerardnico@orcl>create table map (n number primary key, i1 number referencing I1(n), i2 number referencing i2(n));

Table created.

gerardnico@orcl>create unique index IDX_MAP on MAP(i1,i2);

Index created.

gerardnico@orcl>select *
  2  from i1, map, i2
  3  where i1.n = map.i2
  4   and  i2.n = map.i2
  5   and  i1.v = 'cx'
  6   and  i2.v = 'y';

no rows selected

gerardnico@orcl>set autotrace traceonly
gerardnico@orcl>/

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3200757213

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |    79 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |     1 |    79 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |              |     1 |    59 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL          | MAP          |     1 |    39 |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| I1           |     1 |    20 |     1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | SYS_C0027592 |     1 |       |     0   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | I2           |     1 |    20 |     1   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN          | SYS_C0027593 |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("I1"."V"='cx')
   5 - access("I1"."N"="MAP"."I2")
   6 - filter("I2"."V"='y')
   7 - access("I2"."N"="MAP"."I2")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        578  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Insertion of the data

gerardnico@orcl>set autotrace off

gerardnico@orcl>insert into i1
  2  select rownum, rpad('*',10,'*') from all_objects;

gerardnico@orcl>insert into i2
  2  select rownum, rpad('*',10,'*') from all_objects;

66649 rows created.

gerardnico@orcl>insert into map
  2  select rownum, rownum, rownum from all_objects;

66649 rows created.

gerardnico@orcl>set autotrace traceonly
gerardnico@orcl>select *
  2  from i1, map, i2
  3  where i1.n = map.i2
  4   and  i2.n = map.i2
  5   and  i1.v = 'cx'
  6   and  i2.v = 'y';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2430328108

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    79 |   119   (6)| 00:00:02 |
|   1 |  NESTED LOOPS                |              |     1 |    79 |   119   (6)| 00:00:02 |
|*  2 |   HASH JOIN                  |              |     3 |   177 |   116   (6)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | I1           |     3 |    60 |    58   (6)| 00:00:01 |
|   4 |    TABLE ACCESS FULL         | MAP          | 58951 |  2245K|    57   (4)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| I2           |     1 |    20 |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | SYS_C0027593 |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("I1"."N"="MAP"."I2")
   3 - filter("I1"."V"='cx')
   5 - filter("I2"."V"='y')
   6 - access("I2"."N"="MAP"."I2")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     133491  consistent gets
          0  physical reads
        116  redo size
        578  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

For a query that return no rows, 133491 Oracle Database - Consistent (Read get|Buffer Mode) (CR) is very high.

Let's analyze now the tables.

Analyze of the tables

gerardnico@orcl>analyze table i1 compute statistics;

Table analyzed.

gerardnico@orcl>analyze table i2 compute statistics;

Table analyzed.

gerardnico@orcl>analyze table map compute statistics;

Table analyzed.

gerardnico@orcl>select *
  2  from i1, map, i2
  3  where i1.n = map.i2
  4   and  i2.n = map.i2
  5   and  i1.v = 'cx'
  6   and  i2.v = 'y';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2430328108

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    40 |   118   (6)| 00:00:02 |
|   1 |  NESTED LOOPS                |              |     1 |    40 |   118   (6)| 00:00:02 |
|*  2 |   HASH JOIN                  |              |     1 |    26 |   117   (6)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | I1           |     1 |    14 |    58   (6)| 00:00:01 |
|   4 |    TABLE ACCESS FULL         | MAP          | 66649 |   781K|    57   (4)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| I2           |     1 |    14 |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | SYS_C0027593 |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("I1"."N"="MAP"."I2")
   3 - filter("I1"."V"='cx')
   5 - filter("I2"."V"='y')
   6 - access("I2"."N"="MAP"."I2")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        238  consistent gets
          0  physical reads
          0  redo size
        578  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

It's better.

Creation of an index on I1

gerardnico@orcl>create index i1_idx on i1(v);

Index created.

gerardnico@orcl>analyze table i1 compute statistics;

Table analyzed.

gerardnico@orcl>/

Table analyzed.

gerardnico@orcl>select *
  2  from i1, map, i2
  3  where i1.n = map.i2
  4   and  i2.n = map.i2
  5   and  i1.v = 'cx'
  6   and  i2.v = 'y';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 996891587

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |    40 |    62   (7)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |     1 |    40 |    62   (7)| 00:00:01 |
|*  2 |   HASH JOIN                   |              |     1 |    26 |    61   (7)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| I1           |     1 |    14 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | I1_IDX       |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | MAP          | 66649 |   781K|    57   (4)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | I2           |     1 |    14 |     1   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN          | SYS_C0027593 |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("I1"."N"="MAP"."I2")
   4 - access("I1"."V"='cx')
   6 - filter("I2"."V"='y')
   7 - access("I2"."N"="MAP"."I2")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        578  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

gerardnico@orcl>

Reference





Discover More
Card Puncher Data Processing
Oracle Database - Buffer IO (Logical IO)

A buffer is a container for data. A logical I/O, also known as a buffer I/O, refers to reads and writes of buffers in the buffer cache. When a requested buffer is not found in memory, the database performs...



Share this page:
Follow us:
Task Runner