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

> Database > Oracle Database

1 - About

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

3 - Example

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

3.2 - 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.

Advertising

3.3 - 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.

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

4 - Reference