Links
• Home
• Oracle DBA Forum
Frequent Oracle Errors
•TNS:could not resolve the connect identifier specified
•Backtrace message unwound by exceptions
•invalid identifier
•PL/SQL compilation error
•internal error
•missing expression
•table or view does not exist
•end-of-file on communication channel
•TNS:listener unknown in connect descriptor
•insufficient privileges
•PL/SQL: numeric or value error string
•TNS:protocol adapter error
•ORACLE not available
•target host or object does not exist
•invalid number
•unable to allocate string bytes of shared memory
•resource busy and acquire with NOWAIT specified
•error occurred at recursive SQL level string
•ORACLE initialization or shutdown in progress
•archiver error. Connect internal only, until freed
•snapshot too old
•unable to extend temp segment by string in tablespace
•Credential retrieval failed
•missing or invalid option
•invalid username/password; logon denied
•unable to create INITIAL extent for segment
•out of process memory when trying to allocate string bytes
•shared memory realm does not exist
•cannot insert NULL
•TNS:unable to connect to destination
•remote database not found'>ora-02019
•exception encountered: core dump
•inconsistent datatypes
•no data found
•TNS:operation timed out
•PL/SQL: could not find program
•existing state of packages has been discarded
•maximum number of processes exceeded
•error signaled in parallel query server
•ORACLE instance terminated. Disconnection forced
•TNS:packet writer failure
•see ORA-12699
•missing right parenthesis
•name is already used by an existing object
•cannot identify/lock data file
•invalid file operation
•quoted string not properly terminated
X$ksmsp (OSEE 10.2.0.2 on Solaris 8)

X$ksmsp (OSEE 10.2.0.2 on Solaris 8)

2006-06-27       - By Lawie, Duncan

Reply:     1     2     3     4     5     6     7     8     9     10     >>  

Charles,

I agree that a row in x$ksmsp should equal a single memory chunk - but is it
possible that there is a chunk with an outrageous value?  I don't recall that
particular case, but I have seen  x$ksmsp sum to values significantly larger
than the size of the SGA.

In addition, I have also had significant performance issues on a production
system when selecting on this table in a very busy system which is severely
fragmented.

Cheers,
Duncan.

Duncan Lawie
DBE - Oracle.

"There will always be plenty of things to compute in the detailed affairs of
millions of people doing complicated things."  -- Vannevar Bush, As We May
Think; Atlantic Monthly - July 1945.


 __ __  

From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On
Behalf Of Schultz, Charles
Sent: 26 June 2006 15:59
To: Hallas, John, Tech Dev; oracle-l@(protected)
Subject: RE: X$ksmsp (OSEE 10.2.0.2 on Solaris 8)


Sorry, let me clarify. We have thousands of entries in ksmsp as well, but I was
under the impression that 1 row = 1 contiguous chunk. Sorry for the confusion.

 __ __  

From: Hallas, John, Tech Dev [mailto:John.Hallas@(protected)]
Sent: Monday, June 26, 2006 9:57 AM
To: Schultz, Charles; oracle-l@(protected)
Subject: RE: X$ksmsp (OSEE 10.2.0.2 on Solaris 8)



Whilst I was looking at x$ksmsp I came across the following link which provides
a useful summary of the x$tables and some interesting queries against some of
them.



http://www.stormloader.com/yonghuang/computer/x$table.html <http://www
.stormloader.com/yonghuang/computer/x$table.html>



Charles, in your database is there only 1 entry in total for that table as I
see many thousands of entries (same Oracle version but 2.9 Solaris)



 __ __  

From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On
Behalf Of Schultz, Charles
Sent: 26 June 2006 15:27
To: oracle-l@(protected)
Subject: X$ksmsp (OSEE 10.2.0.2 on Solaris 8)



Granted that x$ksmsp is not documented and therefore subject to changes from
version to version (or patch to patch), I have an Oracle Support Engineer
telling me that one entry in ksmsp might actually show fragmented memory in the
case of a memory leak. My understanding, from what I have gleaned from others
much smarter than I, was that each row of ksmsp showed one contiguous chunk of
memory, be it small or large, FREE or PERM (or something in between). The whole
idea of a memory leak causing ksmsp to report a fragmented chunk as one piece
is a little disturbing.

Can anyone corroborate or refute this?

Oracle Server Enterprise Edition 10.2.0.2
Solaris 8 (SunOS 5.8) 64-bit

charles schultz
oracle dba
aits - adsd
university of illinois




