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> 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> 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 | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/
>

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