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

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