Friday, January 22, 2010

Checking for high version counts

"Versions" of a statement occur where the SQL is character for character identical but the underlying objects or binds or etc.. are different.
For example:
User scott has a dept table:
SQL>select * from dept
User Tom has his own dept table:
SQL>select * from dept

When Scott and Tom executes the above same SQL in terms of characters, because the base object "dept" is different, Oracle will treat the two statements as two different versions of SQL.

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

Labels: ,

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home