expdp导出一张带有clob的表很慢，分析下跟踪下导出等待事件
expdp导出一张表很慢，定位问题及给出解决方法。
理论上Expdp导出一张表应该很快，如果发现导出操作卡顿，我们需要确定等待事件，以及等待事件操作的对象，从而分析应对策略。
下面我们开启10046跟踪数据泵导出操作会话 10145
SQL> select pid,spid from v$process where addr in (select paddr from v$session where sid=9903);
PID SPID
---------- ------------------------
570 205520
SQL> oradebug setorapid 570
Oracle pid: 570, Unix process pid: 205520, image: oracle@n-pc-sr850-267 (DM00)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever ,level 12
Statement processed.
SQL> oradebug tracefile_name
/oracle/diag/rdbms/xxxx15/xxxx152/trace/xxxx152_dm00_205520.trc
SQL> oradebug event 10046 trace name context off;
Statement processed.
SQL> select sid,serial#,machine from v$session where sid=464;
SID SERIAL#
---------- ----------
MACHINE
----------------------------------------------------------------
464 3972
n-pc-sr850-267
SQL> select pid,spid from v$process where addr in (select paddr from v$session where sid=464);
PID SPID
---------- ------------------------
580 205632
SQL> oradebug setorapid 580
Oracle pid: 580, Unix process pid: 205632, image: oracle@n-pc-sr850-267 (DW00)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever ,level 12
Statement processed.
SQL> oradebug tracefile_name
/oracle/diag/rdbms/xxxx15/xxxx152/trace/xxxx152_dw00_205632.trc
SQL> oradebug event 10046 trace name context off;
Statement processed.
分析trace文件
tkprof /oracle/diag/rdbms/xxxx15/xxxx152/trace/xxxx152_dw00_205632.trc exp2.txt
cat exp2.txt
.......
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path read 10882 0.42 101.76
Disk file operations I/O 4 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 6 0.00 0.00 0 4 6 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.00 0.00 0 4 6 4
Misses in library cache during parse: 1
Misses in library cache during execute: 1
0 user SQL statements in session.
3 internal SQL statements in session.
3 SQL statements in session.
********************************************************************************
Trace file: /oracle/diag/rdbms/xxxx15/xxxx152/trace/xxxx152_dw00_205632.trc
Trace file compatibility: 12.2.0.0
Sort options: default
1 session in tracefile.
0 user SQL statements in trace file.
3 internal SQL statements in trace file.
3 SQL statements in trace file.
3 unique SQL statements in trace file.
11310 lines in trace file.
56 elapsed seconds in trace file.
主要等待事件为 direct path read ，继续看xxxx152_dw00_205632.trc跟踪文件
大量类似的操作
11274 WAIT #140737181705840: nam='direct path read' ela= 3010 file number=595 first dba=2051529 block cnt=1 obj#=730036 tim=70516965594132
11275 WAIT #140737181705840: nam='direct path read' ela= 25836 file number=595 first dba=2108424 block cnt=2 obj#=730036 tim=70516965620010
11276 WAIT #140737181705840: nam='direct path read' ela= 7813 file number=595 first dba=2108426 block cnt=2 obj#=730036 tim=70516965627973
11277 WAIT #140737181705840: nam='direct path read' ela= 14783 file number=595 first dba=1992441 block cnt=3 obj#=730036 tim=70516965642881
11278 WAIT #140737181705840: nam='direct path read' ela= 729 file number=601 first dba=2157373 block cnt=1 obj#=730036 tim=70516965643778
11279 WAIT #140737181705840: nam='direct path read' ela= 12146 file number=601 first dba=2157846 block cnt=8 obj#=730036 tim=70516965655968
11280 WAIT #140737181705840: nam='direct path read' ela= 7682 file number=601 first dba=2170586 block cnt=3 obj#=730036 tim=70516965663768
说明对象730036在执行了direct path read这是物理读，我们看看该对象是什么类型
SQL> col owner for a20
SQL> col object_name for a20
SQL> col object_type for a30
SQL> col object_name for a40
SQL> select owner,object_name,object_type from dba_objects where object_id=730036
OWNER OBJECT_NAME OBJECT_TYPE
-------------------- ---------------------------------------- ------------------------------
t11 SYS_LOB0000730035C00002$$ LOB
显然这就是导出lob字段。
加速方式：开启并行操作。
含有lob字段的表定义
DBMS_METADATA.GET_DDL('TABLE','G_QB_CONTENT_JSON','t11')
--------------------------------------------------------------------------------
CREATE TABLE "t11"."G_QB_CONTENT_JSON"
( "QBID" VARCHAR2(32) NOT NULL ENABLE,
"JSONS" CLOB,
"INDATE" DATE DEFAULT sysdate,
CONSTRAINT "KEY_G_QB_CONTENT_JSON" PRIMARY KEY ("QBID")
USING INDEX (CREATE INDEX "t11"."IDX_G_QB_CONTENT_JSON_ID" ON "t11"."G_QB_CO
NTENT_JSON" ("QBID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "t11" ) ENABLE,
CONSTRAINT "ENSURE_JSON" CHECK (jsons IS json) ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "t11"
LOB ("JSONS") STORE AS SECUREFILE (
TABLESPACE "t11" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
注意： expdp的worker进程dw00默认使用direct path来读写数据。
相关文章