Diff betn exec plan in appl and sqlplus
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Diff betn exec plan in appl and sqlplus

  1. #1
    Join Date
    Jan 2001
    Posts
    216

    Diff betn exec plan in appl and sqlplus

    Please explain how this can be possible..

    One of the queries in our application was running very slow. I ran statspack during that time and obtained the query which consumed most resources (cpu and tons of buffer gets). This is the same query that is seen when I "Edit" the session details on OEM. ie this query is marked as a long running query on OEM.

    Now that I know the problem query, to debug further, I turned on tracing (10046) for that session and obtained the bind variable values for the query. Plugged these values into the query and ran it from sqlplus. It runs extremely fast in sqlplus. How is that possible?

    Then I turned on tracing in sqlplus and obtained the execution plan and compared it with the execution plan of the query from the application (this was available in Session Details of OEM as well as the trace file). One from the application (both oem and trace file) shows cartesian joins and one from the sqlplus session shows hash joins.

    Why the difference? I am lost now.. at the end of my debugging adventure! please help!

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    how does the app connect to the database?

    are your stats up to date - it can make a different when using binds instead of literals

  3. #3
    Join Date
    Jan 2001
    Posts
    216
    apps connect through JDBC. Stats were generated on Sunday afternoon and are current as not much activity on the db since then.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by chikkodi
    I turned on tracing (10046) for that session and obtained the bind variable values for the query. Plugged these values into the query and ran it from sqlplus.
    What do you mean by that exactly? Did you actually replaced the occurancess of bind variables in the query with their litteral values?

    If you turned the original query that was something like that:
    Code:
    select * from emp where ename = :1;
    into something like that (by replacing a bind with a litteral value):
    Code:
    select * from emp where ename = 'SCOTT';
    then those two queries are totaly different things for the optimizer.

    Or did you define a variable in SQL*Plus, assign it a value and run your query?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jan 2001
    Posts
    216
    I replaced the values literally in the bind variable. Let me retry it the correct way by defining bind variables.

  6. #6
    Join Date
    Jan 2001
    Posts
    216
    You guys are way too good!!
    Using bind variables, the query is slow on sqlplus also!!
    Unfortunately that also means I have more work to do :(
    Need to fix it now. Thanks a bunch!

  7. #7
    Join Date
    Jan 2001
    Posts
    216
    An update on this -
    Noticed that the query was running very fast on the test system which is a clone of the production. The only difference between the test system and the production system is, the tables in the production system were analyzed last weekend. So, as a first try, I deleted the statistics on the tables referred to in the query and boom! the sql ran very fast in production too! The report which was running for the past 2 days finished in a matter of minutes! Now.. how can this be explained ?? I then tried it several times on test too. Everytime I analyze the tables, the query runs slow (uses cartesian joins). I delete statistics and the query runs fast. Do you think we might be having some unnecessary indexes on the tables?

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I suspect cardinality issue gather stats on that column with histograms and let us know what happens.

    regards
    Hrishy

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Use 10053 event to konw about why CBO chooses the wrong path, and fix them. There may be several reasons that the CBO uses wrong execution plan.

    If nothing works, then go for plan stability.

    Tamil

  10. #10
    Join Date
    Jan 2001
    Posts
    216
    Just wanted to update you all on this issue as it has been resolved (yeah!!!)

    The problem got solved when I changed the statistics generation procedure to compute stats instead of estimate. All queries are superfast now.

Posting Permissions

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


Click Here to Expand Forum to Full Width