RE: Rough order of magnitude for rows/second

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 31 Dec 2004 11:05:18 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87BBD@irvmbxw02>

This will give you a baseline to work from: For an OLTP system using Oracle database software, you should be able to do 1,184,893 transactions per minute with an Oracle database. Anything less and you have insufficient CPU, RAM or I/O.

http://www.tpc.org/tpcc/results/tpcc_perf_results.asp

-----Original Message-----

sol beach

Yes, I realize "it depends" on many factors. Assume sufficient CPU, RAM & I/O exist; a lightly loaded OLTP system For the query below what is the rough order of magnitude for rows/second being read

        1
      10
     100

    1000
   10000
  100000
 1000000
10000000

SELECT d.product_id id,

       d.name data,

       NULL url,

       COUNT(DISTINCT li.customer_id) customers,

       COUNT(DISTINCT li.order_id) orders,

       SUM(li.price) revenue,

       SUM(li.shipping) shipping,

       SUM(li.qty) products,

       DECODE(COUNT(DISTINCT li.order_id), 0, 0, SUM(li.price)/COUNT(DISTINCT li.order_id)) avg_selling_price,

       SUM(li.discount) discount

FROM DW_2000000010525.line_item li,

       DW_2000000010525.product d

WHERE li.date_created >=3D TO_DATE('2004-12-23','YYYY-MM-DD')

AND li.date_created < TO_DATE('2004-12-24','YYYY-MM-DD')

AND li.product_id =3D d.product_id

AND li.prod_category_id =3D 3611326867412

GROUP BY d.name,

       d.product_id,

       NULL ORDER BY ORDERS DESC,        d.product_id
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Dec 31 2004 - 13:04:00 CST