CODEWITHSUNDEEP
oracle-databaseoracle11g
kanna
kanna

Reputation: 101

Oracle 11g Sys Query

Can someone please enlighten me why the below query is being fired and when it will be fired. I belive this query is having huge impact on the performance. I am not able to trace from where this is being fired.

Query :

SELECT SUM (used), SUM (total)
FROM (SELECT /*+ ORDERED */ 
             SUM (d.BYTES) / (1024 * 1024) - MAX (s.BYTES) used,
             SUM (d.BYTES) / (1024 * 1024) total
      FROM ( SELECT   tablespace_name, SUM (BYTES) / (1024 * 1024) BYTES
             FROM ( SELECT/*+ ORDERED USE_NL(obj tab) */DISTINCT ts.NAME
                    FROM SYS.obj$ obj, SYS.tab$ tab,  SYS.ts$ ts
                    WHERE obj.owner# = USERENV ('SCHEMAID')
                    AND obj.obj# = tab.obj#
                    AND tab.ts# = ts.ts#
                    AND BITAND (tab.property, 1) = 0
                    AND BITAND (tab.property, 4194400) = 0) tn,
             dba_free_space sp
             WHERE sp.tablespace_name = tn.NAME
             GROUP BY sp.tablespace_name) s, dba_data_files d
      WHERE d.tablespace_name = s.tablespace_name
      GROUP BY d.tablespace_name)

It is having a Full table scan on SYS.TS$ and SYS.FILE$. I belive it is utilizing the maximum resoures of Database.

Please help me.

Thanks in Advance.

Upvotes: 7

Views: 1094

Answers (3)

user8000219
user8000219

Reputation: 1

Common problem in 11.2.0.3. Several workarounds are available on MOS.

Query against DBA_EXTENTS slow after upgrade to 11.2.0.3 (Doc ID 1453425.1)

The Cost Based Optimizer uses a cardinality estimate of 100,000 rows for this table, and the 11.2.0.3 execution plan is doing a full table scan. If you have a large number of extents, this query can take more than 1 hour to complete. There are a number of unpublished bugs open on slow performance for queries on DBA_EXTENTS.

Upvotes: 0

Durga Viswanath Gadiraju
Durga Viswanath Gadiraju

Reputation: 3956

OEM issues many queries in the database for the book keeping. This query is to get the available free space on the tablespaces and there might be alerts configured in OEM to monitor it.

Upvotes: 0

OldProgrammer
OldProgrammer

Reputation: 12169

Have you done a query in v$sqlarea so see if it is in there, and then see what session/user it is?

Upvotes: 1

Related Questions