v$object_usage invisible index used

I had a number of indexes on a table I made invisible, they were large indexes on a table that seemed totally useless, many were duplicates and as they accounted a large amount of the disk I was keen to see them gone and get some space back.  The application developers were happy for me to give it a try when I explained how making an index invisible in Oracle 11.2 works.

Invisible is not the same as unusable.  An invisible index is totally maintained, you are simply telling the CBO not to use it, and if you find you hit a performance problem you can quickly make it visible again without having to rebuild it.

alter index schema_owner.my_index_name invisible;
alter index schema_owner.my_index_name visible;

Dropping the indexes of this size though would be a huge issue for me to recreate so I wanted to be absolutely sure they weren't being used before I did that, so I decided to monitor them for a while.  This is easy too:

alter index schema_owner.my_index_name monitoring usage;

Now have a look at V$object_usage (as the object owner - if you do it as any other user you won't get any data returned)

select * from v$object_usage where index_name='MY_INDEX_NAME';
MY_INDEX_NAME MY_TABLE_NAME  YES NO date_and_time_monitoring_started

The USED=NO is good, this means Oracle hasn't used my index. But every morning when I got in the used had become 'YES'.

No matter how hard I tried I could not catch the sql that was causing the field to be changed

The sql pool was showing that DBMS_STATS was using a use_invisible_indexes hint but surely Oracle would have factored that in. This was the proof they had:

Alter system flush shared pool;

select count(*)
from v$sqlarea a, v$sql_plan p
where p.hash_value=a.hash_value
and p.address=a.address
and p.object_name='MY_INDEX_NAME';
zero rows returned

select index_name, index_type, visibility  from dba_indexes where table_name='MY_TABLE_NAME';
MY_INDEX_NAME NORMAL VISIBLE
alter index schema_owner.my_index_name invisible;

select index_name, index_type, visibility  from dba_indexes
 where table_name='MY_TABLE_NAME';
MY_INDEX_NAME NORMAL INVISIBLE
alter index schema_owner.my_index_name nomonitoring usage;
alter index schema_owner.my_index_name monitoring usage;

(as schema owner)
select USED from v$object_usage where index_name='MY_INDEX_NAME';
NO

select index_name, last_analyzed  from dba_indexes where table_name='MY_TABLE_NAME';
MY_INDEX_NAME 29/07/2012 15:46:08
exec dbms_stats.gather_table_stats('SCHEMA_OWNER','MY_TABLE_NAME',cascade => TRUE);
PL/SQL procedure successfully completed.

select index_name, last_analyzed from dba_indexes where table_name='MY_TABLE_NAME';
MY_INDEX_NAME 09/08/2012 12:44:11
(as schema owner)
select USED from v$object_usage where index_name='MY_INDEX_NAME';
NO

select count(*)
from v$sqlarea a, v$sql_plan p
where p.hash_value=a.hash_value
and p.address=a.address
and p.object_name='MY_INDEX_NAME';
1 row returned

select a.sql_text
from v$sqlarea a, v$sql_plan p
where p.hash_value=a.hash_value
and p.address=a.address
and p.object_name='MY_INDEX_NAME';
select /*+  no_parallel_index(t, "MY_INDEX_NAME")  dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad  no_expand use_invisible_indexes index(t,"MY_INDEX_NAME") */ count(*) as nrw,count(distinct sys_op_lbid(36991,'L',t.rowid)) as nlb,null as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "SCHEMA_OWNER"."MY_TABLE_NAME" t where ("OSUSER" is not null) and (TBL$OR$IDX$PART$NUM........etc


So Oracle is using the index when dbms_stats is run but v$object_usage is not updated.

I was stuck here for a long time, every day I reset monitoring, every night it was updated but there was nothing in the sql pool to give me an idea as to why.  In the hope that it was being pushed out of the sql area I set a cron job to run every 5 minutes over night that ran the select sql_text query if USED in v$object_usage had changed to yes. I could see the point it changed but still I was getting no sql other than that from dbms_stats.

The application developers were adamant they did not use hints in their code anywhere. 

So what on earth was using it?  Then a number of weeks after I had been pulling my hair out I got cc'd on an email from the schedulers to the developers about a job that was over-running and the sql output they attached from the job named my index.  In fact being aware that I was 'working' on the index I was blamed for making the job over run.

It turns out that every night as a part of the batch the application rebuilt every partition of every index belonging to this table.  They weren't checking whether the index was visible or not.  I wouldn't expect them to check for that to be fair, what I still cannot fathom though is why they were rebuilding every subpartition of a local index on a partitioned table every night.  Local indexes manage themselves really.  Drop the table partition, the index partition goes too.  I can only think it's a code hangover from pre local indexes, unless you want to tell me different.

So here's the proof it was an index rebuild causing my headache:

alter system flush shared pool;

select count(*)
from v$sqlarea a, v$sql_plan p
where p.hash_value=a.hash_value
and p.address=a.address
and p.object_name='MY_INDEX_NAME';
zero rows returned


select index_name, index_type, visibility from dba_indexes where table_name='MY_TABLE_NAME';
MY_INDEX_NAME NORMAL VISIBLE
alter index schema_owner.my_index_name invisible;

select index_name, index_type, visibility from dba_indexes
where table_name='MY_TABLE_NAME';
MY_INDEX_NAME NORMAL INVISIBLE
alter index schema_owner.my_index_name nomonitoring usage;
alter index schema_owner.my_index_name monitoring usage;

(as schema owner)
select USED from v$object_usage where index_name='MY_INDEX_NAME';
NO

alter index schema_owner.my_index_name REBUILD SUBPARTITION my_subpartition_name;

(as schema owner)
select USED from v$object_usage where index_name='MY_INDEX_NAME';
YES

select count(*)
from v$sqlarea a, v$sql_plan p
where p.hash_value=a.hash_value
and p.address=a.address
and p.object_name='MY_INDEX_NAME';
zero rows returned



Oracle have logged an enhancement request for v$object_usage to include a column that reports the action that causes USED to be altered, this would have saved me a whole lot of head scratching.

*Update April 2015

Enhancement request 'ADD A COLUMN TO V$OBJECT_USAGE IDENTIFYING THE TYPE OF ACTION THAT USED THE INDEX' has been reported as unlikely to implement.
But a bug has been raise about the rebuild of the subpartitions triggering an entry into v$object_usage Bug 20951043 : MISLEADING MONITORING INFORMATION FOR INDEX REBUILD OPERATIONS


I am disappointed with Oracle, I don’t think the approach is the best way to deal with it for customers but at least it is being partially dealt with. I'm seriously unimpressed that it them 2 years to even get to the point of acknowledging it.

Read more...

Oracle Forum Hostility

Thomas Kyte has written a very good post on The Keys to Oracle, the best starting points for a journey to a decent working knowledge of Oracle.  But he also recommends a great way to learn is to start asking questions on http://otn.oracle.com/forums. On this point I have to disagree with him.

I only use Oracle forums when a search engine leads me there.  They can be good as catalysts for me to get to the root of my problem but all too often I am appalled by responses I see to questions asked.

Now I know I have to be fair here, there are some very helpful and dedicated people that work hard to support and help people in the forums.  I regularly see the same names giving some great support and answers. If it weren't for them the forums wouldn't hold so much of the great information they do.

But even with my many years of Oracle experience, I am too scared to ask a question in the forums.  There are just too many sarcastic, unhelpful responses given that I know I would find very upsetting if aimed at me.  Damn! I find them upsetting when they are aimed at others.

This problem with hostility in Oracle forums is well recognised and I have to say I have never seen it as bad in any other IT area.  In fact the Unix ones I've used have people falling over themselves to give the best piece of advice, they don't often chuck in an RTFM because they are dying to let you know how much they know and if nothing else at least point you to a part of a document, white paper or other web site.

Tom himself talked about some of the Oracle forum issues back in 2005.

But here's a very recent question in the forums where the first answer is a sarcastic unhelpful response.

http://www.lmgtfy.com/?q=oracle+convert+long+to+varchar2

Of course the poster of the question hunted via the search engines first but you don't always manage to hit the spot.  Sometimes in google I'm just not asking the right question, my terminology isn't quite accurate enough to get me there but another DBA with experience of that area might have a few more terms to throw into the mix that means I start to get closer to where I need to be. 

Here's another one, with the first response:

The correct URL for doc questions is
http://tahiti.oracle.com
not
http://forums.oracle.com
Thank you

The poster points out they had looked at the documentation. Sometimes the Oracle documentation is a minefield.  There is so much of it, yes you can search it but it's back to the point about search words, if you are unsure of the ground you are treading you often don't get the search terms right.  This takes us back to Tom's advice, if you feel the need to point someone to the documentation why not give them a pointer of which part you are thinking of specifically.

And if you are still thinking I'm just exaggerating the 'hostility' part then how is this for a response:

Oracle documentation is SEARCHABLE.
Most people here can not be bothered to search.
They don't need guidance, they need corporal punishment or their wage subtracted for every question they ask here.

Or this

Almost all posters have 'I don't hate my work, and I want to maximize my income by doing nothing' inscribed in capitals on their forehead.

These sorts of attitudes and responses make me quite sad. This explains the need for respect in forums very well and the difference in creating passionate people it can make or break.

The people that I respect the most are the people that are always happy to share what they know, happy to re-explain the basics, when I ask a dumb question they don't make me feel small and stupid.  They often say "I'm glad you asked that, let's get back to basics, we all need to go there sometimes". I ask a lot of dumb questions especially at places like OUGs but time after time I have had people say to me later "I'm glad you asked that, I was thinking that too".  I know I'm not the only person that walks the earth that wasn't born knowing it all and hasn't managed to learn (or sometimes just plain remember) it all yet.

The people I know that time and time again are explaining the same questions with the patience of a saint as the more experienced of us have blonde moments and the newbies start to learn are the greats like Thomas Kyte and Jonathon Lewis.  I put Pete Finnagen in that pot too.  How about Jeff Smith or Martin Berger ...oh there is an endless list of helpful DBAs hanging out in twitter that will give you some wider search terms to see you closer to a problems end.

So all I am saying is when there are so many really great and good out there, why oh why has no one managed to silence the sarcastic, egotistical idiots that still think answering a question RTFM or LMGTFY is an acceptable or respectful way to talk to anyone.

OK, I'm off my soap box...until next time.

Read more...