Table of Contents
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 :
- first, you must compute the statistic to give all information to the Oracle Database - Query optimizer in order to find the best Oracle Database - Execution/Query 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 gets statistic 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>
