Re: 12.2.0.1 scheduler tables not being purged

The easiest way is to run
 select logid from... Minus select logid from... Where {predicates from delete}

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE Associate
http://orasql.org

On Tue, May 25, 2021, 18:04 Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> What do you mean?
> You will see delete statements, just compare their predicates with your
> old logs and you'll see which predicates exclude your data.
> For example, if you have rows with non-null dbid
>
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning expert
> Oracle Database Developer Choice Award winner
> Oracle ACE Associate
> http://orasql.org
>
> On Tue, May 25, 2021, 17:52 Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org>
> wrote:
>
>> 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
>> *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
>>
>>
>>
>> Hi Jeffrey,
>>
>>
>>
>> That's pretty easy to investigate:
>>
>> 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)
>>
>>
>>
>> SQL> _at_find_sql "DELETE%SCHEDULER$%" all
>>
>>
>>
>> it will return a few SQL_IDs:
>>
>> 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...
>>
>>
>>
>> [I've shortened the output to make it more readable]
>>
>>
>>
>> 3. Get their full text:
>>
>> 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>
>> 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>;
>> 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>;
>> 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>;
>> 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>
>> *Sent:* Monday, May 24, 2021 4:32 PM
>> *To:* Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org>; 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 <oracle-l-bounce_at_freelists.org> on
>> behalf of Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org>
>> *Sent:* Monday, May 24, 2021 2:45:42 PM
>> *To:* oracle-l_at_freelists.org <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-l