Re: 12.2.0.1 scheduler tables not being purged

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Tue, 25 May 2021 17:22:21 +0300
Message-ID: <CAOVevU6PmSbxEf8mj_ik7CrXi5sx3wREZ3V-3=Mx23gqXKMagA_at_mail.gmail.com>

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 (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%7C3645f5c859224001456608d91ef30261%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637574851358670049%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=u16vpoQbSrTCWw6eLX%2FxCwbs4kgeU2u1NSrrcn%2FjgIM%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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 25 2021 - 16:22:21 CEST