Wednesday 16 December 2009

Force Cursor Invalidation

Many times it occurs that an inappropriate execution plan is used which was produced by using the current values of bind variables provided at the time of the hard parse. But later on the variables change so much that another execution plan would be required. Unfortunately there is no automatism in 9i and 10g that would spot this fact. Oracle finally resolved this problem in 11g.

The trick is to virtually set the statistics for the object which is involved in the query. What I mean by virtually is that I read the current statistics and store the same statistics back what makes no harm but the side effect is that the cursor is invalidated and hence it will be re-parsed and hopefully this time optimized for the right values of bind variables.

Here is the code:

CREATE OR REPLACE PROCEDURE Invalidate_statistics (
p_ownname VARCHAR2,
p_tabname VARCHAR2
) IS
m_srec DBMS_STATS.STATREC;
m_distcnt NUMBER;
m_density NUMBER;
m_nullcnt NUMBER;
m_avgclen NUMBER;
m_colname VARCHAR2(30);
--
-- This procedure invalidates all cursors present in v$sql
-- which refer to the table passed as input parameter
--
-- requires DBA privileges
--
-- This is a workaround to solve the problem of bind variable peeking
--
BEGIN
-- first find column definition for the first column - doesn't matter for which
-- column we reset the statistics
BEGIN
SELECT column_name INTO m_colname
FROM DBA_TAB_COLUMNS
WHERE owner = p_ownname
AND table_name = p_tabname
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR( -20000,
'Table '||p_ownname||'.'||p_tabname||' does not exist');
END;
-- virtually change the statistics - replace with the same values what will cause
-- invalidation of all cursors which refer to this table - NO_INVALIDATE must be FALSE
DBMS_STATS.GET_COLUMN_STATS (ownname => p_ownname,
tabname => p_tabname,
colname => m_colname,
distcnt => m_distcnt,
density => m_density,
nullcnt => m_nullcnt,
srec => m_srec,
avgclen => m_avgclen
);
DBMS_STATS.SET_COLUMN_STATS (ownname => p_ownname,
tabname => p_tabname,
colname => m_colname,
distcnt => m_distcnt,
density => m_density,
nullcnt => m_nullcnt,
srec => m_srec,
avgclen => m_avgclen,
no_invalidate => FALSE
);
END;
/

The procedure reads and stores back the statistics for the first column of the table.

SQL> exec Invalidate_statistics('SCOTT','EMP')

PL/SQL procedure successfully completed.

Sometimes it requires to re-execute the invalidation as the cursor is currently executing. One can use this in 9i,10g,11g.

10 comments:

  1. Hi Jose,

    I think it is not ported to 10.2.0.4.

    http://kerryosborne.oracle-guy.com/2008/09/flush-a-single-sql-statement/

    and does this method superior to do a dummy grant on EMP table ?

    ReplyDelete
  2. sorry I was trying to say it is ported to 10.2.0.4

    ReplyDelete
  3. Besides dbms_shared_pool.purge, it's much simpler to issue a "comment on table <> is <>";

    ReplyDelete
  4. I know that dbms_shared_pool.purge was backported from 11g to 10.2.0.4. I tried to use that one (in 10.2.0.4) at customer site some time ago but didn't work while mine was working.
    However, if you are on version < 10.2.0.4 than you even can't try with purge, while this one works.
    Of course there are other possible methods for invalidation, like commenting the table, ...
    I prefer to use this one.

    ReplyDelete
  5. I can use it also when you can't use comment:

    SQL> comment on table sys.cdef$ is 'blah';
    comment on table sys.cdef$ is 'blah'
    *
    ERROR at line 1:
    ORA-00701: object necessary for warmstarting database cannot be altered

    ReplyDelete
  6. True.
    It would be interesting to know how many times have you used that trick for data dictionary.

    PS. force=>true would be necessary in 10g if the statistics is locked

    ReplyDelete
  7. I have developed this piece of code years ago and my clients are using that on production systems. I don't know how many times it was used on data dictionary tables - probably never.

    Recently I found that comment on dictionary table doesn't work, but this one works.

    What I like in Oracle is that you can resolve the problem using different approaches (most of the time).

    Probably the simplest way is to use grant as Coskan mentioned above (followed immediately by revoke)

    Both are working, but it's always fine if you have more options to choose among.

    ReplyDelete
  8. we can also use dbms_shared_pool.purge procedure to clean those cursors in 10g/11g

    ReplyDelete
  9. Nice one.

    I normally do 'alter session set statistics_level = basic/all'. That changes the optimizer environment and forces hard parse.

    ReplyDelete
  10. Just to clarify my ealier comment - setting statistics_level doesn't force out the existing cursor, so really doesn't achieve what we want to. It won't help other sessions, as they will still reuse the existing cursor.

    It only comes handy as a 'quick-and-dirty' method to force a hard parse when you are investigating a performance problem.

    ReplyDelete