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 use database EM to monitor the query, its 100% activity is on db file sequential read which impact my database to performance.
Do you know which user executes this query?
Seems to be segment advisory is causing the problem. Please check the OEM and disable the segment advisory.
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.
Is there any solution for this. We are also facing a lot of performance issues with this query.
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.
Is there any way to stop this query execution.
Also, could you please give some further information like from where and when this SYS query will be executed.