==============================================================================
Please access the attached hyperlink for an important electronic communications
disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:v =
"urn:schemas-microsoft-com:vml" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word" xmlns:st1 =
"urn:schemas-microsoft-com:office:smarttags"><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>X$ksmsp (OSEE 10.2.0.2 on Solaris 8)</TITLE><!--[if !mso]>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<STYLE>v\:* {
  BEHAVIOR: url(#default#VML)
}
o\:* {
  BEHAVIOR: url(#default#VML)
}
w\:* {
  BEHAVIOR: url(#default#VML)
}
.shape {
  BEHAVIOR: url(#default#VML)
}
</STYLE>
<![endif]--><o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PlaceName"></o:SmartTagType><o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PlaceType"></o:SmartTagType><o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="place"
downloadurl="http://www.5iantlavalamp.com/"></o:SmartTagType><!--[if !mso]>
<STYLE>st1\:* {
  BEHAVIOR: url(#default#ieooui)
}
</STYLE>
<![endif]-->
<STYLE>@(protected) {
  font-family: Tahoma;
}
@(protected) {
  font-family: Comic Sans MS;
}
@(protected) Section1 {size: 595.3pt 841.9pt; margin: 72.0pt 90.0pt 72.0pt 90.0pt; }
P.MsoNormal {
  FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
  FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
  FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
  COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
  COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
  COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
  COLOR: purple; TEXT-DECORATION: underline
}
P {
  FONT-SIZE: 12pt; MARGIN-LEFT: 0cm; MARGIN-RIGHT: 0cm; FONT-FAMILY: "Times New
Roman"; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto
}
SPAN.EmailStyle18 {
  FONT-WEIGHT: normal; COLOR: blue; FONT-STYLE: normal; FONT-FAMILY: "Comic Sans
MS"; TEXT-DECORATION: none; mso-style-type: personal-reply
}
DIV.Section1 {
  page: Section1
}
</STYLE>

<META content="MSHTML 6.00.2900.2873" name=GENERATOR></HEAD>
<BODY lang=EN-GB vLink=purple link=blue>
<DIV dir=ltr align=left><SPAN class=798040412-27062006><FONT face=Georgia
color=#0000ff size=2>Charles,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=798040412-27062006><FONT face=Georgia
color=#0000ff size=2></FONT></SPAN>&nbsp;</DIV>
<DIV dir=ltr align=left><SPAN class=798040412-27062006><FONT face=Georgia
color=#0000ff size=2>I agree that a row in x$ksmsp should equal a single memory
chunk - but is it possible that there is a chunk with an outrageous value?&nbsp
;
I don't recall that particular case, but I have seen&nbsp;
x$ks</FONT></SPAN><SPAN class=798040412-27062006><FONT face=Georgia
color=#0000ff size=2>msp sum to values significantly larger than the size of
the
SGA.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=798040412-27062006><FONT face=Georgia
color=#0000ff size=2></FONT></SPAN>&nbsp;</DIV>
<DIV dir=ltr align=left><SPAN class=798040412-27062006><FONT face=Georgia
color=#0000ff size=2>In addition, I have also had significant performance
issues
on a production system when selecting on this table in a very busy system which
is severely fragmented.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=798040412-27062006><FONT face=Georgia
color=#0000ff size=2></FONT></SPAN>&nbsp;</DIV>
<DIV dir=ltr align=left><SPAN class=798040412-27062006><FONT face=Georgia
color=#0000ff size=2>Cheers,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=798040412-27062006><FONT face=Georgia
color=#0000ff size=2>Duncan.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=798040412-27062006><FONT face=Georgia
color=#0000ff size=2></FONT></SPAN>&nbsp;</DIV>
<DIV dir=ltr align=left><SPAN class=798040412-27062006><!-- Converted from text
/rtf format -->
<P><SPAN lang=en-gb><FONT face=Georgia size=2>Duncan Lawie</FONT></SPAN>
<BR><SPAN lang=en-gb><FONT face=Georgia><FONT size=2>DBE - Oracle<SPAN
class=798040412-27062006>.</SPAN></FONT></FONT></SPAN></P>
<P><SPAN lang=en-gb><FONT face=Georgia size=2>"There will always be plenty of
things to compute in the detailed affairs of millions of people doing
complicated things."&nbsp; -- Vannevar Bush, As We May Think; Atlantic Monthly
-
July 1945.</FONT></SPAN></P></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] <B>On Behalf Of </B>Schultz,
Charles<BR><B>Sent:</B> 26 June 2006 15:59<BR><B>To:</B> Hallas, John, Tech Dev
;
oracle-l@(protected)<BR><B>Subject:</B> RE: X$ksmsp (OSEE 10.2.0.2 on Solaris
8)<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr align=left><SPAN class=766195814-26062006><FONT face=Arial
color=#0000ff size=2>Sorry, let me clarify. We have thousands of entries in
ksmsp as well, but I was under the impression that 1 row = 1 contiguous chunk.
Sorry for the confusion.</FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> Hallas, John, Tech Dev
[mailto:John.Hallas@(protected)] <BR><B>Sent:</B> Monday, June 26, 2006
9:57 AM<BR><B>To:</B> Schultz, Charles;
oracle-l@(protected)<BR><B>Subject:</B> RE: X$ksmsp (OSEE 10.2.0.2 on Solaris
8)<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face="Comic Sans MS" color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Comic Sans MS'">Whilst I was
looking at x$ksmsp I came across the following link which provides a useful
summary of the x$tables and some interesting queries against some of
them.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Comic Sans MS" color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Comic Sans MS'"><o:p>&nbsp;<
/o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Comic Sans MS" color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Comic Sans MS'"><A
href="http://www.stormloader.com/yonghuang/computer/x$table.html">http://www
.stormloader.com/yonghuang/computer/x$table.html</A><o:p></o:p></SPAN></FONT></P
>
<P class=MsoNormal><FONT face="Comic Sans MS" color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Comic Sans MS'"><o:p>&nbsp;<
/o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Comic Sans MS" color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Comic Sans MS'">Charles, in
your database is there only 1 entry in total for that table as I see many
thousands of entries (same Oracle version but 2.9
Solaris)<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Comic Sans MS" color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Comic Sans MS'"><o:p>&nbsp;<
/o:p></SPAN></FONT></P>
<DIV>
<DIV class=MsoNormal style="TEXT-ALIGN: center" align=center><FONT
face="Times New Roman" size=3><SPAN lang=EN-US style="FONT-SIZE: 12pt">
<HR tabIndex=-1 align=center width="100%" SIZE=2>
</SPAN></FONT></DIV>
<P class=MsoNormal><B><FONT face=Tahoma size=2><SPAN lang=EN-US
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN><
/FONT></B><FONT
face=Tahoma size=2><SPAN lang=EN-US
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] <B><SPAN style="FONT-WEIGHT: bold">On
Behalf Of </SPAN></B>Schultz, Charles<BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> 26 June 2006 15:27<BR><B><SPAN
style="FONT-WEIGHT: bold">To:</SPAN></B> oracle-l@(protected)<BR><B><SPAN
style="FONT-WEIGHT: bold">Subject:</SPAN></B> X$ksmsp (OSEE 10.2.0.2 on Solaris
8)</SPAN></FONT><SPAN lang=EN-US><o:p></o:p></SPAN></P></DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p>&nbsp;</o:p></SPAN></FONT></P>
<P><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Granted that x$ksmsp is not
documented and therefore subject to changes from version to version (or patch
to
patch), I have an Oracle Support Engineer telling me that one entry in ksmsp
might actually show fragmented memory in the case of a memory leak. My
understanding, from what I have gleaned from others much smarter than I, was
that each row of ksmsp showed one contiguous chunk of memory, be it small or
large, FREE or PERM (or something in between). The whole idea of a memory leak
causing ksmsp to report a fragmented chunk as one piece is a little
disturbing.</SPAN></FONT><o:p></o:p></P>
<P><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"
>Can
anyone corroborate or refute this?</SPAN></FONT> <o:p></o:p></P>
<P><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Oracle Server Enterprise Edition
10.2.0.2</SPAN></FONT> <BR><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Solaris 8 (SunOS 5.8)
64-bit</SPAN></FONT> <o:p></o:p></P>
<P><B><FONT face=Tahoma size=1><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 7.5pt; FONT-FAMILY: Tahoma">charles
schultz</SPAN></FONT></B> <BR><B><FONT face=Tahoma size=1><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 7.5pt; FONT-FAMILY: Tahoma">oracle
dba</SPAN></FONT></B> <BR><B><FONT face=Tahoma size=1><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 7.5pt; FONT-FAMILY: Tahoma">aits -
adsd</SPAN></FONT></B> <BR><st1:place w:st="on"><st1:PlaceType
w:st="on"><B><FONT face=Tahoma size=1><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 7.5pt; FONT-FAMILY: Tahoma">university<
/SPAN></FONT></B></st1:PlaceType><B><FONT
face=Tahoma size=1><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 7.5pt; FONT-FAMILY: Tahoma"> of
<st1:PlaceName w:st="on">illinois</st1:PlaceName></SPAN></FONT></B></st1:place>
<o:p></o:p></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p>&nbsp;</o:p></SPAN></FONT></P></DIV>
<P><pre wrap>==================================================================
============
Please access the attached hyperlink for an important electronic communications
disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================
</pre></P></BODY></HTML>