PL/SQL Developer not returning data & unable to terminate

[AK_Itim]

Hi all, I'm running the following setup:

Oracle 11g 64bit DB on Unix
Oracle 11g 32bit Client on Windows Server 2K8 R2 Standard (64bit)
PL/SQL Developer v7.1.5.1397

And have the following issue:

When I run select * from a table containing 120 columns & around 80,000 records, the application hangs on "Executing" and does not return any data regardless of how long I leave it for. It also does not allow me to terminate the query, so I need to kill the application session via Task Manager.

I had the same issue with SQLPlus, but this has been rectified by setting DEFAULT_SDU_SIZE = 975 in sqlnet.ora.

Can you please advise??

 

[Marco Kalter]

The DEFAULT_SDU_SIZE is set in sqlnet.ora or tnsnames.ora? If so, this should resolve things in PL/SQL Developer as well if it is using the same Oracle Home as SQL*Plus. Can you verify this? The Oracle Home that PL/SQL Developer is using can be seen through Help > Support Info.

 

[AK_Itim]

Hi Marco, the DEFUALT_SDU_SIZE is set as expected in the sqlnet.ora file in the same home directory that PL/SQL Developer uses.

Do you have any further suggestions??!

 

[Marco Kalter]

To obtain some more diagnostic information, can you modify the shortcut and add the DebugSQL parameter? For example:

"C:\Program Files\PLSQL Developer\plsqldev.exe" DebugSQL

Reproduce the problem and send me the debug.txt file that is generated in the PL/SQL Developer directory or in the %APPDATA%\PLSQL Developer directory (e.g. C:\Users\\AppData\Roaming\PLSQL Developer).

 

[AK_Itim]

Hi Marco, see the below:

PL/SQL Developer
Version 7.1.5.1397
Windows XP (64-bit) 5.1 Build 2600 (Service Pack 2)

Physical memory : 1,048,576 kB (1,048,576 available)
Paging file : 2,097,152 kB (1,048,576 available)
Virtual memory : 2,097,024 kB (1,960,324 available)

Parameters
C:\Program Files (x86)\PLSQL Developer\plsqldev.exe
DebugSQL

Preferences
Session mode: Multi
OCI Library:
Use OCI7: False

Preference Files

Plug-Ins

