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

From: Justin Mungal <justin_at_n0de.ws>
Date: Thu, 8 May 2014 09:24:57 -0500
Message-ID: <CAO9=aUz=k4HnbbG=y53xYHbSvAhBqJPmrbXT_GYB5wzhZpHgFA_at_mail.gmail.com>

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>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]
> *Sent:* Thursday, May 08, 2014 12:29 AM
> *To:* Bheemsen Aitha
> *Cc:* 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
>
>
>
>
> 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 not like 'log_archive_dest%'
> /
>
> --
> Best regards,
> Sayan Malakshinov
> http://orasql.org
>
> > 2014-05-08 8:39 GMT+02:00 Bheemsen Aitha <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 - 16:24:57 CEST