RE: DB performance after upgrade from 9i to 11gR2
Date: Thu, 8 May 2014 18:04:13 +0000
Message-ID: <3E9FC3C66B6DD445A50671ECBA1F423EEF358C7B_at_plt-exch-01.Itradenetwork.com>
Justin,
We did not test with the same amount of load as in production. I know that’s the best way, but unfortunately we did not have resources and hence did not do that. Yes, I did try Mohamed’s suggestion. Helped only some queries. Our management is not interested in setting that parameter even at session level and have a fix.
Thanks
BA
From: Justin Mungal [mailto:justin_at_n0de.ws]
Sent: Thursday, May 08, 2014 7:25 AM
To: Bheemsen Aitha
Cc: Sayan Sergeevich Malakshinov; mohamed.houri_at_gmail.com; oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org
Subject: Re: DB performance after upgrade from 9i to 11gR2
How is load looking from the storage side? You mentioned that you tested thoroughly in QA; was the QA load the same as it is on production (sorry if that is a silly question)? Otherwise it's not a valid test in my opinion. Have you tried Mohamed's suggestion yet?
On Thu, May 8, 2014 at 9:14 AM, Bheemsen Aitha <baitha_at_itradenetwork.com<mailto:baitha_at_itradenetwork.com>> wrote: Here is the output. Any suggestions?
PARAMETER VALUE processes
1300
timed_statistics
TRUE resource_limit
TRUE sga_max_size
45G
shared_pool_size
15G
large_pool_size
512M
java_pool_size
2G
resource_manager_plan
sga_target
45G
control_files
/v02/oradata/ITNPROD/control01.ctl, /v03/oradata/ITNPROD/control02.ctl, /v04/oradata/ITNPROD/control03.ctl
log_file_name_convert
ITNPROD, ITNPROD db_block_size
8192
db_cache_size
20G
compatible
11.2.0.4.0
fal_client
ITNPROD01 fal_server
ITNPROD02 log_archive_config
DG_CONFIG=(ITNPROD01,ITNPROD02,ITNPROD03) log_archive_format
arch_%t_%s_%r.ARC
log_checkpoint_interval
0
log_checkpoint_timeout
1800
db_files
500
db_file_multiblock_read_count
16
standby_file_management
AUTO fast_start_mttr_target
300
undo_management
AUTO undo_tablespace
UNDOTBS undo_retention
10800
remote_login_passwordfile
EXCLUSIVE db_domain
instance_name
ITNPROD session_cached_cursors
1000
utl_file_dir
*
job_queue_processes
10
cursor_sharing
FORCE parallel_max_servers
32
core_dump_dest
/v01/app/oracle/admin/ITNPROD/cdump
sort_area_size
12582912
db_name
ITNPROD db_unique_name
ITNPROD01 open_cursors
1000
star_transformation_enabled
TRUE query_rewrite_enabled
FALSE pga_aggregate_target
20G
workarea_size_policy
auto
aq_tm_processes
5
diagnostic_dest
/v01/app/oracle
Thanks
BA
From: Sayan Sergeevich Malakshinov [mailto:malakshinovss_at_psbank.ru<mailto:malakshinovss_at_psbank.ru>]
Sent: Thursday, May 08, 2014 12:29 AM
To: Bheemsen Aitha
Cc: mohamed.houri_at_gmail.com<mailto:mohamed.houri_at_gmail.com>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>; oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>
Subject: Re: DB performance after upgrade from 9i to 11gR2
I would start with checking the parameters:
select name as parameter
,decode
(p.type
,1,'boolean'
,2,'string'
,3,'number'
,4,'file'
,6,'size(bytes)'
,'Unknown: '||p.type) type
,description
,decode(p.type,6,p.display_value,p.value) as value
,update_comment
,ISMODIFIED
,ISADJUSTED
,ISDEPRECATED
,ISBASIC
,ISSES_MODIFIABLE
,ISSYS_MODIFIABLE
,ISINSTANCE_MODIFIABLE
from v$parameter p
where p.isdefault='FALSE'
and p.name<http://p.name> not like 'log_archive_dest%' /
--
Best regards,
Sayan Malakshinov
http://orasql.org<http://orasql.org/>
> 2014-05-08 8:39 GMT+02:00 Bheemsen Aitha <baitha_at_itradenetwork.com<mailto:baitha_at_itradenetwork.com>>: > ...Some of the queries have differences in plan (FTS to index scans on small tables) > when compared to QA environment. We gathered stats with default auto > sample size and default method_opt options. > Thanks > BA >
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 08 2014 - 20:04:13 CEST