06/01/2012 14:56:10 Application started
06/01/2012 14:56:10 InitPreferences
06/01/2012 14:56:10 Load C:\Program Files (x86)\PLSQL Developer\Preferences\Default = 0
06/01/2012 14:56:10 PreferenceSet.Load C:\Program Files (x86)\PLSQL Developer\Preferences\Default\Default.ini
06/01/2012 14:56:10 PreferenceSet.Load OK
06/01/2012 14:56:10 Determine LocalPrefPath
06/01/2012 14:56:10 [1]: C:\Program Files (x86)\PLSQL Developer\Preferences
06/01/2012 14:56:10 LocalPrefPath: C:\Program Files (x86)\PLSQL Developer\Preferences\aking
06/01/2012 14:56:10 Load C:\Program Files (x86)\PLSQL Developer\Preferences\aking = 0
06/01/2012 14:56:10 PreferenceSet.Load C:\Program Files (x86)\PLSQL Developer\Preferences\aking\default.ini
06/01/2012 14:56:10 PreferenceSet.Load OK
06/01/2012 14:56:10 PreferenceSet.Load C:\Program Files (x86)\PLSQL Developer\Preferences\aking\user.prefs
06/01/2012 14:56:10 PreferenceSet.Load OK
06/01/2012 14:56:10 InitPreferences end
06/01/2012 14:56:11 SortPreferenceSets
06/01/2012 14:56:11 - Default aking
06/01/2012 14:56:11 - System Default
06/01/2012 14:56:11 pfReadRegistry(PL/SQL Developer\Shell\Open\Command, )
06/01/2012 14:56:11 ReadString OK
06/01/2012 14:56:11 pfReadRegistry: "C:\Program Files (x86)\PLSQL Developer\plsqldev.exe" "%1"
06/01/2012 14:56:11 LoadPreferences
06/01/2012 14:56:11 LoadPreferences searching for default
06/01/2012 14:56:11 C:\Program Files (x86)\PLSQL Developer\Preferences\aking\default.ini
06/01/2012 14:56:11 LoadPreferences NewPref := nil
06/01/2012 14:56:11 LoadPreferences
06/01/2012 14:56:11 LoadPreferences Reading preferences
06/01/2012 14:56:11 pfReadRegistry(PL/SQL Developer\Shell\Open\Command, )
06/01/2012 14:56:11 ReadString OK
06/01/2012 14:56:11 pfReadRegistry: "C:\Program Files (x86)\PLSQL Developer\plsqldev.exe" "%1"
06/01/2012 14:56:11 LoadPreferences ReadPosition
06/01/2012 14:56:11 LoadPreferences ToolsBrowserItemClick(nil)
06/01/2012 14:56:11 LoadPreferences CreateBrowserFilters...
06/01/2012 14:56:11 LoadPreferences Files
06/01/2012 14:56:11 LoadPreferences Logon
06/01/2012 14:56:11 LoadPreferences Buttons
06/01/2012 14:56:11 LoadPreferences Printer
06/01/2012 14:56:11 LoadPreferences BrowserFolders
06/01/2012 14:56:11 LoadPreferences
06/01/2012 14:56:11 LoadPreferences end
06/01/2012 14:56:11 MacroLibFilename: C:\Program Files (x86)\PLSQL Developer\Macro\aking.lib
06/01/2012 14:56:14 TOracleSession PLSQLDevForm.Session Start Session.LogOn as ipo_admin@PRODIPO
06/01/2012 14:57:17 TOracleSession PLSQLDevForm.Session End
Result = ORA-12547: TNS:lost contact
Duration = 63.25
06/01/2012 14:59:01 TOracleSession PLSQLDevForm.Session Start Session.LogOn as ipo_admin@PRODIPO
06/01/2012 14:59:01 TOracleQuery BytesPerCharacterQuery Start Query.Execute
SQL = select length(chr(2000000000)) l4, length(chr(2000000)) l3, length(chr(20000)) l2, 'c' c1 from dual
06/01/2012 14:59:01 TOracleQuery BytesPerCharacterQuery End (1 record processed)
Duration = 0.031
06/01/2012 14:59:01 TOracleQuery BytesPerCharacterQuery Start Query.Execute
SQL = select lengthb(nchr(20)), nchr(20) from dual
06/01/2012 14:59:01 TOracleQuery BytesPerCharacterQuery End (1 record processed)
Duration = 0.063
06/01/2012 14:59:01 TOracleQuery Query Start Query.Execute
SQL = select to_char(userenv('SESSIONID')) from dual
06/01/2012 14:59:01 TOracleQuery Query End (1 record processed)
Duration = 0.047
06/01/2012 14:59:01 TOracleSession PLSQLDevForm.Session End
Duration = 0.375
06/01/2012 14:59:01 LogonHistorySave start
06/01/2012 14:59:01 ChangeUserPref LogonHistory,
06/01/2012 14:59:01 ChangeUserPref DSA,
06/01/2012 14:59:01 ChangeUserPref end
06/01/2012 14:59:01 ChangeUserPref ColumnAssociations,
06/01/2012 14:59:01 ChangeUserPref end
06/01/2012 14:59:01 ChangeUserPref ImportAssociations,
06/01/2012 14:59:01 ChangeUserPref end
06/01/2012 14:59:01 ChangeUserPref BrowserFilters,
06/01/2012 14:59:01 ChangeUserPref end
06/01/2012 14:59:01 ChangeUserPref BrowserFolders,
06/01/2012 14:59:01 ChangeUserPref end
06/01/2012 14:59:01 ChangeUserPref SessionQueries,
06/01/2012 14:59:01 ChangeUserPref end
06/01/2012 14:59:01 ChangeUserPref ExternalTools,
06/01/2012 14:59:01 ChangeUserPref end
06/01/2012 14:59:01 ChangeUserPref Documents,
06/01/2012 14:59:01 ChangeUserPref end
06/01/2012 14:59:01 ChangeUserPref Reports,
06/01/2012 14:59:01 ChangeUserPref end
06/01/2012 14:59:01 LogonHistorySave end
06/01/2012 14:59:01 FormCreate Session.Connected
06/01/2012 14:59:01 FormCreate Logon.Executed -1
06/01/2012 14:59:01 FormCreate TDBNames.Make
06/01/2012 14:59:01 FormCreate SessionChanged
06/01/2012 14:59:01 TOracleQuery Query Start Query.Execute
SQL = select * from v$version
06/01/2012 14:59:01 TOracleQuery Query End (5 records processed)
Duration = 0.031
06/01/2012 14:59:01 SessionChanged SetDBAViews
06/01/2012 14:59:01 TOracleQuery $0503C9F0 Start Query.Execute
SQL = select null from all_synonyms where 1=0
06/01/2012 14:59:01 TOracleQuery $0503C9F0 End (0 records processed)
Duration = 0.047
06/01/2012 14:59:01 TOracleQuery $0503C9F0 Start Query.Execute
SQL = select null from dba_synonyms where 1=0
06/01/2012 14:59:01 TOracleQuery $0503C9F0 End (0 records processed)
Duration = 0.031
06/01/2012 14:59:01 SessionChanged Authorizations.Load
06/01/2012 14:59:01 TOracleQuery $0503C5D0 Start Query.Execute
SQL = select grantee, name from
sys.plsqldev_authorization
where grantee in (user, 'PUBLIC')
or grantee in (select role from sys.session_roles)
06/01/2012 14:59:01 TOracleQuery $0503C5D0 End
Result = ORA-00942: table or view does not exist
Duration = 0.047
06/01/2012 14:59:01 SessionChanged EnableAuthorizedMenus
06/01/2012 14:59:01 TOracleSession $057A0EA0 Start Session.LogOn as ipo_admin@PRODIPO
06/01/2012 14:59:01 TOracleQuery Query Start Query.Execute
SQL = select to_char(userenv('SESSIONID')) from dual
06/01/2012 14:59:01 TOracleQuery Query End (1 record processed)
Duration = 0.047
06/01/2012 14:59:01 TOracleSession $057A0EA0 End
Duration = 0.266
06/01/2012 14:59:01 SetSessionAction starts: Main session
06/01/2012 14:59:01 TOracleQuery $0503C9F0 Start Query.Execute
SQL = select null from dual
06/01/2012 14:59:01 TOracleQuery $0503C890 Start Query.Execute
SQL = select object_name, object_type
from sys.user_objects o
where o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')
06/01/2012 14:59:01 TOracleQuery $0503C9F0 End (1 record processed)
Duration = 0.047
06/01/2012 14:59:01 TOracleQuery $0503C9F0 Start Query.Execute
SQL = begin sys.dbms_application_info.set_module('PL/SQL Developer', :action); end;
:ACTION = Main session
06/01/2012 14:59:01 TOracleQuery $0503C9F0 End
Duration = 0.016
06/01/2012 14:59:01 SetSessionAction ends
06/01/2012 14:59:01 ExecuteConnectScript starts
06/01/2012 14:59:01 ExecuteConnectScript ends
06/01/2012 14:59:01 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Execute
SQL = select value from v$nls_parameters where parameter = 'NLS_LENGTH_SEMANTICS'
06/01/2012 14:59:01 TOracleQuery $0503C890 End (59 records processed)
Duration = 0.079
06/01/2012 14:59:01 TOracleQuery $0503C890 Start Query.Execute
SQL = select s.synonym_name object_name, o.object_type
from all_synonyms s,
sys.all_objects o
where s.owner in ('PUBLIC', user)
and o.owner = s.table_owner
and o.object_name = s.table_name
and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')
06/01/2012 14:59:01 TOracleQuery PLSQLDevForm.DynamicQuery End (1 record processed)
Duration = 0.047
06/01/2012 14:59:01 SessionChanged LoadRecentObjects
06/01/2012 14:59:01 SessionChanged Notify children
06/01/2012 14:59:01 SessionChanged Logoff secondary sessions
06/01/2012 14:59:01 SessionChanged SessionMode = smMulti
06/01/2012 14:59:01 SessionChanged EnableChildFunctions
06/01/2012 14:59:02 SessionChanged
OCIDLL forced to C:\app\aking\product\11.2.0\client_1\bin\oci.dll
06/01/2012 14:59:02 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Execute
SQL = select value from sys.nls_database_parameters where parameter = 'NLS_CHARACTERSET'
06/01/2012 14:59:02 TOracleQuery PLSQLDevForm.DynamicQuery End (1 record processed)
Duration = 0.031
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.296
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.016
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.016
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.016
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 SortPreferenceSets
06/01/2012 14:59:02 - Default aking
06/01/2012 14:59:02 - System Default
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.015
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.032
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C730 Start Query.Execute
SQL = select null from all_synonyms where 1=0
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.015
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C730 End (0 records processed)
Duration = 0.046
06/01/2012 14:59:02 TOracleQuery $0503C730 Start Query.Execute
SQL = select null from dba_synonyms where 1=0
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.016
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C730 End (0 records processed)
Duration = 0.032
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.016
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.016
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.016
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.016
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.032
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 WMUpdatePopups starts
06/01/2012 14:59:02 GetCrashList: 1049914.cfg
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.015
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 GetCrashList: 1049914.cfg
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.016
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.047
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.032
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:02 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:02 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:03 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.047
06/01/2012 14:59:03 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:03 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:03 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:03 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.016
06/01/2012 14:59:03 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:03 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.047
06/01/2012 14:59:03 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:03 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.047
06/01/2012 14:59:03 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:04 Create TSQLForm Start
06/01/2012 14:59:04 Create TSQLForm Inherited
06/01/2012 14:59:04 TSQLForm.FormCreate Start
06/01/2012 14:59:04 SQLForm
06/01/2012 14:59:04 TSQLForm.FormCreate Finished
06/01/2012 14:59:04 Create TSQLForm SetupMainFont
06/01/2012 14:59:04 Create TSQLForm Activate
06/01/2012 14:59:04 Create TSQLForm Activate done
06/01/2012 14:59:04 Create TSQLForm UpdateWindowList
06/01/2012 14:59:04 Create TSQLForm Positioning
06/01/2012 14:59:04 Create TSQLForm Finished
06/01/2012 14:59:04 FileAge: C:\Users\aking\AppData\Local\Temp\2\PLS-Recovery\1049914.001 = 14:51:10
06/01/2012 14:59:04 SQLForm
06/01/2012 14:59:04 SQLForm
06/01/2012 14:59:04 SQLForm Recovery file loaded
06/01/2012 14:59:04 Create TSQLForm Start
06/01/2012 14:59:04 Create TSQLForm Inherited
06/01/2012 14:59:04 TOracleQuery $0503C890 End (100 records processed)
Duration = 1.453
06/01/2012 14:59:04 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:04 TSQLForm.FormCreate Start
06/01/2012 14:59:04 SQLForm
06/01/2012 14:59:04 TSQLForm.FormCreate Finished
06/01/2012 14:59:04 AddNavigationBookmark starts
06/01/2012 14:59:04 Create TSQLForm SetupMainFont
06/01/2012 14:59:04 Create TSQLForm Activate
06/01/2012 14:59:04 Create TSQLForm Activate done
06/01/2012 14:59:04 Create TSQLForm UpdateWindowList
06/01/2012 14:59:04 Create TSQLForm Positioning
06/01/2012 14:59:04 Create TSQLForm Finished
06/01/2012 14:59:04 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.141
06/01/2012 14:59:04 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:04 FileAge: C:\Users\aking\AppData\Local\Temp\2\PLS-Recovery\1049914.002 = 14:51:10
06/01/2012 14:59:04 SQLForm
06/01/2012 14:59:04 SQLForm
06/01/2012 14:59:04 SQLForm Recovery file loaded
06/01/2012 14:59:04 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.063
06/01/2012 14:59:04 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:04 NavigationBookmarks.EnableButtons(Rebuild)
06/01/2012 14:59:04 NavigationBookmarks: Backward=0, Forward=0
06/01/2012 14:59:04 WMUpdatePopups ends
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.281
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.032
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.015
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.078
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.047
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.016
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.047
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.032
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.015
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.047
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.032
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.015
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.016
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.016
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.016
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.015
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.032
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.015
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.032
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.015
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.016
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.047
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (100 records processed)
Duration = 0.031
06/01/2012 14:59:05 TOracleQuery $0503C890 Start Query.Next for up to 100 records
06/01/2012 14:59:05 TOracleQuery $0503C890 End (4 records processed)
Duration = 0.031
06/01/2012 14:59:05 TOracleSession $057A0EA0 Start Session.LogOff
06/01/2012 14:59:06 TOracleSession $057A0EA0 End
Duration = 0.032
06/01/2012 14:59:09 Create TSQLForm Start
06/01/2012 14:59:09 Create TSQLForm Inherited
06/01/2012 14:59:09 TSQLForm.FormCreate Start
06/01/2012 14:59:09 SQLForm
06/01/2012 14:59:09 TSQLForm.FormCreate Finished
06/01/2012 14:59:10 AddNavigationBookmark starts
06/01/2012 14:59:10 AddNavigationBookmark GNV00001
06/01/2012 14:59:10 NavigationBookmarks.EnableButtons(Rebuild)
06/01/2012 14:59:10 NavigationBookmarks: Backward=1, Forward=0
06/01/2012 14:59:10 AddNavigationBookmark ends
06/01/2012 14:59:10 Create TSQLForm SetupMainFont
06/01/2012 14:59:10 Create TSQLForm Activate
06/01/2012 14:59:10 Create TSQLForm Activate done
06/01/2012 14:59:10 Create TSQLForm UpdateWindowList
06/01/2012 14:59:10 Create TSQLForm Positioning
06/01/2012 14:59:10 Create TSQLForm Finished
06/01/2012 14:59:10 NavigationBookmarks.EnableButtons(Rebuild)
06/01/2012 14:59:10 NavigationBookmarks: Backward=1, Forward=0
06/01/2012 14:59:20 SQLForm Initializing...
06/01/2012 14:59:20 SetConnected starts
06/01/2012 14:59:20 SetConnected: Connect starts
06/01/2012 14:59:20 TOracleSession $057AB6A0 Start Session.LogOn as ipo_admin@PRODIPO
06/01/2012 14:59:20 TOracleQuery Query Start Query.Execute
SQL = select to_char(userenv('SESSIONID')) from dual
06/01/2012 14:59:20 TOracleQuery Query End (1 record processed)
Duration = 0.031
06/01/2012 14:59:20 TOracleSession $057AB6A0 End
Duration = 0.266
06/01/2012 14:59:20 SetConnected: (Dis)connect ends
06/01/2012 14:59:20 SetSessionAction starts: SQL Window - New
06/01/2012 14:59:20 TOracleQuery $0503C050 Start Query.Execute
SQL = select null from dual
06/01/2012 14:59:20 TOracleQuery $0503C050 End (1 record processed)
Duration = 0.047
06/01/2012 14:59:20 TOracleQuery BytesPerCharacterQuery Start Query.Execute
SQL = select length(chr(2000000000)) l4, length(chr(2000000)) l3, length(chr(20000)) l2, 'c' c1 from dual
06/01/2012 14:59:20 TOracleQuery BytesPerCharacterQuery End (1 record processed)
Duration = 0.031
06/01/2012 14:59:20 TOracleQuery BytesPerCharacterQuery Start Query.Execute
SQL = select lengthb(nchr(20)), nchr(20) from dual
06/01/2012 14:59:20 TOracleQuery BytesPerCharacterQuery End (1 record processed)
Duration = 0.047
06/01/2012 14:59:20 TOracleQuery $0503C050 Start Query.Execute
SQL = begin sys.dbms_application_info.set_module('PL/SQL Developer', :action); end;
:ACTION = SQL Window - New
06/01/2012 14:59:20 TOracleQuery $0503C050 End
Duration = 0.016
06/01/2012 14:59:20 SetSessionAction ends
06/01/2012 14:59:20 ExecuteConnectScript starts
06/01/2012 14:59:20 ExecuteConnectScript ends
06/01/2012 14:59:20 TOracleQuery $0503C050 Start Query.Execute
SQL = select sid, serial# from v$session where audsid = userenv('SESSIONID')
06/01/2012 14:59:20 TOracleQuery $0503C050 End (1 record processed)
Duration = 0.046
06/01/2012 14:59:20 EnableOutput starts
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.EnableOutputQuery Start Query.Execute
SQL = begin
if :enable = 0 then
sys.dbms_output.disable;
else
sys.dbms_output.enablesize);
end if;
end;
:ENABLE = 1
:SIZE = 10000
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.EnableOutputQuery End
Duration = 0.016
06/01/2012 14:59:20 EnableOutput done
06/01/2012 14:59:20 InitStatistics starts
06/01/2012 14:59:20 TStatistics.Init starts
06/01/2012 14:59:20 InitStatNames starts
06/01/2012 14:59:20 select name from v$statname order by statistic#
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Execute
SQL = select name from v$statname order by statistic#
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.047
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.016
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.015
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.031
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.016
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.016
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.015
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.032
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.015
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.031
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.016
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.031
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.016
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.016
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.031
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.015
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.032
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.015
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.016
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.015
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.016
06/01/2012 14:59:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:21 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.031
06/01/2012 14:59:21 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:21 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.031
06/01/2012 14:59:21 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:21 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.032
06/01/2012 14:59:21 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:21 TOracleQuery PLSQLDevForm.DynamicQuery End (25 records processed)
Duration = 0.031
06/01/2012 14:59:21 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Next for up to 25 records
06/01/2012 14:59:21 TOracleQuery PLSQLDevForm.DynamicQuery End (13 records processed)
Duration = 0.031
06/01/2012 14:59:21 InitStatNames ends
06/01/2012 14:59:21 StatQuery.Describe starts
06/01/2012 14:59:21 TOracleQuery PLSQLDevForm.StatQuery Start Query.Describe
SQL = select value from v$sesstat where sid = :sid order by statistic#
:SID = Null
06/01/2012 14:59:21 TOracleQuery PLSQLDevForm.StatQuery End
Duration = 0.031
06/01/2012 14:59:21 StatQuery.Describe ends
06/01/2012 14:59:21 TStatistics.Init ends
06/01/2012 14:59:21 TOracleQuery PLSQLDevForm.StatQuery Start Query.Execute
SQL = select value from v$sesstat where sid = :sid order by statistic#
:SID = 145
06/01/2012 14:59:21 TOracleQuery PLSQLDevForm.StatQuery End (638 records processed)
Duration = 0.063
06/01/2012 14:59:21 InitStatistics ends
06/01/2012 14:59:21 NavigationBookmarks.EnableButtons(Rebuild)
06/01/2012 14:59:21 NavigationBookmarks: Backward=1, Forward=0
06/01/2012 14:59:21 SQLForm Executing...
06/01/2012 14:59:21 SQLForm Executing...
06/01/2012 14:59:21 TSQLThread SQLThread.Execute
06/01/2012 14:59:21 TOracleQuery Query Start Query.Execute
SQL = begin :id := sys.dbms_transaction.local_transaction_id; end;
:ID = Null
06/01/2012 14:59:21 TOracleQuery Query End
Duration = 0.016
06/01/2012 14:59:21 TOracleQuery $0503B970 Start Query.Execute
SQL = select * from d_item
06/01/2012 14:59:36 Aborting thread
06/01/2012 14:59:38 Aborting thread
06/01/2012 14:59:40 Aborting thread
06/01/2012 14:59:42 Aborting thread
06/01/2012 14:59:43 Aborting thread
06/01/2012 14:59:45 Aborting thread
06/01/2012 14:59:47 Aborting thread
06/01/2012 14:59:49 Aborting thread
06/01/2012 14:59:51 Aborting thread
06/01/2012 14:59:53 Aborting thread
06/01/2012 14:59:54 Aborting thread
06/01/2012 14:59:56 Aborting thread
06/01/2012 14:59:58 Aborting thread
06/01/2012 14:59:59 Destroying TSQLForm
06/01/2012 15:00:21 TOracleSession $057AB6A0 Start Session.LogOff
06/01/2012 15:00:21 TOracleSession $057AB6A0 End
Duration = 0
06/01/2012 15:00:21 TOracleQuery $0503B970 End
Result = ORA-03113: end-of-file on communication channel
Process ID: 24126
Session ID: 145 Serial number: 34011
Duration = 59.703
06/01/2012 15:00:21 TSQLThread SQLThread Postmessage
06/01/2012 15:00:21 TSQLThread SQLThread Suspend
06/01/2012 15:00:21 TSQLThread SQLThread Suspend ended
06/01/2012 15:00:21 Destroyed TSQLForm
06/01/2012 15:00:23 TSQLForm.ThreadFinished
06/01/2012 15:00:23 Exception: Access violation at address 00929FDA in module 'plsqldev.exe'. Read of address 0000000E
06/01/2012 15:00:23 Exception: A call to an OS function failed
06/01/2012 15:00:23 AddNavigationBookmark starts
06/01/2012 15:00:23 AddNavigationBookmark GNV00002
06/01/2012 15:00:23 NavigationBookmarks.EnableButtons(Rebuild)
06/01/2012 15:00:23 NavigationBookmarks: Backward=1, Forward=0
06/01/2012 15:00:23 AddNavigationBookmark ends
06/01/2012 15:00:23 NavigationBookmarks.EnableButtons(Rebuild)
06/01/2012 15:00:23 NavigationBookmarks: Backward=1, Forward=0
06/01/2012 15:00:30 TOracleQuery Query Start Query.Execute
SQL = select * from v$version
06/01/2012 15:00:30 TOracleQuery Query End (5 records processed)
Duration = 0.031
06/01/2012 15:00:30 TOracleQuery $0503BD90 Start Query.Execute
SQL = select * from nls_database_parameters where parameter like 'NLS%CHARACTERSET'
06/01/2012 15:00:30 TOracleQuery $0503BD90 End (2 records processed)
Duration = 0.047
06/01/2012 15:00:35 TOracleQuery Query Start Query.Execute
SQL = select * from v$version
06/01/2012 15:00:35 TOracleQuery Query End (5 records processed)
Duration = 0.062
06/01/2012 15:00:35 TOracleQuery $0503BD90 Start Query.Execute
SQL = select * from nls_database_parameters where parameter like 'NLS%CHARACTERSET'
06/01/2012 15:00:35 TOracleQuery $0503BD90 End (2 records processed)
Duration = 0.063
06/01/2012 15:00:40 TOracleQuery Query Start Query.Execute
SQL = select * from v$version
06/01/2012 15:00:40 TOracleQuery Query End (5 records processed)
Duration = 0.14
06/01/2012 15:00:40 TOracleQuery $0503BD90 Start Query.Execute
SQL = select * from nls_database_parameters where parameter like 'NLS%CHARACTERSET'
06/01/2012 15:00:40 TOracleQuery $0503BD90 End (2 records processed)
Duration = 0.125
06/01/2012 15:00:44 TOracleQuery Query Start Query.Execute
SQL = select * from v$version
06/01/2012 15:00:44 TOracleQuery Query End (5 records processed)
Duration = 0.047
06/01/2012 15:00:44 TOracleQuery $0503BD90 Start Query.Execute
SQL = select * from nls_database_parameters where parameter like 'NLS%CHARACTERSET'
06/01/2012 15:00:44 TOracleQuery $0503BD90 End (2 records processed)
Duration = 0.031
06/01/2012 15:00:49 TOracleQuery Query Start Query.Execute
SQL = select * from v$version
06/01/2012 15:00:49 TOracleQuery Query End (5 records processed)
Duration = 0.047
06/01/2012 15:00:49 TOracleQuery $0503BD90 Start Query.Execute
SQL = select * from nls_database_parameters where parameter like 'NLS%CHARACTERSET'
06/01/2012 15:00:49 TOracleQuery $0503BD90 End (2 records processed)
Duration = 0.031
06/01/2012 15:00:54 TOracleQuery Query Start Query.Execute
SQL = select * from v$version
06/01/2012 15:00:55 TOracleQuery Query End (5 records processed)
Duration = 0.063
06/01/2012 15:00:55 TOracleQuery $0503BD90 Start Query.Execute
SQL = select * from nls_database_parameters where parameter like 'NLS%CHARACTERSET'
06/01/2012 15:00:55 TOracleQuery $0503BD90 End (2 records processed)
Duration = 0.062

 

