How to find SQL for DPA alerts

Question

Is there a way to view the queries DPA uses for alert checks? Specifically I am looking for the SQL it uses for the "Oracle Stale Statistics" alert.

When I check the dba_tab_statistics view in my database I see some tables marked stale that DPA does not alert for and vice versa. I would like to understand DPA's check before I switch to a custom alert in case the built-in alert is taking some other factors into account that I am not.

mandevil · Accepted Answer

Here is the query I see running in my environment. However, I think it depends on how the alert is configured as to how the ObjList is built out.

A quick trace may reveal more details.

DECLARE

ObjList dbms_stats.ObjectTab;

BEGIN

dbms_stats.gather_database_stats(objlist=>ObjList, options=>'LIST AUTO');

FOR i in ObjList.FIRST..ObjList.LAST

LOOP

INSERT

INTO ignite_temp_stats

(

obj_owner,

obj_name,

obj_type,

obj_partition

)

VALUES

(

ObjList(i).ownname,

ObjList(i).ObjName,

ObjList(i).ObjType,

ObjList(i).partname

)

;

END LOOP;

COMMIT;

END;

How to find SQL for DPA alerts

Top Replies