Re: db and storage snapshot - may be a little OFF-TOPIC
Date: Mon, 1 Jul 2013 10:09:06 +0200
Message-ID: <51D13922.6070906_at_nordea.com>
I would certainly agree a the large fraction of the snapshot - even 70-80% as there are such environments. But here we have 100% and it does not mean 99,0% but rather whole 100% - of course the os tools report with some error but for example one of the environments in question is a database of the size ~100G, so 1G is 1% and by this I mean I will see such difference, so I assume this is really 100%. In this db the indexes are ~15% of the whole.
That is why it is hard to belive for me this problem is about indexing.
With this 100% I suspect rather something in the snapshot mechanism then
in the db itself, but of course this is something about guessing and
suspecting but not about hard evidence and facts, so not really
something the tiggers like much.
Yet I may try to do such test by rebuild of every index in such db and
see. Thanks for suggestion.
On 28.06.2013 11:08, Jonathan Lewis wrote:
> The immediate response to this is to look at indexes.
>
> Given the right (which means "unlucky") index, you could find that inserting 1,000 rows into a table updates 1,000 blocks in the index. This won't quite match your description - but if you have a few very large indexes on a couple of critical table it could easily result in the snapshot size becoming a large fraction of the database size.
>
> (There is an option in the latest versions of dbv that allows you to specify a "high_scn" when you check the database, and the output shows you the number of blocks changed since that SCN for each file - this might be a long report, but might give you some interesting information. See: http://iiotzov.wordpress.com/2013/05/14/measuring-the-benefits-of-database-virtualizationthin-provisioning-solutions/
>
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Remigiusz Sokolowski [remigiusz.sokolowski_at_nordea.com]
> Sent: 28 June 2013 09:24
> To: oracle-l
> Subject: db and storage snapshot - may be a little OFF-TOPIC
>
> hi,
>
> we support test env creation partially by making snapshots on the
> storage array level. One of the nice sides of this are storage savings.
> The algorithm behind snapshot is simple - modification of the snapshot
> copies modified block content to snapshot storage and modifies it there,
> while source stays intact.
>
> We found few cases where the storage designated for snapshot
> modification become equal to the snapshot source, which means every
> resource block modified - all of them Oracle databases.
> Are You able to name a database operation, which would modify every
> block in database (or assuming this is about array blocks (in this case
> 256k) one per every 32 blocks)?
>
> Of course there are no operations like database restore and no visible
> pattern despite the fact that only Oracle databases are prone to it (but
> I would not say this is 100% sure). The array is IBM Storwize. The db is
> 11.2. The OS is Linux flavour.
>
> I appreciate the slightest hypothesis, thanks in advance
>
> Best regards
> Remigiusz
>
>
> --
> Pole na kazi
>
> ----------------------------------------------------------------------
> Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
> pos : Senior DBA at DIiUSI
> addr : Nordea Bank Polska SA, Luzycka 6A st, 81-537 Gdynia, Poland
> phone : +48 58 667 17 43
> mobile: +48 602 42 42 77
>
> Nordea Bank Polska S.A. z siedziba w Gdyni, ul. Kielecka 2, 81-303 Gdynia,
> wpisana do Rejestru Przedsiebiorców Krajowego Rejestru Sadowego pod numerem: 0000021828,
> dla której dokumentacje przechowuje Sad Rejonowy Gdansk - Pólnoc w Gdansku,
> VIII Wydzial Gospodarczy Krajowego Rejestru Sadowego,
> o kapitale zakladowym i wplaconym w wysokosci: 277.493.500,00 zlotych,
> NIP: 586-000-78-20, REGON: 190024711--
> http://www.freelists.org/webpage/oracle-l.
>
-- Pole na kazi ---------------------------------------------------------------------- Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com> pos : Senior DBA at DIiUSI addr : Nordea Bank Polska SA, Luzycka 6A st, 81-537 Gdynia, Poland phone : +48 58 667 17 43 mobile: +48 602 42 42 77 Nordea Bank Polska S.A. z siedziba w Gdyni, ul. Kielecka 2, 81-303 Gdynia, wpisana do Rejestru Przedsiebiorców Krajowego Rejestru Sadowego pod numerem: 0000021828, dla której dokumentacje przechowuje Sad Rejonowy Gdansk - Pólnoc w Gdansku, VIII Wydzial Gospodarczy Krajowego Rejestru Sadowego, o kapitale zakladowym i wplaconym w wysokosci: 277.493.500,00 zlotych, NIP: 586-000-78-20, REGON: 190024711-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jul 01 2013 - 10:09:06 CEST