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

(a) the execution plan produced by the optimizer set back to the 9i version (b) the sub optimal execution plan produced by the optimizer in its 11gR2 release (without alter session set optimizer.....)

And compare them or post them here so that someone can spot any clue

Best regards
Mohamed Houri
www.hourim.wordpress.com

2014-05-08 19:51 GMT+02:00 Bheemsen Aitha <baitha_at_itradenetwork.com>:

> 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_at_gmail.com]
> *Sent:* Thursday, May 08, 2014 7:26 AM
> *To:* Justin Mungal
> *Cc:* Bheemsen Aitha; Sayan Sergeevich Malakshinov; oracle-l_at_freelists.org;
> oracle-l-bounce_at_freelists.org
>
> *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_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
>

-- 
Bien Respectueusement
Mohamed Houri

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