THIS BLOG INCLUDES THE LATEST REVIEWS BY BASSOCANTOR

Oracle Performance: How Fast Can I Extract Data?

Oracle Performance: How Fast Can I Extract Data?

 Data Pump in Action

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:

blocks per hour.png

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.

The Financial Diet: A Total Beginner's Guide to Getting Good with Money by  Chelsea Fagan

The Financial Diet: A Total Beginner's Guide to Getting Good with Money by Chelsea Fagan

Oracle Performance: Find Sql that are "Invisible"

Oracle Performance: Find Sql that are "Invisible"