very impressed by 11g

[Gerard H. Pille]

Months ago we upgraded our main database to 11g, and after tuning a number of selects, we were
running happily along. A couple of weeks ago, we noticed we'd forgotten to change the
compatible parameter to 11.2 or something, so, yesterday we set that straight.

And brought that system to it's knees.

There was nothing to be done about it, we didn't find any specific queries that were
responsible, but the system kept hitting 100% cpu.

Finally, we doubled the number of processors, and that solved it. Now and then, the system
still reaches 50%, but mostly it stays way below it.

That parameter can not be set back, it appears, so we were very lucky we had spare processing
power, or we would still be struggling.

[Matthias Hoys]

What was the compatible parameter set to originally? 10.2.0.5? Must be that the change activated some new Oracle 11g optimizer features...

Matthias

[Mladen Gogala]

On Tue, 24 Apr 2012 21:50:44 +0200, Gerard H. Pille wrote:


> There was nothing to be done about it, we didn't find any specific
> queries that were responsible, but the system kept hitting 100% cpu.

So, what was using all that CPU? Any particular process? Did you look
into V$SESSMETRIC? V$SQLSTAT? Are execution plans different? What is the
precise version of 11G that you have? There is a known bug in 11.2.0.2:
when automatic baseline collection is turned on, CPU is being burnt like
crazy.



--
http://mgogala.byethost5.com

[Mladen Gogala]

On Tue, 24 Apr 2012 13:41:56 -0700, Matthias Hoys wrote:

> What was the compatible parameter set to originally? 10.2.0.5? Must be
> that the change activated some new Oracle 11g optimizer features...
>
> Matthias

Sounds like it was set to 5.1.22 or 6.0.33.



--
http://mgogala.byethost5.com

[Gerard H. Pille]

Op woensdag 25 april 2012 01:06:44 UTC+2 schreef Mladen Gogala het volgende:

It was all the little processes together. We keep session statistics so we will be able to compare now the users are back to their workstations.

compatible = "11.2.0.0"
was
compatible = "10.1.0"

servr1.DBRMG>select version from v$instance;
VERSION
-----------------
11.2.0.2.0

It looks like we don't capture the plan baselines automatically:
servr1.DBRMG>show parameter optim
...

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
object_cache_optimal_size integer 102400
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 0
optimizer_features_enable string 11.2.0.2
optimizer_index_caching integer 50
optimizer_index_cost_adj integer 10
optimizer_mode string CHOOSE
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
plsql_optimize_level integer 2

[John Hurley]

On Apr 24, 3:50 pm, "Gerard H. Pille" <g...@skynet.be> wrote:
> Months ago we upgraded our main database to 11g, and after tuning a number of selects, we were
> running happily along.  A couple of weeks ago, we noticed we'd forgotten to change the
> compatible parameter to 11.2 or something, so, yesterday we set that straight.

You did not test out the impact of changing the compatible parameter
for your "main database" prior to the production implementation?

Can you clone your database and setup an adequate test environment and
figure out what has changed?

[John Hurley]

Sorry did not see the 11.2.0.2 stuff ... may have a number of bugs in
that version that are now fixed in 11.2.0.3 ...

[Mladen Gogala]

Gerard, 11.2.0.2 has a bug in the mutex code. The bug number is 12431716.
The same thing happened to me a year ago, I even wrote about it on this
very forum. I know the bug number because I opened a SR and that's what
they gave me. The issues were gone once the April 2011 PSU for 11.2.0.2
was installed. Personally, my advice would be to upgrade to 11.2.0.3 and
install the April 2012 PSU.



--
http://mgogala.byethost5.com

[Eric]

On 2012-04-24, Mladen Gogala <gogala...@gmail.com> wrote:
> On Tue, 24 Apr 2012 13:41:56 -0700, Matthias Hoys wrote:
>
>> What was the compatible parameter set to originally? 10.2.0.5? Must be
>> that the change activated some new Oracle 11g optimizer features...
>>
>> Matthias
>
> Sounds like it was set to 5.1.22 or 6.0.33.

