Re: expdp - cell single block physical read/ read request

From: Tim Gorman <tim.evdbt_at_gmail.com>
Date: Wed, 15 Dec 2021 20:23:32 -0800
Message-ID: <e589d5a3-9829-70c9-8a14-e1d1135e3bc4_at_gmail.com>



Jack,

When you say "/spending a lot of time/", do you mean a lot of executions, or each execution averaging a lot of time?

Are you seeing this looking at AWR/ASH or extended 10046 event trace files?

Depending on the age of the Exadata or the workload, "cell single block physical read" usually averages no more that 0.5 milli-seconds, frequently less.  I'd be surprised if the average wait was over 1 ms.

If you're tracking the expdp sessions or monitoring using V$ASH, can you capture what P1 (i.e. file#) and P2 (i.e. block#)?  P3 should be "1" for single-block reads, of course.

If the expdp is hitting a lot of small tables, or tables with lots of small partitions or subpartitions of only a few blocks, then that's one reason single block reads might be prevalent.  Is table compression involved?  That's why it would be good to capture P1 and P2 from V$ASH or 10046 and translate it to an object name using DBA_EXTENTS.

Hope this helps...

-Tim

On 12/15/2021 6:58 PM, Jack van Zanen wrote:
> Hi
>
>
> Oracle 12.2.0.1 Exadata
>
> I am running a datapump and two of the workers are spending a lot of
> time on
> - cell single block physical read
> - cell single block read request
>
> This does not make sense to me as it is full expdp I would expected a
> full table scan type of wait, not single block
>
> I checked mos but my search did not find anything related to my version
>
> Does anyone know of any reason why this might happen?
>
>
>
> Jack van Zanen
>
>
> -------------------------
> This e-mail and any attachments may contain confidential material for
> the sole use of the intended recipient. If you are not the intended
> recipient, please be aware that any disclosure, copying, distribution
> or use of this e-mail or any attachment is prohibited. If you have
> received this e-mail in error, please contact the sender and delete
> all copies.
> Thank you for your cooperation

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 16 2021 - 05:23:32 CET

Original text of this message