September 26, 2010

Find when was last time block was modified

Filed under: Uncategorized — srivenu @ 5:56 am

I felt very good after seeing this post (Last Modified) on Jonthan Lewis’s blog.
I had given a similar reply for an users’s posting on oracle groups some months back.
Feels good to know that atleast once i had thought like the Guru!

September 25, 2010

Invalidation of dependent code objects for dropping an index on the base table?

Filed under: Uncategorized — srivenu @ 10:21 am

“When you drop an index, Oracle invalidates all objects that depend on the underlying table, including views, packages, package bodies, functions, and procedures.”

This statement has been part of Oracle documentation since 9i doc (At least it wasn’t there in the same section in 8i doc)

I never thought for a minute that dropping an index would invalidate the code objects dependant on the underlying base table. That was till a couple of days back !

As part of tuning some sql, i was creating new indexes ones and dropping some old ones. All the index changes were on look-up tables. Even though it was on a production system, I thought that it wouldn’t impact anyone as the tables were small & static. How wrong & ignorant i was !

Next day i had been told that the end-users experienced an application freeze for around 6 minutes. On analysis it was found that the database faced several latch free events on shared pool & library cache and library cache pins. It was also noted that several code objects got invalid during that time and had undergone implicit recompilation. We had a DDL trigger at database level to capture all DDL changes. And the only DDL’s that we saw prior to the impact was the INDEX DROP. Some of my colleagues suggested that the INDEX drop has invalidated the procedures. I immediately rubbished their claim that an index drop would cause object invalidations.

To find the culprit, i decided to do some log mining. I was searching for the details of the session which invalidated the procs. I looked for SQL_REDO where the STATUS column in the OBJ$ table for the PROC object (OBJ#) was updated to a status of 5(invalid). I found out the session and after further analysis, i found that it was my session! I looked at all the SQL_REDO’s of my session and found that it had first updated the status of all the dependent procedures to an INVALID STATUS before executing the DROP INDEX statement. I immediately ran a quick test.

Create table X(a number);

Create index ix on X(a);

Create or replace procedure PX as
delete from X where a=10;

I dropped the index IX and checked for the status of PX. It was still VALID

On close observation i found out that the index i dropped during the issue was a FUNCTION-BASED index. So i repeated the test with a function-based index

Create table X(a number);

Create index ix on X(to_char(a));

Create or replace procedure PX as
delete from X where a=10;

I dropped the index IX and checked for the status of PX. It was now INVALID

So dependent code objects on a base table are only invalidated if you drop a Function-Based index on that table. (I did not check for other types). I raised an SR for fixing the documentation.

But this left me pondering the reason for this invalidation !

As a commentor has pointed out, the invalidation is no longer happening in 11g. I had infact raised a SR to get the Oracle documentation fixed
(SR 3-2143692451)

September 15, 2010

Outlines and Library Cache Latch Contention in Oracle 9i

Filed under: Uncategorized — srivenu @ 8:46 am

I faced severe library cache latch contention in all 3 nodes of a Oracle Apps 9i RAC database.
While tuning some custom sql, i wanted to use OUTLINES and the latch contention started after setting the USE_STORED_OUTLINES to DEFAULT at the system level. The latch contention manifested in almost all sessions and for all sql. I did not debug it in detail as i immediately turned off the parameter to false.
I found one relevant note – Bug 3312874 – USE_STORED_OUTLINES causes increased “library cache lock” requests in RAC [ID 3312874.8].

Also found 2 useful notes on how to check the current setting for the USE_STORED_OUTLINES parameter.

1) How to check the category of a Stored Outline – By Fairlie Rego

2) Geek Stuff – By Jonathan Lewis

Blog at