Oracle Performance: How Fast Can I Extract Data?
Data Pump in Action
Our company needed to do a Data Pump Export of about 20 TB, using a RAC Cluster. We only had a few hours to do this, so I needed to maximize the rate of disk reads.
Finding Total Throughput
To estimate the maximum capacity of our cluster, I checked the system metrics going back several months. I wanted to see the most disk reads in one hour for any node. It turns out that the table, Dba_Hist_Seg_Stat contains a nice metric we can use.
Here is the script I used (I've found that a little Parallelism helps a lot here):
Select /*+Leading(D) Parallel(D 8) */
S.Snap_Id,
To_Char(Begin_Interval_Time, 'Dd-Mon-Yy-Hh24:Mi') Time_Start, S.Instance_Number,
Round(Sum(Physical_Reads_Delta)/1000000) Meg_Block_Reads
From Dba_Hist_Seg_Stat D, Dba_Hist_Snapshot S
Where S.Snap_Id = D.Snap_Id
And S.Instance_Number = D.Instance_Number
And S.Snap_Id between [TBD]
Group By S.Snap_Id, To_Char(Begin_Interval_Time, 'Dd-Mon-Yy-Hh24:Mi'), S.Instance_Number
Having Sum(Physical_Reads_Delta) > 999999
Order By 1,2;
The script above yields the number of disk blocks read by Snapshot_id (in our case, hour.) This is easily converted to a chart, shown here:
It's Easy to Estimate
By using the above script to look in the past, I can see the maximum number of blocks our system has ever read. You can also group by instance, if needed.
