This discussion is archived
7 Replies Latest reply: Jul 31, 2012 8:42 AM by PaoloM RSS

Find DB Objects window too slow in latest SQL Developer version (bug?)

PaoloM Newbie
Currently Being Moderated
Hello,
I come back on the issue that is affecting the most my productivity when using SQL Developer:
the Find DB Objects window is too slow to be usable when connecting to an Oracle schema with a lot of objects (like the APPS schema of a recent Oracle e-Business Suite instance, that contains about 170000+ objects).

I noticed that the Find DB Objects window performs way better in SQL Developer version 1.5.5 and I believe this is due to the fact that at a certain point the Find DB Objects window and the Extended Search window where unified to give the unusable window present in the latest version.

For example, when I am using the Find DB Objects window to look for a package with a certain name, in SQL Developer 3.1 the search takes about *25 seconds* to return the result, while in version 1.5.5 about *3 seconds*. Moreover, when I click on the results in version 3.1 there is a further delay and the whole application freezes for about *40 seconds* before opening a new window to show the package source, while in version 1.5.5 it takes about *1 or 2 seconds* to show the source after you click on the results.

Is it possible to do some performance optimization on the Find DB Objects window in the next release to make it perform like it used to in the oldest version?

Thanks,
Paolo
  • 1. Re: Find DB Objects window too slow in latest SQL Developer version (bug?)
    Vadim Tropashko Pro
    Currently Being Moderated
    Trying to reproduce 115@9.2. Witnessed 50 sec search objects when schema=ALL. It is snappy when schema is chosen with type=PACKAGE. Then, opening PL/SQL editor is fast as well. What db version are you running?

    Edit 3.1.0.6.78: that was poorly tuned instance. On another 9.2 instance I witnessed instantaneous listing of FND_STATS both by APPS and AK users. Then navigating to PL/SQL editor is momentary as well. It must be that your db version is 10 or 11?
  • 2. Re: Find DB Objects window too slow in latest SQL Developer version (bug?)
    PaoloM Newbie
    Currently Being Moderated
    Hello Vadim,
    thanks for taking time to look at my issue.

    I am connecting to an Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production on which runs an Oracle eBusiness Suite instance R12.1.3, while SQL Developer is version 3.1.07.42.
    I am willing to run some tests for you on the instance I have here if you cannot access a similar one.

    Currently I reverted to SQLDev 1.5 to avoid the performance issue.

    Thanks,
    Paolo
  • 3. Re: Find DB Objects window too slow in latest SQL Developer version (bug?)
    Vadim Tropashko Pro
    Currently Being Moderated
    What SQL worksheet toolbar timing when you run:


    select count(*) from (
    select null name, -1 LINE, -1 COL, null USAGE,
    case when obj.object_type like 'JAVA%' then 'JAVA' else obj.object_type end TYPE,
    OWNER, case when obj.object_type like 'JAVA%' then 'JAVA' else obj.object_type end OBJECT_TYPE,
    obj.OBJECT_NAME
    from sys.dba_objects obj where rownum <= 500 and obj.object_type != 'TABLE PARTITION'
    and obj.object_type != 'TABLE SUBPARTITION' and obj.object_type != 'JAVA CLASS'
    AND NOT EXISTS (SELECT 1 FROM SYS.ALL_MVIEWS WHERE MVIEW_NAME = obj.OBJECT_NAME
    AND OWNER = obj.OWNER and obj.object_type = 'TABLE')
    AND NOT EXISTS (SELECT 1 from all_queue_tables WHERE QUEUE_TABLE = obj.OBJECT_NAME AND OWNER = obj.OWNER
    and obj.object_type = 'TABLE') and object_name like 'AD_DD'
    union all
    select null name, -1 LINE, -1 COL, null USAGE,
    'MATERIALIZED VIEW LOG' TYPE, log_owner OWNER,
    'MATERIALIZED VIEW LOG' OBJECT_TYPE, master OBJECT_NAME
    from all_snapshot_logs where master like 'AD_DD'
    union
    select null name, -1 LINE, -1 COL, null USAGE,
    'DATABASE LINK' TYPE, owner OWNER,
    'DATABASE LINK' OBJECT_TYPE, db_link OBJECT_NAME
    from all_db_links where rownum <= 500 and db_link like 'AD_DD'
    );

    If it's more than 10 sec, then can you run autotrace (F6, the third left toolbar button), autotrace output can be exported as html from grid tree table context menu. I observed 2.4 sec/6451 buffer gets @11.2.0.3.0.
  • 4. Re: Find DB Objects window too slow in latest SQL Developer version (bug?)
    PaoloM Newbie
    Currently Being Moderated
    Vadim,
    I run your query and it took 26,3 seconds (consistent gets=41028) on the first run on our DEV instance, while it took 42,1 seconds (consistent gets=41184) on the first run on our PROD instance. All subsequent runs take less than 2 seconds.

    I also performed the autotrace and I exported it, but since I believe it's not possible to attach anything to a forum message, how can I send it to you?

    Thanks,
    Paolo
  • 5. Re: Find DB Objects window too slow in latest SQL Developer version (bug?)
    Vadim Tropashko Pro
    Currently Being Moderated
    My email is firstname.lastname@o...cle.com. As you see from the query it is the two disjunctive parts that were added in latest releases. Without them I experienced much better performance (in 9.2 drop from 80K buffer gets to 3K). Can you please double check that the query

    select count(*) from (
    select null name, -1 LINE, -1 COL, null USAGE,
    case when obj.object_type like 'JAVA%' then 'JAVA' else obj.object_type end TYPE,
    OWNER, case when obj.object_type like 'JAVA%' then 'JAVA' else obj.object_type end OBJECT_TYPE,
    obj.OBJECT_NAME
    from sys.dba_objects obj where rownum <= 500 and obj.object_type != 'TABLE PARTITION'
    and obj.object_type != 'TABLE SUBPARTITION' and obj.object_type != 'JAVA CLASS'
    AND NOT EXISTS (SELECT 1 FROM SYS.ALL_MVIEWS WHERE MVIEW_NAME = obj.OBJECT_NAME
    AND OWNER = obj.OWNER and obj.object_type = 'TABLE')
    AND NOT EXISTS (SELECT 1 from all_queue_tables WHERE QUEUE_TABLE = obj.OBJECT_NAME AND OWNER = obj.OWNER
    and obj.object_type = 'TABLE') and object_name like 'AD_DD'
    );

    runs fast?
  • 6. Re: Find DB Objects window too slow in latest SQL Developer version (bug?)
    PaoloM Newbie
    Currently Being Moderated
    Vadim,
    I can confirm that the new query is performing quickly in all the instances I tested it. I also sent you via email the exported output of the autotrace of both queries run on our PROD instance.

    If it can be useful, I may have access to other eBS instances if you need some output from eBS instances with older versions, though I believe all of them have at least a 10g database.

    Thanks,
    Paolo
  • 7. Re: Find DB Objects window too slow in latest SQL Developer version (bug?)
    PaoloM Newbie
    Currently Being Moderated
    Vadim,
    I have noticed a huge performance increase in the "Find DB Object" window when connecting to one of our instances where the database has been recently upgraded from 11.2.0.1 to 11.2.0.3.

    Then I found this MOS note (Sql Developer Slow Against 11g Database [ID 1215063.1]) and I believe that I am hitting the same issue. So very likely the bug is in the database and not in SQL Developer.

    Hope it helps other people seeing the same performance problem.

    Thanks,
    Paolo

Legend

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