[Marco Kalter]

Can you let me know which query failed?

 

[AK_Itim]

select * from d_item;

 

[Marco Kalter]

I see that the query text is sent to the server, but it does not return a result before the query is cancelled.

 

[AK_Itim]

This is true, a result is not returned, and PL/SQL Developer hangs when we try and cancel the query.

This presents 2 questions:

1) Why does running the query through PL/SQL Developer not return any results, but SQLPlus does?

2) Why does PL/SQL Developer crash when we try and cancel the query?

 

[AK_Itim]

Hi Marco, do you have any further ideas on this issue?

 

[Marco Kalter]

Not yet. Since the query does simply not return, there is little we can do.

 

[AK_Itim]

What possible reason could there be for the application to not return data, and subsequently freeze, and for SQLPlus to return data as expected?

 

[Marco Kalter]

That question is difficult to answer. Maybe SQL*Plus uses different array sizes or other query attributes. Somehow your DEFAULT_SDU_SIZE fix worked for SQL*Plus, but not for PL/SQL Developer, but why exactly that is is difficult to say.

 

[Alan Jacobson]

We are having a similar problem. We have a couple of simple queries that just hang in PL/SQL developer but run in SQLPlus. (They also fail using Oracle SQL Developer). We are starting to think that there is an issue with the Oracle 11.2.0.3 listener. They work when we use the Oracle 11.2.0.2 listener. It does not mater which version of the Oracle Client we use or which version of Windows (XP or Windows 7). Also, it does not matter what version of PL/SQL developer we use (we tried 7.x, 8.x and 9.x). Since it also failes in Oracle SQL Developer, it does not appear to be a PL/SQL developer issue.

