Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 28604 invoked from network); 8 Aug 2007 09:46:25 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 8 Aug 2007 09:46:25 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 52B8F7251F4; Wed, 8 Aug 2007 10:44:07 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16808-06; Wed, 8 Aug 2007 10:44:07 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BB5F77251D5; Wed, 8 Aug 2007 10:44:06 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 08 Aug 2007 10:01:31 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B3675722F48 for ; Wed, 8 Aug 2007 10:01:31 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 04810-04 for ; Wed, 8 Aug 2007 10:01:31 -0400 (EDT) Received: from web35402.mail.mud.yahoo.com (web35402.mail.mud.yahoo.com [66.163.179.111]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 4C0E3720D7B for ; Wed, 8 Aug 2007 10:01:30 -0400 (EDT) Received: (qmail 86082 invoked by uid 60001); 8 Aug 2007 14:03:48 -0000 X-YMail-OSG: 48Tzg3cVM1lHgnK1Fee4RNdoeNQ4BzWZUJ.p1mfY8OUc3Utxij0Rg.jz8a8tFBEbO.h9j1Ho2vm.gxSUzeDLv6g_vGviHA2LAVq.SUaagynMveErWeI- Received: from [64.147.161.61] by web35402.mail.mud.yahoo.com via HTTP; Wed, 08 Aug 2007 07:03:48 PDT Date: Wed, 8 Aug 2007 07:03:48 -0700 (PDT) From: Dan Norris Subject: Re: History on dynamic parameters To: andrew.kerber@gmail.com, jurgen_mortier@amis.com Cc: oracle-l@freelists.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-194782161-1186581828=:86039" Message-ID: <332024.86039.qm@web35402.mail.mud.yahoo.com> X-archive-position: 414 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: dannorris@dannorris.com Precedence: normal Reply-to: dannorris@dannorris.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain --0-194782161-1186581828=:86039 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable If you're taking statspack snapshots, you can also find the non-default par= ameters captured in the statspack repository. I don't remember the exact ta= ble in the STATS$ tables, but it shouldn't be that hard to find if you're r= eally desperate (and you're taking regularly-scheduled snapshots).=0A=0ADan= =0A=0A----- Original Message ----=0AFrom: Andrew Kerber =0ATo: jurgen_mortier@amis.com=0ACc: oracle-l@freelists.org=0ASent: W= ednesday, August 8, 2007 8:43:04 AM=0ASubject: Re: History on dynamic param= eters=0A=0A=0AThere should be a record of the changes in the alert log if y= ou can find the entries from that far back.=0A=0A=0AOn 8/8/07, jurgen_morti= er@amis.com wrote:=0AHi,=0A=0AI'm a junior DBA an= d I have an Oracle 9.2.0.4.0 running on Solaris 8 which=0A=0Ahas been resta= rted on July 28th (by my collegue currently on FTO) to change=0Asome parame= ters:=0A- shared_pool_reserved_size=3D from 60M to 100 M=0A- shared_pool_si= ze=3D from 600M to 1000M=0A- undo_retention=3D from 1800 to 3600=0A=0A=0ASi= nce then users report that queries are running twice as long as before.=0A= =0AOn August 2nd I tried to change back the last 2 parameters (since they w= ere=0Adynamic), but the CKPT process was consuming 1 complete CPU and I was= =0A=0Aforced to reboot the system on the 3rd. At that time I changed the=0A= parameters back to 600M and 1800 seconds.=0A=0ASince that didn't improve th= e response time, I started realizing that other=0Adynamic parameters might = have been changed dynamically without the init=0A=0Afile being updated.=0A= =0ADoes anyone know of a way to find back the history of those (dynamic)=0A= parameters (accross DB restarts) ?=0A=0ARegards=0AJ=FCrgen Mortier=0A=0AAMI= Semiconductor - "Silicon Solutions for the Real World"=0A=0ANOTICE:=0AThis= =0Aelectronic message contains information that may be confidential or=0Apr= ivileged. The information is intended for the use of the individual=0Aor en= tity named above. If you are not the intended recipient, please be=0Aaware = that any disclosure, copying, distribution or use of the contents=0Aof this= information is prohibited. If you received this electronic=0Amessage in er= ror, please notify the sender and delete the copy you=0Areceived.=0A=0A--= =0Ahttp://www.freelists.org/webpage/oracle-l=0A=0A=0A=0A=0A=0A=0A-- =0AAndr= ew W. Kerber=0A=0A'If at first you dont succeed, dont take up skydiving.'= =0A=0A=0A=0A --0-194782161-1186581828=:86039 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
If you're taking statspack snapshots, you can also = find the non-default parameters captured in the statspack repository. I don= 't remember the exact table in the STATS$ tables, but it shouldn't be that = hard to find if you're really desperate (and you're taking regularly-schedu= led snapshots).

Dan

----- Original Message ----
Fr= om: Andrew Kerber <andrew.kerber@gmail.com>
To: jurgen_mortier@ami= s.com
Cc: oracle-l@freelists.org
Sent: Wednesday, August 8, 2007 8:43= :04 AM
Subject: Re: History on dynamic parameters


There shoul= d be a record of the changes in the alert log if you can find the entries from that far back.
=0A
On 8/8/07, = jurgen_mortier@amis.com <jur= gen_mortier@amis.com> wrote:=0A
Hi,

I'm a junior DBA and I have an Oracl= e 9.2.0.4.0 running on Solaris 8 which=0A
has been restarted on July 28t= h (by my collegue currently on FTO) to change
some parameters:
- shar= ed_pool_reserved_size=3D from 60M to 100 M
- shared_pool_size=3D from 60= 0M to 1000M
- undo_retention=3D from 1800 to 3600=0A

Since then u= sers report that queries are running twice as long as before.

On Aug= ust 2nd I tried to change back the last 2 parameters (since they were
dy= namic), but the CKPT process was consuming 1 complete CPU and I was=0A
f= orced to reboot the system on the 3rd. At that time I changed the
parame= ters back to 600M and 1800 seconds.

Since that didn't improve the re= sponse time, I started realizing that other
dynamic parameters might hav= e been changed dynamically without the init=0A
file being updated.
Does anyone know of a way to find back the history of those (dynamic)
= parameters (accross DB restarts) ?

Regards
J=FCrgen Mortier
AMI Semiconductor - "Silicon Solutions for the Real World"=0A
NOTICE:<= br>This=0Aelectronic message contains information that may be confidential = or=0Aprivileged. The information is intended for the use of the individual= =0Aor entity named above. If you are not the intended recipient, please be= =0Aaware that any disclosure, copying, distribution or use of the contents= =0Aof this information is prohibited. If you received this electronic=0Ames= sage in error, please notify the sender and delete the copy you=0Areceived.=

--
http://www.freelists.org/webpage/oracle-l




--
Andrew W. Ker= ber

'If at first you dont succeed, dont take up skydiving.'=0A
=
--0-194782161-1186581828=:86039-- -- http://www.freelists.org/webpage/oracle-l