SQL Developer: Tastes Great, Less Filling?

thatjeffsmith SQL Developer 43 Comments

Tell Others About This Story:

The ‘Holy Grail’ of software tools is the environment where things are made easy, but not at the expense of usability or simplicity. You use a tool because it makes something ‘easier’, but some software products are anything but.

SQL Developer is fairly scaled down. You have a few toolbars, several menus, and 2 driving windows – the Worksheet and the Browser. But what if it could be even simpler? SQL Developer is asked to be the database IDE. We serve many masters, yet a majority of our users simply look to the tool to be a GUI replacement of SQL*Plus.

What if you as a user could ‘slim’ down SQL Developer? Less menu items, fewer widgets to load? A leaner, meaner SQL Developer?

Well, you can do that today.

Disable all the extensions.

This question came up at my presentation at RMOUG, and I didn’t have a great answer. Fortunately my boss was there and he had a really good answer: simply go into the preferences and disable all the extensions.

We ship with everything 'on.' That can be easily rectified in the preferences.

I suggest you turn off everything but the ‘SearchBar.’ Once you make a change here you will need to re-start SQL Developer to make the changes take effect. You will see two primary advantages:

  • Faster load time
  • Simplified UI

Ok, so the first item is purely subjective. On my WIN7x64 machine SQL Developer is loading in a little less than 10 seconds versus about 30 seconds. I’m not guaranteeing this benefit, but try it for yourself and see how it goes.

For the primary benefit, you will notice the main menu items are scaled down. No items for Source Control, the Data Modeler, Migrations, or Data Miner. These are not ‘bad’ things, but chances are you rarely use these features. You can in fact turn them off (and turn them on, as required.)

‘Lite’ Mode

Stuff Turned Off

Default Installation

SQL Developer Default Interface

Further Optimize Your SQL Developer Experience

There are a few more techniques and preferences you can exploit to get SQL Developer running faster and using less system resources. Note that I run SQL Developer ‘out of the box’ with no tweaks and it runs just fine. However what works for many people won’t necessarily be right for everyone. Here are a few changes you can make in a few moments to dramatically affect SQL Developer’s performance.

Set Look and Feel Preference to Host OS

Look and Feel is a Java property that controls how the application’s GUI ‘widgets’ are drawn and behave. You can let the host OS control this, or you can implement your own themes. SQL Developer ships with an ‘Oracle’ Look and Feel theme. I think it’s nicer than what Windows gives out of the box for Java applications.

BUT.

It costs more, CPU to be specific. It takes more work for the tool to run if it’s controlling the ‘look and feel’ instead of just farming that out to the operating system. Now the funny or weird part here is that I’ve heard a few OS X folks tell me they see the exact opposite behavior. So, if you see SQL Developer running slow or it chewing up the CPU, try switching this preference and restarting the application.

Oracle looks better but Windows will run leaner

Close Grids and Files When You’re Done with Them

This is hopefully common sense. Having data loaded in a grid consumes memory. Lots of rows = lots of memory. Close the grids when you’re done. When you close a grid it forces the JVM to do garbage collection. This is fancy talk for saying that the java application will release memory back to the java virtual machine.

Don’t Set Your SQL History Limit to Some Obscenely High Value

Like 30,000. That’s too high. I think the default (100) is too low. Mine is currently set to 500. Just know that what you set it to affects memory. The SQL History data is read into memory at start up. The more you save, the more it costs.

Still Experiencing SQL Developer Performance Issues?

Drop me a line at [email protected] or leave a comment here. We’ll do our best to get your issue sorted out. Keep in mind I’ll ask you to try the above tips first, then ask you what version of SQL Developer you’re running and what JDK you’re using. Answers including anything BUT ‘Latest version and Java 6/1.6 JDK’ will be me replied with ‘try that and get back to me.’

With more than 2,500,000 active users today, Oracle SQL Developer is bound to be running in some environments that cause performance issues. We won’t be satisfied until everyone has a good of an experience with it as we do!

Tell Others About This Story:

