Forum Stats

  • 3,824,872 Users
  • 2,260,435 Discussions
  • 7,896,336 Comments

Discussions

Version SQL Developer 21.4 is very slow

lagerlofm
lagerlofm Member Posts: 2 Blue Ribbon
edited Dec 15, 2021 6:28AM in SQL Developer

Any query will take at least 8 seconds,even "Select * from Dual". Using previous version any test query execute below 1 second.

Are there any new settings that has to be performed to get reasonable performance in 21.4?

User_Z1XCNPatrick Speicher
«1345

Answers

  • MadMexx
    MadMexx Member Posts: 3 Red Ribbon

    Hi

    I have the same problem with Version 21.4.0.346 Build 346.2239. Query against a table with 15 rows is running 7 seconds for the first time. If there is a syntax error in the SQL statement it always takes long. Compiling is also very slow. Feels like freezing. Please help

  • User_2DKLA
    User_2DKLA Member Posts: 41 Red Ribbon

    Hello,

    Menu View -> Log (Ctrl + shift + L)

    Can you have a look at the Logging Page and Statements tabs, see if there's anything abnormal there?

    Also, what is the target database version?

    Using Oracle 11.2.0.4—sorry!—right now I'm seeing the following errors:

    Caused by queries obviously not suited for this (venerable) Oracle release:

    select distinct aa.owner, aa.package_name, aa.subprogram_id, aa.object_name, aa.overload, aa.argument_name, aa.position, aa.data_type, aa.data_length, aa.in_out, aa.type_name, aa.type_subname, aa.type_link, aa.type_object_type, aa.pls_type, aa.char_length, aa.char_used, aa.defaulted from all_procedures ap, all_arguments aa where aa.object_id = ap.object_id and ap.subprogram_id = aa.subprogram_id and aa.package_name = ap.object_name and aa.owner = :OWNER and aa.package_name = :PACKAGE order by aa.owner, aa.package_name, aa.subprogram_id, aa.object_name, aa.overload, aa.position
    

    And:

    select attributes from all_plsql_types where owner = :OWNER and package_name = :PACKAGE and type_name = :OBJECT
    

    And apparently SQL Developer keeps trying... and trying... and trying...

    Afraid I might have to go back to 21.2... 😕

    Regards,

    P.S. MOS Doc 2828123.1 makes it clear that Log4j is a non-issue in any release of SQL Developer, so there's no rush either.

    MadMexx
  • stom
    stom Member Posts: 119 Bronze Badge

    I see the same behaviour in 21.4. Queries take much longer to show results.

    It works fine on 21.2 on the same database.


    21.2


    21.4



    Patrick Speicher
  • User_2DKLA
    User_2DKLA Member Posts: 41 Red Ribbon

    Hi,

    @Alastair M : Thanks! I might try that.

    So far I'm back on 21.2 (for my day-job duties). Meanwhile, I've always had 'Enable Semantic Analysis Info Tip' checked in 21.2 and prior versions...

    I took an ASH report and a session trace of the hanging session this morning. The ASH report says that all time was spent on SQL id 4mrsvxt0ppwdu (select distinct owner, object_name from all_procedures order by owner, object_name ), not fast in any case yet executed repeatedly: 108 sampled execs in ASH... And all that was triggered (apparently) as I called a small SQL script (@tinyscript) in the SQL worksheet.

    The trace file also shows a couple of dozens of failed parses, of the following queries:

    The following statements encountered a error during parse:
    
    select distinct aa.owner, aa.package_name, aa.subprogram_id, aa.object_name, aa.overload, aa.argument_name, aa.position, aa.data_typ
    e, aa.data_length, aa.in_out, aa.type_name, aa.type_subname, aa.type_link, aa.type_object_type, aa.pls_type, aa.char_length, aa.char
    _used, aa.defaulted from all_procedures ap, all_arguments aa where aa.object_id = ap.object_id and ap.subprogram_id = aa.subprogram_
    id and aa.package_name = ap.object_name and aa.owner = :OWNER and aa.package_name = :PACKAGE  order by aa.owner, aa.package_name, aa
    .subprogram_id, aa.object_name, aa.overload, aa.position
    
    Error encountered: ORA-00904
    --------------------------------------------------------------------------------
    select attributes from all_plsql_types where owner = :OWNER and package_name = :PACKAGE and type_name = :OBJECT
    
    Error encountered: ORA-00942
    --------------------------------------------------------------------------------
    select distinct aa.owner, aa.package_name, aa.subprogram_id, aa.object_name, aa.overload, aa.argument_name, aa.position, aa.data_typ
    e, aa.data_length, aa.in_out, aa.type_name, aa.type_subname, aa.type_link, aa.type_object_type, aa.pls_type, aa.char_length, aa.char
    _used, aa.defaulted from all_procedures ap, all_arguments aa where aa.object_id = ap.object_id and ap.subprogram_id = aa.subprogram_
    id and aa.package_name = ap.object_name and aa.owner = :OWNER and aa.package_name = :PACKAGE  and aa.object_name = :OBJECT  order by
     aa.owner, aa.package_name, aa.subprogram_id, aa.object_name, aa.overload, aa.position
    
    Error encountered: ORA-00904
    

    Possible explanation: the database version (11.2.0.4) was not correctly detected or handled, causing SQL Developer 21.4 to submit data dictionary queries which could not be parsed successfully on that DB version. The resulting exception was not handled gracefully/appropriately, causing SQL Developer to try again multiple times.

    Somehow the hanging ended by itself before I attempted to kill the session, so that was not an infinite loop, just a bunch of very slow queries repeated plenty of times... ☹️

    Regards,

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,569 Employee

    There's a lot going on in this thread...but I'm not seeing any slowdown in queries. It's not that I don't believe you, I do. I just need to be able to reproduce what you're seeing so we can fix something if there is something to fix.

    I ran your query ...

    No delay, 0.117 seconds.

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,569 Employee

    11.2.0.4 - is basically no longer supported.

    "Extended Support end date has been extended until 31st December 2020." That was a year ago.

    " patch support for 11.2.0.3 has now ended (as of 27th August 2015). Currently only 11.2.0.4 is supported in terms of receiving patch fixes and only if and Extended Support fees have been paid."

  • User_2DKLA
    User_2DKLA Member Posts: 41 Red Ribbon

    Hi,

    @thatJeffSmith-Oracle ,

    I ran your query ...

    Sorry, not my query, SQL Developer's query! And (maybe) the problem is that it doesn't parse on 11.2.0.4...

    But yes, I agree with you, there could be intermixed reports in this thread. Sorry about that.

    Regards,

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,569 Employee
This discussion has been closed.