v$sql.object_status 疑问

vmcd_gg

As we know  11g new feature -finer dependency management. ,In Oracle 11g  if we do DDL command on tables ,v$sql.object_status will  change,let's do a test:

first

SQL cursor are dependent on table t1.

SQL> desc t1;
Name
   Null?    Type
----------------------------------------- -------- ----------------------------
C1
    NUMBER(38)
C2
    NUMBER(38)

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

and then I do a ddl:

alter table t1 add c3 int;



SQL> desc t1;
Name
   Null?    Type
----------------------------------------- -------- ----------------------------
C1
    NUMBER(38)
C2
    NUMBER(38)
C3
    NUMBER(38)

SQL>

in 10g:

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

no rows selected


in 11g:

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

SQL_ID
      OBJECT_STATUS
------------- -------------------
87nk78p1sdwn9 INVALID_UNAUTH

SQL>


Object_status changed to 'INVALID_UNAUTH', and then  I do a 10053 trace and run this SQL again:

SQL> Alter session set events '10053 trace name context forever,level 2';

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


If event 10053 is set, this event will be triggered in a hard parse (not a soft parse).

the trace show that oracle do a hard parse like:

BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1  (NOT ANALYZED)

Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
....


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

SQL_ID
      OBJECT_STATUS
------------- -------------------
c8f88bcmhcqgr VALID
87nk78p1sdwn9 INVALID_UNAUTH


My question is what is  'INVALID_UNAUTH' meaning ? and if I Execution a some SQL  oracle will create a new cursor ,It seems that there are no difference
between 11g and 10g  and how can oracle be benefited from the new feature in 11g?

Maclean Liu(刘相兵

ODM Data


Bug 3645969: V$SQL.OBJECT_STATUS COLUMN IS RETURNING A NOT DOCUMENTED VALUE

this document said

"PROBLEM:
--------
v$sql.object_status column can return a value that is not documented.

Documented values are VALID, INVALID.
But it can return:
    'INVALID_UNAUTH' object status

That cursors all has 0 executions.
This SQL is actually spinning (100% CPU).

The understanding of this status may help solving the real problem.

DIAGNOSTIC ANALYSIS:
--------------------
The definition of GV$SQL contains:
Select ..
decode(kglobsta, 1, 'VALID', 2, 'VALID_AUTH_ERROR', 3, 'VALID_COMPILE_ERROR',
4, 'VALID_UNAUTH', 5, 'INVALID_UNAUTH', 6, 'INVALID'), ...
  from x$kglcursor
..."

OBJECT_STATUS         VARCHAR2(19)         Status of the cursor:

    VALID - Valid, authorized without errors

    VALID_AUTH_ERROR - Valid, authorized with authorization errors

    VALID_COMPILE_ERROR - Valid, authorized with compilation errors

    VALID_UNAUTH - Valid, unauthorized

    INVALID_UNAUTH - Invalid, unauthorized

    INVALID - Invalid, unauthorized but keep the timestamp

Abstract: INCLUDE THE OBJECT STATUS OF THE CHILD CURSOR IN V$SQL AND GV$SQL


Problem:
The status of a cursor is availabile in X$KGLCURSOR but is not included in
V$SQL or GV$SQL.  This
means it is not possible to use the latter views to access for eaxmple all
valid cursors, all
unauthorized cursors etc.

Versions:
oracle release 8.0 and 8i

Workaround:
The following view includes the status and also the child address:
create or replace view v$sql2 as
select
userenv('instance')    INST_ID,
kglnaobj               SQL_TEXT,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16
SHARABLE_MEM,
kglobt08+kglobt11      PERSISTENT_MEM,
kglobt10               RUNTIME_MEM,
kglobt01               SORTS,
decode(kglobhs6,0,0,1) LOADED_VERSIONS,
decode(kglhdlmd,0,0,1) OPEN_VERSIONS,
kglhdlkc               USERS_OPENING,
kglhdexc               EXECUTIONS,
kglobpc6               USERS_EXECUTING,
kglhdldc               LOADS,
substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19) FIRST_LOAD_TIME,
decode(kglobsta, 1, 'VALID
', 2, 'VALID_AUTH_ERROR'
,3 ,'VALID_COMPILE_ERROR
', 4, 'VALID_UNAUTH
', 5, 'INVALID_UNAUTH
', 6, 'INVALID'
, kglobsta)           OBJECT_STATUS,
kglhdivc               INVALIDATIONS,
kglobt12               PARSE_CALLS,
kglobt13               DISK_READS,
kglobt14               BUFFER_GETS,
kglobt15               ROWS_PROCESSED,
kglobt02               COMMAND_TYPE,
decode(kglobt32,
        0, 'NONE',
        1, 'ALL_ROWS',
        2, 'FIRST_ROWS',
        3, 'RULE',
        4, 'CHOOSE',
           'UNKNOWN')   OPTIMIZER_MODE,
kglobtn0               OPTIMIZER_COST,
kglobt17               PARSING_USER_ID,
kglobt18               PARSING_SCHEMA_ID,
kglhdkmk               KEPT_VERSIONS,
kglhdpar               ADDRESS,
kglhdadr               CHILD_ADDRESS,
kglobtp0               TYPE_CHK_HEAP,
  kglnahsh               HASH_VALUE,
kglobt09               CHILD_NUMBER,
kglobts0               MODULE,
kglobt19               MODULE_HASH,
kglobts1               ACTION,
kglobt20               ACTION_HASH,
kglobt21               SERIALIZABLE_ABORTS,
kglobts2               OUTLINE_CATEGORY
from           x$kglcursor
where         kglobt02 != 0
and            kglhdadr != kglhdpar ;

grant select on v$sql2 to public;
drop public synonym v$sql2 ;
create public synonym v$sql2 for v$sql2 ;

Related Bug(s):
1376603, 1376567

Maclean Liu(刘相兵

Finer Grained Dependencies

In previous releases, metadata recorded mutual dependencies between objects with the granularity of the whole object. For example, PL/SQL unit P depends on PL/SQL unit Q or that view V depends on table T. This means that dependent objects were sometimes invalidated when there was no logical requirement to do so. For example, if view V depends only on columns C1, C2, and C3 in table T and a new column, C99, is added, the validity of view V is not logically affected. Nevertheless, in earlier releases, V was invalidated by the addition of column C99.

Maclean Liu(刘相兵

I have never tested this new feature ,   It looks like  sql cursor has more fine control in 11g .

I guess the edition based redefinition also need this find control level , I am guessing here !! not really ... really have no idea!!