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

Also, if you're using flashback_time (which I think it defaults to systimestamp when it starts) then it's also possible there was a long running transaction that changed a lot of blocks that the export needs access to so it could be reading UNDO blocks (which I believe are single block reads)

Chris

On Wed, Dec 15, 2021 at 11:23 PM Tim Gorman <tim.evdbt_at_gmail.com> wrote:

> 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