Shulou(Shulou.com)06/02 Report--

Xiaobian to share with you what are the three performance diagnosis methods of Expdp/Impdp, I hope you have gained something after reading this article, let's discuss it together!

overview

During the export and import process, you will occasionally encounter some performance problems, which can be caused by host resources, database version, PSU version, datapump itself bugs, etc.

Here, you can first determine which link has a problem according to the requirements, whether there is a performance problem when exporting/importing metadata or data; whether there is a performance problem when exporting/importing specific objects?

For these problems, the MOS provides the following 3 diagnostic methods (only the third is demonstrated):

method one

Add METRICS=Y TRACE=480300 (or 480301 Capture SQL trace) to the expdp/imdp command and rerun expdp/imdp

methodology II

10046 trace enabling level 12 for DataPump Master (DM) and Worker (DW) processes

--Version>= 11g and

alter system set events 'sql_trace {process : pname = dw |pname = dm} level=12'; --version = 12c SQL> alter system set events 'sql_trace {process: pname = dw| process: pname = dm} level= 12'; then add the command line launch expdp/impdp with the parameter METRICS = Y

Method 3:(Recommended)

1. View the running DataPump export process

connect / as sysdba set lines 350 pages 400 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 and (UPPER(s.program) LIKE '%DM0%' or UPPER(s.program) LIKE '%DW0%');

Track DataPump export processes that have started running

--Confirm Data Pump Worker SID and SERIAL#(e.g. For DM and DW processes), trace Master/Worker processes at level 12 (bind and wait information): SQL> exec sys.dbms_system.set_ev (SID, SERIAL#, 10046, 12, '');

Description:

DM00 is Data Pump Master Process The Data Pump master (control) process is started during job creation and coordinates all tasks performed by the Data Pump job. It handles all client interactions and communication, establishes all job contexts, and coordinates all worker process activities on behalf of the job. The Data Pump Master (Control) process starts during job creation and coordinates all tasks performed by the Data Pump job. It handles all client interactions and communications, establishes all job context, and coordinates all worker process activities on behalf of the job. DW00 is Data Pump Worker Process The Data Pump worker process is responsible for performing tasks that are assigned by the Data Pump master process, such as the loading and unloading of metadata and data. The Data Pump worker process is responsible for performing tasks assigned by the Data Pump master process, such as loading and unloading metadata and data.

3. End tracking

Wait for a period of time (at least 1 hour) to capture enough information.

--End trace SQL> exec sys.dbms_system.set_ev (SID, SERIAL#, 10046, 0, '');

For all methods, DataPump Master and Worker trace files are written to BACKGROUND_DUMP_DEST or/trace.

4. Capture trace files

SQL> show parameter BACKGROUND_DUMP_DEST; SQL> select * from v$diag_info WHERE name='Default Trace File';

The diagnostic methods provided above for MOS can be used flexibly. After querying the SID of the DataPump process, you can query whether there is session blocking. What is the abnormal waiting event?

For example, a critical performance issue with the expdp / impdp command due to the "StreamsAQ: enqueue blocked on low memory" wait event was caused by Bug 27634991 (fixed in version 19.1 and later).

After reading this article, I believe you have a certain understanding of "Expdp/Impdp three performance diagnosis methods". If you want to know more relevant knowledge, welcome to pay attention to the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.