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);
VARIABLE v1 VARCHAR2(30);
Language
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
ALTER SESSION SET NLS_LANGUAGE = �GERMAN';
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';
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;
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 <
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