Wednesday Apr 28, 2010

Oracle Database: Say No Thanks! to a New Index

.. unless you are working with a database that is largely read-only or if the new index is supposed to be fixing a critical performance issue with no side effect(s).

Two topics covered in this blog entry with plenty of simple examples:

  1. Why creating new indexes on a heavily modified table may not be a good idea? and
  2. How to identify unused indexes?
Read on.

Indexes are double-edged swords that may improve the performance of targeted queries, but in some cases they may accidentally degrade the performance of other queries that are not targeted. In any case, exercise caution while adding a new index to the database. After adding a new index, monitor the overall performance of the database, not just the targeted query.

If DML statements that modify data (INSERT, UPDATE, or DELETE) are being executed large number of times on a table, make sure that the addition of a new index on the same table does not negatively affect the performance of those DML operations. Usually this is not a problem if the SQLs being executed are simply retrieving but not adding or modifying the existing data. In all other cases, there is some performance overhead induced by the addition of each new index. For example, if there are 10 indexes created on a table DUMMY, adding a new row of data to the table DUMMY may require updating all 10 indexes behind the scenes by the database management system.

Here is an example demonstrating the performance overhead of a new index on a table.


SQL> CREATE TABLE VIDEO
  2  (BARCODE VARCHAR(10) NOT NULL,
  3  TITLE VARCHAR2(25) NOT NULL,
  4  FORMAT VARCHAR2(10),
  5  PRICE NUMBER,
  6  DATA_OF_RELEASE DATE)
  7  /

Table created.

SQL> insert into VIDEO values ('9301224321', 'AVATAR', 'BLU-RAY', 19.99, '22-APR-2010');

1 row created.

..

SQL> insert into VIDEO values ('3782460017', 'THE SIMPSONS - SEASON 20', 'BLU-RAY', 29.99, '04-JUL-2009');

1 row created.

SQL> select \* from VIDEO;

BARCODE 	TITLE				    FORMAT		 PRICE DATA_OF_RELEASE
--------------- ----------------------------------- --------------- ---------- ---------------
9301224321	AVATAR				    BLU-RAY		 19.99 22-APR-10
7619203043	BEN-HUR 			    VHS 		  9.79 12-MAR-63
7305832093	THE MATRIX			    DVD 		 12.29 03-DEC-99
4810218795	MEMENTO 			    DVD 		  8.49 02-FEB-02
3782460017	THE SIMPSONS - SEASON 20	    BLU-RAY		 29.99 04-JUL-09

SQL> select \* from USER_INDEXES where TABLE_NAME = 'VIDEO';

no rows selected

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> select \* from VIDEO where FORMAT = 'BLU-RAY';

BARCODE 	TITLE				    FORMAT		 PRICE DATA_OF_RELEASE
--------------- ----------------------------------- --------------- ---------- ---------------
9301224321	AVATAR				    BLU-RAY		 19.99 22-APR-10
3782460017	THE SIMPSONS - SEASON 20	    BLU-RAY		 29.99 04-JUL-09

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL trace file has the following contents.


SQL ID: 0pu5s70nsdnzv
Plan Hash: 3846322456
SELECT \* 
FROM
 VIDEO WHERE FORMAT = :"SYS_B_0"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         16          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         16          0           2

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 28  

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  TABLE ACCESS FULL VIDEO (cr=16 pr=0 pw=0 time=3 us cost=4 size=100 card=2)

Let's create an index and see what happens.


SQL> create index VIDEO_IDX1 on VIDEO (FORMAT);

Index created.

SQL>  alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> select \* from VIDEO where FORMAT = 'BLU-RAY';

BARCODE 	TITLE				    FORMAT		 PRICE DATA_OF_RELEASE
--------------- ----------------------------------- --------------- ---------- ---------------
9301224321	AVATAR				    BLU-RAY		 19.99 22-APR-10
3782460017	THE SIMPSONS - SEASON 20	    BLU-RAY		 29.99 04-JUL-09

