RE: Rolling invalidate window exceeded
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 13 May 2011 15:25:10 -0400
I found this note for 10g, (below). The _optimizer_invalidation_period
parameter is set to default 180000, (5 hours).
This database was restarted on 28-APR-11. The app stops each night for a
couple hours, and nobody really uses it again until daybreak give or take, so
it makes sense that ROLL_INVALID_MISMATCH would increment a lot (if I'm
reading this right).
If this can be assumed, and everything is 'normal' for this situation, it
brings up the question of why are we using so much 'trace file' space to tell
me.... A lot.?
http://prutser.wordpress.com/2009/07/16/rolling-cursor-invalidation/
I'm thinking there is no impact to the database, and that there isn't anything
to do about it. Anyone concur?
(I'll be back Monday).
Joel Patterson
Database Administrator
904 727-2546
On Thu, May 12, 2011 at 3:49 PM,
<Joel.Patterson@xxxxxxxxxxx<mailto:Joel.Patterson@xxxxxxxxxxx>> wrote:
Curious as to whether I can do anything about this trace file notice, or
examine what impact it has. (is it worth it).
Third party application ('lawson', hence what can I do).
Oracle 11.2.0.1, with optimize_features_enable set to 10.2.0.1 (recommended by
vendor for previous issue).
Solaris 5.10 running in virtual container.
*m001_*.trc shows a lot of Cursor Sharing Diagnostics Nodes.... Already
processed, invalidation window, bind mismatch...
Querying v$sql_shared_cursor shows 2365 rows where ROLL_INVALID_MISMATCH = 'Y'
of a total of about 14274, or about 16 or 17% of the cursors are marked 'Y',
(hence the impact portion).
Another note is that Lawson likes CHAR data types.
19822 CHAR colums verses 1231 VARCHAR2.
5380 CHAR are length 1, and 1066 VARCHAR2's are length 1.
The rest are scattered..
I googled some and so far found one from Jonathan Lewis, but that was 2007, and
oracle 9, still going, but thought I'd post this to see if anyone would give it
a lot of attention given the application.
Joel Patterson
Database Administrator
904 727-2546<tel:904%20727-2546>
Other related posts:
