About
To decrease the Oracle Database - Buffer IO (Logical IO), you have several possibilities :
- first, you must compute the statistic to give all information to the SQL Engine - Query Optimizer (Query Optimization) in order to find the best Sql Engine - (Physical|Execution) Plan.
- second, to add an index (Not always good)
- precompute sql statement in Oracle Database - Materialized view (Snapshot)
Articles Related
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>