This content has been marked as final. Show 8 replies
Hi Jeff, using either give the same results. The query is "running", as is the little graphic with the bouncing gray bar is moving back and forth saying either "Query Results" or "Scriptrunner Task" as appropriate.
OK this is odd. I run a count(*) on plan_table in SQL*Plus and get 4, in SQL Developer I get 487. Hun? That makes no sense I'm connect as the same user in each. Where are all these other entries coming from and why can't I see them in SQL Plus? Does SQL Developer have it's own PLAN_TABLE?
**EDIT --- Yes that seems to be the case. The PLAN_ID I see in SQL Plus doesn't even exist in the SQL Deveropler version of the table. OK that's good to know. I assume the plan_table for SQL Developer is local to it somehow? It's not in the database as best I can see.
Edited by: Ric Van Dyke on Feb 7, 2013 5:19 PM
Check your user_objects view and see if your plan_table is actually a public synonym owned by SYSTEM ...
CREATE OR REPLACE PUBLIC SYNONYM "PLAN_TABLE" FOR "SYS"."PLAN_TABLE$";and also if sys.plan_table$ is a table of type
GLOBAL TEMPORARY TABLEIf you are connecting as the same user in both SQL*Plus and SQL Developer, and the underlying table is in fact temporary, then that explains the row count difference.
Roger that. The "solution" was I forgot to commit the delete in SQL Plus... so of course the version of the table is SQL Developer (which had connected before the SQL Plus session) still had the read consistent version of the table, which was all the old data before the delete. Arrge. Silly novice mistake, you'd think after working with the database for 1000 years I'd know better then to do something that silly. <head slap>
Thanks, Ric. So my point about global temporary tables was a bit off-the-mark in that, for a single user with multiple connected sessions, there exists only a single copy of the table. Any differences in an individual session's logical view is solely due to standard database concurrency rules.
Edited by: Gary Graham on Feb 8, 2013 8:49 AM
But the poor performance in SQL Developer when trying to ignore the uncommitted data is still a bit surprising to me. Presumably it would have been just as bad if the second session were a SQL*Plus session instead. Otherwise there must be some difference between SQL*Net and JDBC.
Edited by: Gary Graham on Feb 8, 2013 8:55 AM
... or the table is locked and the other session is just waiting.
Roger that Gary, it is odd that it went out to lunch and never came back. I know the simple query I wrote expected only one plan to be there but still even with about 500 rows in the table is shouldn't have gone off and choked like it did. Maybe I'll set this up again and turn on trace to see what is happening. I suspect some sort of massive rollback operation for the read consistency.