-
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!
-
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
-
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
-
Did you run dbms_stats after the upgrade?.
-
Originally Posted by skhanal
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
-
Originally Posted by jodie
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
-
Originally Posted by LKBrwn_DBA
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|