Wednesday, April 24, 2013

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.

If you have an Oracle book on Amazon, which you would like to advertise here for free, please write to me at international_dba@yahoo.co.uk.


No comments: