RE: move table with securefiles extremely slow
Date: Thu, 10 Jul 2014 17:56:51 -0400
Message-ID: <027901cf9c89$dd32f2b0$9798d810$_at_rsiz.com>
Right. So the question is really not the output side, but rather "Is there a faster way to read the existing lobs?"
I don't detect any slowness in the securefiles write part of this move.
I see you set your chunk size to 8192. Do you have a gross histogram of lob size? % > 32K, % between 16K and 32K, % between 8K and 16K, % < 8K?
Not that this matters now, since you are trying to move to securefiles where you'll get an automatically optimized chunk size per lob.
It just looks to me as if you're going to have to grind through reading the "basicfiles" lobs. I am unaware of a way to speed that up other than if you can move the tablespace file #9 to some really fast media, so each drink takes less time. That is moving the database file instead of reading the logical contents of the database file. Depending on your available storage and the file's size, you might be able to isolate it in a memory file or on SSD long enough to read the contents out. Since you're moving out, I'm presuming this file #9 is not concurrently being updated. (If it is, that just adds competition to all these little reads to fetch your big lobs.) Make sure you take a backup of your file before you consider temporarily making it a memory file (aka ramdisk). For SDD normal protocols should be fine.
Maybe Kevin has a better idea. He and his team are definitely the right folks to ask.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Anton
Sent: Thursday, July 10, 2014 11:52 AM
To: Kevin Jernigan; oracle-l
Subject: Re: move table with securefiles extremely slow
Thx for reply, both ways are slow, on prod system i use alter table move, while investigating i do CTAS, it was slow to and trace files looks similar ( lots of 1,2 block read of lob )
09.07.2014 23:34, Kevin Jernigan пишет:
> How are you moving the table - ALTER TABLE MOVE? Online redefinition?
> Create Table As Select (CTAS)? -KJ
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 10 2014 - 23:56:51 CEST