X

Alejandro Vargas' Blog

  • December 7, 2006

How to check why identical SQL Statements have high version count

Alejandro Vargas
Technical Leader, ACS Global Delivery, Infrastructure & BigData

Sometimes we use bind variables, but we see that our statements are not shared, why?

The most common causes are:


Bind Type mismatch

VARIABLE v1 VARCHAR2(60);
VARIABLE v1 VARCHAR2(30);

Language
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
ALTER SESSION SET NLS_LANGUAGE = �GERMAN';

SQL Trace
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE;
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Environmental variables that affect the optimizer
ALTER SESSION SET optimizer_mode = CHOOSE;
ALTER SESSION SET optimizer_mode = FIRST ROWS;
 
This script check the statements with the top number of versions and check on v$sql_shared_cursor to find the mismatch.

It works on 8i and 9i.  v$sql_shared_cursor structure has changed on 10g so needs to be adapted for it.


#!/usr/bin/ksh
# set -x
# chkvercnt
# This script check for high version counts
# Alejandro

. /mysrv/scripts/cshrc/817/.profile
cd /mysrv/scripts/av/freezedb/chkvercnts

ts=`date +%d%m%Y%H%M`
export ts
sid=$1
newsid=`echo $sid | tr '[a-z]' '[A-Z]'`
sqlplus -s sys/$x1@$sid <<eof1
set pages 100 feed off veri off flush off lines 120
column  sql_text for a60

spool $sid.$ts.chkvercnt

prompt * Get the statements with the highests version counts.
prompt

select  sql_text,
        version_count,
        executions,
        address
from    v$sqlarea where version_count>= (select        max(version_count) -5
                                          from          v$sqlarea)
order   by version_count
/

prompt
prompt * v$sql_shared_cursor - Use the describe to identify the type of mismatch
prompt

describe v$sql_shared_cursor

select  *
from    v$sql_shared_cursor
where   KGLHDPAR =
        (select address
         from   v$sqlarea
         where  version_count=(select   max(version_count)
                               from     v$sqlarea))
/


spool off
exit
eof1
exit
## eof chkvercnt

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.