How to compare the performance of Oracle database before and after migration

There are more and more databases that need to be migrated. How to measure the database performance before and after has become a difficult point. This article uses the AWR Compare report to solve this problem.

The following are test library contents

Environment description

SID	DBID	OS	Description
Finally	4033498616	Windows 7 x64	Assumed to be a migrated database
Zhadanren	97461353	Solaris Sparc 6 x64	Assumed to be a pre-migration database

Process

The detailed procedure is as follows

Export Snapshot data

Attention:
The snapshot exported here should preferably contain the time period when the business is busiest, in order to more accurately measure the database performance before and after the migration.

Related sql:

create directory dpdir as ‘&dir_path‘ ;@?/rdbms/admin/awrextr.sql

Steps:

1as‘/u01/dpdir‘ ;Directory created.2@?/rdbms/admin/awrextr.sql

Here are a few parameters that you need to enter specifically:

Enter the dbid you want to export snapshot, where you can use the default carriage return
Enter value for dbid:

The list date range of the snapshot, which is reserved for 8 days by default, can be listed in full, and the snapshot number to be exported is specified.
Enter value for num_days:100

The start snapshot number to export
Enter value for begin_snap:271

The cutoff snapshot number to export

Enter value for end_snap:280

To the directory where the files are exported (in fact Awrextr.sql is also based on DataPump)
Enter value for Directory_name:dpdir

Name of the export file
Enter value for file_name:zhadanren1026

The export process is then started. The dump files and log logs are generated under the defined directory. You can view the log logs to see if the export is normal.

Using the dump file prefix:zhadanren1026|| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| The AWR extract dump file would be located| In the following directory/file:| /u01/dpdir| Zhadanren1026.dmp| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|| AWR Extract Started ...|| This operation would take a few moments. the| Progress of the AWR extract operation can be| Monitored in the following directory/file:| /u01/dpdir| Zhadanren1026.log|End of AWR Extract

Import Snapshot data

Related sql:

create directory dpdir as ‘&dir_path‘ ;@?/rdbms/admin/awrload.sql

Steps:

1、创建directory（源库也是使用datapump的方式导入的）[email protected]FINALLY>createas‘d:\apps\dpdir‘ ;目录已创建。2、然后将导出的dump文件上传到迁移后的数据库服务器相应的新创建的directory下。3、执行相应的sql[email protected]FINALLY>@?/rdbms/admin/awrload.sql

Here are a few parameters that you need to enter specifically:

Enter the value of the Directory_name: Dpdir

Note here that you do not include the file suffix name.
Enter the value of the file_name: zhadanren1026

This is a temporary use of the schema, which is automatically deleted when the import process is complete.
Enter the value of the schema_name: Awr_stage

Default tablespace for temporary schema
Enter the value of the Default_tablespace: Sysaux

Default temporal tablespace for temporary schema
Enter a value for Temporary_tablespace: TEMP

The import process is then started. The import log log is generated at the end of the defined directory. You can view the log logs to see if the import is normal.

... Creating AWR_STAGE user|| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|  Loading the AWR data from the following|  directory/file:|   d:\apps\dpdir|   zhadanren1026.dmp| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~||  ...||  This operation will take a few moments. The|  progress of the AWR load operation can be|  in the following directory/file:|   d:\apps\dpdir|   zhadanren1026.log|... Dropping AWR_STAGE userEnd of AWR Load

Generate AWR Compare Report

After the above steps, the source library's snapshot information has been imported into the migrated database finally, we can use the AWR Compare report to measure the database performance before and after the migration.

Related sql:

View Snapshotsql Select t.dbid, t.snap_id, To_char (begin_interval_time, ' Yyyymmddhh24miss ') begin_time, To_char (End_interval_time, ' Yyyymmddhh24miss ') end_time  from Dba_hist_snapshot t order 
     
      by 
      1, 
      4 
      desc;
     Generating Reports Sqlspool awr_compare_report.html set echo off; set veri off; set feedback off; set head offset verify offset lines 8000Select *  from TABLE(dbms_workload_repository.awr_diff_report_html (4033498616,1,315,  97461353,1, BA,bayi));Spool off

Steps

1, first find the pre-migration database snapshot business busy time period corresponding snapshot ID.

For example, we chose this time period from 7 to 8 in 2016.10.26. Then the corresponding snap_id start is 279 cutoff of 280.

select t.dbid,       t.snap_id,       to_char(begin_interval_time, ‘yyyymmddhh24miss‘) begin_time,       to_char(end_interval_time, ‘yyyymmddhh24miss‘) end_time  from DBA_HIST_SNAPSHOT t  where dbid=97461353 order by 1, 4 desc;

2. Locate the corresponding snapshot ID for the time period after the migration database snapshot business busy.

There is a need for contrast, so the same period is generally chosen as the comparison. Not necessarily the same date, but at least select a representative time period. The migrated database has a snap_id start of 412 for the 7-to-8 o'clock time period and a cutoff of 413.

select t.dbid,       t.snap_id,       to_char(begin_interval_time, ‘yyyymmddhh24miss‘) begin_time,       to_char(end_interval_time, ‘yyyymmddhh24miss‘) end_time  from DBA_HIST_SNAPSHOT t  where dbid=4033498616 order by 1, 4 desc;

Based on the information above, we can use dbms_workload_repository.awr_diff_report_html to generate the Compare report.

Spool awr_compare_report.htmlSetEchooff;SetVerioff;SetFeedbackoff;SetHeadoffSetVerifyoffSetLines8000Select* fromTABLE (dbms_workload_repository.awr_diff_report_html (97461353,1,279,280,4033498616,1,412,413)); spoolif

The sample content is as follows:

Physical configuration Comparison

Load comparison This is important, but also to measure the performance of the migration before and after the important indicators (but carefully choose a good time, to ensure that the time period of the database before and after the migration of the same business volume)

The report contains a lot of content, but also includes the parameters of the initialization of the comparison, we can build their own later to see more. Specific indicator content is no longer detailed here.

How to compare the performance of Oracle database before and after migration