RE: DB performance after upgrade from 9i to 11gR2
- From: Bheemsen Aitha <baitha@xxxxxxxxxxxxxxxxx>
- To: Mohamed Houri <mohamed.houri@xxxxxxxxx>, Justin Mungal <justin@xxxxxxx>
- Date: Thu, 8 May 2014 17:51:17 +0000
Thanks Mohamed. I tried this. It worked for some queries but not all. Also, the
performance was not same as 9i but better than 11g.
Thanks
BA
From: Mohamed Houri [mailto:mohamed.houri@xxxxxxxxx]
Sent: Thursday, May 08, 2014 7:26 AM
To: Justin Mungal
Cc: Bheemsen Aitha; Sayan Sergeevich Malakshinov; oracle-l@xxxxxxxxxxxxx;
oracle-l-bounce@xxxxxxxxxxxxx
Subject: Re: DB performance after upgrade from 9i to 11gR2
I suggested you to take one query and do this
11gR2> alter session set optimizer_features_enable='9.0.1'; -- put your exact
release
Session altered.
11gR2> run your query
And tell me if you got acceptable response time?
Can you do this?
Best regards
Mohamed Houri
2014-05-08 16:24 GMT+02:00 Justin Mungal
<justin@xxxxxxx<mailto:justin@xxxxxxx>>:
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@xxxxxxxxxxxxxxxxx<mailto:baitha@xxxxxxxxxxxxxxxxx>> 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@xxxxxxxxx<mailto:malakshinovss@xxxxxxxxx>]
Sent: Thursday, May 08, 2014 12:29 AM
To: Bheemsen Aitha
Cc: mohamed.houri@xxxxxxxxx<mailto:mohamed.houri@xxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>;
oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxx<mailto:baitha@xxxxxxxxxxxxxxxxx>>:
> ...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
>
--
Bien Respectueusement
Mohamed Houri
Other related posts:
