To Index or Not to Index on Exadata System

I started reading Expert Oracle Exadata 2nd Edition yesterday,I read 1 st edition before. I can say if you like working with Exadata ,you should read this book.

When the topic comes to Exadata, one of the popular discussion is about  using index on Exadata. Some says (Sales forces 🙂 ) you don’t need any indexes on Exadata but I don’t agree with them.

Most of the databases  are in the real world system  have mixed workload,long running query  which is throughput  sensitive in DSS and latency-sensitive statement in OLTP , so how to deal with indexes ? DSS doesn’t like index even it is analytic function index (Thanks smart scan) but on the other hand OLTP system need indexes.

So you can create 2 services ,one is for DSS system named, for example DSSSRV the other one is for OLTP is OLTPSRV

But you don’t want DSS system to use index , so put your indexes in INVISIBLE mode (come with 11g) which you don’t need to use them in DSS connection ,and make it VISIBLE when your connections come from OLTP service.

ALTER INDEX  oltp_index1 INVISIBLE;

select index_name, visibility  from user_indexes 3 where index_name = ‘oltp_index1’;

Write little procedure and put it into LOGON trigger later.

CREATE procedure coming_service is
begin
if LOWER(sys_context(‘userenv’,’service:name’)) = ‘DSSRV’ then
execute immediate ‘Alter session set optimizer_use_invisible_indexes = FALSE’;
elsif  LOWER(sys_context(‘userenv’,’service:name’)) = ‘OLTPSRV’ then
execute immediate ‘Alter session set optimizer_use_invisible_indexes =TRUE’;
end if;
end;
/

Alter session set optimizer_use_invisible_indexes = TRUE

This command is session level command , so it is valid only your session.

P.S

The procedure was taken from Chapter 17 –Unlearning  Some Things We Thought We Knew

Advertisements

About Ugurcan

Exadata Engineer
This entry was posted in Oracle Database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s