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

Comments (6) -

12/6/2011 9:38:00 AM #

Ecreditattorney

Your post is really good very well written and impressive to all reader.

Ecreditattorney United States

1/23/2012 4:46:01 AM #

carpet cleaning kenmore

I just added this feed to my bookmarks. I have to say, I really enjoy reading your blogs. Thanks!

carpet cleaning kenmore United States

2/11/2012 11:23:54 PM #

carpet cleaning provo

For those looking for tech blogs, i would suggest reading this blog. has a bunch of nice articles.

carpet cleaning provo United States

6/3/2012 2:28:42 AM #

Directory Submission Service

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:

Directory Submission Service United Kingdom

6/10/2012 3:28:22 PM #

Iraqi Dinar

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:

Iraqi Dinar United Kingdom

6/20/2012 2:31:35 PM #

Diminished Value

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:

Diminished Value United Kingdom

Pingbacks and trackbacks (1)+

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Filter by APML

Calendar

<<  August 2019  >>
MoTuWeThFrSaSu
2930311234
567891011
12131415161718
19202122232425
2627282930311
2345678

View posts in large calendar

TextBox