Re: Oracle Securefile Lobs
Date: Mon, 24 Feb 2020 23:32:27 +0000
Message-ID: <LNXP265MB1562EEBC001E872260319167A5EC0_at_LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM>
It's always tough when you want to restructure a database because it's got to big to be handled easily - but you can't restructure it because it's too big to handle fast enough. Sometimes it's even too hard to measure something properly (let alone restructure it) because the thing you want to measure is too larger.
To check for wasted space there's a quick and dirty script I used a few years ago to estimate the space used by LOB values in a basicfile LOB segment to see if there was any benefit in using a different block size of the LOB's tablespace.
The code uses dbms_lob.getlength(), but you can use just length() in modern versions of Oracle. There is a note at the end of the article warning about the way Oracle is counting lengths and the effects of multibyte charactersets. So you might want to test on the same platform with a small table and segment to compare what you put in with what the query reports back.
Regards
Jonathan Lewis
From: Sanjay Mishra <smishra_97_at_yahoo.com> Sent: 24 February 2020 23:02
To: Oracle-L Freelists; Jonathan Lewis
Subject: Re: Oracle Securefile Lobs
Jonathan
I saw some of your blogs to check the LOBS details. Is there a script that you can point and can be used to check this versioned information of Lob entries that can tell the candidate for a rebuild ? Isn't the Expired copy space cannot be reused. I used one of the script from your site(https://jonathanlewis.wordpress.com/category/oracle/infrastructure/fragmentation/) and has the following output for one of the LOB Partition
SQL> _at_j_lob2 PRD_SCHEMA SYS_LOB0000018235C00088$$ 'LOB PARTITION' SYS_LOB_P2192
Secure files
Segment Blocks: 2,721,816 Bytes: 22,297,116,672
Used Blocks: 2,530,477 Bytes: 20,729,667,584
Expired Blocks: 191,339 Bytes: 1,567,449,088
Unexpired Blocks: 0 Bytes: 0
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.61
Generic details
Segment Total blocks: 2,721,816
Object Unused blocks: 0
PL/SQL procedure successfully completed.
Tx
Sanjay
On Monday, February 24, 2020, 05:38:58 PM EST, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
If you update a specific LOB value several times over a short interval of time then you could easily end up with several aged copies of that LOB value since LOB segments don't use the standard undo mechanism, instead Oracle creates a new copy of the value and keeps track of the SCN at which the old copy was marked for deletion. This means that you can find some applications where a LOB segment is several times larger than it ought to be (and some people would use the term "fragmented" to describe this state,) You might want to rebuild the LOB segment from time to time, but if you can use partitioning by time then it's probably nicest to do the rebuild only once after the data in it has got to a state of no further change. (Generally "pack it well when it's about to be made read-only" is a nice strategy to aim for in all cases).
Wasted space won't necessary result in bad performance in LOBs, but there have been several bugs with space management where Oracle spends too much time checking whether or not some space is ready for re-use. Others may be able to give you specific up to date cases for your version of Oracle.
On the other hand, I have seen systems where a LOB value gets updated 30 or 40 times in the space of a few minutes, and each update generates a whole new LOB value, so a transaction that might generate 30 redo entries of 200 bytes and end up writing one or two Oracle blocks actually writes 30 or 40 blocks - one each time it modifies that LOB value. And that's a design that's inherently a bad performer.
Regards
Jonathan Lewis
From: 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 Sanjay Mishra <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>>
Sent: 24 February 2020 22:18
To: Oracle-L Freelists
Subject: Oracle Securefile Lobs
I had one application that stores lots of transactional data in LOB and the same is updated heavily. Does frequent update on LOB
1. Impact system performance heavily 2. Can cause lots of Fragmentation 3. Need any frequent maintenance as part of optimization
The database is 18c and LOBS are secure files and Partitioned.
TIA
Sanjay
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 25 2020 - 00:32:27 CET