Re: Sun T2000

From: Alexander Fatkulin <afatkulin_at_gmail.com>
Date: Mon, 26 Mar 2007 10:10:12 +1100
Message-ID: <49d668000703251610s571f98f8j84d432dc9b979cfc@mail.gmail.com>

> Interesting that the fetch count is different.

That is because Proliant were running with default SQL*Plus fetch size of 15 while T2000 with 100.

>Since there was no PIO,
>the Proliant would likely get better performance without indirect data
>buffers BTW.

Indeed but I can't bounce it.

> I'd like to see somehting that runs a bit longer such as JL Computing
> Index
> http://www.miraclebenelux.nl/jloci.html I'd also like to have
> measurements from my silly little benchmark
> (http://kevinclosson.wordpress.com/2007/01/30/oracle-on-opteron-with-lin

Interesting - results are quite different:

Proliant:

select count(*)

   from (

     select n
     from cpu_test_dummy
     connect by n > prior n
     start with n = 1 )

   where rownum < 20000

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     77.16      75.42          0      20015          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     77.17      75.42          0      20016          0           1

T2000:

select count(*)

   from (

     select n
     from cpu_test_dummy
     connect by n > prior n
     start with n = 1 )

   where rownum < 20000

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     69.96      68.30          0      20015          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     69.96      68.31          0      20016          0           1

Looks like the cache subsystem of the T2000 is pretty good so the local (in relation to a cache) algorithms can benefit.

I'll continue with some simple lookup-table test:

create table lookup
(

	n,
	m,
	constraint pk_lookup primary key (n)
) organization index as select level, level
	from dual
	connect by level <= 500000;

Proliant:

declare
 l_n number;
begin
 for i in 1 .. 500000
 loop
  select m into l_n from lookup where n=i;  end loop;
end;

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.01       0.01          0          3          0           0
Execute      1     28.66      30.14          0    1000004          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     28.67      30.16          0    1000007          0           1

T2000:

declare
 l_n number;
begin
 for i in 1 .. 500000
 loop
  select m into l_n from lookup where n=i;  end loop;
end;

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.04       0.03          0          3          0           0
Execute      1    131.05     132.07          0    1000004          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    131.09     132.11          0    1000007          0           1

That again - seems what as long as there is no locality of memory references the T2000 is just slow.

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

Received on Sun Mar 25 2007 - 18:10:12 CDT