2 Replies Latest reply on Sep 4, 2015 3:56 PM by aaronhoes

    How to find SQL for DPA alerts

    aaronhoes

      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.

        • Re: How to find SQL for DPA alerts
          mandevil

          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;

          1 of 1 people found this helpful