Re: High cell single Block physical Read

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 15 Dec 2017 12:00:17 +0000
Message-ID: <MM1P12301MB165825CB05EBD62FA574519EA50B0_at_MM1P12301MB1658.GBRP123.PROD.OUTLOOK.COM>


Mark,

I think I'd agree with the chained / migrated row hypothesis if the single block read were for the same object number, but they are for obj# = 0. This suggests is a variant of a "commit-cleanout" / "get commit time" problem. In a non-exadata system we could confirm (or disprove) the hypothesis by checking the file# of the single block reads but since exadata isn't using db file sequential reads we can't.

The OP could, however, check the session statistics as the update runs looking for "table fetch continued rows" or "xxxxx - undo records applied". (I'm betting on xxxx being "transaction table read consistent") as a clue to which hypothesis is more likely to be correct.

Regards
Jonathan Lewis



From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Mark W. Farnham <mwf_at_rsiz.com> Sent: 15 December 2017 11:05:50
To: dmarc-noreply_at_freelists.org; 'Oracle-L Freelists' Subject: RE: High cell single Block physical Read

From what you’ve written so far leading possibilities in my mind are:

  1. Multiple row pieces per block
  2. Artifacts of updating advanced compressed rows with very little free space in the block (or in general)

Perhaps you can rule in or out #1 quickly by whether your table:

  1. Has too many columns for a single row piece, especially if columns in different row pieces are being updated
  2. Has a column type that makes the rows have multiple row pieces
  3. Has a lot of migrated rows

Are you getting direct reads or cached reads? Is your table partitioned (and if so, are the updates to many partitions or just 1 or a few partitions) ?

mwf

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sanjay Mishra (Redacted sender "smishra_97" for DMARC) Sent: Wednesday, December 13, 2017 7:51 PM To: dmarc-noreply_at_freelists.org; Oracle-L Freelists Subject: Re: High cell single Block physical Read

Sorry small correction on the trace. If object is 540334 then it is "cell multiblock Physical read" but obj#=0 is "cell single block physical read" There is One "cell multiblock Physical read" after several rows on "cell multiblock Physical read"

name='cell single block physical read' ela=1511 celhas=12345555 diskhash=12345555 bytes=8192 obj#0 time=11111111 name='cell multiblock physical read' ela=1511 celhas=12345555 diskhash=12345555 bytes=8192 obj#540334 time=11111111

These are the only one taking 95% of time for the query.

Sanjay

On Wednesday, December 13, 2017 7:44 PM, Sanjay Mishra <smishra_97_at_yahoo.com<mailto:smishra_97_at_yahoo.com>> wrote:

I checked and found that in 10046 trace, there are around 1mil rows with obj#=0 and around 100K with obj#=540334 which is the object I am updating.

Sanjay

On Wednesday, December 13, 2017 7:33 PM, Sanjay Mishra <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>> wrote:

Hi

I am running an update on Advance row compress table by selecting data from another table on Unique Seq No. Runing Select from other table is very fast and even running Select instead of update columns is very fast BUT WHEN update comes, it is taking almost an hour to just update 5 mil Records. Does Compress for OLTP be factor for so many single block phsuicall Read as I am monitoring the trace and what it is doing is name='cell single block physical read' ela=1511 celhas=12345555 diskhash=12345555 bytes=8192 obj#0 time=11111111 name='cell single block physical read' ela=1511 celhas=12345555 diskhash=12345555 bytes=8192 obj#540334 time=11111111

So can see doing this against obj#0 and obj#540334

Any help is appreciated.

TIA
Sanjay

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Dec 15 2017 - 13:00:17 CET

Original text of this message