Comments 43

    1. thatjeffsmith Post
      Author

      Everything changed in 4. For example, if you want to disable Migrations, you would do that in the Features menu item under Tools. But, it won’t speed things up much as version 4 doesn’t load extensions/jars until they’re needed (OSGi)

  1. Hey Jeff,

    I’ve been trying to find a solution for my scenario. I work in an environment that we have several hundred different database connections. I was able to get a script to convert my .ORA into an XML and import that into my connections and it worked perfectly. I cant say exactly how many connections I have but my .XML connection file is approaching 4mb. The problem I have now is that the first time I launch Developer and the first time a connect to a connection, both of those actions now take around ~3 minutes. When I was supporting closer to 30 connections my load time was nearly immediate so I doubt I have an environmental issue. Is there any way to speed up load time when I need this many connections or am I at the mercy of just something that is working as designed? Extra note, I have this many connections in case I need in on demand but I may only use 3-5 of the connections per day.

    Thanks for any help in advance.

  2. Hi,
    below is the trace file entry for the run of the a test, under the test we have four test implementation.

    TKPROF: Release 11.2.0.4.0 – Development on Mon Sep 1 18:00:52 2014

    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

    Trace file: UTDB_ora_7487.trc
    Sort options: default

    ********************************************************************************
    count = number of times OCI procedure was executed
    cpu = cpu time in seconds executing
    elapsed = elapsed time in seconds executing
    disk = number of physical reads of buffers from disk
    query = number of buffers gotten for consistent read
    current = number of buffers gotten in current mode (usually for update)
    rows = number of rows processed by the fetch or execute call
    ********************************************************************************

    The following statements encountered a error during parse:

    SELECT 1 FROM DUAL WHERE dest.UTR_ID = src.UTR_ID

    Error encountered: ORA-00904
    ——————————————————————————–
    SELECT 1 FROM DUAL WHERE dest.UTIR_ID = src.UTIR_ID

    Error encountered: ORA-00904
    ——————————————————————————–
    SELECT 1 FROM DUAL WHERE dest.UTIAR_ID = src.UTIAR_ID

    Error encountered: ORA-00904
    ——————————————————————————–
    SELECT 1 FROM DUAL WHERE dest.UTIVR_ID = src.UTIVR_ID

    Error encountered: ORA-00904
    ********************************************************************************

    Error in CREATE TABLE of EXPLAIN PLAN table: sys.plan_table
    ORA-01031: insufficient privileges

    EXPLAIN PLAN option disabled.
    ********************************************************************************

    SQL ID: 0hf85bfn1ujf5 Plan Hash: 374915200

    select name,ut_id
    from
    ut_test order by 1

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 1 0.00 0.00 0 5 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 1 0.00 0.00 0 2 0 14
    ——- —— ——– ———- ———- ———- ———- ———-
    total 3 0.00 0.00 0 7 0 14

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 1

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    14 14 14 TABLE ACCESS BY INDEX ROWID UT_TEST (cr=2 pr=0 pw=0 time=68 us cost=2 size=756 card=14)
    14 14 14 INDEX FULL SCAN UT_TEST_UK1 (cr=1 pr=0 pw=0 time=26 us cost=1 size=0 card=14)(object id 88700)

    ********************************************************************************

    SQL ID: 56nmwb37tna1p Plan Hash: 418640185

    select count(1)
    from
    ut_test_impl where ut_id = :ID

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 2 0.00 0.00 0 0 0 0
    Execute 2 0.00 0.00 0 0 0 0
    Fetch 2 0.00 0.00 0 2 0 2
    ——- —— ——– ———- ———- ———- ———- ———-
    total 6 0.00 0.00 0 2 0 2

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 2

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    1 1 1 SORT AGGREGATE (cr=1 pr=0 pw=0 time=72 us)
    3 4 4 INDEX RANGE SCAN UT_TEST_IMPL_UK1 (cr=1 pr=0 pw=0 time=60 us cost=1 size=111 card=3)(object id 88697)

    ********************************************************************************

    select
    CONNECTION_NAME
    from
    UT_TEST
    where
    UT_ID = :ID

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 2 0.00 0.00 0 0 0 0
    Execute 2 0.00 0.00 0 3 0 0
    Fetch 2 0.00 0.00 0 4 0 2
    ——- —— ——– ———- ———- ———- ———- ———-
    total 6 0.00 0.00 0 7 0 2

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 2

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    1 1 1 TABLE ACCESS BY INDEX ROWID UT_TEST (cr=2 pr=0 pw=0 time=43 us cost=1 size=71 card=1)
    1 1 1 INDEX UNIQUE SCAN UT_TEST_PK (cr=1 pr=0 pw=0 time=34 us cost=0 size=0 card=1)(object id 88624)

    ********************************************************************************

    SELECT
    T.UT_ID,
    T.NAME,
    T.CREATED_ON,
    T.CREATED_BY,
    T.OBJECT_NAME,
    T.OBJECT_TYPE,
    T.OBJECT_OWNER,
    T.OBJECT_CALL,
    T.COVERAGE,
    T.CONNECTION_NAME
    FROM
    UT_TEST T
    WHERE
    T.UT_ID = :ID

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 4 0.00 0.00 0 0 0 0
    Execute 4 0.00 0.00 0 0 0 0
    Fetch 4 0.00 0.00 0 8 0 4
    ——- —— ——– ———- ———- ———- ———- ———-
    total 12 0.00 0.00 0 8 0 4

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 3

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    1 1 1 TABLE ACCESS BY INDEX ROWID UT_TEST (cr=2 pr=0 pw=0 time=19 us cost=1 size=157 card=1)
    1 1 1 INDEX UNIQUE SCAN UT_TEST_PK (cr=1 pr=0 pw=0 time=12 us cost=0 size=0 card=1)(object id 88624)

    ********************************************************************************

    SELECT
    S.STARTUP_ID,
    S.INDEX_NO,
    S.STARTUP,
    SV.LIB_STARTUP_ID,
    SV.LIB_STARTUP_NAME,
    SV.LIB_STARTUP
    FROM
    UT_STARTUPS S,
    UT_LIB_STARTUPS SV
    WHERE
    S.UT_ID = :UT_ID AND
    S.LIB_STARTUP_ID = SV.LIB_STARTUP_ID(+)
    ORDER BY
    S.INDEX_NO

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 4 0.00 0.00 0 0 0 0
    Execute 4 0.01 0.01 0 0 0 0
    Fetch 4 0.00 0.00 0 7 0 1
    ——- —— ——– ———- ———- ———- ———- ———-
    total 12 0.01 0.01 0 7 0 1

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 3

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    0 0 0 SORT ORDER BY (cr=1 pr=0 pw=0 time=55 us cost=4 size=1628 card=1)
    0 0 0 NESTED LOOPS OUTER (cr=1 pr=0 pw=0 time=37 us cost=3 size=1628 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID UT_STARTUPS (cr=1 pr=0 pw=0 time=34 us cost=2 size=655 card=1)
    0 0 0 INDEX RANGE SCAN UT_STARTUPS_TEST_IX (cr=1 pr=0 pw=0 time=28 us cost=1 size=0 card=1)(object id 88683)
    0 0 0 TABLE ACCESS BY INDEX ROWID UT_LIB_STARTUPS (cr=0 pr=0 pw=0 time=0 us cost=1 size=973 card=1)
    0 0 0 INDEX UNIQUE SCAN UT_LIB_STARTUPS_PK (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 88640)

    ********************************************************************************

    SELECT
    T.TEARDOWN_ID,
    T.INDEX_NO,
    T.TEARDOWN,
    TV.LIB_TEARDOWN_ID,
    TV.LIB_TEARDOWN_NAME,
    TV.LIB_TEARDOWN
    FROM
    UT_TEARDOWNS T,
    UT_LIB_TEARDOWNS TV
    WHERE
    T.UT_ID = :UT_ID AND
    T.LIB_TEARDOWN_ID = TV.LIB_TEARDOWN_ID(+)
    ORDER BY
    T.INDEX_NO

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 4 0.00 0.00 0 0 0 0
    Execute 4 0.00 0.00 0 0 0 0
    Fetch 4 0.00 0.00 0 8 0 1
    ——- —— ——– ———- ———- ———- ———- ———-
    total 12 0.00 0.00 0 8 0 1

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 3

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    0 0 0 SORT ORDER BY (cr=2 pr=0 pw=0 time=104 us cost=3 size=1924 card=1)
    0 0 0 NESTED LOOPS OUTER (cr=2 pr=0 pw=0 time=87 us cost=2 size=1924 card=1)
    0 0 0 TABLE ACCESS FULL UT_TEARDOWNS (cr=2 pr=0 pw=0 time=85 us cost=2 size=1009 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID UT_LIB_TEARDOWNS (cr=0 pr=0 pw=0 time=0 us cost=0 size=915 card=1)
    0 0 0 INDEX UNIQUE SCAN UT_LIB_TEARDOWNS_PK (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 88645)

    ********************************************************************************

    SQL ID: 13wqjcrf0g8qp Plan Hash: 1299690962

    SELECT *
    FROM
    UT_TEST_ARGUMENTS where UT_ID = :ID order by POSITION

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 4 0.00 0.00 0 0 0 0
    Execute 4 0.00 0.00 0 0 0 0
    Fetch 4 0.00 0.00 0 8 0 4
    ——- —— ——– ———- ———- ———- ———- ———-
    total 12 0.00 0.00 0 8 0 4

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 3

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    1 1 1 SORT ORDER BY (cr=2 pr=0 pw=0 time=92 us cost=3 size=185 card=1)
    1 1 1 TABLE ACCESS FULL UT_TEST_ARGUMENTS (cr=2 pr=0 pw=0 time=63 us cost=2 size=185 card=1)

    ********************************************************************************

    SELECT
    TI.UTI_ID,
    TI.UT_ID,
    TI.NAME,
    TI.EXPECTED_RETURN,
    TI.EXPECTED_RETURN_ERROR,
    TI.DYNAMIC_VALUE_QUERY,
    DQ.LIB_DYN_QUERY_ID,
    DQ.LIB_DYN_QUERY_NAME,
    DQ.LIB_DYN_QUERY,
    TI.CREATED_ON,
    TI.CREATED_BY
    FROM
    UT_TEST_IMPL TI,
    UT_LIB_DYN_QUERIES DQ
    WHERE
    TI.UT_ID = :ID and
    TI.LIB_DYN_QUERY_ID = DQ.LIB_DYN_QUERY_ID(+)

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 4 0.00 0.00 0 0 0 0
    Execute 4 0.00 0.00 0 0 0 0
    Fetch 4 0.00 0.00 0 12 0 13
    ——- —— ——– ———- ———- ———- ———- ———-
    total 12 0.00 0.00 0 12 0 13

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 3

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    3 3 3 NESTED LOOPS OUTER (cr=3 pr=0 pw=0 time=111 us cost=3 size=4224 card=3)
    3 3 3 TABLE ACCESS FULL UT_TEST_IMPL (cr=3 pr=0 pw=0 time=78 us cost=2 size=915 card=3)
    0 0 0 TABLE ACCESS BY INDEX ROWID UT_LIB_DYN_QUERIES (cr=0 pr=0 pw=0 time=11 us cost=1 size=1103 card=1)
    0 0 0 INDEX UNIQUE SCAN UT_LIB_DYN_QUERIES_PK (cr=0 pr=0 pw=0 time=6 us cost=0 size=0 card=1)(object id 88655)

    ********************************************************************************

    SELECT
    L.VALIDATION_ID,
    L.INDEX_NO,
    L.VALIDATION,
    LV.LIB_VALIDATION_ID,
    LV.LIB_VALIDATION_NAME,
    LV.LIB_VALIDATION,
    L.APPLY_VALIDATION
    FROM
    UT_VALIDATIONS L,
    UT_LIB_VALIDATIONS LV
    WHERE
    L.UTI_ID = :UTI_ID AND
    L.LIB_VALIDATION_ID = LV.LIB_VALIDATION_ID(+)
    ORDER BY
    L.INDEX_NO

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 13 0.00 0.00 0 0 0 0
    Execute 13 0.00 0.00 0 0 0 0
    Fetch 13 0.00 0.00 0 22 0 9
    ——- —— ——– ———- ———- ———- ———- ———-
    total 39 0.00 0.00 0 22 0 9

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 3

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    1 1 1 SORT ORDER BY (cr=2 pr=0 pw=0 time=79 us cost=3 size=3231 card=1)
    1 1 1 NESTED LOOPS OUTER (cr=2 pr=0 pw=0 time=47 us cost=2 size=3231 card=1)
    1 1 1 TABLE ACCESS BY INDEX ROWID UT_VALIDATIONS (cr=2 pr=0 pw=0 time=40 us cost=2 size=1078 card=1)
    1 1 1 INDEX RANGE SCAN UT_VALIDATIONS_TEST_IMPL_IX (cr=1 pr=0 pw=0 time=23 us cost=1 size=0 card=1)(object id 88706)
    0 0 0 TABLE ACCESS BY INDEX ROWID UT_LIB_VALIDATIONS (cr=0 pr=0 pw=0 time=3 us cost=0 size=2153 card=1)
    0 0 0 INDEX UNIQUE SCAN UT_LIB_VALIDATIONS_PK (cr=0 pr=0 pw=0 time=2 us cost=0 size=0 card=1)(object id 88650)

    ********************************************************************************

    SQL ID: 9kf349fr78kmu Plan Hash: 1199766055

    SELECT *
    FROM
    UT_TEST_IMPL_ARGUMENTS where UTI_ID = :ID

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 13 0.00 0.00 0 0 0 0
    Execute 13 0.00 0.00 0 0 0 0
    Fetch 13 0.00 0.00 0 26 0 13
    ——- —— ——– ———- ———- ———- ———- ———-
    total 39 0.00 0.01 0 26 0 13

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 3

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    1 1 1 TABLE ACCESS BY INDEX ROWID UT_TEST_IMPL_ARGUMENTS (cr=2 pr=0 pw=0 time=71 us cost=2 size=393 card=1)
    1 1 1 INDEX RANGE SCAN UT_TEST_IMPL_ARGUMENTS_PK (cr=1 pr=0 pw=0 time=47 us cost=1 size=0 card=1)(object id 88702)

    ********************************************************************************

    SQL ID: 3x12sr05w9h6t Plan Hash: 418339696

    select id,name
    from
    ut_lookup_categories where (:NAME is null or name != :NAME)

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 3 0.00 0.00 0 0 0 0
    Execute 3 0.00 0.00 0 0 0 0
    Fetch 3 0.00 0.00 0 6 0 9
    ——- —— ——– ———- ———- ———- ———- ———-
    total 9 0.00 0.00 0 6 0 9

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 3

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    3 3 3 TABLE ACCESS FULL UT_LOOKUP_CATEGORIES (cr=2 pr=0 pw=0 time=52 us cost=2 size=41 card=1)

    ********************************************************************************

    SQL ID: f9vpdp25a0jq3 Plan Hash: 1604256170

    select utr_id
    from
    ut_test_results where ut_id = :ID order by run_date desc

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 3 0.00 0.00 0 0 0 0
    Execute 3 0.00 0.00 0 0 0 0
    Fetch 3 0.00 0.00 1 15 0 29
    ——- —— ——– ———- ———- ———- ———- ———-
    total 9 0.00 0.00 1 15 0 29

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 3

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    13 10 14 SORT ORDER BY (cr=5 pr=0 pw=0 time=239 us cost=4 size=1105 card=13)
    13 10 14 TABLE ACCESS FULL UT_TEST_RESULTS (cr=5 pr=0 pw=0 time=52 us cost=3 size=1105 card=13)

    ********************************************************************************

    SQL ID: 26u75yzykyuqd Plan Hash: 4033182254

    select r.name,r.status,r.message,a.position,a.in_out
    from
    ut_test_impl_arg_results r, ut_test_arguments a where r.arg_id = a.arg_id
    and r.utir_id = :UTIR_ID order by a.position asc, r.status asc

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 118 0.00 0.00 0 0 0 0
    Execute 118 0.00 0.00 0 0 0 0
    Fetch 118 0.00 0.00 0 592 0 118
    ——- —— ——– ———- ———- ———- ———- ———-
    total 354 0.00 0.01 0 592 0 118

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 3

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    1 1 1 SORT ORDER BY (cr=5 pr=0 pw=0 time=107 us cost=4 size=154 card=1)
    1 1 1 NESTED LOOPS (cr=5 pr=0 pw=0 time=86 us cost=3 size=154 card=1)
    1 1 1 NESTED LOOPS (cr=4 pr=0 pw=0 time=70 us cost=3 size=154 card=1)
    1 1 1 TABLE ACCESS BY INDEX ROWID UT_TEST_IMPL_ARG_RESULTS (cr=3 pr=0 pw=0 time=56 us cost=2 size=110 card=1)
    1 1 1 INDEX RANGE SCAN UT_TEST_IMPL_ARG_RESULTS_IX3 (cr=2 pr=0 pw=0 time=42 us cost=1 size=0 card=1)(object id 88712)
    1 1 1 INDEX UNIQUE SCAN UT_TEST_ARGUMENTS_PK (cr=1 pr=0 pw=0 time=12 us cost=0 size=0 card=1)(object id 88693)
    1 1 1 TABLE ACCESS BY INDEX ROWID UT_TEST_ARGUMENTS (cr=1 pr=0 pw=0 time=5 us cost=1 size=44 card=1)

    ********************************************************************************

    SQL ID: ar1d58uqggzja Plan Hash: 2923916072

    select lib_validation_name,status,message,val_duration,index_no,val_type
    from
    ut_test_impl_val_results r, ut_validations v, ut_lib_validations lv where
    r.utir_id = :UTIR_ID and v.validation_id=r.val_id and
    lv.lib_validation_id(+)=v.lib_validation_id order by index_no asc

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 118 0.00 0.00 0 0 0 0
    Execute 118 0.00 0.00 0 0 0 0
    Fetch 118 0.00 0.00 0 304 0 62
    ——- —— ——– ———- ———- ———- ———- ———-
    total 354 0.00 0.01 0 304 0 62

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 3

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    1 0 1 SORT ORDER BY (cr=2 pr=0 pw=0 time=66 us cost=4 size=237 card=1)
    1 0 1 NESTED LOOPS OUTER (cr=2 pr=0 pw=0 time=51 us cost=3 size=237 card=1)
    1 0 1 NESTED LOOPS (cr=2 pr=0 pw=0 time=48 us cost=3 size=181 card=1)
    1 0 1 TABLE ACCESS BY INDEX ROWID UT_TEST_IMPL_VAL_RESULTS (cr=1 pr=0 pw=0 time=39 us cost=2 size=120 card=1)
    1 0 1 INDEX RANGE SCAN UT_TEST_IMPL_VAL_RES_IX3 (cr=1 pr=0 pw=0 time=27 us cost=1 size=0 card=1)(object id 88720)
    1 0 1 TABLE ACCESS BY INDEX ROWID UT_VALIDATIONS (cr=1 pr=0 pw=0 time=6 us cost=1 size=61 card=1)
    1 0 1 INDEX UNIQUE SCAN UT_VALIDATIONS_PK (cr=0 pr=0 pw=0 time=4 us cost=0 size=0 card=1)(object id 88698)
    0 0 0 TABLE ACCESS BY INDEX ROWID UT_LIB_VALIDATIONS (cr=0 pr=0 pw=0 time=1 us cost=0 size=56 card=1)
    0 0 0 INDEX UNIQUE SCAN UT_LIB_VALIDATIONS_PK (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 88650)

    ********************************************************************************

    SQL ID: 47nz0wtfra549 Plan Hash: 4284258276

    select name, status, message, run_date,coverage,test_user_name
    from
    ut_test_results where utr_id = :ID

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 29 0.00 0.00 0 0 0 0
    Execute 29 0.00 0.00 0 0 0 0
    Fetch 29 0.00 0.00 0 58 0 29
    ——- —— ——– ———- ———- ———- ———- ———-
    total 87 0.00 0.00 0 58 0 29

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 3

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    1 1 1 TABLE ACCESS BY INDEX ROWID UT_TEST_RESULTS (cr=2 pr=0 pw=0 time=28 us cost=1 size=182 card=1)
    1 1 1 INDEX UNIQUE SCAN UT_TEST_RESULTS_PK (cr=1 pr=0 pw=0 time=17 us cost=0 size=0 card=1)(object id 88670)

    ********************************************************************************

    SQL ID: 95d8gn6wfg6tx Plan Hash: 1014508128

    select utir_id,name,status,message,duration,startup_status, startup_message,
    startup_duration, op_call_status, op_call_message, op_call_duration,
    teardown_status, teardown_message, teardown_duration
    from
    ut_test_impl_results where utr_id = :UTR_ID order by run_date asc

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 29 0.00 0.00 0 0 0 0
    Execute 29 0.00 0.00 0 0 0 0
    Fetch 29 0.00 0.00 0 90 0 118
    ——- —— ——– ———- ———- ———- ———- ———-
    total 87 0.00 0.00 0 90 0 118

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 3

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    4 4 4 SORT ORDER BY (cr=3 pr=0 pw=0 time=103 us cost=4 size=1940 card=4)
    4 4 4 TABLE ACCESS BY INDEX ROWID UT_TEST_IMPL_RESULTS (cr=3 pr=0 pw=0 time=72 us cost=3 size=1940 card=4)
    4 4 4 INDEX RANGE SCAN UT_TEST_IMPL_RESULTS_UT_T_IX2 (cr=2 pr=0 pw=0 time=45 us cost=1 size=0 card=4)(object id 88714)

    ********************************************************************************

    SQL ID: c01s475sswtwj Plan Hash: 0

    DECLARE handle_id number; BEGIN DBMS_LOCK.ALLOCATE_UNIQUE(:1 ,handle_id); :2
    := handle_id; END;

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 5 0.00 0.00 0 0 0 0
    Execute 5 0.00 0.00 0 0 0 5
    Fetch 0 0.00 0.00 0 0 0 0
    ——- —— ——– ———- ———- ———- ———- ———-
    total 10 0.00 0.00 0 0 0 5

    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    ********************************************************************************

    SQL ID: 1aqkx9sphqcqz Plan Hash: 1388734953

    select DBMS_LOCK.REQUEST(:HANDLE,6,2)
    from
    dual

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 3 0.00 0.00 0 0 0 0
    Execute 3 0.00 0.00 0 0 0 0
    Fetch 3 0.00 0.00 0 0 0 3
    ——- —— ——– ———- ———- ———- ———- ———-
    total 9 0.00 0.00 0 0 0 3

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 3

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)

    ********************************************************************************

    MERGE INTO UT_TEST_RESULTS dest
    USING (SELECT
    :UTR_ID utr_id,
    :UT_ID ut_id,
    :TEST_NAME name,
    :STATUS status,
    substrb(:MESSAGE, 1, 2000) message,
    :COVERAGE coverage,
    :TEST_USER_NAME test_user_name,
    :CONNECTION_NAME connection_name
    from dual) src
    ON (dest.UTR_ID = src.UTR_ID)
    WHEN MATCHED THEN
    UPDATE SET
    dest.STATUS = src.STATUS,
    dest.MESSAGE = src.MESSAGE,
    dest.COVERAGE = src.COVERAGE,
    dest.TEST_USER_NAME = src.TEST_USER_NAME,
    dest.CONNECTION_NAME = src.CONNECTION_NAME
    WHEN NOT MATCHED THEN
    INSERT (
    UTR_ID,
    UT_ID,
    NAME,
    STATUS,
    MESSAGE,
    COVERAGE,
    TEST_USER_NAME,
    CONNECTION_NAME,
    RUN_DATE)
    VALUES (
    src.UTR_ID,
    src.UT_ID,
    src.NAME,
    src.STATUS,
    src.MESSAGE,
    src.COVERAGE,
    src.TEST_USER_NAME,
    src.CONNECTION_NAME,
    Current_Timestamp)

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 2 0.00 0.00 0 0 0 0
    Execute 2 0.00 0.01 0 8 12 2
    Fetch 0 0.00 0.00 0 0 0 0
    ——- —— ——– ———- ———- ———- ———- ———-
    total 4 0.00 0.01 0 8 12 2

    Misses in library cache during parse: 2
    Misses in library cache during execute: 2
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 2

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    0 0 0 MERGE UT_TEST_RESULTS (cr=4 pr=0 pw=0 time=1690 us)
    1 1 1 VIEW (cr=4 pr=0 pw=0 time=84 us)
    1 1 1 NESTED LOOPS OUTER (cr=4 pr=0 pw=0 time=78 us cost=3 size=272 card=1)
    1 1 1 TABLE ACCESS FULL DUAL (cr=2 pr=0 pw=0 time=56 us cost=2 size=2 card=1)
    0 0 1 TABLE ACCESS BY INDEX ROWID UT_TEST_RESULTS (cr=2 pr=0 pw=0 time=16 us cost=1 size=270 card=1)
    0 0 1 INDEX UNIQUE SCAN UT_TEST_RESULTS_PK (cr=1 pr=0 pw=0 time=12 us cost=0 size=0 card=1)(object id 88670)

    ********************************************************************************

    SQL ID: c749bc43qqfz3 Plan Hash: 1388734953

    SELECT SYSDATE
    FROM
    DUAL

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 22 0.00 0.00 0 0 0 0
    Execute 22 0.00 0.00 0 0 0 0
    Fetch 22 0.00 0.00 0 0 0 22
    ——- —— ——– ———- ———- ———- ———- ———-
    total 66 0.00 0.00 0 0 0 22

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5 (recursive depth: 1)
    Number of plan statistics captured: 1

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=1 us cost=2 size=0 card=1)

    ********************************************************************************

    SQL ID: d6r1mk4p3j2uf Plan Hash: 1388734953

    SELECT USER
    FROM
    DUAL

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 22 0.00 0.00 0 0 0 0
    Execute 22 0.00 0.00 0 0 0 0
    Fetch 22 0.00 0.00 0 0 0 22
    ——- —— ——– ———- ———- ———- ———- ———-
    total 66 0.00 0.00 0 0 0 22

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5 (recursive depth: 1)
    Number of plan statistics captured: 1

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)

    ********************************************************************************

    MERGE INTO UT_TEST_IMPL_RESULTS dest
    USING (SELECT
    :UTIR_ID utir_id,
    :UTI_ID uti_id,
    :UTR_ID utr_id,
    :IMPL_NAME name,
    :STATUS status,
    substrb(:MESSAGE, 1, 2000) message,
    :START_TIME START_TIME,
    :END_TIME END_TIME,
    :DURATION DURATION,
    :STARTUP_STATUS startup_status,
    substrb(:STARTUP_MESSAGE, 1, 2000) startup_message,
    :STARTUP_START_TIME startup_start_time,
    :STARTUP_END_TIME startup_end_time,
    :STARTUP_DURATION startup_duration,
    :OP_CALL_STATUS op_call_status,
    substrb(:OP_CALL_MESSAGE, 1, 2000) op_call_message,
    :OP_CALL_START_TIME op_call_start_time,
    :OP_CALL_END_TIME op_call_end_time,
    :OP_CALL_DURATION op_call_duration,
    :TEARDOWN_STATUS teardown_status,
    substrb(:TEARDOWN_MESSAGE, 1, 2000) teardown_message,
    :TEARDOWN_START_TIME teardown_start_time,
    :TEARDOWN_END_TIME teardown_end_time,
    :TEARDOWN_DURATION teardown_duration
    from dual) src
    ON (dest.UTIR_ID = src.UTIR_ID)
    WHEN MATCHED THEN
    UPDATE SET
    dest.STATUS = src.STATUS,
    dest.MESSAGE = src.MESSAGE,
    dest.START_TIME = src.START_TIME,
    dest.END_TIME = src.END_TIME,
    dest.DURATION = src.DURATION,
    dest.STARTUP_STATUS = src.STARTUP_STATUS,
    dest.STARTUP_MESSAGE = src.STARTUP_MESSAGE,
    dest.STARTUP_START_TIME = src.STARTUP_START_TIME,
    dest.STARTUP_END_TIME = src.STARTUP_END_TIME,
    dest.STARTUP_DURATION = src.STARTUP_DURATION,
    dest.OP_CALL_STATUS = src.OP_CALL_STATUS,
    dest.OP_CALL_MESSAGE = src.OP_CALL_MESSAGE,
    dest.OP_CALL_START_TIME = src.OP_CALL_START_TIME,
    dest.OP_CALL_END_TIME = src.OP_CALL_END_TIME,
    dest.OP_CALL_DURATION = src.OP_CALL_DURATION,
    dest.TEARDOWN_STATUS = src.TEARDOWN_STATUS,
    dest.TEARDOWN_MESSAGE = src.TEARDOWN_MESSAGE,
    dest.TEARDOWN_START_TIME = src.TEARDOWN_START_TIME,
    dest.TEARDOWN_END_TIME = src.TEARDOWN_END_TIME,
    dest.TEARDOWN_DURATION = src.TEARDOWN_DURATION
    WHEN NOT MATCHED THEN
    INSERT (
    UTIR_ID,
    UTI_ID,
    UTR_ID,
    NAME,
    STATUS,
    MESSAGE,
    START_TIME,
    END_TIME,
    DURATION,
    STARTUP_STATUS,
    STARTUP_MESSAGE,
    STARTUP_START_TIME,
    STARTUP_END_TIME,
    STARTUP_DURATION,
    OP_CALL_STATUS,
    OP_CALL_MESSAGE,
    OP_CALL_START_TIME,
    OP_CALL_END_TIME,
    OP_CALL_DURATION,
    TEARDOWN_STATUS,
    TEARDOWN_MESSAGE,
    TEARDOWN_START_TIME,
    TEARDOWN_END_TIME,
    TEARDOWN_DURATION)
    VALUES (
    src.UTIR_ID,
    src.UTI_ID,
    src.UTR_ID,
    src.NAME,
    src.STATUS,
    src.MESSAGE,
    src.START_TIME,
    src.END_TIME,
    src.DURATION,
    src.STARTUP_STATUS,
    src.STARTUP_MESSAGE,
    src.STARTUP_START_TIME,
    src.STARTUP_END_TIME,
    src.STARTUP_DURATION,
    src.OP_CALL_STATUS,
    src.OP_CALL_MESSAGE,
    src.OP_CALL_START_TIME,
    src.OP_CALL_END_TIME,
    src.OP_CALL_DURATION,
    src.TEARDOWN_STATUS,
    src.TEARDOWN_MESSAGE,
    src.TEARDOWN_START_TIME,
    src.TEARDOWN_END_TIME,
    src.TEARDOWN_DURATION)

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 8 0.00 0.00 0 0 0 0
    Execute 8 0.02 0.02 0 40 60 8
    Fetch 0 0.00 0.00 0 0 0 0
    ——- —— ——– ———- ———- ———- ———- ———-
    total 16 0.03 0.03 0 40 60 8

    Misses in library cache during parse: 2
    Misses in library cache during execute: 3
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 7

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    0 0 0 MERGE UT_TEST_IMPL_RESULTS (cr=5 pr=0 pw=0 time=1059 us)
    1 1 1 VIEW (cr=4 pr=0 pw=0 time=89 us)
    1 1 1 NESTED LOOPS OUTER (cr=4 pr=0 pw=0 time=80 us cost=3 size=487 card=1)
    1 1 1 TABLE ACCESS FULL DUAL (cr=2 pr=0 pw=0 time=47 us cost=2 size=2 card=1)
    0 0 1 TABLE ACCESS BY INDEX ROWID UT_TEST_IMPL_RESULTS (cr=2 pr=0 pw=0 time=27 us cost=1 size=485 card=1)
    0 0 1 INDEX UNIQUE SCAN UT_TEST_IMPL_RESULTS_PK (cr=2 pr=0 pw=0 time=21 us cost=0 size=0 card=1)(object id 88694)

    ********************************************************************************

    SQL ID: 7upqnutt3cbr9 Plan Hash: 1388734953

    SELECT CURRENT_TIMESTAMP
    FROM
    DUAL

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 1 0.00 0.00 0 0 0 0
    Execute 4 0.00 0.00 0 0 0 0
    Fetch 4 0.00 0.00 0 0 0 4
    ——- —— ——– ———- ———- ———- ———- ———-
    total 9 0.00 0.00 0 0 0 4

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5 (recursive depth: 1)
    Number of plan statistics captured: 1

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=3 us cost=2 size=0 card=1)

    ********************************************************************************

    MERGE INTO UT_TEST_IMPL_ARG_RESULTS dest
    USING (SELECT :UTIAR_ID utiar_id, :UTIR_ID utir_id, :UTI_ID uti_id, :ARG_NAME name,:ARG_ID arg_id, :STATUS status, substrb(:MESSAGE, 1, 2000) message from dual) src
    ON (dest.UTIAR_ID = src.UTIAR_ID)
    WHEN MATCHED THEN
    UPDATE SET
    dest.STATUS = src.STATUS,
    dest.MESSAGE = src.MESSAGE
    WHEN NOT MATCHED THEN
    INSERT (UTIAR_ID, UTIR_ID, UTI_ID, ARG_ID, NAME, STATUS, MESSAGE, RUN_DATE)
    VALUES (src.UTIAR_ID, src.UTIR_ID, src.UTI_ID, src.ARG_ID, src.NAME, src.STATUS, src.MESSAGE, Current_Timestamp)

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 4 0.00 0.00 0 0 0 0
    Execute 4 0.00 0.02 1 20 52 4
    Fetch 0 0.00 0.00 0 0 0 0
    ——- —— ——– ———- ———- ———- ———- ———-
    total 8 0.00 0.02 1 20 52 4

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 3

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    0 0 0 MERGE UT_TEST_IMPL_ARG_RESULTS (cr=5 pr=0 pw=0 time=6479 us)
    1 1 1 VIEW (cr=4 pr=0 pw=0 time=121 us)
    1 1 1 NESTED LOOPS OUTER (cr=4 pr=0 pw=0 time=113 us cost=3 size=230 card=1)
    1 1 1 TABLE ACCESS FULL DUAL (cr=2 pr=0 pw=0 time=39 us cost=2 size=2 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID UT_TEST_IMPL_ARG_RESULTS (cr=2 pr=0 pw=0 time=68 us cost=1 size=228 card=1)
    0 0 0 INDEX UNIQUE SCAN UT_TEST_IMPL_ARG_RESULTS_PK (cr=2 pr=0 pw=0 time=66 us cost=0 size=0 card=1)(object id 88701)

    ********************************************************************************

    MERGE INTO UT_TEST_IMPL_VAL_RESULTS dest
    USING (SELECT
    :UTIVR_ID utivr_id,
    :UTIR_ID utir_id,
    :UTI_ID uti_id,
    :VAL_ID val_id,
    :VAL_TYPE val_type,
    :STATUS status,
    substrb(:MESSAGE, 1, 2000) message,
    :VAL_START_TIME val_start_time,
    :VAL_END_TIME val_end_time,
    :VAL_DURATION val_duration
    from dual) src
    ON (dest.UTIVR_ID = src.UTIVR_ID)
    WHEN MATCHED THEN
    UPDATE SET
    dest.STATUS = src.STATUS,
    dest.MESSAGE = src.MESSAGE,
    dest.VAL_START_TIME = src.VAL_START_TIME,
    dest.VAL_END_TIME = src.VAL_END_TIME,
    dest.VAL_DURATION = src.VAL_DURATION
    WHEN NOT MATCHED THEN
    INSERT (
    UTIVR_ID,
    UTIR_ID,
    UTI_ID,
    VAL_ID,
    VAL_TYPE,
    STATUS,
    MESSAGE,
    VAL_START_TIME,
    VAL_END_TIME,
    VAL_DURATION,
    RUN_DATE)
    VALUES (
    src.UTIVR_ID,
    src.UTIR_ID,
    src.UTI_ID,
    src.VAL_ID,
    src.VAL_TYPE,
    src.STATUS,
    src.MESSAGE,
    src.VAL_START_TIME,
    src.VAL_END_TIME,
    src.VAL_DURATION,
    sysdate)

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 4 0.00 0.00 0 0 0 0
    Execute 4 0.01 0.01 0 16 38 4
    Fetch 0 0.00 0.00 0 0 0 0
    ——- —— ——– ———- ———- ———- ———- ———-
    total 8 0.01 0.01 0 16 38 4

    Misses in library cache during parse: 2
    Misses in library cache during execute: 3
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 4

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    0 0 0 MERGE UT_TEST_IMPL_VAL_RESULTS (cr=4 pr=0 pw=0 time=858 us)
    1 1 1 VIEW (cr=4 pr=0 pw=0 time=115 us)
    1 1 1 NESTED LOOPS OUTER (cr=4 pr=0 pw=0 time=110 us cost=3 size=262 card=1)
    1 1 1 TABLE ACCESS FULL DUAL (cr=2 pr=0 pw=0 time=62 us cost=2 size=2 card=1)
    0 0 1 TABLE ACCESS BY INDEX ROWID UT_TEST_IMPL_VAL_RESULTS (cr=2 pr=0 pw=0 time=45 us cost=1 size=260 card=1)
    0 0 1 INDEX UNIQUE SCAN UT_TEST_IMPL_VAL_RES_PK (cr=1 pr=0 pw=0 time=40 us cost=0 size=0 card=1)(object id 88665)

    ********************************************************************************

    SQL ID: 5s3qckpf9urtc Plan Hash: 1388734953

    select DBMS_LOCK.RELEASE(:HANDLE)
    from
    dual

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 2 0.00 0.00 0 0 0 0
    Execute 2 0.00 0.00 0 0 0 0
    Fetch 2 0.00 0.00 0 0 0 2
    ——- —— ——– ———- ———- ———- ———- ———-
    total 6 0.00 0.00 0 0 0 2

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 5
    Number of plan statistics captured: 2

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)

    ********************************************************************************

    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 379 0.02 0.04 0 5 0 0
    Execute 379 0.12 0.15 1 87 162 23
    Fetch 356 0.01 0.02 1 1164 0 433
    ——- —— ——– ———- ———- ———- ———- ———-
    total 1114 0.15 0.22 2 1256 162 456

    Misses in library cache during parse: 25
    Misses in library cache during execute: 24

    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 76 0.00 0.01 0 0 0 0
    Execute 264 0.01 0.01 0 15 25 5
    Fetch 449 0.01 0.00 0 706 0 400
    ——- —— ——– ———- ———- ———- ———- ———-
    total 789 0.03 0.03 0 721 25 405

    Misses in library cache during parse: 8
    Misses in library cache during execute: 5

    69 user SQL statements in session.
    27 internal SQL statements in session.
    96 SQL statements in session.
    0 statements EXPLAINed in this session.
    ********************************************************************************
    Trace file: UTDB_ora_7487.trc
    Trace file compatibility: 11.1.0.7
    Sort options: default

    1 session in tracefile.
    69 user SQL statements in trace file.
    27 internal SQL statements in trace file.
    96 SQL statements in trace file.
    51 unique SQL statements in trace file.
    4853 lines in trace file.
    224 elapsed seconds in trace file.

  3. Sorry, I can’t get it, my company policy won’t allow to send any such files. But I can give you a glimps of the tables. I am dealing with two tables called employees and departments. Employee has 17 rows in total and department 5 rows. I have two procedure emp_dept_change and salary_bonus.
    emp_dept_change will take employee_id as parameter and change the deparment of the employee.
    salary_bonus will take employee_id and depending upon department_id bonus will be awarded.
    My test cases are build around this two procedures. This is it, please let me know if you need any more info.

    1. thatjeffsmith Post
      Author
  4. Hi Jeff,
    Below is my system config.
    OS- Windows 64 bit, CPU- Intel i5 3.4 GHz, 8.00 GB RAM.
    SQL developer version 4.0.2.15. I have followed the tricks you have stated as above and it became fast. But still it is dead slow when it comes in Unit Test module. It is very slow in Loading a test/ Run a test with single implementation/ Saving a test/ Run a suit which has 7 test implementations in total( only single implementation with two values through dynamic query) is taking atleast 5 minute in avarage.
    Please help me here.
    Regards,
    Ayan.

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author

            Oracle Session Trace – collects everything that happens in your session, queries ran, time executed, waits, etc…

            It’ll write a file on the server, so if you don’t have access, you’ll need to get your DBA to get you the file.

  5. OK thanks for that feedback. I’ve also got a few versions locally installed. I am running on Windows 7 64-bit so do I need to use a 64-bit JDK? The only one of this I have is 1.6.0_31.

    I am running version 3.2.20.09 and according to the release notes – http://www.oracle.com/technetwork/developer-tools/sql-developer/sqldev-32-relnotes-1734493.html – supports a minimum java version of 1.6.0_04 to a maximum java version less than 1.7.

    So this should be ok right? Last thing is how do I change the JDK path? Is it simply updating this file – sqldeveloper.conf?

    1. thatjeffsmith Post
      Author

      You don’t have to run a 64 bit JDK if you don’t want to. But if you’re on Windows 32 bit OS, then you HAVE to use a 32 bit JDK – does that make sense?

      In version 3.2 and lower, the JDK is specified in the sqldeveloper.conf file in the bin directory of where you extracted the SQL Developer zip file.

  6. If I am using SQL Developer on a network does the performance of SQL Developer get affected if the Java is on a network share which possibly could have a slower connection rather than a local installation? I’ve got terrible load times and have already disabled extensions so looking for the next thing to try – thought this might be a factor.

    1. thatjeffsmith Post
      Author

      Sorry, are you saying your JDK isn’t actually on the same machine as where you are running SQL Developer? Yeah, that would be bad. Sorry, if I’m not understanding you correctly.

  7. Hi Jeff,

    Your post is really helpful. I disabled the extensions and it improved the performance of query execution. However, I still see slow performance while opening the tables. It takes much time to load the tables. Can anything be done to improve this ?

    1. thatjeffsmith Post
      Author

      Are you connected to a remote database? Network latency is the biggest ‘suck.’

      I’ve noticed general and overall performance improvement in version 4 – I’d try upgrading first and see if that makes a difference.

      1. Yes. We are connected to a remote database. I tried using version 4 but it did not made any difference. Honestly speaking, I found 3.1 with a better speed on executing queries. Only the issue which remains is loading the tables. Can you suggest some solution to it ?

        1. thatjeffsmith Post
          Author

          I would run SQL Developer in ‘debug’ mode. Go to the sqldeveloper.conf file and change the last line from nondebug.conf to debug.conf

          Start up SQL Developer, load your tables.

          Do this for version 3 and version 4.

          Now compare the logs that are generated in the panels and see where the biggest time suck is. Report back.

  8. Maybe you should add something along the lines of “disabling the extensions in 3.2 might break other functions” in big red font? 🙂 I spent a few months being unable to do a database diff (while we were in the heavy migration phase, no less) after disabling versioning support extension. Found it accidentally after discovering that “import from XLS” isn’t working as well because it depends on some other extension.

    P.S. The topic about it at oracle SQLDev forums got wiped&locked (I wonder why), so folks encountering the same problem will not be able to find the solution. Your blog would be a good place to address those issues, really.

    1. thatjeffsmith Post
      Author

      Probably not a bad idea…although it is a BUG that I wasn’t aware of until I actually wrote this post and had people report it back. Most of those issues have been fixed in v4. Not sure why’d you’d wait several months to ask for help though?

      The OTN threads generally only get locked when someone gets abusive, although I’ve noticed a few ‘power users’ like to get heavy handed with the thread locking too. Do you have the forum thread ID handy?

      1. Yep, the thread is here – https://forums.oracle.com/thread/2480742. I am not the creator, but since my problem was the same I went ahead and posted the exception stacktrace that was written to console upon calling diff (you replied there as well, if you remember).

        See, I didn’t wait to ask for help, I just didn’t get any until I went ahead to test everything I could and found the solution myself 🙂 I was about to mail you already, asking for help, though.

  9. Hi,

    interesting post! I just wanted to try to disable those extensions in SQL Developer Version 4.0.0.12 – but where did that tab go? There is “Debugger” and “External Editor” but no “Extensions” in between 🙁

    Funny thing: When I clicked into the help menu to find out and copy my version number I also found an extension tab that gives me lot of information about loading and initialization time (~7s in total). But unfortunately there’s no way to disable anything.

    So how is that done in 4.0?

    Cheers,
    Marcus

    1. thatjeffsmith Post
      Author
  10. Thanks for the tips! I’ve followed your advice, but I’m still loading SQL Developer in 2-3 minutes (down from 15-20 minutes).

    I’ve noticed this only seems to happen on the first load – when I reopen SQL Developer later on (say I run a few queries, close the software, then open it again to do something else), it loads much faster.

    Any thoughts about what could be happening here?

    1. thatjeffsmith Post
      Author
      1. Windows 7 64 bit OS, 4GB RAM, very low CPU load at any given time…

        SQL Developer – no automatic updates, no plugins, set to OS UI settings.

  11. Thanks for these tips!

    It would be helpful to mention that the “SQL History Limit” setting is under Tools, Preferences, then: Database, Worksheet, SQL History Limit.

    1. thatjeffsmith Post
      Author
  12. BTW: I have found over the weekend that many feature that I was interested in are extensions dependent (e.g. DBA Sections, Table Data Import/Export etc). IMHO: It may not be a good idea to switch off extensions.
    I wonder if you have a good list of extensions along with a short description that would be kind of “core” part of SQL Developer? I would immagine at least extensions written by Oracle folks would be part of the list

    1. thatjeffsmith Post
      Author

      A matrix would be helpful I think…on the topic I generally advise folks to at least disable the Migrations and TimesTen extensions. And remember, that’s only IF you notice SQL Developer taking too long to start up.

    1. thatjeffsmith Post
      Author

      You went from not so bad, to pretty fast. Some users will say it takes upwards of a minute to start and by disabling migrations and modeling extensions they can get down to 20 seconds or so…btw, in next version we are completely changing up how java classes are loaded up and such, with hopefully end result of an even meaner and leaner (faster) SQL Developer.

  13. I just read your nice blog and tested to supress some sql developer extensions in sql dev 3.1. It really opens faster but unfortunately there are some curious dependencies. If you uncheck “data miner” your are no more able to open existing “data models” and if you uncheck “scheduler” cut and paste of a table into the sql workspace does not work any longer. It looks like some more work to do 😉
    Roland

    1. thatjeffsmith Post
      Author

      I tested the Modeler with disabled Data Miner extension and it worked just fine for me.

      Here’s what I tried:

      • Preferences – Extensions – Unchecked ‘DataMiner’
      • Close and Restart SQL Developer
      • View – Data Modeler – Browser
      • File – Data Modeler – Recent Diagrams – ‘ModelName’
      • Model opens as expected
      • What are you seeing?

        Also, what do you means ‘cut and paste of a table’?

      1. hi Jeff,
        I run my tests again, here my results (unchecking data miner was not the reason, sorry):

        1) data model does not open after disabling an extension
        Preferences–Extensions–Unchecked ‘version support’, restart SD and try to open an existing data model produces an error: “SEVERE 33 0 oracle.ide.IdeCore Exception firing mainWindowOpened to oracle[email protected]
        –> enable version support again and the data model opens again

        2) drag & drop does not work after disabling “SQL dev scheduler” extension
        when you drag and drop a table from the directory navigation tree into a sql worksheet you get normally a pre generated SQL “… select a,b from table” pasted into the sql worksheet. But when you have the scheduler-extension disabled this does not work any more.
        –> enable “scheduler ext.” and its works again

        I checked and unchecked each extension one by one to avoid side effects, quite boring because it takes about 4 min to open a data model with 10 small tables each time 😉

        Roland

        1. thatjeffsmith Post
          Author

          Your previous comment stated you disabled DataMiner, not Version Support. Version support is tightly integrated with the modeler, so I”m not surprised there’s a conflict there.

          Firing up the VM now to check out your scheduler issue.

        2. thatjeffsmith Post
          Author

          I just tested your 2nd scenario, and it’s actually working for me.

          Here’s what I have:
          SQL Developer 3.1.07
          JDK 1.7.0_05
          Oracle 11.2.0.3
          Windows 7 64 Bit

          I disabled ONLY the ‘Oracle SQL Developer – Scheduler’ extension. Re-started SQL Developer. Connected to my db. Dragged 2 tables into the worksheet. Got the pop-up dialog asking me what I wanted. I selected the ‘Join’, and it splat the expected SQL to my worksheet.

          What does your scenario look like exactly?

          1. I made all tests again and I think I found the dependencies:
            The code for d&d functionality must rely on the “scheduler” or “migration core” extension.

            here how I tested:
            1. all ext. checked = ok
            2. all ext. unchecked = not ok
            3. check at least “scheduler” or “migration core” = ok

            Roland

            my environment:
            SQL Developer 3.1.07
            JDK 1.7.0_05
            Oracle 11.2.0.2.0
            Windows Vista 32 Bit

Leave a Reply

Your email address will not be published. Required fields are marked *