11gR2; The SQL Performance Analyzer to decide an Index Deletion

June 25th, 2012 | Posted in 11gR2, Blog | No Comments

Introduction:

I have been recently asked to investigate a performance issue in one 11gR2 Oracle database.
I have found that some queries need to be tuned so the whole instance performance is improved.
I am concerned with one query executed thousands of time; this query is using an index hint.

Of course hints can improve the performance of the queries but in some cases it can be catastrophic.
The developer who wrote this query used an index hint to force the optimizer choosing an index in the execution plan. Indexes can improve the performance but bad indexes can be dramatic and cause performance issues. Identifying the adequate indexes is not an easy task. In the following example, I am going to show you how an index can cause performance issues, further, how a hint can result in a bad execution plan. In this example, I am going to use the Sql Performance Analyzer (SPA) to compare / analyze the current implemented query in production (using an index) with a query that supposes to improve the performance (without an index).

The environment:

I don’t have the exact query and the table structure in hand now, but, I am going to give an example that describes exactly the issue.
The database version created in my little test laptop is: 11.2.0.1.


SQL> conn wissem

Enter password: 

Connected.

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE	11.2.0.1.0	Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

Create a heap table named product and fill it with 2 millions of record; In the production environment the correspond table has about 200 millions of records and have much more columns, not partitioned.


SQL> CREATE TABLESPACE DATA DATAFILE SIZE 1G;

Tablespace created.

SQL> CREATE TABLE PRODUCT (PRODUCT_ID NUMBER PRIMARY KEY, PRODUCT_NAME VARCHAR2(30), PRODUCT_TYPE NUMBER) TABLESPACE DATA;

Table created.

SQL> 

SQL> BEGIN

  2  FOR I IN 1 .. 2000000 LOOP

  3  INSERT INTO PRODUCT VALUES (I, 'PRODUCT_'||I, 1);

  4  END LOOP;

  5  COMMIT;

  6  END;

  7  /

PL/SQL procedure successfully completed.

SQL>

An index was created on the product_type column, note here the product_type column has a unique value of 1 for all the 2 millions rows. I suppose that the column was designed to have different values but at the end due to an application bug or bad design the column has a unique value of 1.


SQL> CREATE INDEX PROD_TYPE_INDX ON PRODUCT(PRODUCT_TYPE);

Index created.

SQL>

The query executed many times looks like this; (Not exactly that looks like);


 SELECT /*+ INDEX (PRODUCT PROD_TYPE_INDX) */
                     DISTINCT SUBSTR(PRODUCT_NAME,1,6)

    FROM PRODUCT 

 WHERE PRODUCT_TYPE=1;

You may noticed 3 things:

1- Why create a column where the value is constant?
2- Why create an index where the value is constant?
3- Why using a hint in the above query ?

Maybe the structure of this table must be reviewed? As I said before, I suppose the column was initially designed to have different values, not only one.

Let’s compare the number of memory reads + disk reads when using hint and without using hint. In the rest of the post, I am going to flush the buffer cache and the shared pool frequently. I am flushing the buffer cache so I can get accurate comparative results of disk reads + memory reads, like first time executed. I am also flushing the shared pool just in case there is a result cache used, again to get accurate comparative results of disk reads + memory reads, like first time executed (Please only do that in test and NOT in production).


SQL> SET TIMI ON

SQL> SET AUTOTRACE ON

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:03.22

SQL> CREATE INDEX PROD_TYPE_INDX ON PRODUCT(PRODUCT_TYPE);

Index created.

Elapsed: 00:00:19.76

SQL> SELECT DISTINCT SUBSTR(PRODUCT_NAME,1,6)

  2  FROM PRODUCT 

  3  WHERE PRODUCT_TYPE=1;

SUBSTR(PRODUCT_NAME,1,6)

------------------------

PRODUC

Elapsed: 00:00:01.15

Execution Plan

----------------------------------------------------------

Plan hash value: 107077271

--------------------------------------------------------------------------------

------

| Id  | Operation	   | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time

     |

--------------------------------------------------------------------------------

------

|   0 | SELECT STATEMENT   |	     |	1989K|	  56M|	     | 18416   (1)| 00:0

3:41 |

|   1 |  HASH UNIQUE	   |	     |	1989K|	  56M|	  76M| 18416   (1)| 00:0

3:41 |

|*  2 |   TABLE ACCESS FULL| PRODUCT |	1989K|	  56M|	     |	2201   (1)| 00:0

0:27 |

--------------------------------------------------------------------------------

------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("PRODUCT_TYPE"=1)

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

	130  recursive calls

	  0  db block gets

       8158  consistent gets

       8302  physical reads

	  0  redo size

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

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.65

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.20

SQL> SELECT /*+ INDEX (PRODUCT PROD_TYPE_INDX) */ DISTINCT SUBSTR(PRODUCT_NAME,1,6)

  2  FROM PRODUCT 

  3  WHERE PRODUCT_TYPE=1;

SUBSTR(PRODUCT_NAME,1,6)

------------------------

PRODUC

Elapsed: 00:00:02.22

Execution Plan

----------------------------------------------------------

Plan hash value: 1649093299

--------------------------------------------------------------------------------

-----------------------

| Id  | Operation		     | Name	      | Rows  | Bytes |TempSpc|

Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

-----------------------

|   0 | SELECT STATEMENT	     |		      |  1989K|    56M|       |

28209	(1)| 00:05:39 |

|   1 |  HASH UNIQUE		     |		      |  1989K|    56M|    76M|

28209	(1)| 00:05:39 |

|   2 |   TABLE ACCESS BY INDEX ROWID| PRODUCT	      |  1989K|    56M|       |

11994	(1)| 00:02:24 |

|*  3 |    INDEX RANGE SCAN	     | PROD_TYPE_INDX |  1989K|       |       |

 4003	(1)| 00:00:49 |

--------------------------------------------------------------------------------

-----------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("PRODUCT_TYPE"=1)

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

	485  recursive calls

	  0  db block gets

      12040  consistent gets

      11910  physical reads

	  0  redo size

	546  bytes sent via SQL*Net to client

	524  bytes received via SQL*Net from client

	  2  SQL*Net roundtrips to/from client

	  6  sorts (memory)

	  0  sorts (disk)

	  1  rows processed

SQL>

From the above output, the optimizer chooses not to use the index in the first query because it is simply faster to do a full table scan than doing an index + Table full scan. Note that using hint the memory reads + disk reads are 23950 is 140% memory reads + disk reads without using hint 16460.

The index is not useful and must be dropped. Let’s trace the query without hint and without the index:


SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.39

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.26

SQL> SELECT DISTINCT SUBSTR(PRODUCT_NAME,1,6)

  2  FROM PRODUCT 

  3  WHERE PRODUCT_TYPE=1;

SUBSTR(PRODUCT_NAME,1,6)

------------------------

PRODUC

Elapsed: 00:00:01.12

Execution Plan

----------------------------------------------------------

Plan hash value: 107077271

--------------------------------------------------------------------------------

------

| Id  | Operation	   | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time

     |

--------------------------------------------------------------------------------

------

|   0 | SELECT STATEMENT   |	     |	1989K|	  56M|	     | 18416   (1)| 00:0

3:41 |

|   1 |  HASH UNIQUE	   |	     |	1989K|	  56M|	  76M| 18416   (1)| 00:0

3:41 |

|*  2 |   TABLE ACCESS FULL| PRODUCT |	1989K|	  56M|	     |	2201   (1)| 00:0

0:27 |

--------------------------------------------------------------------------------

------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("PRODUCT_TYPE"=1)

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

	  0  recursive calls

	  0  db block gets

       8061  consistent gets

       8058  physical reads

	  0  redo size

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

