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@xxxxxxxxx> 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@xxxxxxxxx>
*Sent:* Tuesday, May 25, 2021 10:22 AM
*To:* Beckstrom, Jeffrey <jbeckstrom@xxxxxxxxx>
*Cc:* Karthikeyan Panchanathan <keyantech@xxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx
*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> @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@xxxxxxxxx>
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@xxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx
*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@xxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx
*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@xxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx
*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@xxxxxxxxx>
*Sent:* Monday, May 24, 2021 4:32 PM
*To:* Beckstrom, Jeffrey <jbeckstrom@xxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
*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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Beckstrom, Jeffrey <jbeckstrom@xxxxxxxxx>
*Sent:* Monday, May 24, 2021 2:45:42 PM
*To:* oracle-l@xxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxx>
*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>
