This content has been marked as final. Show 8 replies
Rapid E-Suite wrote:It does not run on my DB.
I see this unknown query always running and normally takes 20 - 30 % of activities on my database, It normally run longer than 1 hours. I researched about this but can't find information about the query as well. Anybody know what is cause this query always running ?
SELECT SUM(USED), SUM(TOTAL)
(SELECT /*+ ORDERED */ SUM(D.BYTES)/(1024*1024)-MAX(S.BYTES) USED, SUM(D.BYTES)/(1024*1024) TOTAL
(SELECT TABLESPACE_NAME, SUM(BYTES)/(1024*1024) BYTES
(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)
use DBMS_SESSION.SESSION_TRACE_ENABLE(TRUE,TRUE,'ALL_EXECUTIONS'); to create trace file
trace file show where time is being spent
It runs in only a couple of seconds on my puny laptop
Edited by: sb92075 on Jan 9, 2012 9:18 PM
21:18:21 SQL> ed Wrote file afiedt.buf 1 SELECT SUM(used), 2 SUM(total) 3 FROM (SELECT /*+ ORDERED */ SUM(d.bytes) / ( 1024 * 1024 ) - MAX(s.bytes) used 4 , 5 SUM(d.bytes) / ( 1024 * 1024 ) 6 total 7 FROM (SELECT tablespace_name, 8 SUM(bytes) / ( 1024 * 1024 ) bytes 9 FROM (SELECT /*+ ORDERED USE_NL(obj tab) */ DISTINCT ts.NAME 10 FROM sys.obj$ obj, 11 sys.tab$ tab, 12 sys.ts$ ts 13 WHERE obj.owner# = Userenv('SCHEMAID') 14 AND obj.obj# = tab.obj# 15 AND tab.ts# = ts.ts# 16 AND Bitand(tab.property, 1) = 0 17 AND Bitand(tab.property, 4194400) = 0) tn, 18 dba_free_space sp 19 WHERE sp.tablespace_name = tn.NAME 20 GROUP BY sp.tablespace_name) s, 21 dba_data_files d 22 WHERE d.tablespace_name = s.tablespace_name 23* GROUP BY d.tablespace_name) 21:18:25 SQL> / SUM(USED) SUM(TOTAL) ---------- ---------- 21634.5 22660 21:18:27 SQL>
I have disabled the "Segment Advisor" task under "Automated Maintenance Tasks" on OEM and then wait for 3-4 hours but I still see that query finish and run again.
I checked scheduler jobs but see no running jobs found.
I find this query is in MGMT_ADMIN_DATA package on schema SYSMAN and it is execute by SYS user.
some time ago i had the same problem with this statement from the EM. There is a known Problem with query against dba_free_space und recyclebin objects.
Queries on DBA_FREE_SPACE are Slow [ID 271169.1]
Try to purge your recyclebin if you dont need the objects anymore.
Your query should be faster after.