First, most important point - I haven't suggested you change anything, I merely
given you one reason why you might see write complete waits when you read CLOBs.
Secondly, I didn't say that the queries would result in their CLOBs being
written, I said that the act of reading a CLOB might require OTHER INFORMATION
to be flushed from the cache. If your database is a read-only database then
this observation won't apply to you, but if other statements are used to change
data which you're not reading then your select statement might force the
database to write dirty blocks to disc to make space for your data.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Michael Calisi [oracle455@xxxxxxxxx]
Sent: 02 October 2015 15:37
To: Jonathan Lewis
Subject: Re: write complete waits.
We are not using securefile, The sql is just a straight select statements that
aren't modifying data in the database. I don't understand how they could be
related to this event.
The procedure which gets invoked basically does the following:
* executes the SQL to get the data (including CLOB columns) into a local
PL/SQL record type
* PL/SQL runs to manipulate the CLOB data
CLOB data gets output
Looking into whether we should defined as CACHE, but being my production system
I can't make too many changes until certain.
On Thu, Oct 1, 2015 at 5:51 PM, Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> wrote:
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<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf
of Michael Calisi [oracle455@xxxxxxxxx<mailto:oracle455@xxxxxxxxx>]
Sent: 01 October 2015 22:44
To: oracle-l@xxxxxxxxxxxxx<mailto: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