We originally thought it was an issue for a location but they also said that it was failing in the application which is hosted in a different location that the failing PC's.

To make things more interesting, when I take my laptop to one of the locations that are having the problem and connect with their network, my laptop works.

We have opened a service request with Oracle about this issue.

Fortunately, we have not upgraded our production environment yet so the problem is only on the test environment.

 

[Alan Jacobson]

Our issue was similar to the original posters issue.

It turned out to be an issue with a firewall setting. Networking changed the ALG (Application Layer Gateway) on the firewall to uncheck the SQL packet inspection. This fixed the problem.

to recap our issue:
We never had a problem until we upgraded from 11.2.0.2 single instance to 11.2.0.3 RAC One Node

Several SQL statements would hang from one location and also the application which is hosted at a different location.

We were able to repoduce this problem with a simple 99 row table that had about a dozen number and varchar2 columns (names, addresses, etc).

The SQl statement was "select * from aa_temp1". The table had no indexes.

If we conencted with the old 11.2.0.2 listener to the RAC One Node database, the SQl statement would always work.

It was only when we used the new 11.2.0.3 SCAN listener that the SQl statement would hang. It did not matter if we used the 11.2 client or the 10.2 client. Also, windows XP or windows 7 did not make any difference. It would fail when we executed the query from Oracle SQL developer or PL/SQL developer. It would work when we used SQL Plus.

