RE: Urgent: NT 8.0.5 restarted after 76 days, now slow

From: Michael Kline <maklinesr_at_home.com>
Date: Wed, 26 Jul 2000 07:39:28 -0400
Message-Id: <10570.112990@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_000F_01BFF6D4.A0EDC560 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

RE: Urgent: NT 8.0.5 restarted after 76 days, now slowThe various queries statements:

select length from fet$ where file#=:1 and block#=:2 and ts#=:3 select file#,block#length from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 and ext#=:4

delete from fet$ where file#=:1 and block#=:2 and ts#=:3
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 and ext#=:4

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Siva_Chintalapati
Sent: Tuesday, July 25, 2000 11:14 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Urgent: NT 8.0.5 restarted after 76 days, now slow

Hi,
Can U please say what are the queries to b given for deallocating.

Regards
Siva

From: Michael Kline[SMTP:maklinesr_at_home.com] Reply To: ORACLE-L_at_fatcity.com Sent: Wednesday, July 26, 2000 7:54 AM

    To:     Multiple recipients of list ORACLE-L
    Subject:        RE: Urgent: NT 8.0.5 restarted after 76 days, now slow

This appeared to be the clean up of 800 meg of temporary tablespace with 10K

extents.... While there was a pctincrease, it was thousands of segments that

had to all be cleaned up again for a new start. It took 8-10 hours, and then

suddenly the utilization dropped from 85% to 5%. The problem went away as

quickly as it started. Everything is fine once again.

I found queries to fet$ and uet$(I think it was) that had the looks of deallocating and then reassigning the segments. These had all be executed

between 72,000 and 164,000 times each...

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Ron
Rogers

> Sent: Tuesday, July 25, 2000 5:19 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Urgent: NT 8.0.5 restarted after 76 days, now slow
>
>
> Sounds like there was long running process that was active during
> the "shutdown abort" command and it is in the process of rolling
> back what was aborted. It could take many hours..about the same
> time it took to create the rollback data. Check your V$ROLLSTAT
> for rollback segment comsumption.
> Ron Rogers
> DBA
> Atl.GA
>
> >>> maklinesr_at_home.com 07/25/00 11:07AM >>>
> One of my Houston customers bounced the database server this morning,
and

> Oracle80.exe is eating up almost 90% of the server, constantly
> for a couple
> of hours now.
>
> The database did not want to shut down normally. He finally had
> to kill it.
>
> When he brought it back up, it just sucks up all sorts of CPU and
queries

> are taking "forever" to run.
>
> buffer cache and library cache are almost 100%, but dictionary
> cache is only
> 39%.... Running the "less than 5%" query, it's reported to be
> 51.98%. (It's
> been up for 2-3 hours now)
>
> It's 8.0.5.0.0 on NT SP3, with 256MB memory and twin 360mhz
processors.

>
> Previously to this, the system was running just fine. Normal running
is

> Dictionary 99, Library 99, and Buffer 89.
>
> In 16 years I've not seen anything quite like this. Nothing has been
added

> or changed. As I said, the database server had been up steady for over
76

> days. Being NT, there is little they could add without bouncing
> the server.
>
> Metalink shows nothing so far...
>
> Anyone seen anything like this?
>
>
> Michael Kline
> ThinkSpark
> Richmond, VA
> 804-744-1545
>
>
>
> --
> Author: Michael Kline
> INET: maklinesr_at_home.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Author: Ron Rogers
> INET: RROGERS_at_galottery.org
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

    --
    Author: Michael Kline
      INET: maklinesr_at_home.com

    Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
    San Diego, California        -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

------=_NextPart_000_000F_01BFF6D4.A0EDC560 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD><TITLE>RE: Urgent: NT 8.0.5 restarted after 76 days, now = slow</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; = charset=3Dwindows-1252">

<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D160413411-26072000><FONT face=3DArial color=3D#0000ff =

size=3D2>The=20
various queries statements:</DIV> <DIV><FONT face=3DArial color=3D#0000ff =

