RE: DB performance after upgrade from 9i to 11gR2

From: Bheemsen Aitha <baitha_at_itradenetwork.com>
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-l
Received on Thu May 08 2014 - 20:04:13 CEST

Original text of this message