Real Life Index Usage and Index Performance Test in Oracle Database

Hi,

I will explain Real Life Index Usage and Index Performance Test in Oracle Database in this article.

 

If you don’t know What is the Index and how to create it, read following article.

Index Types and Index Create in Oracle Database

Let’s start make a Real life example about Index create and its performance effect.

 

When I query my customer table, there are 100 million records as follows.

SQL> select count(*) from MSDEVECI.CUSTOMER;
COUNT(*)
 ----------
 100000000
SQL>

The script and sample data of this sample table are as follows.

MSDEVECI.MUSTERI table in the Screenshots Means MSDEVECI.CUSTOMER. So MUSTERI and CUSTOMER tables are the same.

 

CREATE TABLE MSDEVECI.MUSTERI
 (
 MUSTERI_ID NUMBER,
 ADI VARCHAR2(50 BYTE),
 TEL_NO NUMBER,
 ADRES VARCHAR2(100 BYTE),
 SEHIR VARCHAR2(50 BYTE),
 ABONE_TARIHI DATE,
 YORUM VARCHAR2(150 BYTE)
 )
 TABLESPACE USERS
 RESULT_CACHE (MODE DEFAULT)
 PCTUSED 0
 PCTFREE 10
 INITRANS 1
 MAXTRANS 255
 STORAGE (
 INITIAL 64K
 NEXT 1M
 MINEXTENTS 1
 MAXEXTENTS UNLIMITED
 PCTINCREASE 0
 BUFFER_POOL DEFAULT
 FLASH_CACHE DEFAULT
 CELL_FLASH_CACHE DEFAULT
 )
 LOGGING
 NOCOMPRESS
 NOCACHE
 NOPARALLEL
 MONITORING;

 

When we examine the above script of the sample table, there is no Index in this table.

 

When I query the customer that musteri_id is 93 as follows. When the query is completed, the response time of this query is 5 minutes and 56 seconds, although just 1 record is queried as shown below. This duration is too much and unacceptable value for Production databases. Both system resources are consumed unnecessarily, and with different waiting times, users become inoperable.

 

SQL> set timing on
 SQL> select * from MSDEVECI.CUSTOMER where musteri_id=93;
MUSTERI_ID       ADI             TEL_NO       ADRES        SEHIR      ABONE_TARIH              YORUM
 ---------- -------------------------------------------------- ---------- ------------------------------------------------------------------------------------------
 93            93.MUSTERI      5429999999     Sincan       Ankara      01-JAN-13   ABONELER TEST AMACLI YUKLENMISTIR
Elapsed: 00:05:56.16
 SQL>

Execution plan of the above query is as follows. Query performs Table Access full 

SQL> set autotrace on
SQL> select * from MSDEVECI.CUSTOMER where musteri_id=93;
MUSTERI_ID ADI TEL_NO
---------- -------------------------------------------------- ----------
ADRES
--------------------------------------------------------------------------------
SEHIR ABONE_TAR
-------------------------------------------------- ---------
YORUM
--------------------------------------------------------------------------------
 93 93.MUSTERI 5429999999
Sincan
Ankara 01-JAN-13
ABONELER TEST AMACLI YUKLENMISTIR

Execution Plan
----------------------------------------------------------
Plan hash value: 2659560815
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 364K (1)| 01:12:52 |
|* 1 | TABLE ACCESS FULL| MUSTERI | 1 | 86 | 364K (1)| 01:12:52 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MUSTERI_ID"=93)

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

 

 

Now let’s create an index on our customer table and after the index is create, query the same select and see differences

 

 

When creating the index, I create the index online and parallel using the following script depending on the number of cpu the system has.

SQL> create index MID_IX on MSDEVECI.CUSTOMER(musteri_id) parallel 4 online;
 Index created.

After creating this Index, check whether the Index is created as follows.

SQL> select * from dba_indexes where table_name='MUSTERI';  -- MUSTERI and CUSTOMER same table

 

After the index is created, the above query which musteri_id is 93 query run as follows ,  you can see that the query runs in a very much less than 1 second.

 

SQL> select * from MSDEVECI.CUSTOMER where musteri_id=93;
MUSTERI_ID       ADI           TEL_NO       ADRES         SEHIR      ABONE_TARIH             YORUM
---------- -------------------------------------------------- ---------- ------------------------------------------------------------------------------------------
    93        93.MUSTERI    5429999999      Sincan        Ankara       01-JAN-13   ABONELER TEST AMACLI YUKLENMISTIR
Elapsed: 00:00:00.01
SQL>   

When we look at the execution plan of this query, it looks like the following. Query performs Index range scan instead of Full table scan.

SQL> set autotrace traceonly
SQL> select * from MSDEVECI.CUSTOMER where musteri_id=93
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3780290458
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MUSTERI | 1 | 86 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MID_IX | 1 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MUSTERI_ID"=93)

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

You can see the difference between the indexed table and non indexed table. when the index is used, system resources are not wasted too much and our queries return in almost 1-2 seconds, whereas the same query lasted more than 5 minutes before using the index.

There is full table access in the first execution plan and Index Range Scan in the second execution plan. Mostly we prefer that queries use Index range scan instead of full table access.

 

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

https://ittutorial.org/oracle-database-performance-tuning-tutorial-12-what-is-the-automatic-sql-tuning-and-how-to-automated-sql-tuning/