5.1.22 ? Those were the days - things were simpler then.

Eric

--
ms fnd in a lbry

[Gerard H. Pille]

Mladen Gogala wrote:
>
> Gerard, 11.2.0.2 has a bug in the mutex code. The bug number is 12431716.
> The same thing happened to me a year ago, I even wrote about it on this
> very forum. I know the bug number because I opened a SR and that's what
> they gave me. The issues were gone once the April 2011 PSU for 11.2.0.2
> was installed. Personally, my advice would be to upgrade to 11.2.0.3 and
> install the April 2012 PSU.
>
>
>

I knew I should have thrown it in this group straight away. Thanks a bunch, MG.

[Gerard H. Pille]

Sorry about that, John, but I was not involved until I noticed smoke coming out of that machine.
There a number of other databases and application servers that keep me 100% occupied. But I
run some automated checks on all the systems and they warned me.

[Mladen Gogala]

I am partial to 6.0. I was the first guy in the EMEA region licensed to
teach Oracle 6 internals course by Oracle Austria. The examiner was a guy
named Wolfgang Schluessel. That was the first version I was utilizing on
Unix. 5.1.22 was, at least in my mind, tied to VAX/VMS. Before image file
(now "UNDO tablespace") was defined as VAX$BI. There was a utility to
start the instance called "ior". One would run "ior w" for warm start or
"ior c" for the cold start. Monitor was called "odt". It was complicated.
Oracle 6 had sqldba, with a nice top-like monitor. That was changed to
"svrgmgrl" in oracle7 and oracle8i, but I still have alias
svrmgrl="sqlplus / as sysdba"
in my .bashrc. Of course, I started with Oracle 4 and "ufi" (user
friendly interface) on dos 3.3. There was a language to write forms, one
had to use an editor, write a form, compile it with iac.exe and run it
with iap.exe. That was running in 512KB of memory. Yes, it was "K" as in
"kilo". Boy, I feel old.
This year, we will see Oracle 12F. A long way for someone who has started
with the version 4 and installed it from floppy disks. However, this year
will be different. It looks like IBM has finally begun to seriously look
into selling DB2 on Linux. They dropped the prices much lower than Oracle
and Oracle should brace for impact. I feel that the pervasive mood has
turned against Oracle.


--
http://mgogala.byethost5.com

[Noons]

Gerard H. Pille wrote,on my timestamp of 25/04/2012 5:50 AM:

>
> That parameter can not be set back, it appears, so we were very lucky we had
> spare processing power, or we would still be struggling.

One thing you can try that worked for me in a similar situation:
optimizer_features_enable = 10.2.0.3
It got rid of the "Invalid Number" message that seems to plague 11gr2 SQL ported
from earlier releases.
As well as a couple of "unexplained" SQL slowdowns.
I'll reset it back once I've got everything on the 2012-04 PSU.
That one seems to bring some stability.

[Gerard H. Pille]

Op woensdag 25 april 2012 20:55:44 UTC+2 schreef Gerard H. Pille het volgende:

I hear now that that parameter change had been done in development and test databases, but probably we did not put enough load on those systems. But, as Mladen pointed out, we should have known, given the PSU documentation.

[Boris Findrik]

I am almost sure that we have 5.0.27 on Unisys 5000/50 (Motorola 68020,
80M mem(?), 3 x 110M HD, SVR3).
15 users, uvt1224 terminals, works like a charm.

And, I expect 12C.

[Mladen Gogala]

On Thu, 26 Apr 2012 20:21:20 +0200, Boris Findrik wrote:

> I am almost sure that we have 5.0.27 on Unisys 5000/50 (Motorola 68020,
> 80M mem(?), 3 x 110M HD, SVR3).
> 15 users, uvt1224 terminals, works like a charm.
>
> And, I expect 12C.

I am not sure that a direct upgrade from 5.0 --> 12C will be possible.



--
http://mgogala.byethost5.com

[Boris Findrik]