Note that the index in our case is not useful; In production environments you have to test if you can safely drop the index. In 11g, Oracle offers the invisible index solution; you can make an index invisible without actually dropping it. By making an index invisible, you can test and decide whenever you should drop the index or not, whenever the index is bad or not.

To make the index invisible, execute the statement:


SQL> ALTER INDEX PROD_TYPE_INDX INVISIBLE;

Index altered.

Elapsed: 00:00:00.07

SQL>

To make the optimizer using the invisible index, set the OPTIMIZER_USE_INVISIBLE_INDEXES parameter to TRUE or simply make it visible again by running:


SQL> ALTER INDEX PROD_TYPE_INDX VISIBLE;

Index altered.

Elapsed: 00:00:00.07

SQL>

The SQL Performance Analyzer (SPA):

The SQL Performance Analyzer (SPA) is part of database replay feature. SPA lets you test the before and after a potential system upgrade, database upgrade. SPA analyses the before and after SQL performance modification. In the following section, I am going to use SPA to compare the use or not of the index in the PRODUCT_TYPE column.

The index is created on Product_type column, let’s gather Product table statistics with its indexes.


SQL> CREATE INDEX PROD_TYPE_INDX ON PRODUCT(PRODUCT_TYPE);

Index created.

Elapsed: 00:00:19.87

SQL> BEGIN

  2  ---With Cascade option so Indexes statistics are also generated

  3  DBMS_STATS.GATHER_TABLE_STATS(USER, 'PRODUCT', CASCADE => TRUE);

  4  END;

  5  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.85

SQL>

Fill the shared pool with the new query with the hint:


SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:08.17

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.21

SQL> SELECT /*+ INDEX (PRODUCT PROD_TYPE_INDX) */ DISTINCT SUBSTR(PRODUCT_NAME,1,6)

  FROM PRODUCT 

  WHERE PRODUCT_TYPE=1;

SUBSTR(PRODUCT_NAME,1,6)

------------------------

PRODUC

Elapsed: 00:00:02.05

Execution Plan

----------------------------------------------------------

Plan hash value: 1649093299

--------------------------------------------------------------------------------

-----------------------

| Id  | Operation		     | Name	      | Rows  | Bytes |TempSpc|

Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

-----------------------

|   0 | SELECT STATEMENT	     |		      |  1993K|    36M|       |

23660	(1)| 00:04:44 |

|   1 |  HASH UNIQUE		     |		      |  1993K|    36M|    53M|

23660	(1)| 00:04:44 |

|   2 |   TABLE ACCESS BY INDEX ROWID| PRODUCT	      |  2000K|    36M|       |

11913	(1)| 00:02:23 |

|*  3 |    INDEX RANGE SCAN	     | PROD_TYPE_INDX |  2000K|       |       |

 3922	(1)| 00:00:48 |

--------------------------------------------------------------------------------

-----------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("PRODUCT_TYPE"=1)

Statistics

----------------------------------------------------------

	395  recursive calls

	  0  db block gets

      11952  consistent gets

      11900  physical reads

	  0  redo size

	546  bytes sent via SQL*Net to client

	524  bytes received via SQL*Net from client

	  2  SQL*Net roundtrips to/from client

	  6  sorts (memory)

	  0  sorts (disk)

	  1  rows processed

SQL>

Let’s create SQl Tuning Set
:


SQL> exec DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => 'PRODUCT_TEST');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.29

SQL>

Let’s Load the SQL Tuning Set
:


SQL> DECLARE

   V_CURSOR DBMS_SQLTUNE.SQLSET_CURSOR;

   BEGIN

    OPEN V_CURSOR FOR

    SELECT VALUE(a)

    FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(

    BASIC_FILTER => 'sql_text like ''%PRODUCT%'' and parsing_schema_name = ''WISSEM''',

    ATTRIBUTE_LIST => 'ALL')

    ) a;

   DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME => 'PRODUCT_TEST', POPULATE_CURSOR => V_CURSOR);

   END;

   /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.44

