We're getting some strange behavior from the following query:
FROM na_employee emp
WHERE emp.ebd_ein = emp.OLD_SSN
AND NOT EXISTS (SELECT *
FROM na_ssn_to_ein t2
WHERE t2.SSN = emp.ebd_ein)
ORDER BY LAST_NAME
In TOAD, repeated executions of this query returns us all 816 rows, or 0 rows. SQL Plus always returns records.
In looking into the problem, I noticed that V$SESS_IO returns a value ~5000 for CONSISTENT_GETS when the query is run from SQLPlus, but shows ~25000 when run from TOAD (regardless of whether data is returned).
Since I can't even find good documentation on the def'n of this value, I don't even know if this is significant at this point. But it's the only thing I've found thus far.
Could anyone offer some pointers as to where I could go from here?
Do an explain plan in both TOAD (CTRL-e, make sure the toad plan tables are setup) and in sqlplus. If they are the same then what v$sess_IO is showing you is that TOAD has been running some of its own queries in the background (probably to understand the schema its logged into). If the plans are different check what optimizer mode has been set in TOAD.
To understand your query better do a search on google for tkprof as that will give more useful stats or stick autotrace on in TOAD.