RE: 12.2.0.1 scheduler tables not being purged
Date: Tue, 25 May 2021 14:52:05 +0000
Message-ID: <DM6PR09MB4677E9148D1DB82695BE21B8DF259_at_DM6PR09MB4677.namprd09.prod.outlook.com>
I'm not sure if a trace will tell WHY certain rows ARE being deleted and OTHERS are NOT.
From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Sent: Tuesday, May 25, 2021 10:22 AM
Hi Jeffrey,
That's pretty easy to investigate:
SQL> _at_find_sql "DELETE%SCHEDULER$%" all
it will return a few SQL_IDs:
[I've shortened the output to make it more readable]
3. Get their full text:
To: Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org>
Cc: Karthikeyan Panchanathan <keyantech_at_gmail.com>; oracle-l_at_freelists.org
Subject: Re: 12.2.0.1 scheduler tables not being purged
1. execute purge job:
SQL> exec Dbms_scheduler.run_job('PURGE_LOG');
2. find DELETEs from SYS.SCHEDULER$ tables: https://github.com/xtender/xt_scripts/blob/master/find_sql.sql<https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fxtender%2Fxt_scripts%2Fblob%2Fmaster%2Ffind_sql.sql&data=04%7C01%7Cjbeckstrom%40gcrta.org%7C573d45b138fb41146d2a08d91f8889d7%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637575494595062396%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=hBKdaooXyzlwq4jg22i9SWf2fgKxb5biI8%2BKFwsZOkE%3D&reserved=0>
(or you can trace it with sql_trace level 1 and get them from a trace file)
INST_ID SQL_ID EXECS ELAEXE SQL_TEXT_TRUNC
------- ------------- ------ -------- -------------------------------------
1 1xcngwj6ypnkx 1 .010972 DELETE FROM SYS.SCHEDULER$_JOB_RUN_...
1 7kvy2vdfs9ffv 1 .005409 DELETE FROM SYS.SCHEDULER$_EVENT_LO...
1 av3n8cwad6f2n 1 .003241 DELETE FROM SYS.SCHEDULER$_EVENT_LO...
1 bycc25ggkmbmw 1 .003204 DELETE FROM SYS.SCHEDULER$_WINDOW_D...
1xcngwj6ypnkx:
DELETE FROM SYS.SCHEDULER$_JOB_RUN_DETAILS
WHERE LOG_ID IN (
SELECT E.LOG_ID
FROM SYS.SCHEDULER$_EVENT_LOG E,
SYS.SCHEDULER$_CLASS C
WHERE E.TYPE# = 66
AND E.CLASS_ID = C.OBJ#(+)
AND (E.FLAGS IS NULL OR BITAND(E.FLAGS, 2)=0)
AND OPERATION NOT LIKE 'CHAIN%'
AND E.LOG_DATE < :B2 - NUMTODSINTERVAL(NVL(C.LOG_HISTORY, :B1 ), 'DAY')
)
/
7kvy2vdfs9ffv
DELETE FROM SYS.SCHEDULER$_EVENT_LOG
WHERE DBID IS NULL
AND LOG_ID IN (
SELECT E.LOG_ID
FROM SYS.SCHEDULER$_EVENT_LOG E,
SYS.SCHEDULER$_CLASS C
WHERE E.TYPE# = 66
AND E.CLASS_ID = C.OBJ#(+)
AND (E.FLAGS IS NULL OR BITAND(E.FLAGS, 2)=0)
AND OPERATION NOT LIKE 'CHAIN%'
AND E.LOG_DATE < :B2 - NUMTODSINTERVAL(NVL(C.LOG_HISTORY, :B1 ), 'DAY')
)
/
av3n8cwad6f2n:
DELETE FROM SYS.SCHEDULER$_EVENT_LOG
WHERE DBID IS NULL
AND LOG_ID IN (
SELECT E.LOG_ID
FROM SYS.SCHEDULER$_EVENT_LOG E
WHERE E.TYPE# = 69
AND E.LOG_DATE < :B2 - NUMTODSINTERVAL(:B1 , 'DAY')
)
/
[I have formatted them...]
4. So you can now analyze why these queries do not delete your old data.
For example, sql_id: av3n8cwad6f2n "DELETE FROM SYS.SCHEDULER$_EVENT_LOG WHERE DBID IS NULL ..." deletes only rows where DBID is null and type#=69, so you can check your DBID and TYPE#:
select dbid, type#, count(*)
from SYS.SCHEDULER$_EVENT_LOG
group by dbid, type#;
etc...
On Tue, May 25, 2021 at 4:52 PM Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org<mailto:jbeckstrom_at_gcrta.org>> wrote: Looks like very recent information is purging but not all information. Checked several databases and all doing something similar.
select trunc(log_date),count(*) from sys.dba_scheduler_job_run_details group by trunc(log_date) order by 1
TRUNC(LOG COUNT(*)
--------- ----------
These go back to 2011
02-JUN-20 16 03-JUN-20 18 04-JUN-20 18 05-JUN-20 16 06-JUN-20 106 07-JUN-20 112 08-JUN-20 16 09-JUN-20 18 10-JUN-20 18 11-JUN-20 17 12-JUN-20 18 13-JUN-20 106 14-JUN-20 111 15-JUN-20 18 16-JUN-20 16 17-JUN-20 18 18-JUN-20 13 19-JUN-20 16 20-JUN-20 11 21-JUN-20 102 22-JUN-20 18 23-JUN-20 16 24-JUN-20 18 25-JUN-20 18 26-JUN-20 16 27-JUN-20 11 - from here on looks like purging 25-APR-21 4 26-APR-21 8 27-APR-21 8 28-APR-21 8 29-APR-21 8 30-APR-21 8 01-MAY-21 10 02-MAY-21 9 03-MAY-21 8 04-MAY-21 8 05-MAY-21 8 06-MAY-21 11
From: Beckstrom, Jeffrey
Sent: Tuesday, May 25, 2021 8:09 AM
To: Karthikeyan Panchanathan <keyantech_at_gmail.com<mailto:keyantech_at_gmail.com>>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
Subject: RE: 12.2.0.1 scheduler tables not being purged
The attached shows no rows from June 28, 2020 to April 24, 2021 so some data is being purged.Howver there is a lot of data from June 27, 2020 and earlier.
From: Beckstrom, Jeffrey
Sent: Tuesday, May 25, 2021 7:43 AM
To: Karthikeyan Panchanathan <keyantech_at_gmail.com<mailto:keyantech_at_gmail.com>>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
Subject: RE: 12.2.0.1 scheduler tables not being purged
Select * from dba_scheduler_global_attribute ;
ATTRIBUTE_NAME
VALUE
MAX_JOB_SLAVE_PROCESSES LOG_HISTORY
30
DEFAULT_TIMEZONE
US/Eastern
LAST_OBSERVED_EVENT
EVENT_EXPIRY_TIME
CURRENT_OPEN_WINDOW
EMAIL_SERVER
EMAIL_SERVER_ENCRYPTION
NONE
EMAIL_SERVER_CREDENTIAL
EMAIL_SENDER
FILE_WATCHER_COUNT
0
11 rows selected.
From: Beckstrom, Jeffrey
Sent: Tuesday, May 25, 2021 7:33 AM
To: Karthikeyan Panchanathan <keyantech_at_gmail.com<mailto:keyantech_at_gmail.com>>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
Subject: RE: 12.2.0.1 scheduler tables not being purged
There is no object called dba_shcheduler_global_Attirbute.
The scheduler shows "purge_log" as running with no failures.
From: Karthikeyan Panchanathan <keyantech_at_gmail.com<mailto:keyantech_at_gmail.com>>
Sent: Monday, May 24, 2021 4:32 PM
To: Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org<mailto:jbeckstrom_at_gcrta.org>>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
Subject: Re: 12.2.0.1 scheduler tables not being purged
Dbms_scheduler.run_job('PURGE_LOG')
That should purge job log.
Please check dba_shcheduler_global_Attirbute where attribute_name='LOG_HISTORY' to see log history value
Get Outlook for iOS<https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Faka.ms%2Fo0ukef&data=04%7C01%7Cjbeckstrom%40gcrta.org%7C573d45b138fb41146d2a08d91f8889d7%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637575494595072349%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=BYUP3zGPmIOYpBR56FtHGlotu3uvcJ6le7yVXdsLKGw%3D&reserved=0>
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org<mailto:jbeckstrom_at_gcrta.org>> Sent: Monday, May 24, 2021 2:45:42 PM
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: 12.2.0.1 scheduler tables not being purged
What purges the scheduler tables? We are seeing entries going back to 2011. The sys owned purge_log job does show as being scheduled with no failures.
select min (log_date) from dba_scheduler_job_log;
MIN(LOG_DATE)
03-OCT-11 10.00.01.100000 PM -04:00 select min (log_date) from sys.SCHEDULER$_EVENT_LOG;
MIN(LOG_DATE)
03-OCT-11 10.00.01.100000 PM -04:00 Jeffrey Beckstrom
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
-- Best regards, Sayan Malakshinov Oracle performance tuning engineer Oracle ACE Associate http://orasql.org<https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Forasql.org%2F&data=04%7C01%7Cjbeckstrom%40gcrta.org%7C573d45b138fb41146d2a08d91f8889d7%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637575494595072349%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=sauIs%2BSItW0mMl4cq0ooe1jiLb8b959hUuZVnKQcQyQ%3D&reserved=0> -- http://www.freelists.org/webpage/oracle-lReceived on Tue May 25 2021 - 16:52:05 CEST