Re: Reorganize Big Table with BLOB column
Date: Wed, 10 Mar 2021 13:50:08 +0100
Message-ID: <d1900975-4ffe-b0fd-9801-681e7dbce284_at_bluewin.ch>
Hi Jonathan,
Questions:
_/
/_
I am currently still in the process of finding the best way of moving
the data. I believe I have now found the best storage characteristics
for the BLOB. (DISABLE STORAGE IN ROW CHUNK 32K RETENTION NONE
NOCACHE NOLOGGING ). Average length of the BLOB is 130886 byte.
My main concern is the text index though. From previous experience I can
say that it was almost impossible to context index a table of this
size. That index create was simply never finishing.
_//_/Do you mean the application doesn't support partitioning by year /
Yes. The partition key won't be used for partition pruning. The table is
rather simple: id, BLOB , timestamp created and last modified.
(Timestamps are stored as Varchar2, which practise I dislike.)
I can construct something like partition key extracting year from
timestamp created. The application would be totally unaware of the
partition key.
The table is accessed mainly by the id.
The Version is 19.0. It does allow partition a context index.
In this case a global index would make more sense. However we might
still have an issue due its size.
"Not able to complete" - you said "write complete waits"
Actually there waits happened when my colleague used alter table move.
It turned out the reason for these waits were that the BLOB was set to
cache. I changed the property to nocache and the waits are gone for
good. We are having "direct path read"/"direct path write" waits right now.
Thanks
Lothar
Am 06.03.2021 um 09:53 schrieb Jonathan Lewis:
Effectively that would mean looping over partitions each and every time
the table is accessed via the context index.
I remember that even looping over empty partitions can slow queries. Can
you guesstimate the effect? We are talking 11 yearly partitions right now.
Hash Partitioning on the id would be an option . However that partitions
would be constantly growing in size.
>
> 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
> <mailto: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-lReceived on Wed Mar 10 2021 - 13:50:08 CET