Interpretation of Explain Plan to Prevent IO Usage Error

From: Ryan Rucker <rxruck2_at_uswest.com>
Date: Fri, 10 Apr 1998 14:03:04 -0500
Message-ID: <352E6CE8.A7355B1A@uswest.com>

Hello,

I have a query (included below) that I need some performance tuning tips on.

When I run this query, I get an "ORA-02395: exceeded call limit on IO usage"
error.

In a very small nutshell, I'm first looking for a row that has a part code
beginning with "9", then doing a join to get all the rows that have the same
order number as the row that has a part code beginning with "9" (making up one
complete order) and ignoring the ones that start with "X" in the order data. Then, of those rows, I want the row with the max line number that's less than the row with a part code beginning with "9" and with a part code = 'INV'. I know it sounds confusing.

Here's what the query looks like:

select count(*) from order_section os1, order_section os2 where os1.part_code like '9%' and
os1.order_number = os2.order_number and substr(os2.order_data, 1, 1) != 'X' and os1.line_number =
(select max(os3.line_number) from order_section os3 where os1.order_number = os3.order_number and os1.line_number > os3.line_number and
os3.part_code = 'INV'
);

And here's what the explain plan looks like:

Query Plan

SELECT STATEMENT Cost =
SORT AGGREGATE
FILTER

      NESTED LOOPS
        TABLE ACCESS BY ROWID ORDER_SECTION
          INDEX RANGE SCAN I_ORDER_SECTION_PART_CODE
        TABLE ACCESS BY ROWID ORDER_SECTION
          INDEX RANGE SCAN SYS_C003705
      SORT AGGREGATE
        TABLE ACCESS BY ROWID ORDER_SECTION
          INDEX RANGE SCAN I_ORDER_SECTION_PART_CODE

11 rows selected.

I've read through the bit in Osborne's Complete Reference, as well as what
the Oracle book Oracle 7 Tuning has to say about explain plan, but I really
don't understand how to interpret it that well. I'm trying to figure out a
way to get this query to run, preventing the IO usage error I'm getting.

If anyone has any specific or general tips about where to start, I'd appreciate the help.

Ryan Received on Fri Apr 10 1998 - 14:03:04 CDT