size=3D2> </DIV>
<DIV>select=20
length from fet$ where file#=3D:1 and block#=3D:2 and = ts#=3D:3</DIV>
<DIV>select=20
file#,block#length from uet$ where ts#=3D:1 and segfile#=3D:2 and = segblock#=3D:3 and=20
ext#=3D:4</DIV>
<DIV>delete=20
from fet$ where file#=3D:1 and block#=3D:2 and = ts#=3D:3</DIV>
<DIV>insert=20
into fet$ (file#,block#,ts#,length) values = (:1,:2,:3,:4)</DIV>
<DIV>delete=20
from uet$ where ts#=3D:1 and segfile#=3D:2 and segblock#=3D:3 and=20 ext#=3D:4</DIV>
<DIV><A = href=3D"mailto:ext@=3D:4"></A><FONT=20 face=3D"Times New Roman"><FONT=20
face=3DArial color=3D#0000ff> </DIV> <DIV><SPAN=20 class=3D160413411-26072000> </DIV> <DIV><SPAN=20 class=3D160413411-26072000> </DIV> <DIV><SPAN=20 class=3D160413411-26072000> -----Original = Message----- From:=20
root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of=20 Siva_Chintalapati Sent: Tuesday, July 25, 2000 11:14 = PM To:=20
Multiple recipients of list ORACLE-L Subject: RE: Urgent: NT = 8.0.5=20
restarted after 76 days, now slow </DIV> <BLOCKQUOTE=20
style=3D"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px = solid">
 Hi, Can U please say what are the queries to b = given for=20
 deallocating. 
 Regards = Siva <UL>
 ---------- = <FONT=20

face=3D"MS Sans Serif" size=3D1>From:   <FONT=20 face=3D"MS Sans Serif" size=3D1>Michael = Kline[SMTP:maklinesr_at_home.com]=20

Reply To:=20       ORACLE-L_at_fatcity.com <FONT face=3D"MS Sans = Serif"=20

size=3D1>Sent:   Wednesday,=20

July 26, 2000 7:54 AM To:     <FONT face=3D"MS Sans = Serif"=20

size=3D1>Multiple recipients of list ORACLE-L <FONT=20 face=3D"MS Sans Serif" size=3D1>Subject:=20        RE:=20

Urgent: NT 8.0.5 restarted after 76 days, now slow This appeared to be the clean up of = 800 meg of=20

temporary tablespace with 10K extents....=20

While there was a pctincrease, it was thousands of segments = that=20

had to all be cleaned up again for a = new start.=20

It took 8-10 hours, and then suddenly the=20

utilization dropped from 85% to 5%. The problem went away as=20 quickly as it started. Everything is = fine once=20

again. 
I found queries to fet$ and uet$(I = think it was)=20

that had the looks of deallocating and=20

then reassigning the segments. These had all be executed = <FONT=20

face=3DArial size=3D2>between 72,000 and 164,000 times = each...

> -----Original = Message----- <FONT=20

face=3DArial size=3D2>> From: root_at_fatcity.com [<FONT = face=3DArial=20

color=3D#0000ff size=3D2><A=20
 =
href=3D"mailto:root_at_fatcity.com">mailto:root_at_fatcity.com</A><F= ONT=20
 face=3DArial size=3D2>]On Behalf Of Ron Rogers <FONT = face=3DArial=20

size=3D2>> Sent: Tuesday, July 25, 2000 5:19 PM <FONT = face=3DArial=20

size=3D2>> To: Multiple recipients of list ORACLE-L = <FONT=20

face=3DArial size=3D2>> Subject: Re: Urgent: NT 8.0.5 restarted = after 76=20

days, now slow > = <FONT=20

face=3DArial size=3D2>> > Sounds like=20

there was long running process that was active during = <FONT=20

face=3DArial size=3D2>> the "shutdown abort" command and it is in = the process=20

of rolling > back what was = aborted. It=20

could take many hours..about the same > time it took to create the rollback data. Check your=20 V$ROLLSTAT > for rollback = segment=20

comsumption. > Ron = Rogers=20

> DBA >=20

Atl.GA > <FONT = face=3DArial=20

size=3D2>> >>> maklinesr_at_home.com 07/25/00 11:07AM=20 >>> > One of my = Houston=20

customers bounced the database server this morning, and = <FONT=20

face=3DArial size=3D2>> Oracle80.exe is eating up almost 90% of = the server,=20

constantly > for a = couple=20

> of hours now. <FONT = face=3DArial=20

size=3D2>> > The = database did not=20

want to shut down normally. He finally had <FONT = face=3DArial=20

size=3D2>> to kill it. >=20

> When he brought it back up, it = just sucks=20

up all sorts of CPU and queries > are=20

taking "forever" to run. >=20

> buffer cache and library cache = are almost=20

100%, but dictionary > = cache is=20

only > 39%.... Running the = "less than=20

5%" query, it's reported to be >=20

51.98%.  (It's > been = up for 2-3=20

hours now) > = <FONT=20

face=3DArial size=3D2>> It's 8.0.5.0.0 on NT SP3, with 256MB = memory and twin=20

360mhz processors. > <FONT=20

face=3DArial size=3D2>> Previously to this, the system was = running just=20

fine.  Normal running is >=20

Dictionary 99, Library 99, and Buffer 89. <FONT = face=3DArial=20

size=3D2>> > In 16 = years I've not=20

seen anything quite like this. Nothing has been added = <FONT=20

face=3DArial size=3D2>> or changed. As I said, the database = server had been=20

up steady for over 76 > = days. Being=20

NT, there is little they could add without bouncing <FONT =

face=3DArial size=3D2>> the server. <FONT face=3DArial =

size=3D2>> > Metalink = shows nothing=20

so far... > = <FONT=20

face=3DArial size=3D2>> Anyone seen anything like this? = <FONT=20

face=3DArial size=3D2>> >=20

> Michael Kline <FONT = face=3DArial=20

size=3D2>> ThinkSpark > = Richmond,=20

VA > 804-744-1545 = <FONT=20

face=3DArial size=3D2>> >=20

> >=20

-- > Author: Michael = Kline=20

>   INET: = maklinesr_at_home.com=20

> > Fat=20

City Network Services    -- (858) 538-5051  FAX: = (858)=20

538-5051 > San Diego,=20 California        -- Public = Internet=20

access / Mailing Lists >=20 =

--------------------------------------------------------------------</FON=

T>=20

> To REMOVE yourself from this = mailing list,=20

send an E-Mail message > = to:=20

ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and = in=20

> the message BODY, include a = line=20

containing: UNSUB ORACLE-L > (or the=20

name of mailing list you want to be removed from).  You = may=20

> also send the HELP command for = other=20

information (like subscribing). > > -- = <FONT=20

face=3DArial size=3D2>> Author: Ron Rogers <FONT = face=3DArial=20

size=3D2>>   INET: RROGERS_at_galottery.org = <FONT=20

face=3DArial size=3D2>> > Fat City=20

Network Services    -- (858) 538-5051  FAX: = (858)=20

538-5051 > San Diego,=20 California        -- Public = Internet=20

access / Mailing Lists >=20 =

--------------------------------------------------------------------</FON=

T>=20

> To REMOVE yourself from this = mailing list,=20

send an E-Mail message > = to:=20

ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and = in=20

> the message BODY, include a = line=20

containing: UNSUB ORACLE-L > (or the=20

name of mailing list you want to be removed from).  You = may=20

> also send the HELP command for = other=20

information (like subscribing). > 
-- Author:=20

Michael Kline   INET:=20 maklinesr_at_home.com 
Fat City Network = Services    --=20

(858) 538-5051  FAX: (858) 538-5051 <FONT = face=3DArial=20

size=3D2>San Diego, =
California        --=20

Public Internet access / Mailing Lists <FONT face=3DArial =

size=3D2>----------------------------------------------------------------=

----=20

To REMOVE yourself from this mailing = list, send=20

an E-Mail message to:=20 ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and = Received on Wed Jul 26 2000 - 06:39:28 CDT