5 Replies Latest reply: Oct 23, 2012 10:03 AM by Aasubram-Oracle RSS

    Connection time slow

    Aasubram-Oracle
      Hello Everyone,

      When using sqplus remotly, via CMAN, it takes 3s to connect to the database. However when I use SQLDeveloper it takes nearly 30 secs to establish the connection.

      Version: 3.2
      Connection type: Basic

      It happens for every client that connects to this DB. SO this is not specific to one installation of SQLDeveloper. It is a RAC DB though. Any pointers?

      Thanks,
      Anand
        • 1. Re: Connection time slow
          Gary Graham-Oracle
          Hi Anand,

          In order to see what activities consume the most time during connect for the remote DB, try the following.

          1. Change the following at the end of your ...\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf file
          IncludeConfFile sqldeveloper-nondebug.conf
          should be changed to...
          IncludeConfFile sqldeveloper-debug.conf
          2. Launch SQL Developer
          3. Right click in the Logging Page and clear the log.
          4. Open the connection
          5. Some lines (Time to initialize/Opening connection for) show milliseconds in the Message column
          6. Otherwise look at the Elapsed column for log entry times.

          Not sure how accurate the values are, if there are gaps, etc.

          Report back on which Source column activities take the longest (db, DBUtil (preparing/loading), NLSProvider, etc.

          Regards,
          Gary
          SQL Developer Team
          • 2. Re: Connection time slow
            Aasubram-Oracle
            Thanks a lot Gary. Will get back to you with the outcome
            • 3. Re: Connection time slow
              Aasubram-Oracle
              Hi Gary,
              This seems to have taken the longest time

              FINER     501     20904     oracle.javatools.db     Opening connection for xlabint took 5445ms

              Thanks,
              Anand
              • 4. Re: Connection time slow
                Gary Graham-Oracle
                Hi Anand,

                If you have the privileges to do this, you can trace what is going on the server side. Create a logon trigger for the user of interest (xlabint). Make sure that user has the alter session privilege. Connect as that user from SQL Developer, then disconnect. Get the sql trace (.trc) from the server and run tkprof against it. Here is a code snippet of the trigger and grant I executed from the sys user on my 11.2.0.1 Oracle DB.
                create or replace trigger scott.logon_trigger                                                
                 after logon on scott.schema                                                                 
                 begin                                                                                         
                   execute immediate 'alter session set events ''10046 trace name context forever, level 4'''; 
                  exception                                                                                    
                   when others then null;                                                                      
                 end;                                                                                          
                 /                                                                                             
                
                grant alter session to scott;
                If you cannot do that, try running the individual sql statements or PL/SQL anonymous blocks that I extracted from a
                sqltrace/tkprof done on my system. See which are taking the longest. Whatever is slow should be investigated by your DBA.
                select * from v$version where banner like '%Oracle%'
                
                select USER from dual
                
                alter session set PLSCOPE_SETTINGS='identifiers:all'
                
                select sys_context('USERENV','SESSIONID') from dual
                
                select sys_context('USERENV','SID') from  dual
                
                alter session set PLSQL_OPTIMIZE_LEVEL=2
                
                alter session set PLSQL_DEBUG=false
                
                select count(1) from all_objects where object_name = :1
                
                select version_no from apex_release
                
                select parameter,value from nls_session_parameters
                 union all SELECT 'DB_TIMEZONE' name, DBTIMEZONE  value FROM DUAL
                 union all SELECT 'SESSION_TIMEZONE' name, SESSIONTIMEZONE value FROM DUAL
                 union all SELECT 'SESSION_TIMEZONE_OFFSET' name, TZ_OFFSET(SESSIONTIMEZONE) value from DUAL
                 union all SELECT parameter, value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET'
                
                ALTER SESSION SET TIME_ZONE = 'America/Los_Angeles'
                
                SELECT DBTIMEZONE FROM DUAL
                
                select con_id
                from (select sys_context('userenv','con_id') as con_id from dual)
                where con_id is not null and con_id = 1
                
                declare
                  m_stmt  varchar2(512);
                begin
                   m_stmt:='delete from sdo_geor_ddl__table$$';
                   EXECUTE IMMEDIATE m_stmt;
                   EXCEPTION
                     WHEN OTHERS THEN
                       NULL;
                end;
                
                delete from sdo_geor_ddl__table$$
                
                select banner from v$version where banner like '%Express Edition%'
                
                select 1 from dba_db_links where 1=2
                
                select 1 from sys.obj$ where 1=0
                
                select text from view$ where rowid=:1
                
                select user_id, username, SYS_CONTEXT('USERENV','CURRENT_SCHEMA') current_schema
                from  all_users
                
                select 1 from dba_tab_privs where 1=2
                
                select 1 from dba_directories where 1=2
                
                select 1 from dba_objects where 1=2
                
                select 1 from dba_indexes where 1=2
                
                select 1 from dba_mviews where 1=2
                
                select 1 from dba_snapshot_logs where 1=2
                
                select 1 from dba_plsql_object_settings where 1=2
                
                select 1 from dba_synonyms where 1=2
                
                select 1 from sys.external_tab$ where 1=2
                
                select 1 from sys.tab$ where 1=2
                
                select 1 from dba_triggers where 1=2
                
                select 1 from dba_views where 1=2
                
                select 1 from sys.dba_recyclebin where 1=2
                
                select 1 from apex_release where 1=2
                
                select 1 from dba_queues where 1=2
                
                select 1 from dba_queue_tables where 1=2
                
                select 1 from dba_editions where 1=2
                
                select 1 from dba_xml_schemas where 1=2
                
                /*+ no_sql_translation */
                  SELECT 1 FROM dual
                
                /*+ no_sql_translation */
                  SELECT dbms_transaction.local_transaction_id FROM dual
                Regards,
                Gary
                • 5. Re: Connection time slow
                  Aasubram-Oracle
                  Thanks a lot again. I will get back to you on this very soon