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:
- Open SQL Developer.
- Go to Package and open the Load_Metrics PACKAGE body.
- Go to the LOAD_SERVER_METRICS procedure.
- 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;
*/
- 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.
- 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