re: How can you have "nearly no I/O" but suffer from write complete waits? The
two symptoms seem contradictory. What is the O/S saying about it?
IF your check for redo (undo applied) is true and massive AND there is a lot of
block relocation of rows where the original first place are direct read and
then it spins row at a time in the PGA reconstructing the correct vintage row
image over and over and over again. Then a ton of all in cache work could be
being done constructing the final image of the block you’re trying to write.
That wait time probably shouldn’t be accounted for as write complete wait,
leaving us with your “The two symptoms seem contradictory, ” unless there is
an accounting problem. Or something else that hasn’t come to mind yet.
A bulk process that can be multi-threaded (meaning parallel at the independent
jobs layer) is to carve up the space holding the object and read one row from
each block (or otherwise drag your otherwise probably PGA reads through the SGA
for permanent cleaning of the block whilst only having to process one row per
block (and possibly only one column of one row per block).
Has anyone tried my suggestion to see if everything except the BLOB can be done
quickly followed by ripping through that to copy the blob into a separate
tablespace as out of line storage? If you’re going to have to row-by-row clean
the blocks via SGA otherwise to get the context index to complete, I think that
would be a faster way to get an untangled “don’t need no stinking undo applied
to get read consistency” structure and then build the context index.
Likewise I endorse JL’s partition if you can – there is probably an n log n
operation in there somewhere and your locally partitioned idea knocks “n” down
to about “n/(number of partitions)” or just the largest partition if it his
very uneven.
Head scratcher.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
Sent: Saturday, March 06, 2021 3:53 AM
To: Lothar Flatz
Cc: Oracle L
Subject: Re: Reorganize Big Table with BLOB column
Do you mean the application doesn't support partitioning by year - or is your
version of Oracle unable to partition a context index?
"Not able to complete" - you said "write complete waits" - there's a lot of
data to be written, to several tables, indexes and lobs for a context index:
I'd have to check the manuals to see what you can do about getting some of the
work done with direct path writes, but I'd guess that your write complete waits
relate to the volume of data being generated in the cache by a serial process.
(It might be a good idea, though, to check the session stats for work done to
achieve read-consistency e.g. undo records applied)
How can you have "nearly no I/O" but suffer from write complete waits? The two
symptoms seem contradictory. What is the O/S saying about it?
If you partition the table AND the index can be locally partitioned in your
version, then the scale of the problem changes because you could (I assume)
create the index unusable then rebuild each partition in turn.
Can you extract the parameters that are currently being used so that you can
see if there's anything that's been changed from default?
Could you copy a couple of million rows from the table and see how Oracle
behaves creating an index on the subset.
Manual parallelism: I was thinking of something like:
create empty table with local indexes (if it's possible), with all the physical
segments required.
write a simple script that does "create table as select data for one year,
create indexes, ecxhange partition" - then run as many copies as the machine
will allow (vary according to what actually works)
Regards
Jonathan Lewis
On Thu, 4 Mar 2021 at 17:30, Lothar Flatz <l.flatz@xxxxxxxxxx> wrote:
Hi Jonathan
thanks for answering. Some does require further explanation:
I can get partitioning by year in place. It is however not supported by the
software and would be just a mean of dividing the data into more manageable
pieces.
So far I observed, that when trying to text index the complete table progress
is dropping after a while. Actually it never finishes.
So far my conclusion was that we lacked CPU as there is nearly no I/O. Is it
possible that we were rather lacking memory (e.g. swapping) and I was missing
the point?
Can I improve my chances to create the text index by partitioning the data
resulting is lesser memory demand?
By manual parallelism do you refer to dbms_parallel execute ?
Regards
Lothar
