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

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_at_n0de.ws>:

> 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
>> >
>>
>
>

-- 
Bien Respectueusement
Mohamed Houri

--
http://www.freelists.org/webpage/oracle-l