SQL>

Get the Task id of sql tuning Set
:


SQL> VAR V_TASK VARCHAR2(64);

SQL> EXEC :V_TASK := DBMS_SQLPA.CREATE_ANALYSIS_TASK (SQLSET_NAME => 'PRODUCT_TEST');

PL/SQL procedure successfully completed.

SQL> print :v_task

V_TASK

--------------------------------------------------------------------------------

TASK_1090

SQL>

Let’s analyze the task before an index deletion:


SQL> BEGIN

  2  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME=> :V_TASK, EXECUTION_TYPE=> 'test execute',

  3  execution_name => 'BEFORE_INDEX_DELETION'); 

  4  END;

  5  /

PL/SQL procedure successfully completed.

SQL>

Now let’s drop the index and gather again the table statistics with its indexes:


SQL> drop index PROD_TYPE_INDX;

Index dropped.

BEGIN

  ---With Cascade option so Indexes statistics are also generated

   DBMS_STATS.GATHER_TABLE_STATS(USER, 'PRODUCT', CASCADE => TRUE);

  4     END;

  5  

  6  /

PL/SQL procedure successfully completed.

SQL>

Let’s analyze the task after index deletion
:


SQL> VAR V_TASK VARCHAR2(64);

SQL> BEGIN

  2  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME=> 'TASK_1090', EXECUTION_TYPE=> 'test execute',

  3  execution_name => 'AFTER_INDEX_DELETION');

  4  END;

  5  /

PL/SQL procedure successfully completed.

SQL>

let’s compare the analysis before and after index deletion:


SQL> VAR V_TASK VARCHAR2(64);

SQL> BEGIN

  2  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK 

  3  (TASK_NAME => 'TASK_1090', EXECUTION_TYPE=> 'compare performance',

  4  execution_params => DBMS_ADVISOR.arglist

  5  ('execution_name1',

  6  'BEFORE_INDEX_DELETION',

  7  'execution_name2',

  8  'AFTER_INDEX_DELETION'));

  9  END;

 10  /

PL/SQL procedure successfully completed.

SQL>

Now, let’s generate the comparative analysis report:


SQL> spool /tmp/report.txt

SQL> SELECT DBMS_SQLPA.report_analysis_task ('TASK_1090')

  2  from dual;

General Information

---------------------------------------------------------------------------------------------

 Task Information:				Workload Information:

 ---------------------------------------------	---------------------------------------------

  Task Name    : TASK_1090			 SQL Tuning Set Name	    : PRODUCT_TEST

  Task Owner   : WISSEM 			 SQL Tuning Set Owner	    : WISSEM

  Description  :				 Total SQL Statement Count  : 4

Execution Information:

---------------------------------------------------------------------------------------------

  Execution Name	     : EXEC_1082	      Started		  : 06/25/2012 18:33:24

  Execution Type	     : COMPARE PERFORMANCE    Last Updated	  : 06/25/2012 18:33:24

  Description		     :			      Global Time Limit   : UNLIMITED

  Scope 		     : COMPREHENSIVE	      Per-SQL Time Limit  : UNUSED

  Status		     : COMPLETED	      Number of Errors	  : 0

  Number of Unsupported SQL  : 1

Analysis Information:

---------------------------------------------------------------------------------------------

 Before Change Execution:			After Change Execution:

 ---------------------------------------------	---------------------------------------------

  Execution Name      : BEFORE_INDEX_DELETION	 Execution Name      : AFTER_INDEX_DELETION

  Execution Type      : TEST EXECUTE		 Execution Type      : TEST EXECUTE

  Scope 	      : COMPREHENSIVE		 Scope		     : COMPREHENSIVE

  Status	      : COMPLETED		 Status 	     : COMPLETED

  Started	      : 06/25/2012 18:26:29	 Started	     : 06/25/2012 18:29:49

  Last Updated	      : 06/25/2012 18:26:34	 Last Updated	     : 06/25/2012 18:29:52

  Global Time Limit   : UNLIMITED		 Global Time Limit   : UNLIMITED

  Per-SQL Time Limit  : UNUSED			 Per-SQL Time Limit  : UNUSED

  Number of Errors    : 0			 Number of Errors    : 0

 ---------------------------------------------

 Comparison Metric: ELAPSED_TIME

 ------------------

 Workload Impact Threshold: 1%

 --------------------------

 SQL Impact Threshold: 1%

 ----------------------

Report Summary

---------------------------------------------------------------------------------------------

Projected Workload Change Impact:

-------------------------------------------

 Overall Impact      :	40.11%

 Improvement Impact  :	40.11%

 Regression Impact   :	0%

SQL Statement Count

-------------------------------------------

 SQL Category  SQL Count  Plan Change Count

 Overall	       4		  1

 Improved	       1		  1

 Unchanged	       2		  0

 Unsupported	       1		  0

Top 3 SQL Sorted by Absolute Value of Change Impact on the Workload

---------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

|	    |		    | Impact on | Execution | Metric  | Metric	| Impact  | Plan   |

| object_id | sql_id	    | Workload	| Frequency | Before  | After	| on SQL  | Change |

--------------------------------------------------------------------------------------------

|	 13 | 30p5zzxd54gvp |	 40.11% |	  1 | 1794413 | 1046643 |  41.67% | y	   |

|	 14 | 3xn2b2wa7fna2 |	  -.52% |	  1 |	39899 |   49528 | -24.13% | n	   |

|	 15 | avvqmh30t2vkb |	  -.34% |	  1 |	29811 |   36122 | -21.17% | n	   |

--------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

SQL>

From the SPA output, it confirms an Improvement Impact of 40.11%
like we already noticed before. You can also use Oracle Enterprise Manager to invoke the SPA.

Links:

http://docs.oracle.com/cd/E11882_01/server.112/e16540/spa_intro.htm#CHDGEIIA
http://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes003.htm#ADMIN12317
http://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes002.htm#CIHJIDJG
http://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes004.htm#BABJJFEE

Scridb filter

Jaspreet: Brilliant explanation.
Yousuf: Very Nice… Just wanted to check you have done all patching at Node 1 only.. is there any thing need to...
Yousuf: Very Nice.. Thanks for sharing.. Once question.. You have executed all commands on Node 1 only.. Is there any...
Emir: Thanks… Great article
borse firmate: Thank you for another informative blog. The place else may just I am getting that kind of information...
leandro: why this parameter is systemwide? could you read from a asm instance from one failure group and from the...
Mohammad: paul, we create pfile from target database to source database and later we change database name, and...
Muhammad Ikram: Thanks Brother for sharing pearls of knowledge. May ALLAH reward you for this both here and...
rgrover: Thanks for the POST. I recently encounter similar issue. Your POST helped.
gopalredy: really its very use full to dbas
Vivian: This is awesome! Thank you so much!
henry zhong: CDB=DB, and PDB=SCHEMA but in a sub dictionary?
Dehbashee: Salam Brother, Thanx for sharing, however, i would be interested in chaging the password for the grid user...
wissem: No we don’t need that Just the scripts I posted above
Md. Tanweer: Thanks for posting the material. I really appreciate if you can send me, if you have tutorial for 11g...
christiaan: Thanks!! This has been bugging the hell out of me for so long.
Rajasekhar: Thank you
Samarjit Panigrahy: Very Nice and Simple Demo… Cool
cq: Any reason not to use auto patch for both CRS and RAC homes with one command? Or use opatch auto for RAC home as...
Hitesh: Hi, Can we use this RACcheck tool on oracle SE version ? Thanks

Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

Wissem's Oracle and NOSQL Tips