How To Control # Of ASM Copy Channels When Create PDB From Another PDB (Pluggable database)

(Doc ID 1988108.1)

Last updated on MAY 05, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

Environment
-------------
4 DB nodes
Oracle Database 12c 12.1.0.2.0

Customer is creating a PDB from another PDB when parallel_degree_policy
is AUTO is using 40 ASM copy channels at the same time, for larger
datafiles it is impacting system performance.
When parallel_degree_policy is manual the copy is serial and very slow.

 

DIAGNOSTIC ANALYSIS: 
-------------------- 
parallel_degree_policy    MANUAL 
parallel_max_servers    96 
parallel_min_servers    20 
parallel_servers_target    72 

C##GHANEMN@ocda 11-02-2015 13:12:18> @sqlm 
enter sql_id:> f67jvz7vvrr6d 
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>TRIM('F67JVZ7VVRR6D'),REPORT_LEVEL=>'A 
LL') 
------------------------------------------------------------------------------ 
-------------------------------------------------------- 
SQL Monitoring Report 
SQL Text 
------------------------------ 
/* SQL Analyze(2743,0) */ SELECT /*+PARALLEL(40) */ * FROM X$KXFTASK 
/*kpdbfParallelCopyOrMove,PDB_FILE_COPY*/<<<<<<<<<<<< 

Global Information 
------------------------------ 
 Status              :  DONE (ALL ROWS) 
... 
... 

Global Stats 
============================================================================== 
========================= 
| Elapsed | Queuing |   Cpu   |    IO    |  Other   | Fetch | Read | 
Read  | Write | Write |  Cell   | 
| Time(s) | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Reqs | 
Bytes | Reqs  | Bytes | Offload | 
============================================================================== 
========================= 
|     506 |    0.00 |      10 |      218 |      278 |     1 | 31269 |   
30GB | 31269 |  30GB | -49.25% | 
============================================================================== 
==== 
Parallel Execution Details (DOP=40 , Servers Allocated=40) 


Top 10 Foreground Events by Total Wait Time 

Event    Waits    Total Wait Time (sec)    Wait Avg(ms)    % DB time   
 Wait Class 
Pluggable Database file copy    2,261,519    36.4K    16.11  96.7   
 User I/O <<<<<<<<<< 
DB CPU         14.9K         39.5 
library cache lock    2,580    294.9    114.30    .8    Concurrency 

How can we control the number of channels?

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms