Re: Bigger block sizes

From: Kevin Jernigan <kevin.jernigan_at_oracle.com>
Date: Thu, 1 Oct 2015 14:24:49 -0700
Message-ID: <560DA4A1.6080308_at_oracle.com>

For future projects, I strongly recommend using SecureFiles instead of BasicFiles for LOB columns: SecureFiles was designed from the start to address the performance and scalability issues inherent in the architecture of BasicFiles, it's completely compatible with the APIs used to access and manipulate BasicFiles LOBs, and there are no licensing implications. And, SecureFiles is the default starting in 12c...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 10/1/15 2:18 PM, Jonathan Lewis wrote:


>

>

> Basicfiles, and only the out of line bits.

>

>

> Regards

> Jonathan Lewis

> http://jonathanlewis.wordpress.com

> _at_jloracle

> ------------------------------------------------------------------------

> *From:* Kevin Jernigan [kevin.jernigan_at_oracle.com]

> *Sent:* 01 October 2015 22:00

> *To:* Jonathan Lewis; oracle-l_at_freelists.org

> *Subject:* Re: Bigger block sizes

>

> Jonathan,

>

> Do you find this to be true for SecureFiles as well as BasicFiles? And 

> did you test both inline and out of line LOBs?

>

> Thanks,

>

> -Kevin J

> -- 

> 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 10/1/15 1:04 PM, Jonathan Lewis wrote:

>>

>> Chris,

>>

>> The behaviour would have changed since then. Oracle added object 

>> queues in the buffer cache so that it could do fast object 

>> checkpoints (for parallel query) and fast object drop/truncate, 

>> identifying all the clean and dirty blocks for a given object as 

>> quickly as possible.

>>

>> Picking a different block size because it isolates a particular 

>> problem to a specifically (limited) cache is also a good supporting 

>> reason for picking a special block size for LOBs - I find that "cache 

>> read" for lobs is a good choice, but I don't want a large volume of 

>> LOB data to waste the rest of the buffer cache.

>>

>>

>>

>> Regards

>> Jonathan Lewis

>> http://jonathanlewis.wordpress.com

>> _at_jloracle

>> ------------------------------------------------------------------------

>> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] 

>> on behalf of Ruel, Chris [Chris.Ruel_at_lfg.com]

>> *Sent:* 01 October 2015 20:14

>> *To:* JSweetser_at_icat.com; oralrnr_at_gmail.com; Andrew Kerber

>> *Cc:* oracle-l_at_freelists.org

>> *Subject:* RE: Bigger block sizes

>>

>> Ok, I’ll join in with my story about multiple block sizes.

>>

>> Not sure if the behavior has changed but this was in a 9iR2 database 

>> on Solaris for a COTS WMS software package.

>>

>> Multiple block sizes saved us. The WMS was utilized primarily by 

>> automated package tracking software (tied into all major carriers) 

>> and robots picking items from a warehouse that shipped out around 

>> 5000 sku’s/hour avg (some were whole pallets of items).

>>

>> The design of this COTS software was such that whenever a scanner 

>> logged in to do activity, it created a trigger, a sequence, and a 

>> table.  When the activity was done, it dropped these objects. The WMS 

>> software was not made to scale at the rate this business (mobile 

>> device logistics) grew.  Regardless, the business did not want to 

>> spend the millions that would be required to upgrade all worldwide 

>> warehouses at the time (they have since).

>>

>> The database also had to support nightly reporting for updates of 

>> orders fulfilled daily as well.  Our buffer cache was in the 

>> neighborhood of 32GB.  The problem was, with all these PLC’s creating 

>> and dropping objects, we were getting crippling waits due to the DBWR 

>> constantly having to scan the large buffer cache and flush blocks. It 

>> hamstrung all transactional activity and just snowballed from there.

>>

>> Since this was a no longer supported COTS package, our only real 

>> choice was to find a fix “inside the database”.   We did this by 

>> using multiple block sizes…not so much for the size, but, to have the 

>> processes create all their transient tables in a much smaller buffer 

>> cache…I can’t remember but in the neighborhood of just a couple 

>> hundred MB. So, it wasn’t a matter of bigger or smaller blocks (I 

>> think we actually went with 4k for our “new” size) but to give the 

