This discussion is archived
5 Replies Latest reply: Oct 23, 2012 8:03 AM by aasubram RSS

Connection time slow

aasubram Expert
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    Thanks a lot Gary. Will get back to you with the outcome
  • 3. Re: Connection time slow
    aasubram Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    Thanks a lot again. I will get back to you on this very soon

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points