SQL Developer Performance Issue

This happened when I was using SQL Developer in an Oracle 11.1.0.6 database. I clicked on the + sign next to Tables (Filtered) in the top left hand corner of the screen below (as usual, click on the image if necessary to enlarge it and bring it into focus):

 

The + turned to a – and Loading … appeared just below it:

After around 30 seconds, the table list appeared:

 

I repeated the process but this time, I traced the underlying session and ran the trace file through tkprof. This was the most expensive SQL in the .prf file. Incidentally, it had one of the biggest and most complicated explain plans I had ever seen (I won’t reproduce it here):

select * from (

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

                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,

                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$%'

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.09       0.11          0          0          0           0

Execute      1      0.83       0.81          0          0          0           0

Fetch        3     20.94      20.65          0    1017382          0         921

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        5     21.86      21.58          0    1017382          0         921

 

I looked elsewhere on the Internet and found somebody else who had experienced the same problem. He had substituted appropriate values for the bind variables and run the SQL with and without the subquery at the end. Removing it made the SQL run much faster so I decided that the problem might be connected with the recyclebin. I counted the number of objects in the SRCE user’s recyclebin and in dba_recyclebin then I purged the latter:

 

SQL> conn srce

Enter password:

Connected.

SQL> select count(*) from recyclebin;

 

  COUNT(*)

----------

        70

 

SQL> conn / as sysdba

Connected.

SQL> select count(*) from dba_recyclebin;

 

  COUNT(*)

----------

      3419

 

SQL> purge dba_recyclebin;

 

DBA Recyclebin purged.

 

SQL>

 

This made no difference. Then I rebuilt the 3 indexes on the recyclebin but this did not work either. Finally, I analyzed the recyclebin:

 

SQL> exec dbms_stats.gather_table_stats( -

> ownname=>'SYS',tabname=>'RECYCLEBIN$');

PL/SQL procedure successfully completed.

SQL>

 

After this, the table list appeared in a couple of seconds.