Export: Release 12.2.0.1.0 - Production on Wed Nov 4 14:35:20 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_27": "/******** AS SYSDBA" directory=DMP_DIR dumpfile=test2.dmp tables=cache100.test1010 trace=480300 exclude=FGA_POLICY,STATISTICS metrics=y
W-1 Startup took 3660 seconds
W-1 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
W-1 Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
W-1 Completed 1 PROCACT_INSTANCE objects in 29 seconds
W-1 Processing object type TABLE_EXPORT/TABLE/TABLE
W-1 Completed 1 TABLE objects in 1 seconds
W-1 . . exported "CACHE100"."TEST1010" 5.515 KB 3 rows in 0 seconds using direct_path
W-1 Completed 1 TABLE_EXPORT/TABLE/TABLE_DATA objects in 0 seconds
W-1 Master table "SYS"."SYS_EXPORT_TABLE_27" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_27 is:
/dmp/test2.dmp
Job "SYS"."SYS_EXPORT_TABLE_27" successfully completed at Wed Nov 4 15:39:57 2020 elapsed 0 01:04:32
导出一个3行数据的表，花了一个小时的时间。
再次执行导出，且跟踪导出会话。
查看expdp会话，正在执行下述语句。
SQL Monitoring Report
SQL Text
------------------------------
SELECT COUNT(*) FROM SYS.ALL_CAPTURE_PREPARED_TABLES WHERE ROWNUM = 1
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
Session : SYS (1139:54505)
SQL ID : 73j5f1u2r14n3
SQL Execution ID : 33554443
Execution Started : 11/06/2020 10:06:34
First Refresh Time : 11/06/2020 10:06:40
Last Refresh Time : 11/06/2020 11:03:23
Duration : 3409s
Module/Action : Data Pump Master/SYS_EXPORT_TABLE_30
Service : SYS$USERS
Program : oracle@fdpdb2 (DM01)
PLSQL Entry Ids (Object/Subprogram) : 11092,1
PLSQL Current Ids (Object/Subprogram) : 9260,5
Fetch Calls : 1
Global Stats
====================================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
====================================================================================================
| 3409 | 3393 | 0.01 | 0.00 | 0.02 | 16 | 1 | 226M | 18 | 144KB |
====================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3425924624)
===============================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
===============================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +3409 | 1 | 1 | | | . | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +3409 | 1 | 1 | | | . | | |
| 2 | COUNT STOPKEY | | | | 1 | +3409 | 1 | 1 | | | . | | |
| 3 | FILTER | | | | 1 | +3409 | 1 | 1 | | | . | | |
| 4 | NESTED LOOPS | | 4M | 31691 | 1 | +3409 | 1 | 1 | | | . | | |
| 5 | NESTED LOOPS OUTER | | 4M | 30606 | 1 | +3409 | 1 | 1 | | | . | | |
| 6 | HASH JOIN | | 12 | 30582 | 3404 | +6 | 1 | 1 | | | 2MB | | |
| 7 | INDEX FULL SCAN | I_USER2 | 266 | 1 | 1 | +6 | 1 | 267 | | | . | | |
| 8 | NESTED LOOPS OUTER | | 12 | 30581 | 1 | +3409 | 1 | 1 | | | . | | |
| 9 | HASH JOIN RIGHT OUTER | | 12 | 30557 | 3404 | +6 | 1 | 1 | | | 2MB | | |
| 10 | INDEX FULL SCAN | I_USER2 | 266 | 1 | 1 | +6 | 1 | 267 | | | . | | |
| 11 | NESTED LOOPS | | 12 | 30556 | 3404 | +6 | 1 | 1 | | | . | | |
| 12 | HASH JOIN | | 303 | 30556 | 3404 | +6 | 1 | 825 | | | 2MB | | |
| 13 | INDEX FULL SCAN | I_USER2 | 266 | 1 | 1 | +6 | 1 | 267 | | | . | | |
| 14 | HASH JOIN RIGHT OUTER | | 78781 | 30554 | 3408 | +2 | 1 | 854 | | | 100MB | | |
| 15 | INDEX FAST FULL SCAN | I_OBJ1 | 2M | 1497 | 1 | +6 | 1 | 2M | | | . | | |
| 16 | HASH JOIN RIGHT OUTER | | 78779 | 27907 | 3409 | +1 | 1 | 854 | | | 109MB | | |
| 17 | INDEX FAST FULL SCAN | I_OBJ1 | 2M | 1497 | 1 | +6 | 1 | 2M | | | . | | |
| 18 | HASH JOIN | | 78378 | 25323 | 3404 | +6 | 1 | 854 | | | 2MB | | |
| 19 | TABLE ACCESS FULL | TS$ | 116 | 19 | 1 | +6 | 1 | 116 | | | . | | |
| 20 | NESTED LOOPS OUTER | | 78378 | 3641 | 3404 | +6 | 1 | 854 | | | . | | |
| 21 | HASH JOIN | | 78378 | 3639 | 3404 | +6 | 1 | 854 | | | 4MB | | |
| 22 | TABLE ACCESS FULL | TAB$ | 32649 | 1400 | 1 | +6 | 1 | 32745 | | | . | | |
| 23 | NESTED LOOPS | | 5M | 373 | 3404 | +6 | 1 | 44104 | | | . | | |
| 24 | TABLE ACCESS FULL | OBJ$ | 2M | 3 | 3404 | +6 | 1 | 6606 | | | . | | |
| 25 | TABLE ACCESS FULL | OBJ$ | 14250 | 1 | 3407 | +3 | 6606 | 44104 | 17 | 136KB | . | | |
| 26 | INDEX RANGE SCAN | I_IMSVC1 | 1 | | | | 854 | | | | . | | |
| 27 | INDEX UNIQUE SCAN | I_STREAMS_PREPARE1 | 1 | | 1 | +3409 | 825 | 1 | | | . | | |
| 28 | VIEW PUSHED PREDICATE | | 1 | 2 | | | 1 | | | | . | | |
| 29 | SORT GROUP BY | | 1 | 2 | | | 1 | | | | . | | |
| 30 | TABLE ACCESS CLUSTER | CDEF$ | 4 | 2 | | | 1 | | | | . | | |
| 31 | INDEX UNIQUE SCAN | I_COBJ# | 1 | 1 | | | 1 | | 1 | 8192 | . | | |
| 32 | TABLE ACCESS CLUSTER | SEG$ | 303K | 2 | 1 | +3409 | 1 | 1 | | | . | | |
| 33 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | 1 | 1 | +3409 | 1 | 1 | | | . | | |
| 34 | VIEW | | 1 | | 1 | +3409 | 1 | 1 | | | . | | |
| 35 | NESTED LOOPS | | 1 | | 1 | +3409 | 1 | 1 | | | . | | |
| 36 | FIXED TABLE FIXED INDEX | X$KSPPI (ind:1) | 1 | | 1 | +3409 | 1 | 1 | | | . | | |
| 37 | FIXED TABLE FIXED INDEX | X$KSPPCV (ind:1) | 1 | | 1 | +3409 | 1 | 1 | | | . | | |
| 38 | NESTED LOOPS SEMI | | 2 | 2 | | | | | | | . | | |
| 39 | FIXED TABLE FULL | X$KZSRO | 2 | | | | | | | | . | | |
| 40 | INDEX RANGE SCAN | I_OBJAUTH1 | 2 | 1 | | | | | | | . | | |
| 41 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 2 | | | | | | | . | | |
| 42 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 2 | | | | | | | . | | |
| 43 | NESTED LOOPS SEMI | | 1 | 3 | | | | | | | . | | |
| 44 | INDEX SKIP SCAN | I_USER2 | 1 | 1 | | | | | | | . | | |
| 45 | INDEX RANGE SCAN | I_OBJ4 | 1 | 2 | | | | | | | . | | |
===============================================================================================================================================================================
查看SYS.ALL_CAPTURE_PREPARED_TABLES对象具体内容。
select pt.table_owner, pt.table_name, pt.scn, pt.timestamp,
pt.supplemental_log_data_pk, pt.supplemental_log_data_ui,
pt.supplemental_log_data_fk, pt.supplemental_log_data_all
from all_tables at, dba_capture_prepared_tables pt
where pt.table_name = at.table_name
and pt.table_owner = at.owner；
单独执行该语句，执行很快。几秒就能出结果，基本能确定是执行计划的问题。
去别的正常的数据库（12.2.0.1）
执行SELECT /*lilei+*/COUNT(*) FROM SYS.ALL_CAPTURE_PREPARED_TABLES WHERE ROWNUM = 1
获取该sql的outline信息
select * from table(dbms_xplan.display_cursor('62zu0nhuz6062',0,'advanced'));
SQL_ID 62zu0nhuz6062, child number 0
-------------------------------------
SELECT /*lilei+*/COUNT(*) FROM SYS.ALL_CAPTURE_PREPARED_TABLES WHERE
ROWNUM = 1
Plan hash value: 1682689154
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 432 (100)| |
| 1 | SORT AGGREGATE | | 1 | 328 | | |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | FILTER | | | | | |
| 4 | NESTED LOOPS | | 14 | 4592 | 432 (1)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 1 | 328 | 431 (0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 322 | 430 (0)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 1 | 318 | 429 (0)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 1 | 309 | 428 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 295 | 422 (0)| 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 1 | 291 | 421 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 265 | 421 (0)| 00:00:01 |
|* 12 | HASH JOIN | | 48 | 11088 | 420 (0)| 00:00:01 |
| 13 | INDEX FULL SCAN | I_USER2 | 193 | 4632 | 1 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 49 | 6076 | 419 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 65 | 6076 | 419 (0)| 00:00:01 |
|* 16 | HASH JOIN | | 65 | 5590 | 267 (0)| 00:00:01 |
| 17 | INDEX FULL SCAN | I_USER2 | 193 | 772 | 1 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 65 | 2665 | 266 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 65 | 2665 | 266 (0)| 00:00:01 |
| 20 | NESTED LOOPS OUTER | | 65 | 650 | 134 (0)| 00:00:01 |
|* 21 | INDEX FAST FULL SCAN | I_STREAMS_PREPARE1 | 66 | 528 | 2 (0)| 00:00:01 |
| 22 | VIEW PUSHED PREDICATE | | 1 | 2 | 2 (0)| 00:00:01 |
| 23 | SORT GROUP BY | | 1 | 9 | 2 (0)| 00:00:01 |
| 24 | TABLE ACCESS CLUSTER | CDEF$ | 3 | 27 | 2 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | I_COBJ# | 1 | | 1 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 31 | 2 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | I_OBJ5 | 1 | | 2 (0)| 00:00:01 |
|* 29 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 38 | 3 (0)| 00:00:01 |
|* 30 | TABLE ACCESS CLUSTER | TAB$ | 1 | 34 | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| |
|* 32 | INDEX RANGE SCAN | I_IMSVC1 | 1 | 26 | 0 (0)| |
| 33 | TABLE ACCESS CLUSTER | TS$ | 6 | 24 | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| |
| 35 | TABLE ACCESS CLUSTER | SEG$ | 94215 | 1288K| 1 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| |
|* 37 | INDEX RANGE SCAN | I_OBJ1 | 1 | 9 | 1 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN | I_USER2 | 1 | 4 | 1 (0)| 00:00:01 |
|* 39 | INDEX RANGE SCAN | I_OBJ1 | 1 | 6 | 1 (0)| 00:00:01 |
| 40 | VIEW | | 14 | | 0 (0)| |
| 41 | NESTED LOOPS | | 29 | 1972 | 0 (0)| |
|* 42 | FIXED TABLE FIXED INDEX | X$KSPPI (ind:1) | 29 | 1595 | 0 (0)| |
|* 43 | FIXED TABLE FIXED INDEX | X$KSPPCV (ind:1) | 1 | 13 | 0 (0)| |
| 44 | NESTED LOOPS SEMI | | 1 | 23 | 2 (0)| 00:00:01 |
| 45 | FIXED TABLE FULL | X$KZSRO | 2 | 26 | 0 (0)| |
|* 46 | INDEX RANGE SCAN | I_OBJAUTH2 | 1 | 10 | 1 (0)| 00:00:01 |
|* 47 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | 1 (0)| 00:00:01 |
|* 48 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | 1 (0)| 00:00:01 |
|* 49 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | 1 (0)| 00:00:01 |
|* 50 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | 1 (0)| 00:00:01 |
| 51 | NESTED LOOPS SEMI | | 1 | 33 | 2 (0)| 00:00:01 |
|* 52 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 53 | INDEX RANGE SCAN | I_OBJ4 | 1 | 13 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$E4F2F930
13 - SEL$E4F2F930 / U@SEL$4
17 - SEL$E4F2F930 / U@SEL$3
21 - SEL$E4F2F930 / CO@SEL$11
22 - SEL$38B0ADEF / CD@SEL$11
23 - SEL$38B0ADEF
24 - SEL$38B0ADEF / CDEF$@SEL$12
25 - SEL$38B0ADEF / CDEF$@SEL$12
26 - SEL$E4F2F930 / O@SEL$11
27 - SEL$E4F2F930 / O@SEL$11
28 - SEL$E4F2F930 / O@SEL$4
29 - SEL$E4F2F930 / O@SEL$4
30 - SEL$E4F2F930 / T@SEL$3
31 - SEL$E4F2F930 / T@SEL$3
32 - SEL$E4F2F930 / SVC@SEL$3
33 - SEL$E4F2F930 / TS@SEL$3
34 - SEL$E4F2F930 / TS@SEL$3
35 - SEL$E4F2F930 / S@SEL$3
36 - SEL$E4F2F930 / S@SEL$3
37 - SEL$E4F2F930 / CX@SEL$3
38 - SEL$E4F2F930 / CU@SEL$3
39 - SEL$E4F2F930 / CO@SEL$3
40 - SEL$8 / KSPPCV@SEL$3
41 - SEL$8
42 - SEL$8 / KSPPI@SEL$8
43 - SEL$8 / KSPPCV@SEL$8
44 - SEL$F6521A81
45 - SEL$F6521A81 / X$KZSRO@SEL$10
46 - SEL$F6521A81 / OA@SEL$9
47 - SEL$5 / UE@SEL$5
48 - SEL$5 / UE@SEL$5
49 - SEL$6 / UE@SEL$6
50 - SEL$6 / UE@SEL$6
51 - SEL$7
52 - SEL$7 / U2@SEL$7
53 - SEL$7 / O2@SEL$7
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
OPT_PARAM('_optimizer_undo_cost_change' '11.2.0.4')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$F6521A81")
UNNEST(@"SEL$10")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SEL$7")
OUTLINE_LEAF(@"SEL$8")
OUTLINE_LEAF(@"SEL$38B0ADEF")
PUSH_PRED(@"SEL$E4F2F930" "CD"@"SEL$11" 5)
OUTLINE_LEAF(@"SEL$E4F2F930")
ELIMINATE_JOIN(@"SEL$59909D30" "U"@"SEL$11")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$E4F2F930")
ELIMINATE_JOIN(@"SEL$59909D30" "U"@"SEL$11")
OUTLINE(@"SEL$59909D30")
MERGE(@"SEL$5571FB64" >"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$5571FB64")
MERGE(@"SEL$11" >"SEL$2")
MERGE(@"SEL$19CFF126" >"SEL$2")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$19CFF126")
MERGE(@"SEL$4" >"SEL$83E937CD")
OUTLINE(@"SEL$83E937CD")
ELIMINATE_JOIN(@"SEL$3" "DS"@"SEL$3")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$3")
INDEX_FFS(@"SEL$E4F2F930" "CO"@"SEL$11" ("STREAMS$_PREPARE_OBJECT"."OBJ#"
"STREAMS$_PREPARE_OBJECT"."CAP_TYPE"))
NO_ACCESS(@"SEL$E4F2F930" "CD"@"SEL$11")
INDEX(@"SEL$E4F2F930" "O"@"SEL$11" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
INDEX(@"SEL$E4F2F930" "U"@"SEL$3" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
INDEX(@"SEL$E4F2F930" "O"@"SEL$4" ("OBJ$"."SPARE3" "OBJ$"."NAME" "OBJ$"."NAMESPACE" "OBJ$"."TYPE#"
"OBJ$"."OWNER#" "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME" "OBJ$"."SUBNAME" "OBJ$"."OBJ#"))
INDEX(@"SEL$E4F2F930" "U"@"SEL$4" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
INDEX(@"SEL$E4F2F930" "T"@"SEL$3" "I_OBJ#")
INDEX(@"SEL$E4F2F930" "SVC"@"SEL$3" ("IMSVC$"."OBJ#" "IMSVC$"."SUBPART#"))
INDEX(@"SEL$E4F2F930" "TS"@"SEL$3" "I_TS#")
INDEX(@"SEL$E4F2F930" "S"@"SEL$3" "I_FILE#_BLOCK#")
INDEX(@"SEL$E4F2F930" "CX"@"SEL$3" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
INDEX(@"SEL$E4F2F930" "CU"@"SEL$3" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
INDEX(@"SEL$E4F2F930" "CO"@"SEL$3" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
NO_ACCESS(@"SEL$E4F2F930" "KSPPCV"@"SEL$3")
LEADING(@"SEL$E4F2F930" "CO"@"SEL$11" "CD"@"SEL$11" "O"@"SEL$11" "U"@"SEL$3" "O"@"SEL$4" "U"@"SEL$4"
"T"@"SEL$3" "SVC"@"SEL$3" "TS"@"SEL$3" "S"@"SEL$3" "CX"@"SEL$3" "CU"@"SEL$3" "CO"@"SEL$3"
"KSPPCV"@"SEL$3")
USE_NL(@"SEL$E4F2F930" "CD"@"SEL$11")
USE_NL(@"SEL$E4F2F930" "O"@"SEL$11")
NLJ_BATCHING(@"SEL$E4F2F930" "O"@"SEL$11")
USE_HASH(@"SEL$E4F2F930" "U"@"SEL$3")
USE_NL(@"SEL$E4F2F930" "O"@"SEL$4")
NLJ_BATCHING(@"SEL$E4F2F930" "O"@"SEL$4")
USE_HASH(@"SEL$E4F2F930" "U"@"SEL$4")
USE_NL(@"SEL$E4F2F930" "T"@"SEL$3")
USE_NL(@"SEL$E4F2F930" "SVC"@"SEL$3")
USE_NL(@"SEL$E4F2F930" "TS"@"SEL$3")
USE_NL(@"SEL$E4F2F930" "S"@"SEL$3")
USE_NL(@"SEL$E4F2F930" "CX"@"SEL$3")
USE_NL(@"SEL$E4F2F930" "CU"@"SEL$3")
USE_NL(@"SEL$E4F2F930" "CO"@"SEL$3")
USE_NL(@"SEL$E4F2F930" "KSPPCV"@"SEL$3")
SWAP_JOIN_INPUTS(@"SEL$E4F2F930" "U"@"SEL$3")
SWAP_JOIN_INPUTS(@"SEL$E4F2F930" "U"@"SEL$4")
ORDER_SUBQ(@"SEL$E4F2F930" "SEL$5" "SEL$6" "SEL$7" "SEL$F6521A81")
PQ_FILTER(@"SEL$E4F2F930" SERIAL)
FULL(@"SEL$8" "KSPPI"@"SEL$8")
FULL(@"SEL$8" "KSPPCV"@"SEL$8")
LEADING(@"SEL$8" "KSPPI"@"SEL$8" "KSPPCV"@"SEL$8")
USE_NL(@"SEL$8" "KSPPCV"@"SEL$8")
INDEX(@"SEL$38B0ADEF" "CDEF$"@"SEL$12" "I_COBJ#")
INDEX_SS(@"SEL$7" "U2"@"SEL$7" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
INDEX(@"SEL$7" "O2"@"SEL$7" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))
LEADING(@"SEL$7" "U2"@"SEL$7" "O2"@"SEL$7")
USE_NL(@"SEL$7" "O2"@"SEL$7")
PARTIAL_JOIN(@"SEL$7" "O2"@"SEL$7")
INDEX_RS_ASC(@"SEL$6" "UE"@"SEL$6" ("USER_EDITIONING$"."USER#"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$6" "UE"@"SEL$6")
INDEX_RS_ASC(@"SEL$5" "UE"@"SEL$5" ("USER_EDITIONING$"."USER#"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5" "UE"@"SEL$5")
FULL(@"SEL$F6521A81" "X$KZSRO"@"SEL$10")
INDEX(@"SEL$F6521A81" "OA"@"SEL$9" ("OBJAUTH$"."GRANTEE#" "OBJAUTH$"."OBJ#" "OBJAUTH$"."COL#"))
LEADING(@"SEL$F6521A81" "X$KZSRO"@"SEL$10" "OA"@"SEL$9")
USE_NL(@"SEL$F6521A81" "OA"@"SEL$9")
PARTIAL_JOIN(@"SEL$F6521A81" "OA"@"SEL$9")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
3 - filter((("O"."SPARE3"=USERENV('SCHEMAID') OR ORA_CHECK_SYS_PRIVILEGE("O"."SPARE3","O"."TYPE#")=1
OR IS NOT NULL) AND (BITAND("U"."SPARE1",16)=0 OR BITAND("O"."FLAGS",1048576)=1048576 OR
("O"."TYPE#"<>88 AND IS NULL) OR ( IS NOT NULL AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$B
ASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_editi
on_id'))) OR IS NOT NULL)))))
12 - access("O"."OWNER#"="U"."USER#")
16 - access("O"."OWNER#"="USER#")
21 - filter("CO"."CAP_TYPE"=0)
25 - access("OBJ#"="CO"."OBJ#")
26 - access("O"."OBJ#"="CO"."OBJ#")
27 - filter(BITAND("O"."FLAGS",128)=0)
28 - access("O"."SPARE3"="U"."USER#" AND "O"."NAME"="O"."NAME")
29 - filter(BITAND("O"."FLAGS",128)=0)
30 - filter((BITAND("T"."PROPERTY",1)=0 AND BITAND("T"."PROPERTY",36893488147419103232)=0))
31 - access("O"."OBJ#"="T"."OBJ#")
32 - access("T"."OBJ#"="SVC"."OBJ#" AND "SVC"."SUBPART#" IS NULL)
34 - access("T"."TS#"="TS"."TS#")
36 - access("T"."TS#"="S"."TS#" AND "T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#")
37 - access("T"."DATAOBJ#"="CX"."OBJ#")
38 - access("CX"."OWNER#"="CU"."USER#")
39 - access("T"."BOBJ#"="CO"."OBJ#")
42 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
43 - filter("KSPPI"."INDX"="KSPPCV"."INDX")
46 - access("GRANTEE#"="KZSROROL" AND "OA"."OBJ#"=:B1)
47 - filter("TYPE#"=:B1)
48 - access("UE"."USER#"=:B1)
49 - filter("UE"."TYPE#"=:B1)
50 - access("UE"."USER#"=:B1)
52 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
53 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
4 - "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22],
"O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."FLAGS"[NUMBER,22]
5 - "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22],
"O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."FLAGS"[NUMBER,22]
6 - "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22],
"O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."FLAGS"[NUMBER,22], "T"."BOBJ#"[NUMBER,22]
7 - "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22],
"O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."FLAGS"[NUMBER,22], "T"."BOBJ#"[NUMBER,22],
"CX"."OWNER#"[NUMBER,22]
8 - "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22],
"O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."FLAGS"[NUMBER,22], "T"."DATAOBJ#"[NUMBER,22],
"T"."BOBJ#"[NUMBER,22]
9 - "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22],
"O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."FLAGS"[NUMBER,22], "T"."DATAOBJ#"[NUMBER,22],
"T"."TS#"[NUMBER,22], "T"."FILE#"[NUMBER,22], "T"."BLOCK#"[NUMBER,22], "T"."BOBJ#"[NUMBER,22]
10 - "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22],
"O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."FLAGS"[NUMBER,22], "T"."DATAOBJ#"[NUMBER,22],
"T"."TS#"[NUMBER,22], "T"."FILE#"[NUMBER,22], "T"."BLOCK#"[NUMBER,22], "T"."BOBJ#"[NUMBER,22]
11 - "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22],
"O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."FLAGS"[NUMBER,22], "T"."OBJ#"[NUMBER,22],
"T"."DATAOBJ#"[NUMBER,22], "T"."TS#"[NUMBER,22], "T"."FILE#"[NUMBER,22], "T"."BLOCK#"[NUMBER,22],
"T"."BOBJ#"[NUMBER,22]
12 - (#keys=1) "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22],
"O"."OBJ#"[NUMBER,22], "O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."FLAGS"[NUMBER,22]
13 - "U"."USER#"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22]
14 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."FLAGS"[NUMBER,22],
"O"."SPARE3"[NUMBER,22]
15 - "O".ROWID[ROWID,10], "O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."OWNER#"[NUMBER,22],
"O"."OBJ#"[NUMBER,22]
16 - (#keys=1) "USER#"[NUMBER,22], "O"."NAME"[VARCHAR2,128]
17 - "U"."USER#"[NUMBER,22]
18 - "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,128]
19 - "O".ROWID[ROWID,10], "O"."OWNER#"[NUMBER,22]
20 - "CO"."OBJ#"[NUMBER,22]
21 - "CO"."OBJ#"[NUMBER,22]
23 - (#keys=1) "OBJ#"[NUMBER,22], SUM(DECODE("TYPE#",14,1,15,2,16,4,17,8,0))[22]
24 - "CDEF$".ROWID[ROWID,10], "OBJ#"[NUMBER,22], "TYPE#"[NUMBER,22]
25 - "CDEF$".ROWID[ROWID,10]
26 - "O".ROWID[ROWID,10], "O"."OWNER#"[NUMBER,22]
27 - "O"."NAME"[VARCHAR2,128]
28 - "O".ROWID[ROWID,10], "O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."OWNER#"[NUMBER,22],
"O"."OBJ#"[NUMBER,22]
29 - "O"."FLAGS"[NUMBER,22]
30 - "T".ROWID[ROWID,10], "T"."OBJ#"[NUMBER,22], "T"."DATAOBJ#"[NUMBER,22], "T"."TS#"[NUMBER,22],
"T"."FILE#"[NUMBER,22], "T"."BLOCK#"[NUMBER,22], "T"."BOBJ#"[NUMBER,22], "T"."PROPERTY"[NUMBER,22]
31 - "T".ROWID[ROWID,10]
33 - "TS".ROWID[ROWID,10], "TS"."TS#"[NUMBER,22]
34 - "TS".ROWID[ROWID,10]
35 - "S".ROWID[ROWID,10], "S"."TS#"[NUMBER,22], "S"."FILE#"[NUMBER,22], "S"."BLOCK#"[NUMBER,22]
36 - "S".ROWID[ROWID,10]
37 - "CX"."OWNER#"[NUMBER,22]
42 - "KSPPI"."INDX"[NUMBER,22], "KSPPI"."KSPPINM"[VARCHAR2,80]
43 - "KSPPCV"."INDX"[NUMBER,22]
45 - "KZSROROL"[NUMBER,22]
48 - "UE".ROWID[ROWID,10]
50 - "UE".ROWID[ROWID,10]
52 - "U2"."USER#"[NUMBER,22]
将正确的outline绑定到现数据库。
执行下述的过程，sql profile绑定执行计划。
declare
v_hints sys.sqlprof_attr;
cl_sql_text clob;
begin
v_hints := sys.sqlprof_attr(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('12.2.0.1')]',
q'[DB_VERSION('12.2.0.1')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$F6521A81")]',
q'[UNNEST(@"SEL$10")]',
q'[OUTLINE_LEAF(@"SEL$5")]',
q'[OUTLINE_LEAF(@"SEL$6")]',
q'[OUTLINE_LEAF(@"SEL$7")]',
q'[OUTLINE_LEAF(@"SEL$8")]',
q'[OUTLINE_LEAF(@"SEL$38B0ADEF")]',
q'[PUSH_PRED(@"SEL$E4F2F930""CD"@"SEL$11"5)]',
q'[OUTLINE_LEAF(@"SEL$E4F2F930")]',
q'[ELIMINATE_JOIN(@"SEL$59909D30""U"@"SEL$11")]',
q'[OUTLINE(@"SEL$9")]',
q'[OUTLINE(@"SEL$10")]',
q'[OUTLINE(@"SEL$12")]',
q'[OUTLINE(@"SEL$E4F2F930")]',
q'[ELIMINATE_JOIN(@"SEL$59909D30""U"@"SEL$11")]',
q'[OUTLINE(@"SEL$59909D30")]',
q'[MERGE(@"SEL$5571FB64">"SEL$1")]',
q'[OUTLINE(@"SEL$1")]',
q'[OUTLINE(@"SEL$5571FB64")]',
q'[MERGE(@"SEL$11">"SEL$2")]',
q'[MERGE(@"SEL$19CFF126">"SEL$2")]',
q'[OUTLINE(@"SEL$2")]',
q'[OUTLINE(@"SEL$11")]',
q'[OUTLINE(@"SEL$19CFF126")]',
q'[MERGE(@"SEL$4">"SEL$83E937CD")]',
q'[OUTLINE(@"SEL$83E937CD")]',
q'[ELIMINATE_JOIN(@"SEL$3""DS"@"SEL$3")]',
q'[OUTLINE(@"SEL$4")]',
q'[OUTLINE(@"SEL$3")]',
q'[INDEX_FFS(@"SEL$E4F2F930""CO"@"SEL$11"("STREAMS$_PREPARE_OBJECT"."OBJ#"]',
q'["STREAMS$_PREPARE_OBJECT"."CAP_TYPE"))]',
q'[NO_ACCESS(@"SEL$E4F2F930""CD"@"SEL$11")]',
q'[INDEX(@"SEL$E4F2F930""O"@"SEL$11"("OBJ$"."OBJ#""OBJ$"."OWNER#""OBJ$"."TYPE#"))]',
q'[INDEX(@"SEL$E4F2F930""U"@"SEL$3"("USER$"."USER#""USER$"."TYPE#""USER$"."SPARE1""USER$"."SPARE2"))]',
q'[INDEX(@"SEL$E4F2F930""O"@"SEL$4"("OBJ$"."SPARE3""OBJ$"."NAME""OBJ$"."NAMESPACE""OBJ$"."TYPE#"]',
q'["OBJ$"."OWNER#""OBJ$"."REMOTEOWNER""OBJ$"."LINKNAME""OBJ$"."SUBNAME""OBJ$"."OBJ#"))]',
q'[INDEX(@"SEL$E4F2F930""U"@"SEL$4"("USER$"."USER#""USER$"."TYPE#""USER$"."SPARE1""USER$"."SPARE2"))]',
q'[INDEX(@"SEL$E4F2F930""T"@"SEL$3""I_OBJ#")]',
q'[INDEX(@"SEL$E4F2F930""SVC"@"SEL$3"("IMSVC$"."OBJ#""IMSVC$"."SUBPART#"))]',
q'[INDEX(@"SEL$E4F2F930""TS"@"SEL$3""I_TS#")]',
q'[INDEX(@"SEL$E4F2F930""S"@"SEL$3""I_FILE#_BLOCK#")]',
q'[INDEX(@"SEL$E4F2F930""CX"@"SEL$3"("OBJ$"."OBJ#""OBJ$"."OWNER#""OBJ$"."TYPE#"))]',
q'[INDEX(@"SEL$E4F2F930""CU"@"SEL$3"("USER$"."USER#""USER$"."TYPE#""USER$"."SPARE1""USER$"."SPARE2"))]',
q'[INDEX(@"SEL$E4F2F930""CO"@"SEL$3"("OBJ$"."OBJ#""OBJ$"."OWNER#""OBJ$"."TYPE#"))]',
q'[NO_ACCESS(@"SEL$E4F2F930""KSPPCV"@"SEL$3")]',
q'[LEADING(@"SEL$E4F2F930""CO"@"SEL$11""CD"@"SEL$11""O"@"SEL$11""U"@"SEL$3""O"@"SEL$4""U"@"SEL$4"]',
q'["T"@"SEL$3""SVC"@"SEL$3""TS"@"SEL$3""S"@"SEL$3""CX"@"SEL$3""CU"@"SEL$3""CO"@"SEL$3"]',
q'["KSPPCV"@"SEL$3")]',
q'[USE_NL(@"SEL$E4F2F930""CD"@"SEL$11")]',
q'[USE_NL(@"SEL$E4F2F930""O"@"SEL$11")]',
q'[NLJ_BATCHING(@"SEL$E4F2F930""O"@"SEL$11")]',
q'[USE_HASH(@"SEL$E4F2F930""U"@"SEL$3")]',
q'[USE_NL(@"SEL$E4F2F930""O"@"SEL$4")]',
q'[NLJ_BATCHING(@"SEL$E4F2F930""O"@"SEL$4")]',
q'[USE_HASH(@"SEL$E4F2F930""U"@"SEL$4")]',
q'[USE_NL(@"SEL$E4F2F930""T"@"SEL$3")]',
q'[USE_NL(@"SEL$E4F2F930""SVC"@"SEL$3")]',
q'[USE_NL(@"SEL$E4F2F930""TS"@"SEL$3")]',
q'[USE_NL(@"SEL$E4F2F930""S"@"SEL$3")]',
q'[USE_NL(@"SEL$E4F2F930""CX"@"SEL$3")]',
q'[USE_NL(@"SEL$E4F2F930""CU"@"SEL$3")]',
q'[USE_NL(@"SEL$E4F2F930""CO"@"SEL$3")]',
q'[USE_NL(@"SEL$E4F2F930""KSPPCV"@"SEL$3")]',
q'[SWAP_JOIN_INPUTS(@"SEL$E4F2F930""U"@"SEL$3")]',
q'[SWAP_JOIN_INPUTS(@"SEL$E4F2F930""U"@"SEL$4")]',
q'[ORDER_SUBQ(@"SEL$E4F2F930""SEL$5""SEL$6""SEL$7""SEL$F6521A81")]',
q'[PQ_FILTER(@"SEL$E4F2F930"SERIAL)]',
q'[FULL(@"SEL$8""KSPPI"@"SEL$8")]',
q'[FULL(@"SEL$8""KSPPCV"@"SEL$8")]',
q'[LEADING(@"SEL$8""KSPPI"@"SEL$8""KSPPCV"@"SEL$8")]',
q'[USE_NL(@"SEL$8""KSPPCV"@"SEL$8")]',
q'[INDEX(@"SEL$38B0ADEF""CDEF$"@"SEL$12""I_COBJ#")]',
q'[INDEX_SS(@"SEL$7""U2"@"SEL$7"("USER$"."USER#""USER$"."TYPE#""USER$"."SPARE1""USER$"."SPARE2"))]',
q'[INDEX(@"SEL$7""O2"@"SEL$7"("OBJ$"."DATAOBJ#""OBJ$"."TYPE#""OBJ$"."OWNER#"))]',
q'[LEADING(@"SEL$7""U2"@"SEL$7""O2"@"SEL$7")]',
q'[USE_NL(@"SEL$7""O2"@"SEL$7")]',
q'[PARTIAL_JOIN(@"SEL$7""O2"@"SEL$7")]',
q'[INDEX_RS_ASC(@"SEL$6""UE"@"SEL$6"("USER_EDITIONING$"."USER#"))]',
q'[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$6""UE"@"SEL$6")]',
q'[INDEX_RS_ASC(@"SEL$5""UE"@"SEL$5"("USER_EDITIONING$"."USER#"))]',
q'[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5""UE"@"SEL$5")]',
q'[FULL(@"SEL$F6521A81""X$KZSRO"@"SEL$10")]',
q'[INDEX(@"SEL$F6521A81""OA"@"SEL$9"("OBJAUTH$"."GRANTEE#""OBJAUTH$"."OBJ#""OBJAUTH$"."COL#"))]',
q'[LEADING(@"SEL$F6521A81""X$KZSRO"@"SEL$10""OA"@"SEL$9")]',
q'[USE_NL(@"SEL$F6521A81""OA"@"SEL$9")]',
q'[PARTIAL_JOIN(@"SEL$F6521A81""OA"@"SEL$9")]',
q'[END_OUTLINE_DATA]'
);
select sql_fulltext
into cl_sql_text
from v$sql
where sql_id = '73j5f1u2r14n3'
and Rownum = 1;
dbms_sqltune.import_sql_profile(cl_sql_text,
v_hints,
'P_73j5f1u2r14n3',
force_match => true,
replace => false);
end;
/
再次执行导出。
Export: Release 12.2.0.1.0 - Production on Fri Nov 6 11:18:55 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_27": "/******** AS SYSDBA" directory=DMP_DIR dumpfile=test2.dmp tables=cache100.test1010 trace=480300 exclude=FGA_POLICY,STATISTICS metrics=y
W-1 Startup took 2 seconds
W-1 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
W-1 Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
W-1 Completed 1 PROCACT_INSTANCE objects in 27 seconds
W-1 Processing object type TABLE_EXPORT/TABLE/TABLE
W-1 Completed 1 TABLE objects in 0 seconds
W-1 . . exported "CACHE100"."TEST1010" 5.515 KB 3 rows in 0 seconds using direct_path
W-1 Completed 1 TABLE_EXPORT/TABLE/TABLE_DATA objects in 0 seconds
W-1 Master table "SYS"."SYS_EXPORT_TABLE_27" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_27 is:
/dmp/test2.dmp
Job "SYS"."SYS_EXPORT_TABLE_27" successfully completed at Fri Nov 6 11:22:15 2020 elapsed 0 00:03:18
花费3分钟。
