Same SQL, different tools, different SQL_IDs?

From: David Mann <>
Date: Thu, 21 Jul 2011 14:34:23 -0400
Message-ID: <>

I have been trying to chase down the same SQL being submitted against the same Solaris database from different query tools used in our organization. I have been striking out when trying to match up by SQL ID. I connected with 4 different tools (and SQLPLUS on 2 platforms) and submitted the following 4 lines of SQL:

 WHERE EMP.DEPTNO=DEPT.DEPTNO The whitespace doesn't show in this format but the SQL was constructed with no spaces after the end of lines 1 and 4 and a single space character after the EMP and DEPT lines.

In each tool I tried to execute with a ; or / at the end of the statement and luckily got the same SQL_ID out of each so at least that syntax difference I can ignore. Apologies to anyone reading without a fixed font:

Tool                                SQL_ID         v$sql.sql_fulltext

SQL Developer 3.0.02 on Win32 01xkza6wk1syd Looks same as input including whitespace

SQLPLUS Solaris 3jt52v806qzu6 Different - Trailing spaces removed from each line

SQLPLUS Win32 3jt52v806qzu6 Different - Trailing spaces removed from each line

PL/SQL Developer Win32 54vxryr83c1pw Looks same as input including whitespace, OPTIMIZER_ENV_HASH_VALUE does differ from other runs

Toad  9.6.11 Win32                  gc3kk1r8ydrgw  Different - Looks
as if single space character was added to the end of last line

Full details from v$sql for these 4 unique SQL_IDs available here:

I would revert to trying to match on SQL_TEXT field but even there I am seeing some white space differences which trip me up when I want to make sure I am matching the same exact text.

So looking at sql_fulltext I did notice some whitespace differences between SQLPLUS and Toad - so that explains the different SQL_ID there. But I can't detect any differences between SQL Developer and PL/SQL Developer. Am I going crazy or is this each tool submitting the statement different enough that it causes a change in SQL ID?



Dave Mann - Database Stuff -
-- Received on Thu Jul 21 2011 - 13:34:23 CDT

Original text of this message