Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Posts
    3

    Unanswered: Poor performance after oracle 8i upgrade

    Hi All

    I am not a DBA, but a unix administrator.

    After our DBA's upgraded from oracle 8.0.5 to oracle 8.1.7.4 on our
    test server, we have noticed a big slow down in our application
    (acocobol). From ~10mins to ~45mins.


    Server info:
    HP Proliant ML350G3
    1024MB Ram
    1 x Intel Xeon 2.8GHz processor
    Unixware 7.1.1


    The DBA's have run a report which indicates an I/O problem.

    Total Wait
    wait Waits
    Event Waits Timeouts Time (cs)
    (ms) /txn
    ---------------------------- ------------ ---------- -----------
    ------ ------
    log file sync 90,709 0 123,003
    14 1.0
    log file parallel write 90,719 0 121,555
    13 1.0



    Our redo logs are on a mirror.


    We also have tried setting OPTIMIZER_FEATURES_ENABLE=8.0.0
    but no luck .....


    The DBA's tried the upgrade 2 different ways
    1. upgrade original database from 8.0.5 to 8.1.7.4
    2. built brand new database from scratch then importing the data.

    Both ways have the same performance problem.


    Does anyone have any idea why this is occurring on 8.1.7.4 and not on
    8.0.5, and how we can fix it.

    thankyou

    regards
    Ruth

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    did you analyze the schema and generated new statistics for the optimizer.
    AFAIK Oracle prefers with 8i cost base optimizer instead of rule based
    what are your initORA parameters for statistics
    did you run traces for your slow sql in question?
    and analyzed the output with tkprof

    did you set trace events (i.e. 10046) ?

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    looks like you have latches on your logfiles.
    how many groups do you have and how large are they?

    also, I would suggest gathering 15-minute statspack reports.

    As stated above, if you imported all the objects, you should:
    1. verify all INDEXES were imported
    2. analyze all tables and indexes for updated statistics
    3. run statspack 15-minute intervals during your busiest times

    My guess is that it has something to do with statistics and indexes.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2004
    Posts
    3

    Poor performance after oracle 8i upgrade

    Hi all,

    Thanks for your responses. I forward them on to the DBA's and this is their reply

    regards
    Ruth

    I've analyzed and validated all user oracle objects (tables, indexes) with compute option.
    The number of user objects (indexes and tables) before and after migration is the same.
    All Database object are VALID and number of extents are very small.
    All user objects are in Local Manage Tablespaces.
    I've used oracle PERFSTAT tool to trace the job.
    This job is executing SQLs with hints and bind variables which will not use Oracle Optimiser stats.
    In Oracle 8.1.7 you can't force Oracle Optimizer to ignore SQL hints. This option is in Oracle 9i.

    There is output form report:

    Instance Efficiency Percentages (Target 100%)
    
    Buffer Nowait %: 100.00 Redo NoWait %: 100.00
    Buffer Hit %: 99.60 In-memory Sort %: 100.00
    Library Hit %: 99.75 Soft Parse %: 90.56
    Execute to Parse %: 99.65 Latch Hit %: 100.00
    Parse CPU to Parse Elapsd %: 93.62 % Non-Parse CPU: 99.70

    Shared Pool Statistics Begin End
    ------ ------
    Memory Usage %: 10.87 12.44
    % SQL with executions>1: 48.89 67.61
    % Memory for SQL w/exec>1: 32.85 58.69

    Top 5 Wait Events
     Wait % Total
    Event Waits Time (cs) Wt Time
    -------------------------------------------- ------------ ------------ -------
    Top 5 Wait Events
     Wait % Total
    Event Waits Time (cs) Wt Time
    -------------------------------------------- ------------ ------------ -------
    PX Deq: Execution Msg 1,378 272,554 52.29
    log file sync 90,709 123,003 23.60
    log file parallel write 90,719 121,555 23.32
    control file parallel write 670 3,376 .65
    db file sequential read 9,245 618 .12
    -------------------------------------------------------------
    ^LWait Events for DB: LOAF Instance: LOAF Snaps: 35 -45
    -> cs - centisecond - 100th of a second
    -> ms - millisecond - 1000th of a second
    -> ordered by wait time desc, waits desc (idle events last)

    Avg
    Total Wait wait Waits
    Event Waits Timeouts Time (cs) (ms) /txn
    ---------------------------- ------------ ---------- ----------- ------ ------
    PX Deq: Execution Msg 1,378 1,334 272,554 1978 0.0
    log file sync 90,709 0 123,003 14 1.0
    log file parallel write 90,719
    control file parallel write 670
    db file sequential read 9,245
    log file switch completion 3
    PX qref latch 17
    log file single write 6
    process startup 4
    SQL*Net more data to client 459
    control file sequential read 94
    PX Deq: Parse Reply 6
    latch free 2
    db file parallel write 1,257
    file open 156
    PX Deq: Execute Reply 18
    PX Deq: Signal ACK 5
    enqueue 5
    Buffer Gets Executions Gets per Exec % Total Hash Value
    --------------- ------------ -------------- ------- ------------
    988,666 201,821 4.9 42.4 3375688107
    SELECT ae_application_no, ae_entity_number, ae_animal_type, ae_t
    ariff, ae_breed, ae_second_breed, ae_sex, ae_description, ae_nam
    e, ae_balance, ae_other_id_type, ae_other_id, ae_other_date, ae_
    microchip_id, ae_microchip_date, ae_exempt, ae_exempt_status, ae
    _nusiance, ae_nusiance_date, ae_nusiance_status, ae_desexed, ae_

    688,948 181,308 3.8 29.5 1365597534
    SELECT /*+ INDEX_ASC (ANL_ENTITY iANL_ENTITY_0) Index Hint */ ae
    _application_no, ae_entity_number, ae_animal_type, ae_tariff, ae
    _breed, ae_second_breed, ae_sex, ae_description, ae_name, ae_bal
    ance, ae_other_id_type, ae_other_id, ae_other_date, ae_microchip
    _id, ae_microchip_date, ae_exempt, ae_exempt_status, ae_nusiance

    272,336 90,685 3.0 11.7 1786727592
    SELECT /*+ INDEX_ASC (ANL_ENTITY iANL_ENTITY_0) Index Hint */ ae
    _application_no, ae_entity_number, ae_animal_type, ae_tariff, ae
    _breed, ae_second_breed, ae_sex, ae_description, ae_name, ae_bal
    ance, ae_other_id_type, ae_other_id, ae_other_date, ae_microchip
    _id, ae_microchip_date, ae_exempt, ae_exempt_status, ae_nusiance


    272,259 90,685 3.0 11.7 3296768686
    SELECT /*+ INDEX_ASC (ANL_APPLIC iANL_APPLIC_0) Index Hint */ aa
    _applic_number, aa_pension_concession, aa_contact_type, aa_conta
    ct_entity, aa_status, aa_status_ymd, aa_creation_ymd, aa_modifie
    d_ymd ,rowid FROM ANL_APPLIC WHERE ( AA_APPLIC_NUMBER > :w0 )
    ORDER BY AA_APPLIC_NUMBER ASC


    Jacek

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    are there load jobs going on that INSERT direct in parallel???
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Also ... Post the INITxxx.ORA parameter file that you are using ...

    Gregg

  7. #7
    Join Date
    Sep 2004
    Posts
    3
    Sorry about the delay

    we have been in contact with the Application vendors again
    and it now may be that the version of the application layer doesnt
    support the upgraded version of oracle, which were not advised of
    until AFTER we had problems post upgrade.

    I will let you know when the vendor upgrades their end and lets us
    know for sure.


    Also please note, the job (report) is read only (SELECT) not UPDATE.
    No information should be written to REDO logs files.


    Thanks for all your help everyone

    kind regards
    Ruth

    below is the initLOAF.ora file

    --------------------------------------------------------------------
    #
    # Copyright (c) 1991, 2000 by Oracle Corporation
    #
    ################################################## ############################
    # Example INIT.ORA file
    #
    # This file is provided by Oracle Corporation to help you customize
    # your RDBMS installation for your site. Important system parameters
    # are discussed, and example settings given.
    #
    # Some parameter settings are generic to any size installation.
    # For parameters that require different values in different size
    # installations, three scenarios have been provided: SMALL, MEDIUM
    # and LARGE. Any parameter that needs to be tuned according to
    # installation size will have three settings, each one commented
    # according to installation size.
    #
    # Use the following table to approximate the SGA size needed for the
    # three scenarious provided in this file:
    #
    # -------Installation/Database Size------
    # SMALL MEDIUM LARGE
    # Block 2K 4500K 6800K 17000K
    # Size 4K 5500K 8800K 21000K
    #
    # To set up a database that multiple instances will be using, place
    # all instance-specific parameters in one file, and then have all
    # of these files point to a master file using the IFILE command.
    # This way, when you change a public
    # parameter, it will automatically change on all instances. This is
    # necessary, since all instances must run with the same value for many
    # parameters. For example, if you choose to use private rollback' segments,
    # these must be specified in different files, but since all gc_*
    # parameters must be the same on all instances, they should be in one file.
    #
    # INSTRUCTIONS: Edit this file and the other INIT files it calls for
    # your site, either by using the values provided here or by providing
    # your own. Then place an IFILE= line into each instance-specific
    # INIT file that points at this file.
    #
    # NOTE: Parameter values suggested in this file are based on conservative
    # estimates for computer memory availability. You should adjust values upward
    # for modern machines.
    #
    ################################################## #############################

    db_name = "LOAF"
    db_domain = world

    instance_name = LOAF

    service_names = LOAF.world


    control_files = ("/usr/v/oradata/LOAF/control01.ctl",
    "/usr/users/app/oracle/oradata/LOAF/control02.ctl"
    )

    open_cursors = 500
    max_enabled_roles = 30
    db_block_buffers = 32000
    db_file_multiblock_read_count = 16
    max_commit_propagation_delay = 90000
    fast_start_io_target = 0
    transactions_per_rollback_segment = 49
    max_commit_propagation_delay = 90000

    optimizer_features_enable = 8.0.5
    # optimizer_index_cost_adj = 30
    # optimizer_index_caching = 99
    # optimizer_mode = rule
    shared_pool_size = 122880000

    large_pool_size = 614400
    java_pool_size = 0

    log_checkpoint_interval = 100000000
    log_checkpoint_timeout = 0
    log_checkpoints_to_alert = true

    processes = 500

    # log_buffer = 368640

    log_buffer = 3276800


    # audit_trail = false # if you want auditing
    # timed_statistics = false # if you want timed statistics
    # max_dump_file_size = 10000 # limit trace file size to 5M each

    # Uncommenting the lines below will cause automatic archiving if archiving has
    # been enabled using ALTER DATABASE ARCHIVELOG.
    # log_archive_start = true
    # log_archive_dest_1 = "location=/usr/users/app/oracle/admin/LOAF/arch"
    # log_archive_format = arch_%t_%s.arc

    # If using private rollback segments, place lines of the following
    # form in each of your instance-specific init.ora files:
    rollback_segments = ( RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6,
    RBS7, RBS8, RBS9, RBS10, RBS11, RBS12 )

    # Global Naming -- enforce that a dblink has same name as the db it connects to
    # global_names = false

    # Uncomment the following line if you wish to enable the Oracle Trace product
    # to trace server activity. This enables scheduling of server collections
    # from the Oracle Enterprise Manager Console.
    # Also, if the oracle_trace_collection_name parameter is non-null,
    # every session will write to the named collection, as well as enabling you
    # to schedule future collections from the console.
    # oracle_trace_enable = true

    # define directories to store trace and alert files
    background_dump_dest = /usr/users/app/oracle/admin/LOAF/bdump
    core_dump_dest = /usr/users/app/oracle/admin/LOAF/cdump
    #Uncomment this parameter to enable resource management for your database.
    #The SYSTEM_PLAN is provided by default with the database.
    #Change the plan name if you have created your own resource plan.# resource_manager_plan = system_plan
    user_dump_dest = /usr/users/app/oracle/admin/LOAF/udump

    db_block_size = 8192
    # dbwr_io_slaves = 2

    remote_login_passwordfile = exclusive

    os_authent_prefix = ""

    local_listener = "listener_LOAF.world"

    transaction_auditing = false
    compatible = "8.1.7.4.0"
    sort_area_size = 983040
    sort_area_retained_size = 983040
    timed_statistics = true
    utl_file_dir = *
    ----------------------------------------------------------------------

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Do you have the execution plans for the worst sql before and after the upgrade as this will help a lot.

    I had exactly the same sort of problem going to 9i and these are some of the things I did

    1) Identify the execution plan you require for your worst sql.

    2) Tune the optimizer* parameters and also other parameteres like db_file_multiblock_read_count (setting it lower will favour indexes)

    3) Reduce the cost of your favoured indexes by compressing them if they have more than one column.

    4) Change your hints to help it get the right execution plan

    5) Use some of the new features such as bitmapped indexes (if appropriate), keep/recycle pools, LMTs, partitioning etc.

    6) Use plan stabilty to fix execution plans as a last resort.

    There are other things you can do, the one thing with oracle is you can even get it to make the tea if you can find write mix of init.ora parameters and DDL

    Alan

Posting Permissions

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