Re: temporary space issue
Date: Tue, 8 Feb 2011 07:21:12 -0800 (PST)
Message-ID: <47ade509-f94e-42b3-b55f-0587b49a3437_at_8g2000prt.googlegroups.com>
On Feb 8, 8:16 am, dba cjb <chris.br..._at_providentinsurance.co.uk> wrote:
> Oracle 10.2.0.4 enterprise windows 2003 standard
>
> temp space is 3 * 64gig v$tempfiles in ts LOCALTEMP
>
> Got the following errors yesterday
>
> Mon Feb 07 14:00:26 2011
> ORA-1652: unable to extend temp segment by 64 in
> tablespace LOCALTEMP
> Mon Feb 07 14:00:53 2011
> Errors in file d:\oracle\product\10.2.0\admin\lvyesmi\bdump
> \lvyesmi_j000_568.trc:
> ORA-00600: internal error code, arguments: [kglhdgn_1],
> [0x7FF84871250], [18446744071639766840], [4], [], [], [], []
>
> What I know
>
> temp resource filling up caused database to be unavailable
>
> Known unknowns
>
> Is there a way I can get details of users/query/temp useage at time of
> crash so that I can say eg Mr x ran a select from tab which used
> 1.5gig & was trying to grab more space
>
> I am hoping AWR may hold key to who was using temp at time of the
> crash..thus giving a more meaningful explanation to the customer of
> the cause and potential solutions
>
> regards
> Chris B
I suppose you've already examined the tracefile? You have already used Metalink ( MyOracleSupport) to look up that error? AWR may tell you how much temp space was used overall but I doubt it will tell you who was using it. It may be simply a coincidence that you ran into an ORA-01652 just prior to the ORA-00600 which was generated by the job queue coordinator process, not by temporary tablespace issues. You did not report which release of Oracle you're using but I see by the dumpfile path that it's 10.2.0.x (and we don't know what x is so please post the results from
select banner from v$version;
so we will all know what patch level you're running). There is a wealth of information available about the temporary tablespace while the database is running; read here:
http://oratips-ddf.blogspot.com/2008/02/temporary-tablespace-insanity.html
To blindly assume that a database crash is related to a temp space allocation error without first examining the trace files Oracle provides can, and often will, send you in the wrong direction in your search for a solution. Check MOS for the cause of the ORA-00600 error and read through the trace file; I'll expect you'll find some other cause for this crash since you report that the temporary tablespace has filled to capacity before and nothing like this crash has occurred. You do state that this error causes the database to be 'unavailable' so does this mean that the queries being executed don't complete? You need to understand who is using the temp space (which you've already figured out) but you also need to know why those queries are using so much temp space. Are your instance parameters set large enough to allow most sorts and hash joins to run in memory? Increasing sort_area_size and hash_area_size may be the beginning of fixing this temp space issue. Tuning the queries is another step in fixing this issue. As I stated before we do not know at which patch level your database software is so it may also be an issue of patching your installation to fix any known bugs that may be lurking. It may also be a hardware or memory issue; has your Windows admin checked the box thoroughly for possible issues? Is the Windows software at the proper service pack? There is a great deal of information you're missing that could reveal a different cause than what you initially suspect and you need to collect that information instead of charging off like Don Quixote only to find that you've been 'tilting at windmills'.
Please investigate this further, not by expecting AWR to report who was using the temp space (as I doubt that is really the cause) but by using the resources your Oracle database and Oracle support has provided.
David Fitzjarrell Received on Tue Feb 08 2011 - 09:21:12 CST