Tech Tip: HOTFIX for ‘Data Migration takes too long due to the large data volume in the ’SERVER_FACT’ table

Document created by sinma15 Employee on Mar 21, 2017Last modified by raipr01 on Mar 23, 2017
Version 2Show Document
  • View in full screen mode

Problem: The database migration takes a long time to complete due to a huge amount of data stored in the SERVER_FACT table. The queries that are currently used for migration take up a lot of resources and are not efficient.

Solution: The following manual update fixes the Load_Metrics package and enhances the LOAD_SERVER_METRICS procedure. Once you deploy the updated package on your system, the migration process will be faster. Follow these steps:

  1. Open SQL Developer.
  2. Go to Package and open the Load_Metrics PACKAGE body.
  3. Go to the LOAD_SERVER_METRICS procedure.
  4. Comment out (disable) the following query by entirely enclosing it in comment signs:

    /*

    SELECT   count(*) into dupCount

             from (

             select /*+  INDEX(a server_fact(GMT_METRIC_DATE)) use_hash(a b)*/  /* 1

             from

             server_fact a

             where

             a.gmt_METRIC_DATE BETWEEN

               (select min(gmt_metric_date)  from server_fact_tmp   )

               AND

               (select max(gmt_metric_date)  from server_fact_tmp   )

             and

             exists

             ( select 1

               from

               server_fact_tmp b

               where

               a.gmt_metric_date = b.gmt_metric_date

               and

               a.entity_id = b.entity_id

              )

              ) where rownum = 1;

    */

  5. Manually add or copy and paste the following query:

    SELECT   count(1) into dupCount

               from server_fact a

               where exists ( select 1 from server_fact_tmp b

                              where a.entity_id = b.entity_id

                              and a.metric_id = b.metric_id

                              and a.source_type = b.source_type

                              and a.gmt_metric_date = b.gmt_metric_date);

    To facilitate tracking, consider adding a clarifying remark to the new query.

  6. Comment out (disable) the following query by entirely enclosing it in comment signs:

/*   delete /*+  INDEX(a server_fact(GMT_METRIC_DATE))  use_hash(a b) */  /*  from server_fact a

           where

           a.gmt_METRIC_DATE BETWEEN

           (select min(gmt_metric_date)  from server_fact_tmp   )

           AND

           (select max(gmt_metric_date)  from server_fact_tmp   )

           and

           exists

           (

             select 1

             from

             server_fact_tmp b

             where

             a.entity_id = b.entity_id

             and a.gmt_metric_date = b.gmt_metric_date

             and a.metric_id = b.metric_id

             and a.source_type = b.source_type

           ); */

7. Manually add or copy and paste the following query:

delete  from server_fact a

           where exists ( select 1 from server_fact_tmp b

                           where a.entity_id = b.entity_id

                             and a.gmt_metric_date = b.gmt_metric_date

                             and a.metric_id = b.metric_id

                             and a.source_type = b.source_type

                           );

To facilitate tracking, consider adding a clarifying remark to the new query.

8. Open the Compile tool and compile the updated package.

9. Verify that the deployment was successful:

  • Connect to the SQL Developer.
  • Go to Package and open the Load_Metrics PACKAGE body.
  • Go to the LOAD_SERVER_METRICS procedure and check if the updated sql queries are available.
  • Verify that Load_Metrics is compiled without errors.

 

Written By :

   Pradeep Rai 

Attachments

    Outcomes