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!