This discussion is archived
9 Replies Latest reply: May 17, 2012 2:26 AM by PaoloM RSS

Perf issue in find db object panel when try to find object second time

675469 Newbie
Currently Being Moderated
There is performance problem in "find db object" window.

sqldeveloper-3.1.07.42

Steps:

1. By using Connection panel connect to any DB.

2. View -> find db object.

3. Select the Connection which is already connected.

4. Enter a name, click more select schema and type, (my case type is Package body), then click lookup button.

5. Object is listed, now click on the name so that it package body will open in new tab. Now we can do whatever operation we want we can do it on the opened package code.

Till now no problem, everything is working fine.

Now i want find another object, so i click on find db object panel to change the name. It is taking more time, i am not able edit the name immediately.

Same problem happens in old version of SQL Developer also.
  • 1. Re: Perf issue in find db object panel when try to find object second time
    Gary Graham Expert
    Currently Being Moderated
    Hi Vasanth,

    I don't think this is the fault of the product per se, although there are cases where the "Connection Busy (Try Again / Abort)" appears. I tried on both a slow WinXP system and a fast Linux system. Each performed fairly well, but the the faster machine was smoother. More objects open simultaneously seemed to have an impact. Getting interrupted for a few minutes (SQL Developer may have gotten partially paged out), then resuming the test seemed to have an impact.

    Did you check SQL Developer's VM Size or memory use during your session?

    Regards,
    Gary
    SQL Developer Team
  • 2. Re: Perf issue in find db object panel when try to find object second time
    675469 Newbie
    Currently Being Moderated
    Hi Gary,

    How to check "SQL Developer's VM Size or memory use during your session" ?

    Thanks,
    Vasanth. V
  • 3. Re: Perf issue in find db object panel when try to find object second time
    Gary Graham Expert
    Currently Being Moderated
    Vasanth,

    To get the actual Java VM heap size you would have to use a memory profiler. In a future release, SQL Developer will probably show this Java VM size at the bottom right of the main window like current JDeveloper versions do. To get a rough idea now, however, you can check ...

    1. On Windows, using Task Manager, enable the VM Size column in the Processes tab and check that for sqldeveloper.exe.
    2. On Linux, using "top", check the memory for the Java process that represents SQL Developer.

    Because the first find is fast, I have not worried about the total number of objects in the schema or database latency. Note that find for all types does take longer than find for a specific type, but apparently that is not your use case.

    -Gary
  • 4. Re: Perf issue in find db object panel when try to find object second time
    675469 Newbie
    Currently Being Moderated
    Hi Gary,

    For this SQL Developer

    Mem usage : 257,000 K
    VM Size : 250,000 K

    Laptop is windows XP with 4 GB RAM, RAM usage is around 1.5 GB.

    I have downloaded this SQL Developer with JDK.

    Thanks,
    Vasanth.
  • 5. Re: Perf issue in find db object panel when try to find object second time
    675469 Newbie
    Currently Being Moderated
    Any update on this please?
  • 6. Re: Perf issue in find db object panel when try to find object second time
    Gary Graham Expert
    Currently Being Moderated
    Hi Vasanth,

    I finally had time to profile this. In my case, especially when the number of objects found is large, it looks like the performance hit is related to use of Oracle Look and Feel. You can try using Tools|Preferences|Environment|Look and Feel: Windows if the prior setting was Oracle and see if that helps.

    -Gary
  • 7. Re: Perf issue in find db object panel when try to find object second time
    675469 Newbie
    Currently Being Moderated
    Hi Gary,

    I always use window look, not the default oracle one.

    Thanks,
    Vasanth.
  • 8. Re: Perf issue in find db object panel when try to find object second time
    Gary Graham Expert
    Currently Being Moderated
    Hi Vasanth,

    Reading over this thread, it strikes me that I was never able to replicate the described behavior with the provided test case. The procedure you give is fine, but to be complete the case description should also include the Oracle database version your connection points to, whether it's remote, the privileges your user has, and so on. By doing lots of other things, I got slower query performance but not slower name prompt performance. And, in those slower query cases, switching from Oracle to Windows look & feel really did help.

    At this point you might try keeping Task Manager open and visible during the test case, with the Processes tab sorted on CPU descending. When the slow performance occurs, are SQL Developer and/or Oracle (if the DB is local) doing anything? If the culprit is a CPU-intensive SQL Developer phase, then the next step would be to do some CPU profiling (using something like JDeveloper or Netbeans) in your environment. Otherwise something must be happening on the DB end of things.

    -Gary
  • 9. Re: Perf issue in find db object panel when try to find object second time
    PaoloM Newbie
    Currently Being Moderated
    Hello Gary,
    it seems I am hitting exactly the same issue as Vasanth and I can confirm that the slow down is real and very annoying.

    I use latest SQLDev downloaded with its JDK and I also am using the Windows Look and Feel. I am connecting to a remote 11.2.0.1.0 database that is part of an e-Business Suite 12.1.3 instance as the APPS user (that has quite a lot of access rights).

    In reality, I experience SEVERAL different cases when SQLDev slows down so much that it seems to freeze for several seconds and I am afraid that the slowdowns may be due to the huge number of objects that are owned by the APPS user (a count on user_objects gives me 170953 results).

    Also I do not see any meaningful CPU usage spikes during those slow downs (I am using Process Explorer by Microsoft to monitor the tasks).

    I would be very glad if I can do some more debugging and help you tracking down the issue that bugs me so much. Please let me know what else I can try to help you.

    Thanks,
    Paolo

Legend

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