SQL> alter session set events '10046 trace name context off';

Session altered.

The latest contents of the trace file are as follows. Notice the reduction in buffer gets from 16 to 4. That is, the new index improved the query performance by 75%.


SQL ID: 0pu5s70nsdnzv
Plan Hash: 2773508764
SELECT \* 
FROM
 VIDEO WHERE FORMAT = :"SYS_B_0"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           2

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 28  (CS90)

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  TABLE ACCESS BY INDEX ROWID VIDEO (cr=4 pr=0 pw=0 time=12 us cost=2 size=100 card=2)
      2   INDEX RANGE SCAN VIDEO_IDX1 (cr=2 pr=0 pw=0 time=10 us cost=1 size=0 card=2)(object id 76899)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      2   TABLE ACCESS (BY INDEX ROWID) OF 'VIDEO' (TABLE)
      2    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'VIDEO_IDX1' (INDEX)

So far so good. Let's add a new row of data and examine the trace file one more time. From hereafter, keep an eye on the "current" column (logical IOs performed due to an INSERT, UPDATE or DELETE) and notice how it changes with different actions -- adding and removing: indexes, new row(s) of data etc.,


SQL ID: dnb2d8cpdj56p
Plan Hash: 0
INSERT INTO VIDEO 
VALUES
 (:"SYS_B_0", :"SYS_B_1", :"SYS_B_2", :"SYS_B_3", :"SYS_B_4")


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          7           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1          7           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 28  (CS90)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=0 us)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
      0   LOAD TABLE CONVENTIONAL OF 'VIDEO'

Now drop the index, re-insert the last row and get the tracing data again.


SQL> drop index VIDEO_IDX1;

Index dropped.

SQL> delete from VIDEO where BARCODE ='4457332907';

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> insert into VIDEO values ('4457332907', 'KING OF THE HILL - ALL', 'DVD', 90.00, '01-JAN-2011');

1 row created.

SQL> alter session set events '10046 trace name context off';

Session altered.

The contents of the latest trace file are shown below.


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          2          5          1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.00          0          2          5           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28  (CS90)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=0 us)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
      0   LOAD TABLE CONVENTIONAL OF 'VIDEO'

This time create two indexes and see what happens.


SQL> CREATE INDEX VIDEO_IDX1 ON VIDEO (FORMAT);

Index created.

SQL> CREATE INDEX VIDEO_IDX2 ON VIDEO (TITLE);

Index created.

Trace file contents:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          9           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1          9           1

Notice the two additional logical IOs (look under "current" column). Those additional logical input/output operations are the result of the new indexes. The number goes up as we add more indexes and data to the table VIDEO.


SQL> delete from VIDEO where BARCODE ='4457332907';

1 row deleted.

SQL> commit;

Commit complete.

SQL> create index VIDEO_IDX3 on VIDEO (PRICE, DATA_OF_RELEASE);

Index created.

SQL>  alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> insert into VIDEO values ('4457332907', 'KING OF THE HILL - ALL', 'DVD', 90.00, '01-JAN-2011');

1 row created.

SQL> alter session set events '10046 trace name context off';

Session altered.


SQL trace:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1         11           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1         11           1

You can try other operations such as UPDATE, DELETE on your own.

Since there are only few rows of data in the table VIDEO, it is hard to notice the real performance impact in these examples. If you really want to see the negative performance impact due to the large number of indexes on a heavily updated table, try adding thousands or millions of rows of data and few more indexes.

Moral of the story: Indexes aren't always cheap - they may have some overhead associated with them. Be aware of those overheads and ensure that the index maintenance overhead do not offset the performance gains resulting from the indexes created on a particular table.


Monitoring Index Usage

Now we know the possible disadvantage of having too many indexes on a heavily updated table. One way to reduce the index maintenance overhead is to instrument the indexes so we can monitor their usage from time to time and remove the unused indexes. To start monitoring the index usage, alter the index by specifying the keywords MONITORING USAGE.


