ANBOB™

You can easily track the duration of each export/import operation by directing the export/import job to write timestamps to the logfile using the LOGTIME parameter. For more details, refer to Expdp/Impdp LOGTIME.

However, simply having this information alone is often insufficient, even if you know there was a version or operating system change. What’s really needed to diagnose or analyze performance is concrete data—and that’s where the METRICS and LOGTIME parameters come in handy.

If you’d like additional insights, such as which process is handling what task and how much time it’s consuming, you can enable the METRICS=Y parameter. These parameters are valuable for investigating Data Pump performance and estimating the time required for similar export or import operations.

METRICS

The METRICS parameter has been around  for a long time, and has been documented since Oracle Database 11g Release 2 (11.2). It has the very useful effect of adding information to the logfile that tells you (and us) how long it took to export a particular type of database object.

 expdp system/manager@anbob parfile=impdp.par  logfile=impdp.log ... METRICS=YES

Export: Release 12.2.0.1.0 - Production on Sat Apr 22 22:20:22 2024
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Completed 1 MARKER objects in 88 seconds
Processing object type DATABASE_EXPORT/SCHEMA/XMLSCHEMA/XMLSCHEMA
Completed 3 XMLSCHEMA objects in 1 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Completed 64 TABLE objects in 98 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Completed 10 TABLE objects in 121 seconds
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Completed 1 MARKER objects in 20 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PROCACT_INSTANCE
Completed 48 PROCACT_INSTANCE objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Completed 33263 TABLE objects in 343 seconds

LOGTIME

This is a 12.1 feature. The parameter LOGTIME=ALL displays the system timestamp in front of the messages in at the screen and in the logfile. The default is NONE and you can also set it to STATUS for screen only and LOGFILE for logfile only.

 expdp system/manager@anbob parfile=impdp.par logfile=impdp.log LOGTIME=ALL

Export: Release 12.2.0.1.0 - Production on Sat Apr 22 22:20:22 2024

Copyright (c) 1982, 2016, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
22-APR-24 22:20:29.671: Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@PDB01 parfile=impdp.par logfile=impdp.log
22-APR-24 22:20:35.505: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
22-APR-24 22:20:36.032: Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
22-APR-24 22:20:36.407: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
22-APR-24 22:20:43.586: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
22-APR-24 22:20:44.126: Processing object type SCHEMA_EXPORT/USER
22-APR-24 22:20:44.199: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
22-APR-17 22:20:44.243: Processing object type SCHEMA_EXPORT/ROLE_GRANT
...
22-APR-24 22:21:00.712: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
22-APR-24 22:21:03.494: . . exported "SCOTT"."DEMO"                              8.789 KB      14 rows
22-APR-24 22:21:03.651: . . exported "SCOTT"."EMP"                               8.781 KB      14 rows
...
22-APR-24 22:21:03.656: . . exported "SCOTT"."BONUS"                                 0 KB       0 rows
22-APR-24 22:21:04.532: Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

SQL TRACE For Datapump Operations

CONNECT / as sysdba

set lines 150 pages 100 numwidth 7 
col program for a38 
col username for a10 
col spid for a7 
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,   
       s.status, s.username, d.job_name, p.spid, s.serial#, p.pid   
  from v$session s, v$process p, dba_datapump_sessions d  
 where p.addr=s.paddr and s.saddr=d.saddr;  

-- In SQL*Plus, activate SQL tracing with DBMS_SYSTEM and SID/SERIAL#  
-- Syntax: DBMS_SYSTEM.SET_EV([SID],[SERIAL#],[EVENT],[LEVEL],'') 

The Event++ syntax is only available in Oracle11g and higher releases. It does not work in lower releases of Oracle.

Pre-12c:

alter system set events 'sql_trace {process : pname = dw | pname = dm} level=12';
Oracle 12c:

alter system set events 'sql_trace {process: pname = dw | process: pname = dm} level=12';

That syntax does not work in 11.2.0.4.
Note that you can use ‘oradebug doc event’ to display the syntax.

Disabling of the tracing can be done in a similar fashion:

Pre-12c:

alter system set events 'sql_trace {process : pname = dw | pname = dm} off';
Oracle 12c:

alter system set events 'sql_trace {process: pname = dw | process: pname = dm} off';

The new event syntax can also be used to enable an event for a specific process, like in:

alter system set events 'sql_trace {process : ospid = 2345} level=12';

In SQL*Plus, activate SQL tracing with ORADEBUG and the SPID:

-- Example to SQL_TRACE Worker process with level 4 (Bind values):
oradebug setospid 17294 
oradebug unlimit 
oradebug event 10046 trace name context forever, level 4 
oradebug tracefile_name 

Data Pump tracing can also be started with a line with EVENT 39089 in the initialization parameter file. This method should only be used to trace the Data Pump calls in an early state, e.g. if details are needed about the DBMS_DATAPUMP.OPEN API call. Trace level 0x300 will trace all Data Pump client processes.

- add the following line to init.ora parameter file: 

EVENT="39089 trace name context forever,level 0x300" 

- Restart the database. 
- Start the Export Data Pump or Import Data Pump job.

So, the next time you can reach out to us about Data Pump performance, we’ll give you a big (virtual) hug if you specify METRICS=Y and LOGTIME=ALL for your Data Pump jobs. Even if we eventually need to request tracing, these parameters will help us pinpoint the exact tracing needed and significantly speed up the analysis!

打赏

微信扫一扫，打赏作者吧～