RE: Reorganize Big Table with BLOB column

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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_at_bluewin.ch> 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

--
http://www.freelists.org/webpage/oracle-l