RE: Cost-Based Optimizer and AIX Virtual Processors

From: Crisler, Jon <>
Date: Thu, 30 Apr 2009 23:30:47 -0400
Message-ID: <56211FD5795F8346A0719FEBC0DB067504363782_at_mds3aex08.USIEXCHANGE.COM>

IIRC the cpu_count parameter is dynamic- you can change it within certain limits after startup. We have done this on certain Sun T1 and T2 based machines to alter behavior of parallel query and RMAN processes, and I had a short class where it was mentioned you can do this on AIX LPAR as well. However, it was more in the context of software licensing for Oracle, as AIX LPAR licensing can get if multi-core and multi-socket licensing was not confusing enough.  

[] On Behalf Of David Barbour Sent: Thursday, April 30, 2009 12:34 PM
Cc: Oracle-L Freelists
Subject: Re: Cost-Based Optimizer and AIX Virtual Processors  

Don't have a sample yet. This incident (which actually may be incidents) occurred on new hardware where we've installed a sandbox as part of a looming SAP upgrade. I'm taking snapshots and doing some other monitoring now. When our development team takes a breath, I'm going to ask them to run the job when the box is ramped up and when it's relatively quiet.

Rich's recollection sort of matches my gut feeling (we had TONS of issues in Production with application servers and load balancing until we stopped the virtual CPU/Memory) but until I can collect some empirical evidence or someone can point me to a discussion or article on the subject, I really don't have much to go on.

Keep you informed.

On Thu, Apr 30, 2009 at 10:13 AM, Rich Jesse <> wrote:

Hey David,

> Has anyone seen execution plans change using virtual processors on
How about VM/Linux or VM/Windows? For the purposes of the optimizer, I'd
think they'd all react in a similar manner.
> We've got several partitions on an AIX system. If a specific instance
a certain allotment of processors (generally >=2) when a query kicks off,
it runs differently than when the instance is basically idle (<= 1 processor allocated) and the query starts.

2-3 years ago, I briefly tested micro-partioning using 10.2 on a 4-core blade with 3 LPARs. Each LPAR had .1 CPUs with a maximum of 2. IIRC, it
was the CPU_COUNT parameter on DB *startup* as well as the gathered system
statistics that had the most impact on execution plan (at least as far as
micro-partitioning was concerned).

I have some dim memory that system statistics widely varied, which made it
difficult to collect, since other LPARs could easily grab more CPU away from
the one that was collecting stats.

All said, I didn't care for it. Made planning very difficult, if not impossible.




Received on Thu Apr 30 2009 - 22:30:47 CDT

Original text of this message