Re: 12.2.0.1 scheduler tables not being purged

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