It will be easy if I manage to install java on SVR3.
Oh, no, but I got only 168M disk free ....

[Jonathan Lewis]

"Gerard H. Pille" <g...@skynet.be> wrote in message
news:4f9703d1$0$3116$ba62...@news.skynet.be...

In principle the compatible parameter is about physical data structures,
not about features. So you wouldn't expect (for example) changes in
optimizer plans from changing this parameter. The sort of thing you might
expect is to see some extra work being done because you have data stored in
one physical format that has to be converted in real time to a different
physical format. (I can't think of any real examples of this kind of need,
though, that would explain your observations.)

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

[Gerard H. Pille]

Op woensdag 25 april 2012 17:57:44 UTC+2 schreef Mladen Gogala het volgende:

Mladen, you wouldn't happen to know a test-scenario that allows one to check if this problem has been solved?

Thanks,

Gerard

[John Hurley]

Gerard:

# Mladen, you wouldn't happen to know a test-scenario that allows one

to check if this problem has been solved?

I do not know without searching around but the mutex problem was
pretty well known in 11.2.0.2 ... lots of blog posts about it and if
you do some googling around on 12431716 it should be all over the
place ( my guess ) and/or words like "11.2.0.2 mutex" etc.

My 1st system now up on 11.2.0.3.2 is stable enough so far but not
much load ... a bunch more testing on the way for bigger systems looks
like probably will not go live until 11.2.0.3.3 or 11.2.0.3.4 ( PSUs
applied ).

As time consuming as applying and testing the PSUs are ... hard to
stay away from them for avoiding stuff that oracle probably should
have figured out a littler earlier but did eventually catch.

[Mladen Gogala]

On Wed, 23 May 2012 00:35:02 -0700, Gerard H. Pille wrote:

> Mladen, you wouldn't happen to know a test-scenario that allows one to
> check if this problem has been solved?
>
> Thanks,

11.2.0.3 doesn't exhibit that problem. I have 2 PSU
sets applied.



--
http://mgogala.freehostia.com

[Gerard H. Pille]

Mladen Gogala wrote:
> On Wed, 23 May 2012 00:35:02 -0700, Gerard H. Pille wrote:
>
>> Mladen, you wouldn't happen to know a test-scenario that allows one to
>> check if this problem has been solved?
>>
>> Thanks,
>
> 11.2.0.3 doesn't exhibit that problem. I have 2 PSU
> sets applied.
>
>
>

You're quite right, Mladen, we've just run into a another bug. The 12976376, if I'm not
mistaken. Luckily, we had some spare room for the shared pool, which seems to alleviate things.
But it was another exciting day. Walking from the mutex into the memory leak*.


* incidit in Scyllam cupiens vitare Charybdim

[John Hurley]

Gerard:

# You're quite right, Mladen, we've just run into a another bug.  The

12976376, if I'm not mistaken.  Luckily, we had some spare room for
the shared pool, which seems to alleviate things.

... But it was another exciting day.  Walking from the mutex into the
memory leak*.

How many systems do you now have running on 11.2.0.2.x? Have you
started patching up 11.2.02.x with PSU's yet?

I for one would seriously recommend you think about swapping out the
11.2.0.2 systems asap and start testing/planning any current 11.2.0.2
systems going up to 11.2.0.3.x ... supposed to be a whole lot better
"supposed to be" ...

[Gerard H. Pille]

Op vrijdag 25 mei 2012 00:08:50 UTC+2 schreef John Hurley het volgende:

> How many systems do you now have running on 11.2.0.2.x? Have you
> started patching up 11.2.02.x with PSU's yet?
>

None, both were upgraded to 11.2.0.3. We should not have done that, we should have applied the PSU's for 11.2.0.2. I entered a SR for a patch for the bug, but I got a questionnaire in reply, asking about millstones and stuff.

[Noons]

FWIW: well impressed with 11.2.0.3 with the May 2012 PSU. It's resolved most of
the little annoying issues we had with testing the DW and it's now in
production, no probs so far (touch wood).