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>: > 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>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] >> *Sent:* Thursday, May 08, 2014 12:29 AM >> *To:* Bheemsen Aitha >> *Cc:* mohamed.houri@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx; >> 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 not like 'log_archive_dest%' >> / >> >> -- >> Best regards, >> Sayan Malakshinov >> http://orasql.org >> >> > 2014-05-08 8:39 GMT+02:00 Bheemsen Aitha <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