Basicfiles, and only the out of line bits.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Kevin Jernigan [kevin.jernigan@xxxxxxxxxx]
Sent: 01 October 2015 22:00
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf
of Ruel, Chris [Chris.Ruel@xxxxxxx<mailto:Chris.Ruel@xxxxxxx>]
Sent: 01 October 2015 20:14
To: JSweetser@xxxxxxxx<mailto:JSweetser@xxxxxxxx>;
oralrnr@xxxxxxxxx<mailto:oralrnr@xxxxxxxxx>; Andrew Kerber
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxx<mailto:cruel@xxxxxxx> * Desk:317.759.2172 * Cell 317.523.8482
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sweetser, Joe
Sent: Thursday, October 01, 2015 11:21 AM
To: oralrnr@xxxxxxxxx<mailto:oralrnr@xxxxxxxxx>; Andrew Kerber
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Orlando L
Sent: Thursday, October 1, 2015 9:13 AM
To: Andrew Kerber
<<mailto:andrew.kerber@xxxxxxxxx>andrew.kerber@xxxxxxxxx<mailto:andrew.kerber@xxxxxxxxx>>
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx<mailto:andrew.kerber@xxxxxxxxx>> 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@xxxxxxxxx<mailto:oralrnr@xxxxxxxxx>> 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.
