A SQL_ID by any other name... Same SQL / Different Tools / Different SQL_ID

Dave October 11, 2011

I have been trying to chase down the same SQL being submitted against the same 10.2.0.4 Solaris database from different query tools used in our organization. I have been striking out when trying to match up with SQL IDs.

I connected with 5 different tools (including SQLPLUS on 2 platforms) and submitted the following 4 lines of SQL:

SELECT *
  FROM EMP, 
       DEPT 
 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.

Tool	SQL_ID	v$sql.sql_fulltext observation:
SQL Developer 3.0.02 on Win32	01xkza6wk1syd	Looks same as input including whitespace
SQLPLUS 10.2.0.4.0 Solaris	3jt52v806qzu6	Trailing spaces removed from each line
SQLPLUS 10.2.0.1.0 Win32	3jt52v806qzu6	Trailing spaces removed from each line
PL/SQL Developer 8.0.4.1514 Win32	54vxryr83c1pw	Looks same as input including whitespace, OPTIMIZER_ENV_HASH_VALUE does differ from other runs
Toad 9.6.11 Win32	gc3kk1r8ydrgw	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: http://ba6.us/content/2011-07-21-SQL_ID_ClientTest.html

I would revert to trying to match on SQL_TEXT field but even there I am seeing some white space differences.

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

Update: The Execute SQL screen in Oracle Enterprise Manager did give me the same SQL_ID as one of the tools above, but I need to find my notes again to verify which one.

You should also read: