Looking for ideas on blocked sessions updating partitioned table with CLOB

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Thu, 30 Aug 2018 18:21:05 -0500
Message-ID: <CAP79kiTkziZ7k1CbEkkh0EUdk4g8gmJiEq8Tbi73zd9mvtb7pQ_at_mail.gmail.com>



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

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Aug 31 2018 - 01:21:05 CEST

Original text of this message