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

About

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

Example

Creation of the table

[email protected]>create table i1 (n number primary key, v varchar2(10));

Table created.

[email protected]>create table i2 (n number primary key, v varchar2(10));

Table created.

[email protected]>create table map (n number primary key, i1 number referencing I1(n), i2 number referencing i2(n));

Table created.

[email protected]>create unique index IDX_MAP on MAP(i1,i2);

Index created.

[email protected]>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

[email protected]>set autotrace traceonly
[email protected]>/

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

[email protected]>set autotrace off

[email protected]>insert into i1
  2  select rownum, rpad('*',10,'*') from all_objects;

[email protected]>insert into i2
  2  select rownum, rpad('*',10,'*') from all_objects;

66649 rows created.

[email protected]>insert into map
  2  select rownum, rownum, rownum from all_objects;

66649 rows created.

[email protected]>set autotrace traceonly
[email protected]>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

[email protected]>analyze table i1 compute statistics;

Table analyzed.

[email protected]>analyze table i2 compute statistics;

Table analyzed.

[email protected]>analyze table map compute statistics;

Table analyzed.

[email protected]>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

[email protected]>create index i1_idx on i1(v);

Index created.

[email protected]>analyze table i1 compute statistics;

Table analyzed.

[email protected]>/

Table analyzed.

[email protected]>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

[email protected]>

Reference

  • Effective Oracle by Design by Tom Kyte

Powered by ComboStrap