I am attempting to extract daily averages of the physical read and write rates for all Windows servers. Because the data in the CAXLCLDD table is stored by disk instances, though, I have to write a subquery to sum up the read and write rates across instances, then embed that subquery in a larger query to calculate the averages.This subquery extracts hourly sums (and does so fairly quickly), but I need averages of the sums over one or more dates, not just the sums:
SELECT LOWER(CAXNODES.COMPUTER_NAME) AS HOST,
CAXINTVL.INT_START_TIME, CAXINTVL.INT_START_DATE,
SUM(CAXLVLDD.T09READS) AS READS_SUM,
SUM(CAXLVLDD.T09WRITES) AS WRITES_SUM
FROM CAXDSKS, CAXNODES, CAXINTVL
JOIN CAXLVLDD ON CAXINTVL.INDEXX = CAXLVLDD.INTVL
WHERE LOWER(CAXNODES.COMPUTER_NAME) IN ('xyzsrv044', 'xyzsrv045')
AND CAXINTVL.INT_START_DATE BETWEEN '09/04/07' AND '09/04/07'
AND CAXLVLDD.T09READS >= 0
AND CAXINTVL.INTTYPE='M' AND CAXINTVL.INT_DURATION = 60
AND CAXINTVL.INT_START_TIME BETWEEN '0000' AND '2359'
AND CAXLVLDD.NTDISKS = CAXDSKS.INDEXX /* Critical! */
AND CAXLVLDD.NODES = CAXNODES.INDEXX
GROUP BY CAXNODES.COMPUTER_NAME,
CAXINTVL.INT_START_DATE, CAXINTVL.INT_START_TIME
This is where I want to go, more or less, except that the query performance is quite awful, even limited to a couple of nodes; note the AVG(IOSUM.READS_SUM) and AVG(IOSUM.WRITES.SUM) in the second and third lines:
SELECT IOSUM.HOST,
AVG(IOSUM.READS_SUM) AS READS_MEAN,
AVG(IOSUM.WRITES_SUM) AS WRITES_MEAN
FROM CAXINTVL, CAXNODES, CAXDSKS,
(SELECT LOWER(CAXNODES.COMPUTER_NAME) AS HOST, /* start of subquery */
CAXINTVL.INT_START_TIME, CAXINTVL.INT_START_DATE,
SUM(CAXLVLDD.T09READS) AS READS_SUM,
SUM(CAXLVLDD.T09WRITES) AS WRITES_SUM
FROM CAXDSKS, CAXNODES, CAXINTVL
JOIN CAXLVLDD ON CAXINTVL.INDEXX = CAXLVLDD.INTVL
WHERE CAXNODES.COMPUTER_NAME IN ('xyzsrv044', 'xyzsrv045')
AND CAXINTVL.INT_START_DATE BETWEEN '09/04/07' AND '09/04/07'
AND CAXLVLDD.T09READS >= 0
AND CAXINTVL.INTTYPE='M'
AND CAXINTVL.INT_START_TIME BETWEEN '0000' AND '2359'
AND CAXLVLDD.NTDISKS = CAXDSKS.INDEXX /* Critical! */
AND CAXLVLDD.NODES = CAXNODES.INDEXX
GROUP BY CAXNODES.COMPUTER_NAME,
CAXINTVL.INT_START_DATE, CAXINTVL.INT_START_TIME
) IOSUM /* end of subquery */
GROUP BY IOSUM.HOST
ORDER BY HOST
My basic question: How do I get an average of the sums across disk instances with reasonable query performance? The answer also needs to bear in mind that once I get this working, the eventual query also has to do the same thing for service time and several other Windows metrics (CAXLVLDD.T09SVC_TIME, .T09QUEUE_LEN, .T09AVG_RD_TIME, .T09AVG_WR_TIME, and .T09TOTAL_IO). For some reason, Oracle reports a query plan that includes a bunch of MERGE JOIN CARTESIAN operations, which as we all know is a Bad Thing. Can I use a JOIN to make this thing run faster? Between Oracle and the database, Oracle reports Invalid Identifier if I try to link tables using JOIN, but the query plans are awful if I stick with the examples that use WHERE to link tables.
Thanks!
J Q Phantom
