This content has been marked as final. Show 5 replies
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.conf2. Launch SQL Developer
should be changed to...
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.
SQL Developer Team
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 22.214.171.124 Oracle DB.
If you cannot do that, try running the individual sql statements or PL/SQL anonymous blocks that I extracted from a
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;
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