RE: x$ksmlru
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: "'Jay Hostetter'" <hostetter.jay@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 13 Jul 2010 13:21:27 -0400
Certainly that size is not reserved for the program generating the problem.
At this point I would focus on the business activities and look for the
application, rather than working from v$sql backward on non-30th days.
Of course you can be poised and ready for the next 30th day, and then mining
v$sql backwards to the problem application should yield results. But I
suspect you would like to find the problem before the next negative event.
Data in the instance between now and then seems unlikely to yield your
answer.
Quizzing the owners of the business processes to find out what is special
about that 30th day is more likely to be fruitfull before the next evidence
supplied by the database.
Regards,
mwf
_____
From: Jay Hostetter [mailto:hostetter.jay@xxxxxxxxx]
Sent: Tuesday, July 13, 2010 11:42 AM
To: oracle-l@xxxxxxxxxxxxx
Cc: tanel@xxxxxxxxxx; mwf@xxxxxxxx
Subject: Re: x$ksmlru
Mark and Tanel,
Thank you for the responses. I suspect a regularly scheduled "bad"
process as well.
I notice that these statements with the 26284 unsharable size occur
several times throughout the week - not just on the day of the 4031 errors.
The KSMLRHON and KSMLROHV columns contain no information that would lead me
back to the culprit. I've been monitoring V$SQL for statements that have
high runtime_mem. Is there a better way to capture the problem statement
(other than heapdumps - I'm afraid this database is too volatile)?
I've been reading Tanel's blog posts on memory troublshooting. I've able
to query x$ksmsp without any repurcussions, but this hasn't told me what is
causing the memory issues.
Thank you,
Jay
On Sun, Jul 11, 2010 at 8:38 AM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
Combined with Tanel's observation of the recurrent unshareable size 26284,
this should be relatively easy to find. A classic mistake is to read an
entire current set of information (such as a personel, payroll, or vendor
object) into a memory structure, edit that structure in a client side
program, and then feed the entire revised structure back into the database
as literals rather than updating only the values that have changed. That
type of edit and cloning a complete record for a new literal based insert
are key drivers of a constant new literal size value demand. Of course your
case could be entirely different, but that a kind of thing that would drive
your problem. An simply using binds for the update or insert should solve
that sort of problem.
Good luck,
mwf
_____
Other related posts:
