Forum Stats

  • 3,839,329 Users
  • 2,262,481 Discussions
  • 7,900,934 Comments

Discussions

SQL Developer takes over 30 seconds to expand the list of tables

user9221921
user9221921 Member Posts: 12 Blue Ribbon

I have a strange issue. Using SQL Developer Version 21.4.3.063 on MacOS.

After a recent upgrade to Oracle 19 if I login as my production user and expand it's list of tables it takes over 30 seconds to display with a "running" progress bar displayed in the lower right footer.

If I connect to our development server using similar user account it expands almost instantly.

If my DBA connects as system and expands the production user table list it is also instant but when he logs in using the user login he gets the same issue of taking over 30 seconds to expand.

This database 1007 tables.

Smaller schema which has only 77 tables also takes over 30 seconds to expand.

Even Smaller schema which has only 22 tables takes 18 seconds to expand.

The fact that the DBA can open as system and get instant results while the user accounts all take 18-30 seconds seems to exclude network issues.

Just running a query of tables is instant as well.

SELECT * FROM user_tables ORDER BY table_name;

So I don't know what SQL Developer is doing that is taking so long.

I don't understand why it only happens on one machine and not the other when they are both Oracle 19 instances.

«1

Answers

  • Philipp Salvisberg
    Philipp Salvisberg Member Posts: 181 Silver Badge

    I don't understand why it only happens on one machine and not the other when they are both Oracle 19 instances.

    I suggest to have a look at the Statements-Log. You can enable it via View -> Log.

    In my case it looks like this:

    You can double click on the SQL to see the full statement. In my case:

    select * from (
      SELECT o.OBJECT_NAME, o.OBJECT_ID ,'' short_name, NULL partitioned,
                    o.sharded,
                    case when o.sharded <> 'Y' then o.duplicated else 'N' end duplicated,
                    NULL iot_type, 
             o.OWNER OBJECT_OWNER, o.CREATED, o.LAST_DDL_TIME, O.GENERATED, O.TEMPORARY, NULL EXTERNAL
        FROM SYS.ALL_OBJECTS O 
        WHERE O.OWNER = :SCHEMA
        AND O.OBJECT_TYPE = 'TABLE' 
    union all
    SELECT OBJECT_NAME, OBJECT_ID , syn.SYNONYM_NAME short_NAME, NULL partitioned,
                    o.sharded,
                    case when o.sharded <> 'Y' then o.duplicated else 'N' end duplicated,
                    NULL iot_type, 
           SYN.TABLE_OWNER OBJECT_OWNER, o.CREATED, o.LAST_DDL_TIME, O.GENERATED, O.TEMPORARY, NULL EXTERNAL
                  FROM SYS.ALL_OBJECTS O, sys.user_synonyms syn
                  WHERE  syn.table_owner = o.owner
                  and    syn.TABLE_NAME = o.object_NAME
                  and    o.object_type = 'TABLE'
                  and    :INCLUDE_SYNS = 1
    )
     WHERE /**/OBJECT_NAME NOT IN (SELECT OBJECT_NAME FROM RECYCLEBIN)
                             AND not object_name like 'BIN$%'
    

    This runs fast on my machine, but I guess that the statement you see in your look runs slow. This should help to solve the problem.

  • user9221921
    user9221921 Member Posts: 12 Blue Ribbon

    Thanks, that's interesting I didn't know about that. I have the same query. At this moment the problem went away. I plan to monitor for the rest of the day to see if it starts happening with increased load on the system. It's running fine at the moment but it's 7am so not many folks working yet.

    I will report back.

  • user9221921
    user9221921 Member Posts: 12 Blue Ribbon

    The problem has returned and our DBA reported this in the alert logs.

    Any idea why the query made by sqldeveloper to get the tables would have parse errors ?

    2022-04-29T08:10:43.001313-10:00

    KC(3):WARNING: too many parse errors, count=100 SQL hash=0x05f798b3

    KC(3):PARSE ERROR: ospid=38202, error=12872 for statement:

    2022-04-29T08:10:43.013246-10:00

    KC(3):

    select * from (

     SELECT o.OBJECT_NAME, o.OBJECT_ID ,'' short_name, NULL partitioned,

            o.sharded,

            case when o.sharded <> 'Y' then o.duplicated else 'N' end duplicated,

            NULL iot_type,

         o.OWNER OBJECT_OWNER, o.CREATED, o.LAST_DDL_TIME, O.GENERATED, O.TEMPORARY, NULL EXTERNAL

      FROM SYS.ALL_OBJECTS O

      WHERE O.OWNER = :SCHEMA

      AND O.OBJECT_TYPE = 'TABLE'

    union all

    SELECT OBJECT_NAME, OBJECT_ID , syn.SYNONYM_NAME short_NAME, NULL partitioned,

            o.sharded,

            case when o.sharded <> 'Y' then o.duplicated else 'N' end duplicated,

            NULL iot_type,

        SYN.TABLE_OWNER OBJECT_OWNER, o.CREATED, o.LAST_DDL_TIME, O.GENERATED, O.TEMPORARY, NULL EXTERNAL

           FROM SYS.ALL_OBJECTS O, sys.user_synonyms syn

           WHERE syn.table_owner = o.owner

           and  syn.TABLE_NAME = o.object_NAME

           and  o.object_type = 'TABLE'

           and  :INCLUDE_SYNS = 1

    )

     WHERE /**/OBJECT_NAME NOT IN (SELECT OBJECT_NAME FROM RECYCLEBIN)

                 AND not object_name like 'BIN$%'

    KC(3):Additional information: hd=0xb9e06b90 phd=0xc84b1b38 flg=0x110676 cisid=144 sid=144 ciuid=144 uid=144 sqlid=1xx15r82zg65m

    KC(3):...Current username=PROD

    KC(3):...Application: SQL Developer Action:

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

    that's a question for the database...and the database support team

    Those errors mean something, go open a SR with MOS.

  • user9221921
    user9221921 Member Posts: 12 Blue Ribbon
    edited May 16, 2022 6:44PM

    We opened an SR and so far they have not been able to solve the problem, it's been open for over 2 weeks now.

    They asked use to try this command before expanding the tables and it does fix the issue so I was hopeful that it would lead to an explanation of what was wrong and a solution but it did not.

    alter session set optimizer_adaptive_plans=false;

    Based on this command helping does anyone know what the actual problem might be. The fact that this issue doesn't exist in our Development System but only in our Production System leads me to believe there should be some cause that can be fixed.

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

    They should have asked for a session trace to ID the offending SQL and / or plans AND correlated that with your ALERT LOG parsing errors.

    What's your SR# ? Feel free to email that to me

    [email protected]

  • user9221921
    user9221921 Member Posts: 12 Blue Ribbon

    Thanks email sent. Another interesting thing about this issue is the DBA finds no issue expanding when logged in as System but when logged in as any user he has the issue in his SQL developer instance as well. This seems like another clue if the System user doesn't' have the issue then does that point to something with granted permissions ?

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

    Could be that SYSTEM has access to DBA_ views, and the other accounts do not.

  • user9221921
    user9221921 Member Posts: 12 Blue Ribbon

    @thatJeffSmith-Oracle I don't see how it could be that given the query that takes long doesn't access any DBA_ views.

    Did my email get to you successfully ?

  • Philipp Salvisberg
    Philipp Salvisberg Member Posts: 181 Silver Badge

    @user9221921 your SQL uses literals. The original and problematic statement uses bind variables. So it's possible that the former is fast and the latter slow.

    I don't see how it could be that given the query that takes long doesn't access any DBA

    SQL Developer replaces "SYS.ALL_" with "SYS.DBA_" if the user has access to DBA views. That's a concept many IDEs are using to improve the query performance since the DBA views are faster (less accessibility checks).