Karl about the Oracle Database

Hi Reader,

after installing Oracle 10.2 64bit on Suse Linux 10.1 I started the dbconsole. A page i visited soon was Tablespace page. Usually on our Windows Server 2003 32Bit system, with the same Oracle version, the Tablespace query lasts for about 20 seconds! On the Linux System with the same number of Application tablespaces the Query response time was < 0.5 seconds. I thought this could not be the result being on just another operating system or driving the database with 64bit.

After reviewing the explain plan i detected the access to the Table RECYCLEBIN$; As by default Tables dropped are moved logically to the RECYCLEBIN$; The segments remain until the place in the tablespace exceeds; And now I recognized a big difference; On the slow system the RECYCLEBIN$ had about 2,600 rows on the fast system only 16 rows.

I started to purge all these tables of the different users and believe me or not the Query got faster and faster; Now with 12 rows I have the same response time for both Systems Linux 64 bit / Windows 2003 32 Bit;

So keep your Oracle 10.2 RECYCLEBIN$ as small as possible;

HTH Karl Reitschuster

The Query :

SELECT Dtp.Tablespace_Name "TABLESPACE_NAME",
       Dtp.Status "STATUS",
       Dtp.Contents "CONTENTS",
       Dtp.Extent_Management "EXTENT MANAGEMENT",
       Dtp.Allocation_Type "ALLOCATION TYPE",
       Dtp.Logging "LOGGING",
       Nvl(Ts.Bytes,
           0) "SIZE (M)",
       Dtp.Initial_Extent "INITIAL EXT SIZE (K)",
       Dtp.Next_Extent "Next Extent",
       Dtp.Pct_Increase "INCREMENT PCT",
       Dtp.Max_Extents "MAX EXTENTS",
       Nvl(Ts.Bytes - Nvl(f.Bytes,
                          0),
           0) "USED BYTES"
 FROM Sys.Dba_Tablespaces Dtp,
       (SELECT Tablespace_Name,
               SUM(Bytes) Bytes
          FROM Dba_Data_Files
         GROUP BY Tablespace_Name) Ts,
       (SELECT Tablespace_Name,
               SUM(Bytes) Bytes
          FROM Dba_Free_Space
         GROUP BY Tablespace_Name) f
 WHERE Dtp.Tablespace_Name = Ts.Tablespace_Name(+)
   AND Dtp.Tablespace_Name = f.Tablespace_Name(+)
   AND NOT
        (Dtp.Extent_Management LIKE ‘LOCAL’ AND Dtp.Contents LIKE ‘TEMPORARY’)
UNION ALL
SELECT Dtp.Tablespace_Name "TABLESPACE_NAME",
       Dtp.Status "STATUS",
       Dtp.Contents "CONTENTS",
       Dtp.Extent_Management "EXTENT MANAGEMENT",
       Dtp.Allocation_Type "ALLOCATION TYPE",
       Dtp.Logging "LOGGING",
       Nvl(a.Bytes,
           0) "SIZE (M)",
       Dtp.Initial_Extent "INITIAL EXT SIZE (K)",
       Dtp.Next_Extent "Next Extent",
       Dtp.Pct_Increase "INCREMENT PCT",
       Dtp.Max_Extents "MAX EXTENTS",
       Nvl(t.Bytes,
           0) "USED BYTES"
 FROM Sys.Dba_Tablespaces Dtp,
       (SELECT Tablespace_Name,
               SUM(Bytes) Bytes
          FROM Dba_Temp_Files
         GROUP BY Tablespace_Name) a,
       (SELECT Ss.Tablespace_Name,
               SUM((Ss.Used_Blocks * Ts.Blocksize)) Bytes
          FROM Gv$sort_Segment Ss,
               Sys.Ts$         Ts
         WHERE Ss.Tablespace_Name = Ts.NAME
         GROUP BY Ss.Tablespace_Name) t
 WHERE Dtp.Tablespace_Name = a.Tablespace_Name(+)
   AND Dtp.Tablespace_Name = t.Tablespace_Name(+)
   AND Dtp.Extent_Management LIKE ‘LOCAL’
   AND Dtp.Contents LIKE ‘TEMPORARY’