SQL> select index_name from user_indexes where table_name = 'VIDEO';

INDEX_NAME
--------------------------------------------------------------------------------
VIDEO_IDX3
VIDEO_IDX1
VIDEO_IDX2

SQL> alter index VIDEO_IDX1 MONITORING USAGE;

Index altered.

SQL> alter index VIDEO_IDX2 MONITORING USAGE;

Index altered.

SQL> alter index VIDEO_IDX3 MONITORING USAGE;

Index altered.

Once the indexes are instrumented, query the V$OBJECT_USAGE view occasionally to see if the instrumented indexes are being used in executing SQL queries.


SQL> select \* from VIDEO where BARCODE LIKE '%22%';

BARCODE 	TITLE				    FORMAT		 PRICE DATA_OF_RELEASE
--------------- ----------------------------------- --------------- ---------- ---------------
9301224321	AVATAR				    BLU-RAY		 19.99 22-APR-10

SQL> select \* from VIDEO where FORMAT = 'VHS';

BARCODE 	TITLE				    FORMAT		 PRICE DATA_OF_RELEASE
--------------- ----------------------------------- --------------- ---------- ---------------
7619203043	BEN-HUR 			    VHS 		  9.79 12-MAR-63

SQL> select \* from VIDEO where PRICE < 20;

BARCODE 	TITLE				    FORMAT		 PRICE DATA_OF_RELEASE
--------------- ----------------------------------- --------------- ---------- ---------------
4810218795	MEMENTO 			    DVD 		  8.49 02-FEB-02
7619203043	BEN-HUR 			    VHS 		  9.79 12-MAR-63
7305832093	THE MATRIX			    DVD 		 12.29 03-DEC-99
9301224321	AVATAR				    BLU-RAY		 19.99 22-APR-10

SQL> select \* from VIDEO where FORMAT = 'BLU-RAY' AND DATA_OF_RELEASE < '01-JAN-2010';

BARCODE 	TITLE				    FORMAT		 PRICE DATA_OF_RELEASE
--------------- ----------------------------------- --------------- ---------- ---------------
3782460017	THE SIMPSONS - SEASON 20	    BLU-RAY		 29.99 04-JUL-09


SQL> column INDEX_NAME format A25
SQL> column START_MONITORING format A30

SQL> select INDEX_NAME, USED, START_MONITORING 
  2  from V$OBJECT_USAGE 
  3  where INDEX_NAME LIKE 'VIDEO_IDX%'
  4  /

INDEX_NAME		  USED	    START_MONITORING
------------------------- --------- ------------------------------
VIDEO_IDX1		  YES	    04/27/2010 01:10:20
VIDEO_IDX2		  NO	    04/27/2010 01:10:25
VIDEO_IDX3		  YES	    04/27/2010 01:10:31

In the above example, the index VIDEO_IDX2 was not in use during the period of index monitoring. If we are convinced that the queries that will be executed are similar to the ones that were executed during the index monitoring period, we can go ahead and remove the index VIDEO_IDX2 to reduce the performance overhead during updates on table VIDEO.

To stop monitoring the index usage, alter the index with the keywords NOMONITORING USAGE.


SQL> alter index VIDEO_IDX1 NOMONITORING USAGE;

Index altered.

Thursday Dec 10, 2009

Oracle RDBMS: Extracting the Table, Index & View Definitions (DDL) and Indexed Columns

(Reproducing a 30 month old blog post from my other blog at blogger. Source URL:
    http://technopark02.blogspot.com/2007/05/oracle-how-to-get-tableview-definition.html
)

Q: How to extract the table definition (DDL statement) from an Oracle database without having to go through a stack of dictionary views?

A: By calling the GET_DDL() function of metadata package DBMS_METADATA.

Syntax:
select DBMS_METADATA.GET_DDL('TABLE','<table_name>') from DUAL;

