RE: 12.2.0.1 scheduler tables not being purged

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 25 May 2021 11:46:57 -0400
Message-ID: <4dc001d7517d$3231df10$96959d30$_at_rsiz.com>

That probably would work well, but does include the overhead of the deletes.

The generalized dinosaur method when one of {Oracle|user} fubared purging until something got too big for comfort was:

0) Make sure an Oracle script exists to recreate the objects empty with the proper permissions

Copy any contents you think you might need elsewhere (including just renaming the tables until you can do the research and possibly copy back selected contents.
Drop/purge/recreate the empty tables.

Don’t skip step 0. Nearly everything like this does have a create script (or at least used to).

3) Eventually decide if you need some of the stuff back from the renamed tables and possible index same exactly to feed your select from <old_obsolete> insert append into <new_spanky_clean>.

This is ASKEW from figuring out how the problem erupted, but unless you have reason to believe this was not an old fubar, my granddaughter can sing you a FROZEN song about it.

mwf

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sayan Malakshinov Sent: Tuesday, May 25, 2021 11:11 AM
To: Beckstrom, Jeffrey
Cc: Karthikeyan Panchanathan; ORACLE-L
Subject: 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:

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

Received on Tue May 25 2021 - 17:46:57 CEST

This message: [ Message body ]
Next message: Mark W. Farnham: "RE: Sanity Check Please: Removing Datafiles after Dropping Tablespace?"
Previous message: Chris Taylor: "Re: Sanity Check Please: Removing Datafiles after Dropping Tablespace?"
In reply to: Sayan Malakshinov: "Re: 12.2.0.1 scheduler tables not being purged"
Next in thread: Michael McMullen: "Re: ora-01113 after incorrect order of moving datafiles"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message