Very informative
March 24, 2005 - 6pm Central time zone
Reviewer: A reader
Well, quite obviously, it's not a bug. From the link
provided by the OP, I really wonder how anyone could
believe that "bug" story (or the "install time" hypothesis).
Anyway, I agree with you that the Oracle community should
fight against the spreading of such myths. I'm (still)
learning much from your *proof-based* approach, that's what
we need. Keep up with the good work!
Thanks,
Tomasz
Log_buffer > 1M.
March 24, 2005 - 7pm Central time zone
Reviewer: Sai from San Jose, CA USA.
Tom,
Oracle flushes redo buffer to online redologs when it is 1/3 full or 1M or with commits...etc. Is
there any downside of setting log_buffer to more than 1M? Metalink note 30753.1 says "Values above
1Mb are unlikely to yield significant benefit."
Even with 1M setting, we are seeing log buffer allocation retry waits.
Thanks.
Followup March 24, 2005 - 8pm Central time zone:
I'll see if I cannot get that note updated.
The thing is -- the 1/3 - 1m - commits - those are points at which lgwr starts flushing out to
disk.. they are triggers for an action.
Say I run a big transaction that generates gobs of redo - lgwr will be constantly flushing while
I'm generating. values way over 1m can be very beneficial in a case like that..
or say I have hundreds/thousands of concurrent transactions - same thing. 1m would be too small.
what is your exact wait there?
cpu cost
March 24, 2005 - 7pm Central time zone
Reviewer: Dave
(1) In 9i, does oracle compute cpu cost and store it in data dictionary for CBO to use cpu cost to
come with better exceution plan? Is it done automatically by oracle, I mean the computation of cpu
cost?
(2) If the answer for the above is 'yes', one does not have to use gather system stats procedure to
collect system stats that includes cpu cost? Thanks.
(3) any change in 10g regarding the above?
Followup March 24, 2005 - 8pm Central time zone:
in 9i, cpu costing was not "on"
in 10g, cpu costing is "on"
Very useful information
March 25, 2005 - 3am Central time zone
Reviewer: Bipul from London, UK
Hi Tom.
Great explanation on cpu_count and thanks for those couple of useful link on hyperthreading.
I was wondering if Oracle installer collects *any* stats at install time? My intial guess is
*none*.
Thanks
bipul
Followup March 25, 2005 - 7am Central time zone:
the installer looks at your OS and system to ensure you meet the minimum requirements, that's about
it.
The database upon startup figures out what resources are available to be used.
Excellent critique.
March 25, 2005 - 9am Central time zone
Reviewer: Kevin from West Palm Beach, FL
March 25, 2005 - 12pm Central time zone
Reviewer: Jay from NJ
Kudos to Tom for all the clarifications with examples, Oracle community is greatful to people like
you.
Thanks.
What cpu costing buys us?
March 25, 2005 - 12pm Central time zone
Reviewer: Mikito Harakiri
Before, a mesure of execution cost was the number of buffer gets. Very simple and clear model from
performance analyst perspective. Now we have extra column with a number in units I have no idea
about. Now I have to do twice a work in order to understand optimizer decision.
On practical side, did CPU costing solve any of the fundamental optimimisation problems (predicate
corellation, complex predicate selectivity estimate, etc)? It did not.
I suspect a miniscule performance improvements that people see in the benchmarks is attributed
solely to clever predicate order evaluation. Can predicate order evaluation be implemented outside
of CPU costing model? I bet it can.
cpu cost in 10g
March 25, 2005 - 10pm Central time zone
Reviewer: Sam
you say, *in 10g, cpu costing is "on" *. However, as a DBA I have to gather system stats for CBO to
use cpu data? right. or does oracle collect cpu information like speed etc automatically in 10g?
could you clarify where does CBO get cpu cost info? Thanks.
Followup March 26, 2005 - 9am Central time zone:
if you query sys.aux_stats$
you'll see where it gets them from by default, gathering system statistics places it's results
there.
CPUSPEEDNW vs CPUSPEED
March 26, 2005 - 11am Central time zone
Reviewer: Alberto Dell'Era from Milan, Italy
May you please clarify what's the meaning of CPUSPEEDNW vs CPUSPEED ?
By reading the docs for dbms_stats.gather_system_stats:
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_stats.htm#1036402
I've understood (please correct me if i'm wrong) that CPUSPEEDNW is the cpu speed "No Workload", so
to-be-collected when the instance is idle (my interpretation of "Oracle recommends to run
GATHER_SYSTEM_STATS ('noworkload') after creation of the database and tablespaces").
CPUSPEED is the cpu speed to-be-collected when the instance is busy servicing a representative
workload (by choosing INTERVAL or START|STOP).
But how can the cpu speed be different - shouldn't this produce the same value (+- the statistical
error in the estimation algorithm of course - so better said, E[CPUSPEEDNW] = E[CPUSPEED]) ?
Probably i'm missing the real meaning of this measures, and what i'm missing explains why you chose
CPUSPEEDNW in your example ...
TIA
Followup March 26, 2005 - 1pm Central time zone:
they should be more or less the "same" (fractional differences).
One is collected on a "basically idle system", it generates a fake workload (just run
dbms_stats.gather_system_stats on 10g without any input, cpu should pop right up and read/write IO
go up as well). You use this to "prime the pump"
Once the system is running with representatitve workloads for real, you can use dbms_stats gather
system stats with an interval to collect them under your real workload and those will be used
instead.
I chose cpuspeednw because I had not gathered 'real' workload stats, so, it was going to use the nw
one.
Thanks
March 26, 2005 - 1pm Central time zone
Reviewer: Alberto Dell'Era from Milan, Italy
crystal clear!
Faulty cpu count
March 26, 2005 - 10pm Central time zone
Reviewer: Parikshit Paul from India
To be very simplistic,if the os tells us to consider the number of cpu's to be 2 at the application
level, we should not be really considering how many cpu 's are there in actual??
Does that make any sense?
Followup March 27, 2005 - 9am Central time zone:
As far as the OS is concerned, there are two CPU's (or 4 or whatever) with hyperthreading.
My task manager in windows has two cpu graphs. A single program running in a tight loop is only
able to utilize 50% of the machine resources. It takes two things running in tandem to fully
utilize the machine (in parallel). There are effectively two cpus on that machine with
hyperthreading.
10g
March 27, 2005 - 9am Central time zone
Reviewer: Anil from Dubai
Hi Tom
<<
in 9i, cpu costing was not "on"
in 10g, cpu costing is "on"
>>
I have tried the test you mentioned , in my 10g database
SYS@NGDEV1-SQL> create table t as select * from all_objects;
Table created.
SYS@NGDEV1-SQL> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
SYS@NGDEV1-SQL> exec dbms_stats.set_system_stats( pname => 'CPUSPEEDNW',pvalue => 1 );
PL/SQL procedure successfully completed.
SYS@NGDEV1-SQL> @trace
Session altered.
SYS@NGDEV1-SQL> @xp
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-------------------------------
Error: cannot fetch last explain plan from plan_table
SYS@NGDEV1-SQL>
SYS@NGDEV1-SQL>
SYS@NGDEV1-SQL> explain plan for select count(*), min(created), max(created) from t
2 /
Explained.
SYS@NGDEV1-SQL> @xp
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-------------------------------
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1119 |
| 1 | SORT AGGREGATE | | 1 | 8 | |
| 2 | TABLE ACCESS FULL| T | 49215 | 384K| 1119 |
-----------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
- cpu costing is off (consider enabling it)
13 rows selected.
Why " cpu costing is off (consider enabling it)" What is wrong here?
Rgds
Anil
Followup March 27, 2005 - 10am Central time zone:
'plan table is old version'.....
went back to 8i, grabbed the plan table:
ops$tkyte@ORA10G> create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(30),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
22 23 24 25 distribution varchar2(30));
Table created.
ops$tkyte@ORA10G> @plan 'select * from dual';
0 rows deleted.
old 1: explain plan for &1
new 1: explain plan for select * from dual
Explained.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 |
----------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
- cpu costing is off (consider enabling it)
12 rows selected.
and now just put the proper one in place:
ops$tkyte@ORA10G> drop table plan_table;
Table dropped.
ops$tkyte@ORA10G> @?/rdbms/admin/utlxplan
Table created.
ops$tkyte@ORA10G> @plan 'select * from dual';
0 rows deleted.
old 1: explain plan for &1
new 1: explain plan for select * from dual
Explained.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 397561404
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
it is not so much that cpu costing is off, but rather the cpu costing information wasn't
available.... due to the old plan table
CPU_COST NUMBER(38)
IO_COST NUMBER(38)
TEMP_SPACE NUMBER(38)
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
PROJECTION VARCHAR2(4000)
TIME NUMBER(38)
QBLOCK_NAME VARCHAR2(30)
Those columns are missing from your plan table.
March 27, 2005 - 10am Central time zone
Reviewer: dave
have you gathered system stats?
Followup March 27, 2005 - 10am Central time zone:
did not need to, I did not. They are defaulted and we are specifically setting the cpuspeednw
setting.
it was all about the plan table (probably).
Two cpu's? or Four?
March 27, 2005 - 10pm Central time zone
Reviewer: Parikshit Paul from India
Tom,You said:
[quote]
As far as the OS is concerned, there are two CPU's (or 4 or whatever) with hyperthreading.
My task manager in windows has two cpu graphs. A single program running in a tight loop is only
able to utilize 50% of the machine resources. It takes two things running in tandem to fully
utilize the machine (in parallel). There are effectively two cpus on that machine with
hyperthreading.
[/quote]
O.k.
Now 2 cpu's hyperthreading=4cpu's (logically)( that's what o.s would show=> what oracle would
assume)
But
[quote]
There are effectively two cpus on that machine with hyperthreading.
[/quote]
So Don's logic holds. Doesn't it??
Followup March 28, 2005 - 7am Central time zone:
no don's logic doesn't hold. (even if it were set wrong, which it is not, the logic of why it would
be bad wasn't there)
I have a windows laptop -- with hyperthreading, it has 2 CPU's.
I have a Dell Poweredge Server -- with hyperthreading, it has 4 CPU's.
The OS sees 2/4/8/whatever.
Whatever the OS reports as being there, that is WHAT IS THERE.
I have 1 "physical" cpu on my laptop that via the magic that is hyperthreading, appears, functions,
is 2 cpus.
I have 2 "physical" cpus on my poweredge server that via the magic that is hyperthreading, appears,
functions IS 4 cpus.
Perhaps I confused by talking about my laptop and poweredge server on the same page, one has 2 cpus
via hyperthreading, the other has 4 cpus via hyperthreading.
Ok, using TOP on asktom.oracle.com:
CPU states: cpu user nice system irq softirq iowait idle
total 6.9% 0.0% 1.8% 0.2% 2.3% 11.3% 77.1%
cpu00 6.1% 0.0% 2.5% 0.9% 7.9% 12.7% 69.5%
cpu01 8.1% 0.0% 1.1% 0.0% 0.9% 9.9% 79.6%
cpu02 7.1% 0.0% 1.3% 0.0% 0.1% 13.5% 77.6%
cpu03 6.3% 0.0% 2.3% 0.0% 0.3% 9.1% 81.6%
Mem: 5931992k av, 5913556k used, 18436k free, 0k shrd, 3460k buff
how many CPU's do I have? What should cpu_count be set to? There are 4 cpu's on that machine as
far as the OS and everything else is concerned (another dell server with hyperthreaded xeon chips).
If I run a single process in a tight loop, one cpu is going to show 100%, do two things, two cpus
and so on. The machine has 4 cpus for all intents and purposes. cpu_count = 4 is the only right
setting.
Number of CPUs
March 28, 2005 - 7am Central time zone
Reviewer: Sriram from Florida
Is there a reason why the number of CPUs on the server should always be even numbers? i.e..2,4,8
instead of 3,5,7?
Thanks,
Sriram
Followup March 28, 2005 - 8am Central time zone:
Like nodes in a cluster, they technically can be odd numbers (we run a 3 node rac cluster)
2,4,6,8,10,.... are more "normal", traditional.
Misunderstanding
March 29, 2005 - 10pm Central time zone
Reviewer: Parikshit Paul from India
Thanks TOM,
Yeah I really misunderstood.When you said 2 cpu's, I thought 2 physical cpu's.
Everything is clear now.:)
Lower CPU_COUNT/better performance on 9i
March 30, 2005 - 3am Central time zone
Reviewer: Tony from Beirut Lebanon
Dear Tom,
Thank you for the great explanations; it really helped me a lot to understand the exact issues
that I am having problem with.
Our production database server has 2 physical CPUs (Dual Xeon) we had big performance problems on
our application (OLTP takes 9 hours) meanwhile on the testing server which a simple PC not even
hyper-threaded it took only 17 minutes on identical database structure and data, the difference was
that on the server there was no I/O activity nor CPU.
Our application/DB support, after many guesses theyve set the CPU_COUNT = 1,
PARALLEL_THREAD_PER_CPU = 1 where they were 2 by 4, and the performance improved greatly. OLTP
takes only 12 minutes, but every other query, report degraded in performance.What i want to know is
why performance increased when we set cpu_count = 1 and parallel_thread_per_cpu = 1.
I have Windows 2003 server standard installed, with Oracle 9i Rel2 Enterprise 9.2.0.1.0
What you suggest I do other than patching Oracle with the right patches and setting the parallel
execution parameters and the parallel option on the tables?
Thanks in Advance
Tony G.
Followup March 30, 2005 - 7am Central time zone:
did you have PARALLEL OPERATIONS ENABLED
and where they being done on the single cpu machine
and where they being done on the multi-cpu machine
performance didn't increase when you set cpu_count = 1, performance changed:
<quote>
OLTP takes only 12 minutes, but every other query, report degraded in
performance
</quote>
It seems you don't want parallel plans for OLTP (makes sense) but do for reports run in batch
(makes sense)
Perhaps you want to do that, put the tables back to default degrees of parallelism (and
cpu_count/whatever back to their defaults). Then for the reports that want parallel query, say
that.
Awesome ...
March 30, 2005 - 7am Central time zone
Reviewer: A reader
Maybe Oracle should collaborate with Discovery ...
http://dsc.discovery.com/fansites/mythbusters/mythbusters.html
Too many myths, too little time ... ;)
Well done, Tom, well explained ...
Followup March 30, 2005 - 9am Central time zone:
I dig those guys -- one of my favorite shows.
Lower CPU_COUNT / better performance!?
March 30, 2005 - 9am Central time zone
Reviewer: Tony from Beirut Lebanon
Hi Tom
On the machine with 2 physical CPUs we didn't have parallel options enabled, but we will enable all
the necessary parameters and alter tables to parallel when they're not.
What I am aiming to do is to have oracle CPU_COUNT = 2 and PARALLEL_THREAD_PER_CPU = 4 (back to the
defaults) and enable parallelism with the parameters and table options.
Doing this will I get (more or less) the same performance as I am now. ???
Thanks in Advance
Tony
Followup March 30, 2005 - 10am Central time zone:
<quote>
On the machine with 2 physical CPUs we didn't have parallel options enabled, but
we will enable all the necessary parameters and alter tables to parallel when
they're not.
</quote>
you must have -- else it would have been the same. the cpu count affects parallel plan generation,
unless you had them "on", they would not be used.
GREAT help
March 31, 2005 - 4am Central time zone
Reviewer: Tony from Beirut Lebanon
Hi Tom
Thanks for the very useful explainations and help.
Keep up the Good work.
Regards
Tony G.
Relation of async IO and lgwr_io_slaves
April 12, 2005 - 8am Central time zone
Reviewer: Mike Ault from Alpharetta, GA
In the docs Oracle states that the loq writer will write asynchronously on async capable systems.
In addition, on non-async systems (and async for that matter) if dbwr_io_slaves is greater than 0
then up to 4 lgwr_io_slaves are also created. How does the above relate to your statement that the
only way to get more than one log writing process is to use RAC?
Physically speaking the async write capability of the log writer process on async capable OS
equates to multiple processes writing to multiple disks. Likewise on non-async systems when
dbwr_io_slaves is greater than 0 then you get up to 4 log writer slave processes. What are these
used for if you can only write with one process?
Thanks for clearing this up as the docs, metalink and other sources just mention this in passing
without really discussing it in detail.
Followup April 12, 2005 - 8pm Central time zone:
But the real question is, will Don ever retract his paper -- which as far as I can see has almost
nothing technically accurate starting with the title and ending with lgwr.
lgwr doing async io is not even remotely related to having multiple lgwrs. The statement regarding
lgwr doesn't even seem to make "sense"
<quote>
The number of CPUs is important to the value of log_buffer, because multiple log writer (LGWR)
processes may be spawned by Oracle to asynchronously offload the redo information.
</quote>
why is the number of cpu's important because of multiple log writers? why would that be important
to the value of log_buffer? and how would cpu_count relate to multiple log writers.
Not that an instance will have more than one lgwr....
IO Slaves (have not had lgwr specific io slaves for many many many releases), are NOT lgwr's.
They are a technique to simulate async IO.
LGWR, there is and will be ONE. It is a point of serialization in the database instance, there be
but one lgwr to wait on.
So in answer to:
<quote>
How does the above relate to your statement that the only way to get
more than one log writing process is to use RAC?
</quote>
the quote was directly about LGWR, there is only one of them.
LGWR can and will use async to write.
LGWR can and will use IO slaves to simulate async IO.
ASYNC IO is not LGWR. We are not even mincing terms here. There is a single LGWR process
responsible for initiating all writes to the redo logs.
And even if we made the broad jump to consider IO slaves lgwr (which we won't really), what
connection between them and cpu_count could you make?
They updated the page
April 14, 2005 - 7pm Central time zone
Reviewer: Thomas Kyte from Leesburg, VA
for the second time. It is mostly accurate, but inconclusive now.
they say:
<quote>
Oracle doubles the value for cpu_count
</quote>
but that is not true, the OS is reporting to us the number of CPUs. Oracle is not doubling
anything. As reported above, with hyperthreading enabled, there is only one correct cpu_count
value that which the OS reports.
They say this is an issue when "You have turned-on CPU-based optimizer costing", but they fail to
say "why", so I'm left curious? Aren't you?
This:
<quote>
If cpu_count is doubled and the parallel_automatic_tuning is set to true, the CBO will believe
that full-table scans are less expensive based on doing them in parallel using a default degree of
cpu_count x
</quote>
Is still false, as demonstrated above in the original answer. It takes a stated desire on your
part to have a table be considered for parallel operations -- and if you have, well, you basically
do have two cpu's -- that is the entire goal of hyperthreading.
<quote>
The Oracle cpu_count is determined when you start Oracle. The cpu_count affects the Oracle
cost-based optimizer through many calculated parameters that use cpu_count as their basis and are
considered every time that Oracle creates an execution plan for a SQL statement. A doubled setting
for cpu_count can result in changes to yhour explain plans when moving from a single to multiple
CPUs or when you increase the number of CPUs in your system.
</quote>
I still think it would be really neat for them to give us at least a single example... Which
parameters? Some of them? A hint as to what they are? Why they are relevant?
As demonstrated above, they should be able to cook up a simple example in a couple of seconds (if
they know what parameters might be affected..)
They talk of db_block_lru_latches, which is cool -- but they don't really say why it is relevant or
why having hyperthreading on is good bad or indifferent... Why this parameter? Why not something
else?
<quote>
This parameter controls the maximum number of OPQ factotum processes (p000, p001) that are spawned
during a parallel query when parallel_automatic_tuning is enabled.
</quote>
that is still not accurate (but I like the use of the word factotum still, interesting application
of the word)
<quote>
There is still argument about whether the cpu_count influences the behavior of actions against the
log_buffer.
</quote>
I'm not sure who they are arguing with, it was described above what happens with the copy
latches.... and why having cpu_count set properly is a positive thing
<quote>
yet this appears to be an issue because the final conclusions are still not completely clear.
</quote>
one wonders then why this was titled "Oracle performance bug" in the first place if the jury is
out, that is, if they don't have any information one way or the other?
All in all, the bug was downgraded to an "issue" in the article. It sure sounded more exciting an
an "import oracle bug" though.
Followup April 14, 2005 - 8pm Central time zone:
Damned inconvenient things, facts!
April 14, 2005 - 9pm Central time zone
Reviewer: Howard J. Rogers from Sydney, Australia
Don probably uses the term "factotum processes" for the same reason the national radio broadcaster
here has been known to refer to that well-known piece by Claude Debussy, the Gollyperson's
Cakewalk.
Bearing in mind where Don and his sidekick are located, words like "slave" are probably not ones
that lightly trip off the tongue.
Alternatively, it could be Don's penchant for larding any piece of what ought to be plain speaking
with Latin to make it sound better.
By the way, you quote Don as referring to hyperthreading as meaning the CPU has multiple cores.
That's just one more error to add to the others. Multi-core CPUs have not actually been released
yet (though AMD should have one within weeks, and Intel will have one by mid-year). Hyperthreading
is in effect merely the simulation of multiple cores. True multi-core, however, is (will be) one
CPU die that actually contains two separate, physical processors within it. Initially, each core
will be single threaded, but Intel plan to eventually release multi-core hyper-threaded processes.
Brace yourself for the article Don writes that day!
Regards
HJR
cpu question
September 23, 2005 - 8am Central time zone
Reviewer: thirumaran from INDIA
Hi Tom,
Operating System: WINDOWS 2003 ADV server
RAM: 1 GB RAM
DB : ORACLE 10g Release 2
CPU : INTEL P4 UNI processor
Will oracle run efficiently for 100 concurrent oracle users (mixed transaction size using lob data
when connected to GIS server ) using the above mentioned H/W or it is recommended to go for a dual
processor .
Thanks
Thirumaran
Followup September 23, 2005 - 9am Central time zone:
Oracle will run as efficiently as it can.
But, you have to ask yourself though if 100 users would be satisified with the response time from a
machine that has less resources than my laptop......
Little sparse on memory there.
Probably the disk system isn't too hot either.
And a single cpu....
I would say it looks a bit small. But a benchmark done by you will confirm or deny that.
Number of processors and licence fee
October 6, 2005 - 4pm Central time zone
Reviewer: Laxman Kondal from Springfield, VA
Hi Tom
I have not come across so far but one of our clients had this requirement.
In multiple processors - 4 processors, box client wished to reduce it to half, so client can save
some license fee by setting some parameter or code to use only 2 out of 4.
Is there any way Oracle software can control the use of processors or processors have to be removed
physically from the box and does Oracle really count processors for license fee?
It may seem stupid question but I could not think of this and found answer any where - it's client
- whose wish to be followed/refused politely.
Thanks and regards
Followup October 6, 2005 - 6pm Central time zone:
No, the cpus would have to be unavailable somehow.
they can be disabled, they can be "domained" away (if your OS supports that).
Logical CPUs should be handled differently.
October 7, 2005 - 1am Central time zone
Reviewer: SDiZ from Hong Kong
For the best performance, Logical CPUs should always be handled different then physical CPU.
The "Logical CPUs" in Hyperthread shares the same cache, and part of the instruction pipeline. A
high loading on one logical cpu can make another logical cpu on the same chips slower.
Traditionally, OS Scheduler tends to "stick" a process to a specific CPUs to make better
utilization of memory caches. This arguement, of course, does not holds for logical CPUs.
Followup October 7, 2005 - 8am Central time zone:
But that is an OS issue - not one that the database would be looking at. It won't make the
"another logical cpu on the same chips slower", both run "slower" - meaning both run as fast as
they can.
If you have a single physical cpu that is hyperthreading - the databae needs to know that - needs
to know "2" - the algorithms change and in fact there are conceptually 2 cpus here. While one
process is doing IO or waiting for a resource (a latch) the other process on the same cpu can keep
going.
bottom line here - the cpu count wasn't, isn't set wrong, the cpu count must be set to what the OS
reports.
Hyperthreads Myths
October 7, 2005 - 4am Central time zone
Reviewer: Billy from Cape Town, ZA
SDiZ's (Hong Kong) said:
> For the best performance, Logical CPUs should
> always be handled different then physical CPU.
> The "Logical CPUs" in Hyperthread shares the same
> cache, and part of the instruction pipeline. A
> high loading on one logical cpu can make another
> logical cpu on the same chips slower.
And not just that. There are bugs in hyperthread CPU implementations that allow one process to peek
at the cached values of another process - one can gain access to encryption keys and so on that
way.
However, I'm a firm believer that one does not "interfere" with OS kernel scheduling (despite me
often doing this in R&D environments).
So yes, the OS kernel should be aware of hyperthread CPU (logical CPUs) differences.. but this has
no bearing at all at application level. Including Oracle.
Software getting involved with binding its threads and processes to different CPUs and pulling all
kinds of hacks in order to schedule itself and optimise CPU performance.. It will work nicely in
the development lab on the development server. And only there.
Software should -not- interfere with OS kernel scheduling of processes.
Software should be -aware- of the number of CPUs (logical or physical) that the OS kernel reports,
and makes it self-tuning performance decisions based on that.
NEVER SECOND GUESS THE OS KERNEL SCHEDULER. EVER!
Which sounds like what the author of the quoted article was attempting to do. "Oh, the kernel
reports 4 CPUs, but I know there is only 2 physical CPUs so I am configuring/developing/tuning as
if there are only 2 CPUs".
FWIW, I have been down that road numerous times. Always loved getting under the kernel's hood..
from configuring job queues and streams on mainframe systems in the 80's, to managing to write a NT
process (with the lowest possible priority) that managed to red-line a CPU anyway.
In my experiences.. you leave the kernel to do its job and you focus on your job. Unless of course
you hack around just for the hack of it. :-)
> Traditionally, OS Scheduler tends to "stick" a process
> to a specific CPUs to make better utilization of memory
> caches. This arguement, of course, does not holds for
> logical CPUs.
Yep. This is another myth that seems to still stick it head out at times.. that an OS kernel
scheduler will always balance the load evenly across all available CPUs. Confusion reigns when CPU0
shows 90% usage and CPU1 is idle, and the conclusion is that something is wrong as the 2nd CPU is
not used.
Or the one that states that PQ does not work on a single CPU - despite the same single CPU being
able to run 100's of application threads.
OWT - Old Wives' Tales. Like Occam's Razor. It is easier to believe in "what sounds right" from
so-called experts, than to figure out the truth oneself.
Followup October 7, 2005 - 8am Central time zone:
<quote>NEVER SECOND GUESS THE OS KERNEL SCHEDULER. EVER!</quote>
Never say Never, Never say Always, I Always say :)
But I agree with the sentiment.
OS Schedulers
October 11, 2005 - 9am Central time zone
Reviewer: SDiZ from Hong Kong
Actually, I want the Oracle RDBMS override the OS Schedulers.
Not only the CPU Scheduler, but also the IO Scheduler... The query plans have lots of infomation
about cache utilization predictions, which the OS schedulers have never take account of... With all
those infomation, the schedulars can plan ahead with real data , never readahead useless infomation
or invalidate soon to be used cache, etc.
With Linux, this is not a dream. the -mm series of kernel have a pluggable i/o scheduler
infrastructure already....
Followup October 11, 2005 - 3pm Central time zone:
Maybe in version 20 when we need no OS.
Only a guess, no promise on the release date.
Can cpu_count change dynamically
October 11, 2005 - 12pm Central time zone
Reviewer: A reader
Tom,
In most servers, it is possible to add/remove CPUs without stop/start of server. If I add/remove
CPUs while database is still running, will the cpu_count change automatically? If it does not, is
there a downside?
Thanks
Followup October 11, 2005 - 3pm Central time zone:
it is version dependent - a relatively new feature to dynamically change the number of cpus and
have things "readjust" for it.
*mostly* would affect parallel query.
Consider benchmarking your workload without HT
November 21, 2005 - 11pm Central time zone
Reviewer: Russell H from Pleasanton, CA
These articles indicate possible server performance problems with hyper-threading in Citrix and SQL
Server applications. Perhaps similar situations could occur on some workloads running in Oracle?
It may be worth running sample workloads with and without HT enabled.
http://news.com.com/Does+hyperthreading+hurt+server+performance/2100-1006_3-5965435.html?tag=nefd.to
p
http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
TLP (thread-level parallelism )
March 21, 2006 - 7am Central time zone
Reviewer: A reader
Tom,
can Oracle on Windows 2003 take advantages of multi-core processor architecture? My software vendor
says:
"To fully realize these advantages, both the applications and operating system should support
thread-level parallelism (TLP), allowing portions of programs to run independently ..."
Does Oracle supports TLP? Are there any benchmarks that prove that?
Many thanks in advance
Followup March 22, 2006 - 1pm Central time zone:
we are parallelized by the OS at the thread level on windows - always have been.
No proof via benchmarks necessary (not even sure how a benchmark of Oracle by itself would prove
anything??) - it is the way it is architected - single process, multiple threads - each thread
independent from the others
cpu_count dropped suddenly
June 15, 2006 - 12am Central time zone
Reviewer: Michael from HK
Hi Tom,
I would like to ask what will make cpu_count changed by Oracle? (Besides Hardware chang)
I am using Sun Solaris for running my 10gR1 RAC, both node has 2 (pysical and logical) CPUs. The 2
instances have cpu_count=2 at the beginning While I was checking the system heathiless, I found
that the instance 2 reported cpu_count=1. I checked the system was running with 2 CPUs normally
and the alert log of instance 2 reported that "Detected change in CPU count to 1". Do you think
there is any reason why my RAC instance changed the cpu_count without any hardware changed.
This is very odd to me, and it dose not happen on instance 1.
(At that moment, I am calling support for that.)
Thanks!!!
Michael
Followup June 15, 2006 - 8am Central time zone:
use support, could be flaky hardware, it alerts us to changes and we react to that.
Solaris CPU count
June 15, 2006 - 5pm Central time zone
Reviewer: Roderick
FYI in 10gR1, the CKPT process will periodically poll Solaris for the online cpu-count to detect if
it has changed. Perhaps the OS system call used is returning an incorrect result or Oracle is
misinterpreting the return value. The OS call made will depend on the API for that particular OS.
For Solaris, I just happen to know it will either be pset_info() or sysconf() depending on the
situation [using the truss utility against a test system can be your friend]. Otherwise, best to
handle this via Support as Tom recommends.
Thanks Tom & Roderick!
June 15, 2006 - 10pm Central time zone
Reviewer: Michael from HK
Idle CPU
August 12, 2006 - 8am Central time zone
Reviewer: Arindam Mukherjee from Kolkata, India
Respected Mr. Tom,
On reading this entire thread, where all respected big shots of Oracle have suggested their
comments, I am very sorry to ask you this simple question in this thread.
From Oracle 9i Database Performance and Tuning Guide, chapter - 22
I have got the following lines.
Events that identify that a server process is waiting for more work are known as idle events.
Excessive CPU usage usually means that there is little idle CPU on the system.
Ques 1: My query when usage is excessive, how can I say CPU resources is little idle?
Note that wait I/O should be considered as idle CPU.
Ques 2: My query at peak time, CPU may be used in full swing, so we can get wait event. How can I
say CPU is little idle?
Followup August 12, 2006 - 7pm Central time zone:
this is not a siple question.
define excessive (I'm not a fan of that term). Is 80% for 5 hours excessive? Is 95% for 2 minutes
excessive?
what is little? define little?
ques 1: not a well framed question. "when usage is excessive, how can I say..." - you cannot. You
have to define your terms and what excessive means to you.
if I have a 16 cpu box and I run a 32 way parallel query - I really want 16 cpus to light up and go
all of the way. I'd be most happy if they were near 100%
if I have a 16 cpu box and I run serial queries only for 1,000 transactional users and we run at or
near 100% capacity for extended periods (define extended :), I might not be happy.
Why would IO waits be considered "idle cpu", the only thing that is idle cpu is - well - idle cpu
time?
ques 2: what would you be waiting on? there is no "we were waiting on cpu" wait event.
Dual-core processor
February 14, 2007 - 2am Central time zone
Reviewer: Rajesh from India
Hi Tom,
I would like to know if a single duo core could support max 50 concurrent users.
Or if I should go with a greater number of processors.
What are the performace benefits of using duo core with Oracle.
Is there any standard metrics used for sizing hardware?
Thanks,
Rajesh
Followup February 14, 2007 - 8am Central time zone:
define concurrent.
of course it could - if they do certain things
of course it could not - if they do certain things
so the answer is of course:
yes, no, maybe.
or:
IT DEPENDS
Question on cpu_count
April 1, 2009 - 1pm Central time zone
Reviewer: Tom from Charlottesville, VA USA
I have one question about cpu_count. We were trying to set the db_32k_cache_size parameter to
enable multiple blocksizes in our database, but our db server is a Sun 5240 that reports 128 cpus,
and the default value for db_32k_cache_size was throwing errors. We can get past this by setting
the cpu_count parameter to a much lower value but I was wondering what other side effects this
would have in our system as cpu_count is used for more than this one parameter.
Followup April 1, 2009 - 4pm Central time zone:
well, what was your error first?
question on cpu_count
April 2, 2009 - 9am Central time zone
Reviewer: tom from Charlottesville, VA USA
SQL> alter system set db_32k_cache_size=4M;
alter system set db_32k_cache_size=4M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
SQL> show parameter cpu_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 128
SQL> show parameter db%cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string ON
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2192M
sga_target big integer 2192M
SQL>
and if we do this it works
SQL> alter system set cpu_count=2;
System altered.
SQL> alter system set db_32k_cache_size=4M;
System altered.
SQL> show parameter db%cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 16M
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string ON
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL>
Also, according to the docs I would expect the db_32k_cache_size to be 8M (4M * 2 cpus) but it is
always double what I expect.
We are using Soalris containers on this box and not all 128 cpus should be dedicated to Oracle but
I was wondering if there were any side effects we should be aware of when setting cpu_count?
Followup April 2, 2009 - 10am Central time zone:
that just means your sga target/max size was too small - when cpu count is high, you need a larger default buffer cache.
select component, granule_size from v$sga_dynamic_components;
would show you what the granules are going to be. And with a 2gb SGA, I would fully expect 16mb granules.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#sthref380
Granule size is determined by total SGA size. Generally speaking, on most platforms, if the total SGA size is equal to or less than 1 GB, then granule size is 4 MB. For SGAs larger than 1 GB, granule size is 16 MB. Some platform dependencies may arise. For example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 1 GB. Consult your operating system specific documentation for more details.
setting the cpu count down will impact all of the defaults that depend on cpu count - mostly the parallel settings.
|