OCP Blog
Super DBA
Once more scenario for library latch contention
by Vazha Mantua Friday, December 17, 2010 10:53 AM

In last posts we describe reason of library latch contention and the main reason was literal sql’s

 

Let’s see another scenario

it’ is version of SQL statements:

if two statements are textually identical but cannot be shared then these are called 'versions' of the same statement. If Oracle matches to a statement with many versions it has to check each version in turn to see if it is truely identical to the statement currently being parsed. Hence high version counts are best avoided by:

  • Standardizing the maximum bind lengths specified by the client
  • Avoid using identical SQL from lots of different schemas which use private objects. Eg: SELECT xx FROM MYTABLE; where each user has their own MYTABLE
  • Setting _SQLEXEC_PROGRESSION_COST to '0' in Oracle 8.1

 

In our case we have non standarting maximum m binds lengths

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;

Note: how find SQL’s with many versions:

 

`

SELECT address, hash_value,
version_count ,
users_opening ,
users_executing,
substr(sql_text,1,40) "SQL"
FROM v$sqlarea
WHERE version_count > 10
order by version_count desc

Tags: , ,

Filter by APML

Calendar

<<  October 2019  >>
MoTuWeThFrSaSu
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910

View posts in large calendar

TextBox