>> DBWR a much smaller cache to scan when objects where removed from the 

>> DB.  Not only did we clear up the hanging issues, but everything as 

>> whole speed up significantly.

>>

>> Chris..

>>

>> _____________________________________________________________________

>>

>> Chris Ruel * Oracle Database Administrator * Lincoln Financial Group

>>

>> cruel_at_lfg.com <mailto:cruel_at_lfg.com>* Desk:317.759.2172 * Cell 

>> 317.523.8482

>>

>> *From:*oracle-l-bounce_at_freelists.org 

>> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Sweetser, Joe

>> *Sent:* Thursday, October 01, 2015 11:21 AM

>> *To:* oralrnr_at_gmail.com; Andrew Kerber

>> *Cc:* oracle-l_at_freelists.org

>> *Subject:* RE: Bigger block sizes

>>

>> We have a COTS app that mixes blocksizes (4k, 8k, 16k) in different 

>> tablespaces.  I have not seen any adverse impacts from this.

>>

>> -joe

>>

>> *From:*oracle-l-bounce_at_freelists.org 

>> <mailto:oracle-l-bounce_at_freelists.org>[mailto:oracle-l-bounce_at_freelists.org] 

>> *On Behalf Of *Orlando L

>> *Sent:* Thursday, October 1, 2015 9:13 AM

>> *To:* Andrew Kerber 

>> <<mailto:andrew.kerber_at_gmail.com>andrew.kerber_at_gmail.com>

>> *Cc:* oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>

>> *Subject:* Re: Bigger block sizes

>>

>> "oracle really only tests on the 8k block size": interesting! They 

>> claim the product supports other blocksizes too!

>>

>> There must be places where 8K blocks may not be big enough to store a 

>> row, even at 1% PCTFREE.

>>

>> On Wed, Sep 30, 2015 at 4:53 PM, Andrew Kerber 

>> <andrew.kerber_at_gmail.com <mailto:andrew.kerber_at_gmail.com>> wrote:

>>

>>     I haven't seen any advantages from using any larger block sizes.

>>     I also saw a Tom Kyte article a while back that said they are

>>     only intended for use with transportable table spaces, and oracle

>>     really only tests on the 8k block size.

>>

>>     Sent from my iPad

>>

>>

>>     > On Sep 30, 2015, at 4:29 PM, Orlando L <oralrnr_at_gmail.com

>>     <mailto:oralrnr_at_gmail.com>> wrote:

>>     >

>>     > List,

>>     >

>>     > Does anyone in the list use non default blocksize of greater

>>     than 8K for your oracle DBs; if so, is it for warehousing/OLAP

>>     type applications? What advantages do you get with them; any

>>     disadvantage.

>>     >

>>     > Orlando.

>>

>> Confidentiality Note: This message contains information that may be 

>> confidential and/or privileged. If you are not the intended 

>> recipient, you should not use, copy, disclose, distribute or take any 

>> action based on this message. If you have received this message in 

>> error, please advise the sender immediately by reply email and delete 

>> this message. Although ICAT, Underwriters at Lloyd's, Syndicate 4242, 

>> scans e-mail and attachments for viruses, it does not guarantee that 

>> either are virus-free and accepts no liability for any damage 

>> sustained as a result of viruses. Thank you.

>>

>> Notice of Confidentiality: **This E-mail and any of its attachments 

>> may contain

>> Lincoln National Corporation proprietary information, which is 

>> privileged, confidential,

>> or subject to copyright belonging to the Lincoln National Corporation 

>> family of

>> companies. This E-mail is intended solely for the use of the 

>> individual or entity to

>> which it is addressed. If you are not the intended recipient of this 

>> E-mail, you are

>> hereby notified that any dissemination, distribution, copying, or 

>> action taken in

>> relation to the contents of and attachments to this E-mail is 

>> strictly prohibited

>> and may be unlawful. If you have received this E-mail in error, 

>> please notify the

>> sender immediately and permanently delete the original and any copy 

>> of this E-mail

>> and any printout. Thank You.**

>>

>



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

Received on Thu Oct 01 2015 - 23:24:49 CEST