Re: infoworld call

From: De DBA <dedba_at_tpg.com.au>
Date: Fri, 20 Jan 2012 13:57:06 +1000
Message-ID: <4F18E612.4030706_at_tpg.com.au>

Try again, somehow the list server seemed to have stripped the html formatting first time round.. I'm sorry if you can't read this properly.. ...
I felt bored and did some calculations.. found a presentation from Mark Ault where he suggested a machine that can do 2.4 million IOPS per second. Suppose that could do 2.4 million SCN updates (Moore's law suggests that may happen any moment now...) For the gedanken experiment, imagine a vast environmental monitoring network with thousands of probes, one near each suburb, village and hamlet on a densely populated continent perhaps, each of which every second update/insert one record in a central datawarehouse for, say, wind speed, dry- and wet bulb temperature, relative humidity and surface pressure. For added fun, that is 5 commits per probe per second (different tables for each metric...).

Just by inserting data as fast as possible, it seems Mark's solution would give the hypothetical meteo about 3 months of joy before the sanity check kicks in, and only 4 years before the database is completely full. But allegedly with an Exadata X2 that soft limit could already be hit in just 12 years :)

I would like to think that the hard limit in future versions will be put at a bit higher than a 48-bit integer...

    tx/sec      |years to:
                 |soft limit   |hard limit
    ============|=============|===========
    1   |         ~   | 8,919,892
    1,000   |         ~   |     8,920
         3,328.9|         ~   |     2,679 *Exadata 1*
    10,000   |         ~   |       892
    16,385   |   393,216   |       544
        50,000 |        12   |       178 *Exadata X2*
    1,000,000   |         0.4 |         9
    2,400,000   |         0.2 |         4 *RamSan*  - IOPS (not necessarily SCN increases)

sources:

• exadata1 performance: http://www.oracle.com/us/industries/utilities/performance-benchmark-exadata-wp-161572.pdf
• exadata2 performance: http://www.oracle.com/us/industries/communications/brm-exadata-performance-wp-362789.pdf
• RamSan estimate : http://www.slideshare.net/texasmemorysystems/oracle-exadata-x2-8rev1

The calculation that I used for the soft limit is (hope I reduced that right.. long time since high school..;) ):

       limit_at_1/1/2012 yearly_incr 12,408,273,764,352 517,011,406,848

yrs = --------------  + -------------- * yrs =  ------------------- + --------------- * yrs
       sec_in_year *t sec_in_year *t             31,555,872 * t      31,555,872 * t

which I reduced to

yrs = 393,216 / ( t - 16384 )

Where t = transactions (SCN increases) per second and yrs = years to hit the limit.

The numbers are derived as:

• soft limit _at_1/1/2012: 12,408,273,764,352 = ( 2012 - 1988 ) * 1 year_in_sec * 2^14

                yearly increase:      517,011,406,848 = 1 year_in_sec * 2^14

    * hard limit                : 281,474,976,710,656 = 2^48

and 1 year = 365.23 * 24 * 3600 = 31,555,872 sec

(back to reality now.),
Tony

On 18/01/12 04:00, Rich Jesse wrote:
> Ray writes:
>
>> infoworld say this bug is really ugly.
> Well that certainly does sound potentially ugly. And it describes the
> reason behind the 16K/s transaction limitation that was discussed here a few
> years back.
>
> At least I have my SCN growth patterns for about a year now and we won't
> even hit 80B in the next 10 years. At current rates. Provided nothing else
> changes. Ever...
>
> Marking that patch down for my upgrade to 11g. Thanks Ray!
>
> Rich
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

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

Received on Thu Jan 19 2012 - 21:57:06 CST