Forum Stats

  • 3,728,151 Users
  • 2,245,559 Discussions
  • 7,853,355 Comments

Discussions

Cursor Invalidation is not working in Oracle 19c

sabyakgp
sabyakgp Member Posts: 45 Red Ribbon
edited April 13 in SQL & PL/SQL

Hi

According to SQL Tuning Guide (Oracle 12.2 and Oracle 19c) when we gather statistics of a table the existing cursor gets invalidated (when invoked with NO_INVALIDATE=FALSE) and V$SQL.INVALIDATIONS incremented by 1 (originally 0) and V$SQL.OBJECT_STATUS shows INVALID_UNAUTH (originally VALID). I have conducted the following test (as given in the SQL Tuning Guide - 19c) but neither INVALIDATIONS column incremented by 1 nor OBJECT_STATUS becomes INVALID_AUTH.

SELECT COUNT(*) FROM sales;

--Get the SQL ID

SELECT PREV_SQL_ID SQL_ID FROM V$SESSION WHERE SID = SYS_CONTEXT('userenv', 'SID');

--Check V$SQL

SELECT CHILD_NUMBER, EXECUTIONS, PARSE_CALLS, INVALIDATIONS, OBJECT_STATUS FROM V$SQL WHERE SQL_ID = '<sql id>'

CHILD_NUMBER EXECUTIONS PARSE_CALLS INVALIDATIONS OBJECT_STATUS

------------ ---------- ----------- ------------- -------------

0 1 1 0 VALID

--Gather statistics with NO_INVALIDATE = FALSE

EXEC DBMS_STATS.GATHER_TABLE_STATS('sh','sales',no_invalidate => FALSE);

--Check V$SQL again

SELECT CHILD_NUMBER, EXECUTIONS, PARSE_CALLS, INVALIDATIONS, OBJECT_STATUS FROM V$SQL WHERE SQL_ID = '<sql id>'

--No change in the INVALIDATIONS and OBJECT_STATUS columns

Is there any other factors preventing the cursor invalidation? Your insights would be much helpful.

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,245 Blue Diamond
    edited April 14 Accepted Answer

    Works for me. Maybe try selecting something that relies on the stats and also look at all of the v$sql cursors for the query, not just the previous one.

    create table t_test_1 as
    select level as id
    from dual
    connect by level <= 100
    /
    
    Table created.
    
    
    alter table t_test_1 add constraint pk_t_test_1 primary key (id)
    /
    
    Table altered.
    
    
    select * from t_test_1 where id between 20 and 30
    /
    
    
            ID
    ----------
            20
    [snip...]
            29
            30
    
    
    11 rows selected.
    
    select sql_id,child_number,executions,loads,parse_calls,invalidations, object_status
    from v$sql
    where sql_text like '%from t_test_1%'
    /
    
    
    SQL_ID       |CHILD_NUMBER|EXECUTIONS|     LOADS|PARSE_CALLS|INVALIDATIONS|OBJECT_STATUS
    -------------|------------|----------|----------|-----------|-------------|-------------------
    2tf5p22k0124j|           0|         1|         1|          1|            0|VALID
    5ftwnxd23bt8f|           0|         1|         1|          1|            0|VALID
    
    
    exec dbms_stats.gather_table_stats(user, 'T_TEST_1', no_invalidate => false);
    
    PL/SQL procedure successfully completed.
    
    
    select sql_id,child_number,executions,loads,parse_calls,invalidations, object_status
    from v$sql
    where sql_text like '%from t_test_1%'
    /
    
    
    SQL_ID       |CHILD_NUMBER|EXECUTIONS|     LOADS|PARSE_CALLS|INVALIDATIONS|OBJECT_STATUS
    -------------|------------|----------|----------|-----------|-------------|-------------------
    2tf5p22k0124j|           0|         2|         1|          2|            0|VALID
    5ftwnxd23bt8f|           0|         1|         1|          1|            1|INVALID_UNAUTH
    
    
    select * from t_test_1 where id between 20 and 30
    /
    
    
            ID
    ----------
            20
    [snip...]
            29
            30
    
    
    11 rows selected.
    
    
    select sql_id,child_number,executions,loads,parse_calls,invalidations, object_status
    from v$sql
    where sql_text like '%from t_test_1%'
    /
    
    
    SQL_ID       |CHILD_NUMBER|EXECUTIONS|     LOADS|PARSE_CALLS|INVALIDATIONS|OBJECT_STATUS
    -------------|------------|----------|----------|-----------|-------------|-------------------
    2tf5p22k0124j|           0|         3|         1|          3|            0|VALID
    5ftwnxd23bt8f|           0|         1|         2|          1|            1|VALID
    
    
    


