Memory operations on Sun/Oracle M class servers vs T class servers
- From: "Jorgensen, Finn:(BSC)" <Finn.Jorgensen@xxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Dec 2014 20:42:24 +0000
Good afternoon,
I'm mostly lurking on the list these days but I've run into an interesting
situation that I can't quite explain.
Oracle version is 11.2.0.4.
A client of mine moved their database from a Sun M5000 to a T52 server. The
storage was just moved over so the filesystem the database sits on is literally
the exact same as it has always been. After the move the client started
complaining that certain processes started taking 1.5-2x longer than they used
to and they could back this claim up with log data.
I'll spare you all the digging we've done to find out why but the result of it
is that we've run a small test, first on a M5000 and then on a T52. The test,
very simply stated does this:
Declare
Cursor c is select f1, f2, f3 from tableA;
Begin
For v in c loop
Select count(*) into l
From tableB b where b.f1 = v.f1 b.f2 = c.f2 and b.f3 = c.f3;
End loop;
End;
The cursor c select 1.7 million rows. The second cursor does a unique key
lookup in tableB by the 3 columns f1, f2 and f3. I've set the SGA up so the
whole table and index of both tables will fit so there's no IO the second time
this test is run.
Now for the interesting part. The test consistently runs in 53 seconds on the
m5000 and 1 minute 03 seconds on the T52 despite the fact the CPU's are 2150Mhz
on the m5000 and 3600Mhz on the T52. Looking at the spec sheets for the servers
I see the m5000 architecture has 5MB level 2 cache for each socket whereas the
T52 only has 8MB level 3 cache shared for all (both) sockets. That could/would
lead to faster memory operations on the m5000.
I then ran the test with 10046 tracing turned on. It's a long and very
repetitive trace file but there's essentially 2 things going on over and over
again. EXEC and FETCH of the same cursor. I notice difference in the elapsed
time of the EXEC call between the 2 server types like this:
M5000
EXEC
#18446744071436112528:c=136,e=137,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1003137786,tim=81284649836325
FETCH
#18446744071436112528:c=10,e=8,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=1,plh=1003137786,tim=81284649836347
T52
EXEC
#18446744071429849600:c=210,e=209,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1003137786,tim=15793928374634
FETCH
#18446744071429849600:c=9,e=9,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=1,plh=1003137786,tim=15793928374663
As you can see the elapsed time is 209 microseconds on the m5000 and only 137
on the m5000. These are typical values for the 1.7 million iterations.
Where my question comes in is, if memory operations are what's slowing the T52
down then why is the EXEC call the slow part when cr=0 and the FETCH call is
the same when cr=4?
In a query like this one, what does the EXEC call even do if there's no
physical IO and there's no logical IO yet it takes 200 microseconds?
Thanks for any help with this one.
Finn
This e-mail and any attachments are confidential, may contain legal,
professional or other privileged information, and are intended solely for the
addressee. If you are not the intended recipient, do not use the information
in this e-mail in any way, delete this e-mail and notify the sender. -EXCIP
Other related posts:
