Re: Interpretation of Explain Plan to Prevent IO Usage Error

In article <352E6CE8.A7355B1A_at_uswest.com>,   rxruck2_at_uswest.com wrote:
>
> 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
>

Hi, Ryan.
 It looks to me that your plan is OK. It's quite possible that your  SELECT statement is making a lot of I/O ( depends on number of rows  retrieved ).
 Try to eveluate how many rows you are reading by running SQL_TRACE  and TKPROF.

 Michael.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Sun Apr 12 1998 - 15:08:28 CDT