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
