Query Performance Question

From: Paul Izzo <paul.izzo_at_mosca-ag.com>
Date: 1 Mar 2005 01:08:06 -0800
Message-ID: <1109668086.800351.246090@f14g2000cwb.googlegroups.com>

  I'm running a 9i database with OLAP enabled. I'm looking to run 2 queries on the same table. The 2 queries are pretty much identical except for 2 variables.

  The problem that I'm having is that 1 query works and the other doesn't and I don't know why. Here's an example of the query that I'm running:

  select distinct(papp.instruction_nr),papp.mach_proc,papp.positions_nr   from w100.papp,

(select row_number() over (partition by papp.instruction_nr

             order by papp.positions_nr) rn
      from w100.papp)x

  where x.rn = 2
  and papp.mach_proc = '3530'

  This query works perfectly I get all WORK INSTRUCTIONS that have MACH_PROC '3530' as the second step. The query searches through 2492 records.

  I run a similiar query that looks for the 3rd step of an WORK INSTRUCTION containing MACH_PROC '8860'. This query searches through 35273 records. Here's what that query looks like:

  select distinct(papp.instruction_nr),papp.mach_proc,papp.positions_nr   from w100.papp,

(select row_number() over (partition by papp.instruction_nr

             order by papp.positions_nr) rn
      from w100.papp)x

  where x.rn = 3
  and papp.mach_proc = '8860'

  Although the 2 queries look almost identical they run totally different. When I run the first query it max's out my CPU for the duration of the query and when looking at the session in Enterprise Manager it contains an extra tab called "Long Operations".

  The Long Operations tab tells how many blocks were processed and how long the query should last.

  When I run the 2nd query the CPU is not maxed out. The CPU usage hovers between 10-30%. When I look in Enterprise Manager I don't see a tab for "Long Operations"

  Too test the query I narrowed down it's search records. I added an extra line at the bottom of the query containing:

  and ( ( "instruction_nr" LIKE '0101-010000%') )

  This brings the query down to 30 records, but the same problem occurs.

  I also added this extra line into the query that works and it performs like it did before only faster because it searches less records.

  I don't know why this is happening. Received on Tue Mar 01 2005 - 03:08:06 CST