Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘INVALID_UNAUTH

v$sql.object_status – what the hell is this?

leave a comment »

select count(*) from v$sql where sql_text like '<part_of_sql>%';



This is the query I often use to verify if the cursor is invaliated by dependency chain.

Suddenly I realized that I should use more elegant way on 11g. Simple test case will show what I mean.

1. Create objects. View v1, Procedure proc1 and one SQL cursor are dependent on table t1.

create table t1(c1 int, c2 int);

create or replace view v1 as
select c1, c2 from t1
;

create or replace procedure proc1
is
begin
  update t1 set c1 = c1 + 1;
end;
/

select /* sql */ c1, c2 from t1 where c1 = 1;



2. Load them onto the shared pool.

select /* sql */ c1, c2 from t1 where c1 = 1;
select * from v1;
exec proc1;



3. Now I compare the status of each object on 10g and 11g.

select object_name, status
from user_objects where object_name in ('V1', 'PROC1');

-- 10g
OBJECT_NAME          STATUS
-------------------- --------------------
PROC1                VALID
V1                   VALID
-- 11g
OBJECT_NAME          STATUS             
-------------------- --------------------
PROC1                VALID              
V1                   VALID
select count(*)
from v$sql where sql_text like 'select /* sql */ c1, c2 from t1 where c1 = 1%';

-- 10g
  COUNT(*)
----------
         1
-- 11g
  COUNT(*)
----------
         1
select sql_id, object_status
from v$sql where sql_text like 'select /* sql */ c1, c2 from t1 where c1 = 1%';

-- 10g
SQL_ID                                  OBJECT_STATUS
--------------------------------------- ---------------
87nk78p1sdwn9                           VALID
-- 11g
SQL_ID                                  OBJECT_STATUS
--------------------------------------- ------------------
87nk78p1sdwn9                           VALID



4. What happens if I alter the base table?

alter table t1 add c3 int;
select object_name, status
from user_objects where object_name in ('V1', 'PROC1');

-- 10g
OBJECT_NAME          STATUS
-------------------- ---------
PROC1                INVALID
V1                   INVALID
-- 11g
OBJECT_NAME          STATUS             
-------------------- --------------------
PROC1                VALID    <-- Note here!       
V1                   VALID    <-- Note here!

On 11g, View v1 and Procedure proc is NOT invalidated! This is a new feature of Oracle 11g which is sometimes called finer dependency management. Very cool.

But how about the cursor?  Is SQL cursor also finely managed?

select count(*)
from v$sql where sql_text like 'select /* sql */ c1, c2 from t1 where c1 = 1%';

-- 10g
  COUNT(*)
----------
         0

-- 11g
  COUNT(*)
----------
         1    <-- Note here!



At first, It really looks so. Oracle didn’t invalidate the cursor on 11g. Really? See following result.

select sql_id, object_status
from v$sql where sql_text like 'select /* sql */ c1, c2 from t1 where c1 = 1%';

-- 10g
no rows selected

-- 11g
SQL_ID                                  OBJECT_STATUS    
--------------------------------------- ------------------
87nk78p1sdwn9                           INVALID_UNAUTH    <-- Note here!


Oops. There is a big difference between 11g and lower versions.

  • On lower versions, the v$sql view does not show the invalidated cursor. So my old verifier(select count(*) ..) worked with no problem.
  • But on 11g, the v$sql does show the invalidated cursor with the object_status column value “INVALID_UNAUTH”.

I looked up the exact meaning of v$sql.object_status but with no satisfaction. But one thing is clear.

Always check v$sql.object_status on 11g!

Written by Dion Cho

February 5, 2009 at 8:20 am

Follow

Get every new post delivered to your Inbox.

Join 60 other followers