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:
11gCode: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 ********************************************************************************
As you can see.. it's the same plan.. but the 11g run spends a LOT of time on CPU.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 ********************************************************************************
Any ideas? Are there bugs I'm not aware of.. missing a init parameter
Here are the init parameters:
Thanks for any help/insight you can give!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
Reply With Quote