RE: Bigger block sizes

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 1 Oct 2015 21:33:18 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282A7E04_at_EXMBX01.thus.corp>


Kevin,

Thanks.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle



From: Kevin Jernigan [kevin.jernigan_at_oracle.com] Sent: 01 October 2015 22:33
To: Jonathan Lewis; oracle-l_at_freelists.org Subject: Re: Bigger block sizes

SecureFiles was always part of the database license, from the time it was released with 11gR1 in 2007. It has also always been true that SecureFiles Compression & Deduplication requires licensing the Advanced Compression option, and SecureFiles Encryption requires licensing the Advanced Security option...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:28 PM, Jonathan Lewis wrote: Unfortunately people don't often call me in until their projects have gone from future to past ;) Remind me, I'm feeling lazy, when did SecureFiles change from licenced to unlicenced - or was it just that there were some feature of Securefiles that were (and still are) licensed. Regards Jonathan Lewis http://jonathanlewis.wordpress.com _at_jloracle ________________________________ From: Kevin Jernigan [kevin.jernigan_at_oracle.com<mailto:kevin.jernigan_at_oracle.com>] Sent: 01 October 2015 22:24 To: Jonathan Lewis; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: Re: Bigger block sizes 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<mailto:kevin.jernigan_at_oracle.com>] Sent: 01 October 2015 22:00 To: Jonathan Lewis; <mailto:oracle-l_at_freelists.org> oracle-l_at_freelists.org<mailto: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<mailto:oracle-l-bounce_at_freelists.org> [<mailto:oracle-l-bounce_at_freelists.org>oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Ruel, Chris [<mailto:Chris.Ruel_at_lfg.com>Chris.Ruel_at_lfg.com<mailto:Chris.Ruel_at_lfg.com>] Sent: 01 October 2015 20:14 To: JSweetser_at_icat.com<mailto:JSweetser_at_icat.com>; <mailto:oralrnr_at_gmail.com> oralrnr_at_gmail.com<mailto:oralrnr_at_gmail.com>; Andrew Kerber Cc: oracle-l_at_freelists.org<mailto: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 <mailto:cruel_at_lfg.com>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> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sweetser, Joe Sent: Thursday, October 01, 2015 11:21 AM To: <mailto:oralrnr_at_gmail.com> oralrnr_at_gmail.com<mailto:oralrnr_at_gmail.com>; Andrew Kerber Cc: <mailto:oracle-l_at_freelists.org> oracle-l_at_freelists.org<mailto: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><mailto:andrew.kerber_at_gmail.com>andrew.kerber_at_gmail.com<mailto:andrew.kerber_at_gmail.com>> Cc: <mailto:oracle-l_at_freelists.org> 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 <<mailto:andrew.kerber_at_gmail.com>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 <<mailto:oralrnr_at_gmail.com>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:33:18 CEST

Original text of this message