This discussion is archived
7 Replies Latest reply: Nov 27, 2013 11:26 PM by AnkitV RSS

Why the query access path is different !

AnkitV Newbie
Currently Being Moderated

Hi All

 

I have a query which is running satisfactorily in TEST but is taking a long time in PROD.

SELECT * FROM MTHLY_PROD_BASE WHERE START_DATE >= to_date('1/1/2013','mm/dd/yyyy') AND START_DATE < to_date('1/1/2014','mm/dd/yyyy')

MTHLY_PROD_BASE is a view, containing some underlying tables and views which are doing summations and groupings of data.

I have access plans for it in TEST and PROD in jpg format, but I am not able to attach here as the "Insert Image" button is inactive for me. How can I attach them for explaining my issue properly ?

 

There was some memory added around a week ago to the DB server for improving performance of another database, but since then the DB in which I ran the query is performing poorly.

Data for underlying tables, being referenced in the query, in TEST is same as in PROD, but the plans are different.

 

I need to know what factors may cause the access path for a query to change, so that I can investigate what else might have got changed/impacted in PROD ?

 

Thanks

  • 1. Re: Why the query access path is different !
    sybrand_b Guru
    Currently Being Moderated

    1 You didn't post a four digit database version

    2 You didn't post platform info

    3 You didn't post the SQL statement

    4 You didn't post the explain plans (there is no need to do that in JPEG format)

    5 Factors causing the access path to change are version dependent and described in online documentation, and no one can provide a link, as there is no version info in your post.

     

    What help do you require?

     

    -------------

    Sybrand Bakker

    Senior Oracle DBA

  • 2. Re: Why the query access path is different !
    AnkitV Newbie
    Currently Being Moderated

    Hi all

     

    1) version info of TEST and PROD:

    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

    PL/SQL Release 11.2.0.2.0 - Production

    "CORE    11.2.0.2.0    Production"

    TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production

    NLSRTL Version 11.2.0.2.0 - Production

    2) Platform is Aix 6.1.0.0

    3) Query as mentioned in initial post too :SELECT * FROM MTHLY_PROD_BASE WHERE START_DATE >= to_date('1/1/2013','mm/dd/yyyy') AND START_DATE < to_date('1/1/2014','mm/dd/yyyy')

    4) As I mentioned, I do not have plan in text format, and its lots of lines in the

    .

     

    I need to know what might be the reason for change in the access mode for the query in PROD, which has caused it to take 3-4 hrs instead of 30 mins ?

     

    Any help will be highly appreciated.

  • 3. Re: Why the query access path is different !
    sb92075 Guru
    Currently Being Moderated

    AnkitV wrote:

     

    Hi all

     

    1) version info of TEST and PROD:

    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

    PL/SQL Release 11.2.0.2.0 - Production

    "CORE    11.2.0.2.0    Production"

    TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production

    NLSRTL Version 11.2.0.2.0 - Production

    2) Platform is Aix 6.1.0.0

    3) Query as mentioned in initial post too :SELECT * FROM MTHLY_PROD_BASE WHERE START_DATE >= to_date('1/1/2013','mm/dd/yyyy') AND START_DATE < to_date('1/1/2014','mm/dd/yyyy')

    4) As I mentioned, I do not have plan in text format, and its lots of lines in the

    .

     

    I need to know what might be the reason for change in the access mode for the query in PROD, which has caused it to take 3-4 hrs instead of 30 mins ?

     

    Any help will be highly appreciated.

    different results occur when something is different between the two environments.

    Since we don't have access to either system, YOU are the only one who can actually compare & contrast between the two systems.

     

    HOW To Make TUNING request

    https://forums.oracle.com/forums/thread.jspa?threadID=2174552#9360003

  • 4. Re: Why the query access path is different !
    sybrand_b Guru
    Currently Being Moderated

    Look,

    there are basically two kind of tables, fact tables and lookup tables.

    Lookup tables are reference tables and small, fact tables are potentially huge.

     

    CBO is all about : cardinality.

    When you increase the cardinality of a fact table in production, so it is not anymore in the same range as lookup tables, COMPLETELY different access paths can easily result.

     

    With the minimum information you have chosen to provide that's all what can be said about your issue.

    If you don't help those that want to help you out, so can not get more accurate responses.

     

    ----------

    Sybrand Bakker

    Senior Oracle DBA

  • 5. Re: Why the query access path is different !
    rp0428 Guru
    Currently Being Moderated
    4) As I mentioned, I do not have plan in text format, and its lots of lines in the

    Why not? Oracle produces them in 'text format'. So someone must have changed the format. Find that 'someone' and get the original plans and post them.

     

    Also something as simple as missing or out-of-date statistics for the table and indexes can easily cause your problem.

     

    Post the exact command used to collect those statistics and post the query you used to verify that the stats are current.

  • 6. Re: Why the query access path is different !
    jgarry Guru
    Currently Being Moderated

    I presume you have a screen print or output from EM with the explain plan.  It's buried in the how to ask a question faq, but here is how to get the text version of the plan: HOW TO: Post a SQL statement tuning request - template posting.  Hopefully, you have the plan hash value already.  Unfortunately, the formatting instructions there are out of date, simply use the advanced editor and change the font of the explain plan to Courier New.

  • 7. Re: Why the query access path is different !
    AnkitV Newbie
    Currently Being Moderated

    Hi All

     

    I found that issue was due to the fact that main table was highly fragmented, which caused the FULL table scan to take lots of time and hence probably caused change in access path.

    Following query revealed the fragmentation :

     

    select a.owner,a.table_name,round(b.bytes/1024/1024) CURRENT_SIZE_IN_MB,

    round((a.num_rows*a.avg_row_len)/1024/1024) ACTUAL_SIZE_IN_MB,

    round(((b.bytes/1024/1024-((a.num_rows*a.avg_row_len)/1024/1024))/(b.bytes/1024/1024))*(100-a.pct_free),2) FRAG_PERCENT from dba_segments b,dba_tables

    a where a.owner=b.owner and b.bytes/1024/1024>100 and b.segment_name=a.table_name 

     

    I rebuilt table/indexes using "alter table move" and "alter index rebuilt online" commands, and things are good now.

     

    Thanks a lot to you all for giving valuable suggestions, and I am sorry, still I am doing followup with the guy who gave me plans in image format, he is too busy to again do explain plans and provide me in text format :)) ...

     

    Thanks a lot.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points