Overall Performance--still poor
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Overall Performance--still poor

  1. #1
    Join Date
    Apr 2003
    Posts
    353

    Overall Performance--still poor

    One of our production server,
    with below details runs fine with 100 or less users.
    When more than 200 users come,
    we feel a lot of buffer busy waits, when any one of the table
    goes in db_file_sequential_read for more than 5 seconds.

    And also reading in undo is very poor.

    Attaced a statspack report for your reference.

    Os level, the datafiles are with RAID 5 (Though I told the sysadmin
    that raid 5 will not suit, he told this will not be a big problem).
    Redo log file s are in RAID 1.
    SOlaris 8.
    Oracle 9i.

    Thanks for all the help provided.

    Regards
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    You have some nasty SQL statements, massive buffer gets which leads to buffer busy waits

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

    Whats your parameter for optmizer_index_adj_cost

    what does the plan for the following statement look like

    [code]

    UPDATE SA_TEMP_IRR_WORKING SET CURRENT_BALANCE = :B5 , PREVIOUS_
    BALANCE = :B4 , CHARGES = :B3 WHERE INFLOW_OUTFLOW = :B2 AND DOC
    UMENT_DATE = :B1


    SELECT REFERENCE_VALUE FROM SA_CONSTANT_REFERENCE_DIR WHERE REFE
    RENCE_NAME = :B1

    SELECT DISTINCT SYADPKG00004.DF_V_GET_STRING_POSITION(AMD_ASSET_
    LEVEL_STRING,ADE_ASSET_LEVEL_NO) FROM SA_ASSET_DEFINITION_DETAIL
    S, SA_ASSET_MASTER_DETAILS WHERE ADE_ASSET_DEF_CODE = AMD_ASSET_
    DEF_CODE AND AMD_ASSET_MASTER_CODE = :B1 AND ADE_ASSET_ATTRIB_CO
    DE = DF_V_CONST_REF_DIR('CAP_ASSET_CLASS_ATTRIB_CODE')

    is the distinct necessary ?

    regards
    Hrishy

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    forget

    UPDATE SA_TEMP_IRR_WORKING SET CURRENT_BALANCE = :B5 , PREVIOUS_
    BALANCE = :B4 , CHARGES = :B3 WHERE INFLOW_OUTFLOW = :B2 AND DOC
    UMENT_DATE = :B1


    SELECT REFERENCE_VALUE FROM SA_CONSTANT_REFERENCE_DIR WHERE REFE
    RENCE_NAME = :B1

    those are good enough, we are not gonna question SQL with 2 or 3 buffer gets atm!

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I have a question, why you disable smon transaction recovery with the event?

  6. #6
    Join Date
    Apr 2003
    Posts
    353
    Thanks for your valuable time and analysis.

    Pando I think you are referring
    10513 trace name context forever

    I asked my collegue who is incharge of this database, (On leave),
    it is due to one request from oracle during their analysis.

    After that he forgot to unset that.

    I will unset that tonight.

    Regards

  7. #7
    Join Date
    Apr 2003
    Posts
    353
    After unsetting the value shutdown of the database hangs
    and alerts keep on going with MARKING UNDO XX AS AVAILABLE
    AND MARKING XXX AS AVAILABLE... AND SO ON.

    What could be the reason.

    We have to raise a tar.

    What could be the reason..??

    Are we going suffering due to any bugs.?

    Regards

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