Answers

  • Paulzip
    Paulzip Member Posts: 8,245 Blue Diamond
    edited April 14 Accepted Answer

    Works for me. Maybe try selecting something that relies on the stats and also look at all of the v$sql cursors for the query, not just the previous one.

    create table t_test_1 as
    select level as id
    from dual
    connect by level <= 100
    /
    
    Table created.
    
    
    alter table t_test_1 add constraint pk_t_test_1 primary key (id)
    /
    
    Table altered.
    
    
    select * from t_test_1 where id between 20 and 30
    /
    
    
            ID
    ----------
            20
    [snip...]
            29
            30
    
    
    11 rows selected.
    
    select sql_id,child_number,executions,loads,parse_calls,invalidations, object_status
    from v$sql
    where sql_text like '%from t_test_1%'
    /
    
    
    SQL_ID       |CHILD_NUMBER|EXECUTIONS|     LOADS|PARSE_CALLS|INVALIDATIONS|OBJECT_STATUS
    -------------|------------|----------|----------|-----------|-------------|-------------------
    2tf5p22k0124j|           0|         1|         1|          1|            0|VALID
    5ftwnxd23bt8f|           0|         1|         1|          1|            0|VALID
    
    
    exec dbms_stats.gather_table_stats(user, 'T_TEST_1', no_invalidate => false);
    
    PL/SQL procedure successfully completed.
    
    
    select sql_id,child_number,executions,loads,parse_calls,invalidations, object_status
    from v$sql
    where sql_text like '%from t_test_1%'
    /
    
    
    SQL_ID       |CHILD_NUMBER|EXECUTIONS|     LOADS|PARSE_CALLS|INVALIDATIONS|OBJECT_STATUS
    -------------|------------|----------|----------|-----------|-------------|-------------------
    2tf5p22k0124j|           0|         2|         1|          2|            0|VALID
    5ftwnxd23bt8f|           0|         1|         1|          1|            1|INVALID_UNAUTH
    
    
    select * from t_test_1 where id between 20 and 30
    /
    
    
            ID
    ----------
            20
    [snip...]
            29
            30
    
    
    11 rows selected.
    
    
    select sql_id,child_number,executions,loads,parse_calls,invalidations, object_status
    from v$sql
    where sql_text like '%from t_test_1%'
    /
    
    
    SQL_ID       |CHILD_NUMBER|EXECUTIONS|     LOADS|PARSE_CALLS|INVALIDATIONS|OBJECT_STATUS
    -------------|------------|----------|----------|-----------|-------------|-------------------
    2tf5p22k0124j|           0|         3|         1|          3|            0|VALID
    5ftwnxd23bt8f|           0|         1|         2|          1|            1|VALID
    
    
    


  • mathguy
    mathguy Member Posts: 9,724 Gold Crown

    Like Paulzip, I am unable to reproduce what you are reporting. no_invalidate => FALSE works as expected on my system (Oracle 12.2.0.1).

    Just to make sure - in your post you seem to do things in the right order; you check the INVALIDATIONS count and the OBJECT_STATUS without running the SELECT statement again. That is as it should be; if you do run it again before checking, the statement is re-validated. Just making sure you didn't accidentally (or otherwise) re-run the statement after gathering stats but before checking V$SQL.

    You may also want to include SQL_TEXT in the query against V$SQL to make sure you are looking at the right cursor.

  • sabyakgp
    sabyakgp Member Posts: 45 Red Ribbon

    mathguy and Paulzip. Thanks for your answers. I tried the example you have provided but it still did not work. What finally worked, and quite interestingly, is when I issued a DDL to the table adding a new column. It's then when the INVALIDATIONS got incremented by 1 and OBJECT_STATUS to INVALID_AUTH.

  • Rainer Stenzel
    Rainer Stenzel Member Posts: 63 Bronze Badge
    edited April 15

    Check out the enhanced fine-grained-cursor-invalidations. Updating statistics may only mark the cursor for rolling invalidation (IS_ROLLING_INVALID) and create a new child with roll_invalid_mismatch on next execution.

    SELECT CHILD_NUMBER, EXECUTIONS, PARSE_CALLS, INVALIDATIONS, DDL_NO_INVALIDATE, IS_ROLLING_INVALID, IS_ROLLING_REFRESH_INVALID, OBJECT_STATUS FROM V$SQL WHERE SQL_ID='<sql id>'

    https://asktom.oracle.com/pls/apex/asktom.search?tag=invalidation-of-child-cursor

    https://blogs.oracle.com/optimizer/fine-grained-cursor-invalidation

    <quote>

    There are four actions that we can take for a cursor:

    Cursor remains valid -- this is used when we know that the cursor is safe to execute and that the cursor's plan is still optimal. 

      For example, modifying a table partition to read only.

    Cursor is marked for rolling invalidation -- this is used when we know that the cursor is safe to execute but its plan may be sub-optimal. 

     For example, if we create a visible index, then the cursor is safe to execute 

     but we want to recompile it sometime in the future so the new index can be considered for the plan.

    Cursor is marked for rolling invalidation with metadata refresh -- this is used when the cursor is safe to execute after refreshing some of its metadata at runtime. 

     For example, if we rebuild an index that is used by the cursor, 

     then the cursor can be executed if we refresh the metadata for the index so the cursor uses the new index segment.

    Cursor is invalidated -- this is used if the cursor is no longer safe to execute. 

     For example, if we drop an index that is used by the cursor, 

     we must invalidate the cursor so it is recompiled to get a new plan that does not use the index.

     Note that actions 1, 2, and 3 correspond to DDL_NO_INVALIDATE, IS_ROLLING_INVALID, and IS_ROLLING_REFRESH_INVALID columns in V$SQL. 

    Action 4 is simply the existing invalidation action that was used prior to the fine-grained invalidation feature.

    </quote>

    Looks like the INVALIDATION counter is only incemented for Action 4 with fine-grained-cursor-invalidations.

Sign In or Register to comment.