A SQL_ID by any other name... Same SQL / Different Tools / Different SQL_ID
Submitted by dave on Tue, 10/11/2011 - 23:33
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:
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.
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?
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.
SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNOThe 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.
|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 126.96.36.1994 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|