Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Posts
    57

    Question Unanswered: Strange Things Happening with 2 Queries

    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:

    Code:
    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:

    Code:
    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:

    Code:
    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.

  2. #2
    Join Date
    Nov 2004
    Posts
    57
    Can this be that my instance is not defined correctly? Perhaps my SGA is too small to handle too large a record count?

  3. #3
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    Can you do an autotrace on the queries and show the output? I'm guessing the one that performs badly is doing an insanely high amount of consistent gets. I've seen this behavior before, usually when bitmap indexes are involved. Do you have bitmap indexes on any of these columns?

    Do this:

    Code:
    CONNECT / AS SYSDBA 
    @$ORACLE_HOME/sqlplus/admin/plustrce.sql
    Then connect as your database user that runs the query and run:

    Code:
    @$ORACLE_HOME/rdbms/admin/utlxplan.sql
    After that, you can type:

    Code:
    set autot trace
    and run your queries, displaying the output. Then put it here.
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Whats happening is that Oracle is looking at the value of papp.mach_proc and thinks the second query's value merits a different execution plan.

    Have a look at both execution plans and decide if it is using your indexes correctly or maybe it needs a new index. Then try using either dbms_stats or analyze and do a detailed analysis with histograms on the papp table. If it still doesnt work rewriting your query slightly may give the optimizer enough of a clue.

    If this still doesnt work then you can either use hints (but may cause problems in the future if your data distribution changes), or look the optimizer parameters in your init.ora (not to be taken lightly as it can affect all your queries).

    Alan

  5. #5
    Join Date
    Nov 2004
    Posts
    57
    I looked a little deeper into which "machine processes work and which don't. I have 285 different "machine processes".

    I did a random test and it seems that "machine processes" with record counts larger than 5,120 don't work. Here's a condensed list of "machine processes" that work and don't work.

    Process # | Record Count
    3430 1,130 Works
    3530 2,492 Works
    3300 3,950 Works
    4100 4,593 Works
    36130 5,120 Works
    5600 6,052 Doesn't Work
    5100 6,252 Doesn't Work
    3500 6,897 Doesn't Work
    8550 16,302 Doesn't Work
    8660 35,266 Doesn't Work

    There seems to be some sort of limit that is being reached that I cannot do my query with record counts larger than 5,120. I don't think that it's a problem with my data because all my data is being pulled from the same table.

    I think there might be something wrong with either the way my instance is configured or with the way my SQL statement is written or both.

  6. #6
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    Yes, but we cannot tell what that would be without seeing an explain plan. Did you try to do what I mentioned above? This way we can diagnose what the query is trying to do and why.
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  7. #7
    Join Date
    Nov 2004
    Posts
    57
    Steve,

    I tried what you said but I couldn't get it to work. Attached I have a graphic of the SQL statement execution steps
    Attached Thumbnails Attached Thumbnails 1.bmp  

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    can you rewrite the query thus:

    Code:
    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
          where (instruction_nr) in (select instruction_nr from w100.papp where  mach_proc='8860'))x
      where x.rn = 3
      and papp.mach_proc = '8860'

    It would probably help to have an index on either (mach_proc) or (mach_proc, instruction_nr).

    Alan
    Last edited by AlanP; 03-03-05 at 16:51. Reason: replaced primary key by instruction_nr

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I believe your two first query could be rewrite as:
    Code:
    select *
      from (
    select papp.*,
           row_number( ) over( partition by papp.instruction_nr order by papp.positions_nr ) rn
      from w100.papp
     where papp.mach_proc = '3530' 
           )
     where rn = 2
    ..avoiding the two access to the table in both querys to just one ( and the sort on top of that due to distinct ). Also, your query must be trowing wrong results at a time if papp.positions_nr happens to be non-unique ( row_number( ) will assings sequentially numbers to the same papp.positions_nr's regardless of being the same -- consider dense_rank() or rank() on those cases ).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •