How to check why identical SQL Statements have high version count-优快云博客

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 <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