When we changed the array size to 100 in SQL Plus, then SQL Plus would hang.

 

[MattS]

I just posted another topic on this before I noticed it here. WE have the same problem. Except all other clients work fine just not plsqldeveloper. My test is just "select * from dual;" this crashes plsqldeveloper 9.x but 8.x seems to work consistently. I have tried using a single ip from the scan as the address in tnsnames. Still the same result. Many of our developers are moving over to sqldeveloper because it works. But they like plsqldeveloper.

Looking at the process calls it is waiting on a tcp response. The last action from plsqldev.exe is tcp send.

9:44:33.1803298 AM plsqldev.exe 5984 RegOpenKey HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1 SUCCESS Desired Access: Query Value
9:44:33.1803440 AM plsqldev.exe 5984 RegQueryValue HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1\ORA_NO_KPFT NAME NOT FOUND Length: 144
9:44:33.1803538 AM plsqldev.exe 5984 RegCloseKey HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1 SUCCESS
9:44:33.1803668 AM plsqldev.exe 5984 RegOpenKey HKCU\SOFTWARE\ORACLE\KEY_OraClient11g_home1 NAME NOT FOUND Desired Access: Query Value
9:44:33.1803760 AM plsqldev.exe 5984 RegOpenKey HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1 SUCCESS Desired Access: Query Value
9:44:33.1803895 AM plsqldev.exe 5984 RegQueryValue HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1\ORA_KPF_NBUF NAME NOT FOUND Length: 144
9:44:33.1803992 AM plsqldev.exe 5984 RegCloseKey HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1 SUCCESS
9:44:33.1804118 AM plsqldev.exe 5984 RegOpenKey HKCU\SOFTWARE\ORACLE\KEY_OraClient11g_home1 NAME NOT FOUND Desired Access: Query Value
9:44:33.1804207 AM plsqldev.exe 5984 RegOpenKey HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1 SUCCESS Desired Access: Query Value
9:44:33.1804339 AM plsqldev.exe 5984 RegQueryValue HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1\ORA_KPF_BUFSZ NAME NOT FOUND Length: 144
9:44:33.1804437 AM plsqldev.exe 5984 RegCloseKey HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1 SUCCESS
9:44:33.1804568 AM plsqldev.exe 5984 RegOpenKey HKCU\SOFTWARE\ORACLE\KEY_OraClient11g_home1 NAME NOT FOUND Desired Access: Query Value
9:44:33.1804657 AM plsqldev.exe 5984 RegOpenKey HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1 SUCCESS Desired Access: Query Value
9:44:33.1804786 AM plsqldev.exe 5984 RegQueryValue HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1\ORA_NO_NATIVETCP NAME NOT FOUND Length: 144
9:44:33.1805149 AM plsqldev.exe 5984 RegCloseKey HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1 SUCCESS
9:44:33.1855664 AM plsqldev.exe 5984 TCP Send VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 170
9:44:33.1856195 AM plsqldev.exe 5984 TCP Receive VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 623
9:44:33.1910598 AM plsqldev.exe 5984 TCP Send VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 21
9:44:33.1918578 AM plsqldev.exe 5984 TCP Receive VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 125
9:44:33.1973528 AM plsqldev.exe 5984 TCP Send VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 234
9:44:33.1986821 AM plsqldev.exe 5984 TCP Receive VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 388
9:44:33.2037153 AM plsqldev.exe 5984 TCP Send VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 21
9:44:33.2045064 AM plsqldev.exe 5984 TCP Receive VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 136
9:44:33.2097442 AM plsqldev.exe 5984 TCP Send VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 190
9:44:33.2109989 AM plsqldev.exe 5984 TCP Receive VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 305
9:44:33.2161443 AM plsqldev.exe 5984 TCP Send VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 21
9:44:33.2168931 AM plsqldev.exe 5984 TCP Receive VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 131
9:44:33.2224309 AM plsqldev.exe 5984 TCP Send VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 274
9:44:33.2240061 AM plsqldev.exe 5984 TCP Receive VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 134
9:44:33.2293496 AM plsqldev.exe 5984 TCP Send VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 188
9:44:33.2305616 AM plsqldev.exe 5984 TCP Receive VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 111
9:44:33.2360379 AM plsqldev.exe 5984 TCP Send VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 193
9:44:33.2368720 AM plsqldev.exe 5984 TCP Receive VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 111
9:44:33.2423373 AM plsqldev.exe 5984 TCP Send VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 201
9:44:33.2433231 AM plsqldev.exe 5984 TCP Receive VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 111
9:44:33.2488683 AM plsqldev.exe 5984 TCP Send VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 216
9:44:33.2500288 AM plsqldev.exe 5984 TCP Receive VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 290
9:44:33.2587423 AM plsqldev.exe 5984 TCP Send VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 21
9:44:33.2612748 AM plsqldev.exe 5984 TCP Receive VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 217
9:44:33.2670377 AM plsqldev.exe 5984 TCP Send VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 324
9:44:33.2687065 AM plsqldev.exe 5984 TCP Receive VCMSOUTHCOTT.Paypros.com:1176 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 135
9:44:33.2742852 AM plsqldev.exe 5984 TCP Send VCMSOUTHCOTT.Paypros.com:1146 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 193
9:44:33.2757124 AM plsqldev.exe 5984 TCP Receive VCMSOUTHCOTT.Paypros.com:1146 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 234
9:44:33.2808409 AM plsqldev.exe 5984 TCP Send VCMSOUTHCOTT.Paypros.com:1146 -> vc-orcltrx-scan.qa.paygateway.com:1521 SUCCESS Length: 21

 

