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
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’;
Alter session set optimizer_use_invisible_indexes = TRUE
This command is session level command , so it is valid only your session.
The procedure was taken from Chapter 17 –Unlearning Some Things We Thought We Knew