Upgrade 9i-11.1.0.7, poor performance - cpu elapsed
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Upgrade 9i-11.1.0.7, poor performance - cpu elapsed

Hybrid View

  1. #1
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334

    Upgrade 9i-11.1.0.7, poor performance - cpu elapsed

    Hi everyone!
    Been a while since I've been here.

    I have a db that I am upgrading from 9i to 11.1.0.7. Thankfully we are still in the testing stage. But the performance is much worse on 11g.

    Environment:
    HP-UX Risc 11.23
    SAN disk.
    Both the 9i and 11g databases are on the same disk.

    I have a sample batch job that I have run on both instances. I have traced both. The issue seems to be most apparent for inserts/updates. Here is a sample ( I have removed the column names for security reasons):

    9i:
    Code:
    update TABLE1  set .....
    where
     ID=:b38
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse      819      0.09       0.08          0          7          0           0
    Execute   1341      6.94       7.08          5       4812       5513        1341
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total     2160      7.03       7.16          5       4819       5513        1341
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 391
    Optimizer mode: RULE
    Parsing user id: 24
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          2  UPDATE
          2   INDEX UNIQUE SCAN PK_TABLE1 (object id 18019)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net more data from client                 915        0.00          0.01
      SQL*Net message to client                    1341        0.00          0.00
      SQL*Net message from client                  1341        0.00          0.29
      db file sequential read                         5        0.04          0.11
      latch free                                      1        0.00          0.00
    ********************************************************************************
    11g
    Code:
    update TABLE1  set ....
    where
     ID=:b38
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse      807      0.22       0.21          0          0          2           0
    Execute   1370     25.95      25.23          3       4148       5776        1370
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total     2177     26.17      25.44          3       4148       5778        1370
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 384
    Optimizer mode: ALL_ROWS
    Parsing user id: 24
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          1  UPDATE  TABLE1 (cr=3 pr=0 pw=0 time=0 us)
          1   INDEX UNIQUE SCAN PK_TABLE1 (cr=3 pr=0 pw=0 time=0 us cost=2 size=160 card=1)(object id 18019)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                    1370        0.00          0.00
      SQL*Net message from client                  1370        0.00          0.35
      latch: shared pool                             10        0.00          0.00
      library cache: mutex X                        237        0.00          0.00
      db file sequential read                         3        0.01          0.01
    ********************************************************************************
    As you can see.. it's the same plan.. but the 11g run spends a LOT of time on CPU.

    Any ideas? Are there bugs I'm not aware of.. missing a init parameter

    Here are the init parameters:
    Code:
    NAME                                     VALUE
    ---------------------------------------- ------------------------------------
    _trace_files_public                      TRUE
    archive_lag_target                       1800
    audit_trail                              DB
    compatible                               11.1.0
    db_block_size                            16384
    db_cache_size                            331350016
    db_file_multiblock_read_count            16
    db_files                                 1000
    db_writer_processes                      4
    disk_asynch_io                           FALSE
    dml_locks                                2000
    event                                    60 trace name systemstate level 16
    job_queue_processes                      6
    log_buffer                               2097152
    log_checkpoint_interval                  10000000
    log_checkpoint_timeout                   150
    log_checkpoints_to_alert                 TRUE
    max_dump_file_size                       UNLIMITED
    nls_date_format                          YYYY-MM-DD
    open_cursors                             600
    optimizer_mode                           ALL_ROWS
    pga_aggregate_target                     524288000
    processes                                1000
    recyclebin                               OFF
    remote_login_passwordfile                EXCLUSIVE
    session_cached_cursors                   600
    sessions                                 1105
    sga_max_size                             1073741824
    shared_pool_size                         532676608
    sort_area_retained_size                  1024000
    sort_area_size                           5242880
    statistics_level                         TYPICAL
    tape_asynch_io                           FALSE
    timed_statistics                         TRUE
    transactions_per_rollback_segment        10
    undo_management                          AUTO
    undo_retention                           28800
    undo_tablespace                          TBS_UNDO
    Thanks for any help/insight you can give!

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,471

    Cool Use 11g parameters...

    Try using the 11g parameters, add/change or remove the following:
    Code:
    db_cache_size				#<= Remove
    db_writer_processes			1
    disk_asynch_io				TRUE
    filesystemio_options 			SETALL
    memory_max_target			2400M  #<= Max memory...
    memory_target				2000M  #<= SGA 1G + PGA 0.5G + Extra
    pga_aggregate_target			#<= Remove
    sga_max_size                            #<= Remove
    shared_pool_size                        #<= Remove
    sort_area_retained_size                 #<= Remove
    sort_area_size                          #<= Remove

    PS: Allocate as much memory as possible.
    .
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    I had the memory_target parameters set, and had the old values (sga, etc) set as well so they were the minimums. Still had the issue.

    Async IO is not enabled at the OS level. So, setting disk_async_io to true may be a bad idea.

    Thanks for the suggestions, though.

    We have 11g databases on HP-UX Itanium that don't have this problem. I'm beginning to think that it may be a RISC issue...

    Any other ideas?

    Thanks!
    Jodie

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,471

    Cool

    Quote Originally Posted by jodie View Post

    Async IO is not enabled at the OS level. So, setting disk_async_io to true may be a bad idea.
    That is the purpose of the FILESYSTEMIO_OPTIONS parameter, to allow Oracle to manage disk I/O.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Quote Originally Posted by LKBrwn_DBA View Post
    That is the purpose of the FILESYSTEMIO_OPTIONS parameter, to allow Oracle to manage disk I/O.
    Our HPUX boxes are not set up to handle asynch io. I can't set these parameters.

  6. #6
    Join Date
    Dec 2002
    Posts
    60
    Did you run dbms_stats after the upgrade?.

  7. #7
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Quote Originally Posted by skhanal View Post
    Did you run dbms_stats after the upgrade?.
    Yep. Sorry.. I should have posted this.

    I did:
    Gather_dictionary_stats_10g.sql
    gather_system_stats_nw.sql
    And also gathered schema stats for our application schemas

    During a load, I did:
    gather_fixed_objects_stats.sql
    gather_system_stats_start.sql (WAIT 2 hours, then..)
    gather_system_stats_stop.sql

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