RE: Bigger block sizes

From: Chitale, Hemant K <Hemant-K.Chitale_at_sc.com>
Date: Fri, 2 Oct 2015 07:05:05 +0000
Message-ID: <2FE2AA1C5F8DEC478F58DF8DD32BA6370BA2F9_at_HKWPIPXMB03C.zone1.scb.net>



I did something similar in 9i. TRUNCATE performance was very poor and we had a lot of (non-GTT) transient tables that needed to be TRUNCATEd frequently. We moved them to a smaller 16KB buffer cache for a 16KB tablespace and got better runtimes for our batches.

See Oracle Support Note 334822.1 Quoting from the Note : This is a recommendation directly from the PS/Oracle 9i tuning guide.

    Create the temporary tables on a tablespace that has different Oracle block     size than the rest of the tablespaces. By doing this, the temporary tables will     be placed on a different buffer pool, which will improve truncate time. Having     a separate buffer pool for temporary tables will also reduce RO enqueue     contention when multiple AE jobs are running in parallel and truncating temp     tables.

Hemant K Chitale

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ruel, Chris Sent: Friday, October 02, 2015 3:15 AM
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> [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<mailto:oralrnr_at_gmail.com>; Andrew Kerber Cc: 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 <andrew.kerber_at_gmail.com<mailto: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.**

This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 02 2015 - 09:05:05 CEST

Original text of this message