Re: Sun T2000

  • From: "Alexander Fatkulin" <afatkulin@xxxxxxxxx>
  • To: "Kevin Closson" <kevinc@xxxxxxxxxxxxx>
  • Date: Mon, 26 Mar 2007 10:10:12 +1100

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
--
//www.freelists.org/webpage/oracle-l


Other related posts: