I suppose, given the very recent exchange with Kevin Jernigan I should ask if
this is securefile or basicfile LOBs before commenting, but answering for
basicfiles I'm not surprised.
Depending how you're handling the LOBs and whether they're defined as CACHE, or
CACHE READ, and (picking up the other long thread for today) whether they're
using the standard buffer pool (as opposed to a different block size or the
keep or recycle caches), then you have to remember that LOBs are allowed to be
very large objects - so if you read them into the buffer cache you may have to
kick out a lot of other information; and if you kick out a lot of information
you may have to write the content of some buffers to disk before you can re-use
the buffer for your LOB - at which point the session trying to read the LOB
ends up in a write complete wait.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Michael Calisi [oracle455@xxxxxxxxx]
Sent: 01 October 2015 22:44
To: oracle-l@xxxxxxxxxxxxx
Subject: write complete waits.
We been experiencing log file sync as being in the top wait in our AWR top 10.
This has been resolved, and now a new wait has surfaced. write complete waits.
I have notice that when log write complete waits occur a particular sql is
always running. One thing about the sql it goes up against columns of blob,
clob.
Anyone experience sql performance when blob/clob columns are used?
SELECT 0, AOL_STYLE.AOL_STYLE_ID STYLE_ID, NVL(AOL_STYLE_L10N.HEADER,
AOL_STYLE.HEADER) HEADER, NVL(AOL_STYLE_L10N.FOOTER, AOL_STYLE.FOOTER) FOOTER,
AOL_STYLE.STYLESHEET FROM EG_PROPOSAL_TYPE, AOL_STYLE, CGADMIN.AOL_STYLE_L10N
WHERE EG_PROPOSAL_TYPE.GRANTMAKER_ID = :B3 AND
EG_PROPOSAL_TYPE.EG_PROPOSAL_TYPE_ID = :B2 AND EG_PROPOSAL_TYPE.DONOR_STYLE_ID
= AOL_STYLE.AOL_STYLE_ID AND EG_PROPOSAL_TYPE.GRANTMAKER_ID =
AOL_STYLE.GRANTMAKER_ID AND AOL_STYLE.AOL_STYLE_ID =
AOL_STYLE_L10N.AOL_STYLE_ID(+) AND AOL_STYLE.GRANTMAKER_ID =
AOL_STYLE_L10N.GRANTMAKER_ID(+) AND AOL_STYLE_L10N.LANGUAGE_CODE(+) = :B1 UNION
ALL SELECT 1, AOL_STYLE.AOL_STYLE_ID STYLE_ID, NVL(AOL_STYLE_L10N.HEADER,
AOL_STYLE.HEADER) HEADER, NVL(AOL_STYLE_L10N.FOOTER, AOL_STYLE.FOOTER) FOOTER,
AOL_STYLE.STYLESHEET FROM EG_CONFIGURATION, AOL_STYLE, CGADMIN.AOL_STYLE_L10N
WHERE EG_CONFIGURATION.GRANTMAKER_ID = :B3 AND EG_CONFIGURATION.DONOR_STYLE_ID
= AOL_STYLE.AOL_STYLE_ID AND EG_CONFIGURATION.GRANTMAKER_ID = AOL
_STYLE.GRANTMAKER_ID AND AOL_STYLE.AOL_STYLE_ID =
AOL_STYLE_L10N.AOL_STYLE_ID(+) AND AOL_STYLE.GRANTMAKER_ID =
AOL_STYLE_L10N.GRANTMAKER_ID(+) AND AOL_STYLE_L10N.LANGUAGE_CODE(+) = :B1 ORDER
BY 1
