Same SQL, different tools, different SQL_IDs?
- From: David Mann <dmann99@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 21 Jul 2011 14:34:23 -0400
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 by
SQL ID.
I connected with 4 different tools (and 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. Apologies to anyone reading without a
fixed font:
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 Different -
Trailing spaces removed from each line
SQLPLUS 10.2.0.1.0 Win32 3jt52v806qzu6 Different -
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 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:
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 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
--
Dave Mann
www.brainio.us
www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml
--
//www.freelists.org/webpage/oracle-l
Other related posts:
