Re: move table with securefiles extremely slow

From: Kevin Jernigan <kevin.jernigan_at_oracle.com>
Date: Thu, 10 Jul 2014 22:30:31 -0700
Message-ID: <53BF7677.1000103_at_oracle.com>

Mark: I don't see where Anton says that the source tables uses BasicFiles. Can you point that out?

Anton: Is the source table using BasicFiles or SecureFiles?

Thanks,

-KJ

-- 
Kevin Jernigan
Senior Director Product Management
Advanced Compression, Hybrid Columnar
Compression (HCC), Database File System
(DBFS), SecureFiles, Database Smart Flash
Cache, Total Recall, Database Resource
Manager (DBRM), Direct NFS Client (dNFS),
Continuous Query Notification (CQN),
Index Organized Tables (IOT), Information
Lifecycle Management (ILM)
+1-650-607-0392 (o)
+1-415-710-8828 (m)

On 7/10/14, 2:56 PM, Mark W. Farnham wrote:


> 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-l

>

>


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

Received on Fri Jul 11 2014 - 07:30:31 CEST