Hi Jonathan,
_/
/_
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.
Questions:
1. I wonder if it would be possible to build a global context index by
creating it per partition using partial global indexing. You would
switch on indexing as per partition one at a time and rebuild the
index. (It could rebuild the whole context index though..)
2. Do you think that the time building a context index can increase
more that linear when the number of rows increases? You were
mentioning memory. Does this suggest that memory is needed to build
the context index in relation to it's size? That when memory is
exhausted the text index creation stalls?
_//_/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.
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.
The table is accessed mainly by the id.
Hash Partitioning on the id would be an option . However that partitions
would be constantly growing in size.
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:
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 <mailto: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