[MattS]

This is what we found after hours and hours of tracing network activity. If we run plsqldeveloper without our vpn connected it works fine. But with the vpn connection the servers side issues a connection reset by peer. The client (plsqldeveloper) doesn't notice this or is not notified and waits for the tcp socket to respond. Never times out.

plsqldeveloper is the only application that exhibits this problem. All other client db connections are fine. What is up with plsqldeveloper?

 

[Alan Jacobson]

These type of issues are very hard to resolve.

Does pl/sql developer return any error messages such as ORA-03113?

What is the version of the Oracle database that you are trying to connect to?

We just had one issue where they needed to "enable the path mtu discovery through all firewalls" because of a disconnect issue (but that was not with PL/SQL developer).

Can you startup PL/SQL developer with the DebugSQL option. That is do: "C:\Program Files\PLSQL Developer\plsqldev.exe" DebugSQL

Then when you do the select * from dual, you will see the additional SQL that PL/SQL developer is sending across before it even does the "select * from dual".

Can you compare what version 8 sends when you do that SQL to what version 9 sends? I could be a number of issues but since it works without the VPN, I would think that it is a VPN issue. The VPN might be not allowing certain packets to pass through either from the client to the server or from the server to the client.

If you can pin down the SQL that is causing the issue, can you execute it from SQLPlus? If it returns more than 10 rows, you might need to do: "Set arraysize 100;" first in SQLPlus.