eg.,
SQL> set long 1000
SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL('TABLE','PERSON') from DUAL;

  CREATE TABLE "FS890"."PERSON"
   (    "SSN" VARCHAR2(12),
        "FIRST_NAME" VARCHAR2(25),
        "LAST_NAME" VARCHAR2(25),
        "STREET" VARCHAR2(40),
        "CITY" VARCHAR2(30),
        "STATE" VARCHAR2(30),
        "ZIP" VARCHAR2(15),
        "COUNTRY" VARCHAR2(35)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)  TABLESPACE "PSDEFAULT"



Q: How to extract the index definition (DDL statement) from an Oracle database for a given index nam?

A: By calling the GET_DDL() function of metadata package DBMS_METADATA.

Syntax:
select DBMS_METADATA.GET_DDL('INDEX','<index_name>') from DUAL;

eg.,
SQL> create index PERSON_IDX on PERSON ( SSN ); 

Index created.

SQL> set long 1000
SQL> set pagesize 0

SQL> select  DBMS_METADATA.GET_DDL('INDEX','PERSON_IDX') from DUAL;

  CREATE INDEX "FS890"."PERSON_IDX" ON "FS890"."PERSON" ("SSN")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PSDEFAULT"

Note:

If the interest is only to get the indexed column names for an index, simply query COLUMN_NAME of table USER_IND_COLUMNS.

Syntax:
select COLUMN_NAME from USER_IND_COLUMNS where INDEX_NAME = '<index_name>';

eg.,
SQL> column COLUMN_NAME format A15
SQL> select COLUMN_NAME from USER_IND_COLUMNS where INDEX_NAME = 'PERSON_IDX';

COLUMN_NAME
---------------
SSN



Q: Given a view name, how do we get the definition of the view? i.e., how to get the corresponding DDL statement that was used to create the view?

A: Query the TEXT column of table DBA_VIEWS.

Syntax:
SQL> set long 10000

SQL> select TEXT
  2  FROM DBA_VIEWS
  3  where OWNER = '<owner_name>'
  4  and VIEW_NAME  = '<view_name>';
Here is an example:
% sqlplus fs890/fs890@fs890

SQL> create table PERSON (
  2  SSN        VARCHAR2(12),
  3  FIRST_NAME VARCHAR2(25),
  4  LAST_NAME  VARCHAR2(25),
  5  STREET     VARCHAR2(40),
  6  CITY       VARCHAR2(30),
  7  STATE      VARCHAR2(30),
  8  ZIP        VARCHAR2(15),
  9  COUNTRY    VARCHAR2(35));

Table created.

SQL> create view PERSON_VW as
  2  select SSN, FIRST_NAME, LAST_NAME from PERSON;

View created.

SQL> set long 1000
SQL> select TEXT
  2  from DBA_VIEWS
  3  where OWNER = 'FS890'
  4  and VIEW_NAME  = 'PERSON_VW';

TEXT
--------------------------------------------------------------------------------
SELECT SSN, FIRST_NAME, LAST_NAME FROM PERSON



Q: How to find the schema name and the DB user name from an active session?

A: Run the following query:

select sys_context('USERENV', 'SESSION_USER') SESSION_USER, sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA from dual;

Alternatively run select USER from DUAL; to find the current {session} user name.

sys_context() function returns the value of parameter associated with the context namespace. USERENV is an Oracle provided namespace that describes the current session. Check the table Predefined Parameters of Namespace USERENV for the list of parameters and the expected return values.

eg.,
SQL> column SESSION_USER format A15
SQL> column CURRENT_SEHEMA format A15

SQL> select sys_context('USERENV', 'SESSION_USER') SESSION_USER, 
  2  sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA from dual;

SESSION_USER    CURRENT_SCHEMA
--------------- ---------------
FS890           FS890

SQL> column USER format A6

SQL> select USER from DUAL;

USER
------
FS890



Note:

Be aware that there are multiple ways of extracting the same piece of information from an Oracle database. I just provided the ones that I frequently use as part of my work.

About

Benchmark announcements, HOW-TOs, Tips and Troubleshooting

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today