DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Performance issues

  1. #1
    Join Date
    Jun 2003
    Location
    australia
    Posts
    74

    Performance issues

    Hi all

    We have a production instance (PRD) running on a Sun Solaris 5.8. And every week we refresh a development instance (DEV) onto another machine (Sun Solaris 5.8) with the copy of production. There is a job (which is basically a series of select statements) which takes just 3 secs on the developemnt instance, but the same job takes more than 4 minutes on production. I have checked explain plan and it looks simillar on both. I have executed the follwoing query every 5 secs and noticed changing values for "db file sequential read" in v$session for the SID which is running the job on PRD. The table below shows some of the columns and values.

    SQL> SELECT * FROM v$session_wait where sid =75;

    --------------------------------------------------------------
    |EVENT |P1TXT|P1|P2TXT|P2 |P3TXT |P3|WAIT |SECONDS|STATE |
    | | | | | | | |-TIME|_INWAIT| |
    --------|-----|--|-----|------|------|--|-----|-------|-------
    db file |file#|22|block|243936|blocks| 1| -1 | 1 |WAITED|
    sequenti| | |# | | | | | |KNOWN |
    read | | | | | | | | |TIME |
    --------------------------------------------------------------
    db file |file#|10|block|234942|blocks| 1| -1 | 0 |WAITED|
    sequenti| | |# | | | | | |KNOWN |
    read | | | | | | | | |TIME |
    --------------------------------------------------------------
    db file |file#|10|block|341877|blocks| 1| -1 | 0 |WAITED|
    sequenti| | |# | | | | | |KNOWN |
    read | | | | | | | | |TIME |
    --------------------------------------------------------------
    db file |file#|10|block|156507|blocks| 1| -1 | 0 |WAITED|
    sequenti| | |# | | | | | |KNOWN |
    read | | | | | | | | |TIME |
    --------------------------------------------------------------

    Can someone please guide what might be wrong and guide me to some documentation where I can find help for this problem?

    Thanks in advance
    rajorcl

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Post the plans..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Does your prd instance have alot of updating going on?

    What db version?

    tahiti.oracle.com has some good documentation.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  4. #4
    Join Date
    Jun 2003
    Location
    australia
    Posts
    74

    Here is the explain plan

    Sorry I was wrong the explain plan looks different here it is:

    ON DEV
    --------------

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------
    | Id| Operation | Name | Rows |Bytes|Cost|
    ----------------------------------------------------------------------------
    | 0| SELECT STATEMENT | | 1| 36 | 672|
    | 1| SORT AGGREGATE | | 1| 36 | |
    |* 2| HASH JOIN SEMI | | 1| 36 | 672|
    |* 3| TABLE ACCESS FULL | CONTRACT_FEE_ASS_RATE | 10607| 165K| 34 |
    | 4| TABLE ACCESS BY INDEX ROWID| STUDENT_COURSE_ATTEMPT| 89|1780 | 635|
    |* 5| INDEX RANGE SCAN | SCA_FC_FK_I | 1| | 46|
    ----------------------------------------------------------------------------


    Predicate Information (identified by operation id):

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------
    2 - access("CFAR"."PERSON_ID"="SCA"."PERSON_ID" AND
    "CFAR"."COURSE_CD"="SCA"."COURSE_CD")
    3 - filter("CFAR"."END_DT" IS NULL)
    5 - access("SCA"."FEE_CAT"='INT-OFF')
    filter(UPPER("SCA"."FEE_CAT")='INT-OFF')

    Note: cpu costing is off

    22 rows selected.


    ON PRD
    ---------------

    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------
    | Id| Operation | Name | Rows |Bytes| Cost(%CPU)|
    -------------------------------------------------------------------------------------
    | 0| SELECT STATEMENT | | 1| 36 | 125 (4)|
    | 1| SORT AGGREGATE | | 1| 36 | |
    | 2| NESTED LOOPS SEMI | | 1| 36 | 125 (4)|
    |* 3| TABLE ACCESS FULL | CONTRACT_FEE_ASS_RATE | 10607| 165K| 34 (0)|
    | 4| BITMAP CONVERSION TO ROWIDS | | | | |
    | 5| BITMAP AND | | | | |
    | 6| BITMAP CONVERSION FROM ROWIDS| | | | |
    |* 7| INDEX RANGE SCAN | SCA_PK | 1| | |
    | 8| BITMAP CONVERSION FROM ROWIDS| | | | |
    |* 9| INDEX RANGE SCAN | SCA_FC_FK_I | 1| | |
    -------------------------------------------------------------------------------------

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    3 - filter("CFAR"."END_DT" IS NULL)
    7 - access("CFAR"."PERSON_ID"="SCA"."PERSON_ID" AND
    "CFAR"."COURSE_CD"="SCA"."COURSE_CD")
    filter("CFAR"."COURSE_CD"="SCA"."COURSE_CD" AND
    "CFAR"."PERSON_ID"="SCA"."PERSON_ID")
    9 - access("SCA"."FEE_CAT"='INT-OFF')
    filter(UPPER("SCA"."FEE_CAT")='INT-OFF')

    24 rows selected.

    I was wondering if turning the parameter "_b_tree_bitmap_plans" to false might help? But how come the execution plans are different when DEV is an exact copy of PRD? And all the init parameters are exactly the same.
    rajorcl

  5. #5
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Hi,

    Check whether the index "SCA_PK" is present in DEV environment. It is likely that this index may be present in PROD and the new explain plan of using "index range" scan may be slow. If this index is not used by any other query in the PROD, you can think about dropping this index in PROD.
    -nagarjuna

  6. #6
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Compare the table and index statistics. If you have the same version of Oracle, same data, and different statistics .. your execution plan could easily be different.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  7. #7
    Join Date
    Jun 2003
    Location
    australia
    Posts
    74

    no change in stats

    Dear Nagarjuna

    The DEV has the sca_pk index and is vastly used, so dropping the index is out of question.


    Dear Ken

    The DEV database is refreshed from a cold backup of PRD. everything is exactly the same as on PRD including the oracle version, OS etc. How can the statistics change?

    I have noticed that if I change the optimizer_mode to rule for that particular session on PRD the select is as good as DEV. But in reality both PRD and DEV have choose as optimizer_mode. Is there something that I might be missing? Please help me.
    rajorcl

  8. #8
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Get us the output of the following in both PROD and DEV

    show parameter db_file_multiblock_read_count
    show parameter area_
    show parameter join
    show parameter cost
    -nagarjuna

  9. #9
    Join Date
    Jun 2003
    Location
    australia
    Posts
    74

    Here are the parameters

    PRD

    NAME TYPE VALUE
    ------------------------------ -------- --------
    db_file_multiblock_read_count integer 8
    hash_join_enabled boolean TRUE
    optimizer_index_cost_adj integer 100
    bitmap_merge_area_size integer 1048576
    create_bitmap_area_size integer 8388608
    hash_area_size integer 2048000
    sort_area_retained_size integer 65536
    sort_area_size integer 1024000
    workarea_size_policy string AUTO


    DEV

    NAME TYPE VALUE
    ------------------------------ -------- --------
    db_file_multiblock_read_count integer 8
    hash_join_enabled boolean TRUE
    optimizer_index_cost_adj integer 100
    bitmap_merge_area_size integer 1048576
    create_bitmap_area_size integer 8388608
    hash_area_size integer 1048576
    sort_area_retained_size integer 0
    sort_area_size integer 524288
    workarea_size_policy string AUTO
    rajorcl

  10. #10
    Join Date
    Apr 2003
    Posts
    353
    Post the value of
    _b_tree_bitmap_plans
    in dev and live.

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