Re: Looking for ideas on blocked sessions updating partitioned table with CLOB

Oh - interesting. I hadn't even considered that.

Chris

On Fri, Aug 31, 2018 at 10:04 AM Stefan Knecht <knecht.stefan_at_gmail.com> wrote:

> You can also look at the p1/p2/p3 values of the sessions that are blocked
> by the TX contention - and see which object the contention is on. If it's
> the lobindex, that may lead to further clues about some of the
> possibilities raised by Jonathan
>
> Stefan
>
>
>
> On Fri, Aug 31, 2018 at 8:55 PM, Jonathan Lewis <
> jonathan_at_jlcomp.demon.co.uk> wrote:
>
>>
>> There is a major problem with basicfile LOBs (with similar, but not so
>> drastic symptoms appearing for securefile LOBs), so as a first strategic
>> step you should probably be planning to find a painless way to move to
>> securefile LOBs.
>>
>> I've written an entire system about a problem with basicfile LOBs when
>> you do inserts and deletes - but the problem is the same if you do lots of
>> updates because for a LOB an update is a delete followed by an insert (with
>> the old LOB left in the LOB segment rather than being copied to the UNDO
>> segment). The way LOBs handle deletes is that the LOB index is a two-part
>> index of which the first part indexes in time order the chunks of LOBs that
>> have been deleted and the second part indexes by lobid the current LOB
>> chunks.
>>
>> A problem appears when you try to insert a LOB and there isn't any free
>> space in the segment, but there is plenty of freeable space. Your session
>> will free up all the freeable space (deleting LOB index entries) as it
>> goes. This can take a very long time and while it's happening any other
>> session that want to insert a LOB will wait for your session to finish
>> freeing up the space.
>>
>> Unfortunately I've only see HW enqueues appearing as a result of this
>> action, I don't think I've seen TX enqueues (and I wouldn't really expect
>> to see the "enq TX - row lock contention" enqueue). THe series start at
>> this URL: http://jonathanlewis.wordpress.com/2017/01/26/basicfile-lobs/
>>
>> Different possibility - is the LOB declared with multiple freepools ?
>> This is the default and it means the LOBindex has only the two parts I
>> describe above. If you have N freepools then the index consistents of N
>> pairs of parts. This doesn't avoid the problem I've described above (your
>> session will try to free ALL the free space from ALL the pools if it needs
>> space), but having a single freepool may explain your observations. If I
>> insert a very large LOB PERHAPS you can't insert one until I finish insert
>> LOBIndex entries into my bit of index, in which case maybe you'd show a row
>> wait of some sort - though, again I'd expect a different wait to appear
>> (maybe buffer busy).
>>
>> Another thought - your LOBs enable in-row storage: how many of them are
>> short enough to fit in the row (a few, lots, most) and do they get updated
>> many times in situ, and are many of them likely to start short and then
>> grow a few times before getting too long ? How long are the rest of the
>> columns in the row ? I'm thinking about the possibility that your smaller
>> LOBs spend some time growing in-row and causing row migration before they
>> get long enough to move into the LOB segment - and row migraion does funny
>> things with ITLs. Maybe something odd happens as a row that's migrated
>> moves its LOB to the LOB segment and the row migrates back to it's original
>> block.
>>
>> ---
>>
>> In the absence of answers - a plan for the future might be to range
>> partition by sess_id and hash subpartiiton by sess_id: I'm assuming that
>> sess_id is a value that's going to increase with time when I say that. If
>> that is the case then the benefit of doing this composite partitioning is
>> that on a regular basis you get a new partition and all the action moves
>> into it and any garbage that's happened in the earlier partition can be
>> cleared up (or dropped).
>>
>> If you stick with basicfiles then look at freepools just in case it's
>> relevant - securefile lobs automatically have a better strategy for
>> concurrency but there is a parameter you can set of increase concurrency.
>> (Can't remember which it is at present).
>>
>> Since I've mentioned the series - one feature of the mechanism is that
>> the LOB segment can become much larger than it needs to be, so if you can
>> sum() the sizes of the lobs that exceed the in-linesize and it's much
>> smaller than segment size you'll know that your update mechanism is
>> introducing some sort of problem.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>>
>> ________________________________________
>> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
>> behalf of Chris Taylor <christopherdtaylor1994_at_gmail.com>
>> Sent: 31 August 2018 14:23:46
>> To: knecht.stefan_at_gmail.com
>> Cc: ORACLE-L
>> Subject: Re: Looking for ideas on blocked sessions updating partitioned
>> table with CLOB
>>
>> Ah, very good questions.
>>
>> For the blocking issue:
>> 1. Blocking Type = enq: TX - row lock contention, yet the SESS_IDs (PK)
>> is different.
>> 2. Basicfile LOBs (I assume these were carried over from an upgrade from
>> a prev version)
>>
>> For the PHP object transfer being slow issue:
>> 1. TNS settings:
>> db server:
>> sqlnet.ora
>> ---------------------------------
>> send_buf_size=2097152
>> recv_buf_size=2097152
>> default_sdu_size=32768
>> default_tdu_size=32768
>>
>> tnsnames.ora
>> -----------------------------
>> Nothing on the tnsnames.ora
>>
>> web/app server:
>> tnsnames.ora
>> -------------------------------
>> Nothing specified here either for SDU etc
>>
>> 2. OS = Red hat Linux 6.8 64-bit , kernel 2.6.32-642.el6.x86_64
>>
>> 3. Block Size = 8192
>>
>> Thanks,
>> Chris
>>
>>
>> On Thu, Aug 30, 2018 at 10:02 PM Stefan Knecht <knecht.stefan_at_gmail.com
>> <mailto:knecht.stefan_at_gmail.com>> wrote:
>> When you say "blocking" - what event are the blocked sessions waiting on?
>>
>> Also, what's your TNS config - particularly SDU sizes between the
>> mid-tier and the database (TNS connection string and the receiving
>> listener)?
>>
>> Are you using securefile or basicfile LOBs?
>>
>> What OS is the database on and what block size are you using in the
>> tablespace where the lobs are stored?
>>
>> On Fri, Aug 31, 2018 at 6:21 AM, Chris Taylor <
>> christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com>>
>> wrote:
>> Env: 12.1.0.2
>>
>> We have a table that stores session data (base64 encoded) from web
>> sessions.
>> The table has 32 HASH partitions and contains a CLOB.
>>
>> The table is hash partitioned by SESSION_ID which is a 64-char string.
>>
>> We have this code that executed in PHP into the Oracle database:
>> UPDATE sessions_table
>> SET session_data = :session_data // (CLOB)
>> WHERE session_id = :session_id;
>>
>> For 95% of the sessions this runs very,very fast.
>>
>> For 5% of the sessions that LOB being passed from PHP is 15MB and for
>> some reason PHP is really slow about transmitting those session OBJECTS
>> over to the database. (Verified through strace etc).
>>
>> While those sessions are waiting to complete the update to the CLOB, they
>> end up BLOCKING other sessions that aren't trying to update the same
>> session id but are in the same partition.
>>
>> I believe they're blocked on the CLOB on the same partition that the long
>> running session has open as I've confirmed that the SESS_ID being updated
>> is different in most cases.
>>
>> So, here's my question, what performance strategy should I be
>> investigating for CLOBS. Should I just add more partitions and spread out
>> the likelihood that a session will end up in the same partition? Seems
>> logical but I'm not sure how CLOBS play into this.
>>
>> The CLOBS are stored like this:
>> Segment Name = SYS_LOB0051640773C00002$$
>> Index Name = SYS_LOB0051640773C00002$$
>> Chunk = 8192
>> PCTVERSION = 10
>> CACHE = YES
>> LOGGING = None
>> Encrypt = None
>> Compression = None
>> IN_ROW = Yes
>> Partition = Yes
>> Retention = Yes
>>
>> Looking for any ideas on how to prevent a session from blocking other
>> non-related sessions that are updating the CLOB.
>>
>> I'm trying to mitigate the blocking at the db layer while we stand up a
>> product such as Redis to handle the session caching at the server level
>> (and remove it from the db).
>>
>> Chris
>>
>>
>>
>>
>>
>>
>>
>> --
>> //
>> zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
>> Visit us at zztat.net<http://zztat.net/> | _at_zztat_oracle | fb.me/zztat<
>> http://fb.me/zztat> | zztat.net/blog/<http://zztat.net/blog/>
>>
>
>
>
> --
> //
> zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
> Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/
>

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