Queries on Oracle tuning......

[Former Member]

Hello all,

I am currently in process of tuning a server with 24 GB ram 8 core cpus brand new HP proliant machine with new technology

I have SAN connected on on clustered environment with RAID 1+0 with mirrioring & striping

I have updated all windows & fixed with hot fixes to control high paging...

Tuned appliaction as per sap note 88416 Zero admin m emory management for windows.. Eveyt hing seems to be okay..

But some where i am lagging in DB & when users are trying to fetch large reports it is taking too much tie to process...

The following are my oracle parameters...

**.db_cache_size=7516192768

***.sga_max_size=7G

*.log_buffer=1048576

*.optimizer_index_cost_adj=20

*.parallel_threads_per_cpu=1

*.pga_aggregate_target=3221225472

*.processes=260

*.sessions=520

*.shared_pool_size=620756992*

Do i also need to set SGA_TARGET, If yes what should be the value...& if i set SGA_TARGET do i need to pull out any parameters.."pretty confused with "SGA_TARGEt" since it is not in sap note 830576.. .Please advice me to tune to best level..where i can reach optimum response time.

thanks

-rahul

Edited by: Rahul on Jul 20, 2010 10:53 PM

[stefan_koehler · Accepted solution]

Hello Rahul,

> The following are my oracle parameters...

Hopefully not all ... because there are much more to set according to sapnote #830576.

> Do i also need to set SGA_TARGET, If yes what should be the value

No. SGA_TARGET is used for ASMM (Automatic Shared Memory Management), which can be used but it is not recommended by SAP according to sapnote #105047 (Point 4. Automatic Shared Memory Management (ASMM)).

My experience with ASMM and Oracle 10g R2 is also not pretty good.

> Please advice me to tune to best level..where i can reach optimum response time.

The optimum response time can not be reached by just setting a few parameters. You need to understand the SQLs and how they are processed (execution plan, logical / physical reads, cache, etc.). After that you can tune them or change some relevant system parameters.

Regards

Stefan

[Former Member · Accepted solution]

Hello stefan,

I actually all the parameters as per note 830576 just did not paste may be that can cause a confusion infact my automatic check for oracle parameters did not suggest anny thing more ... just to update

*._b_tree_bitmap_plans=FALSE

*._cursor_features_enabled=10

*._first_spare_parameter=1

*._fix_control='5099019:ON','5705630:ON','5765456:3','6120483:OFF','6221403:ON','6329318:ON','6399597:ON','6430500:ON','6440977:ON','6626018:ON','6670551:ON','6972291:ON','7325597:ON','7692248:ON','7891471:ON'

*._optim_peek_user_binds=FALSE

*._optimizer_better_inlist_costing='OFF'

*._optimizer_mjc_enabled=FALSE

*._push_join_union_view=FALSE

*._sort_elimination_cost_ratio=10

*.compatible='10.2.0'

*.control_file_record_keep_time=30

*.db_block_size=8192

*.db_cache_size=7516192768

*.db_files=512

*.event='44951 trace name context forever, level 1024','10753 trace name context forever, level 2','38087 trace name context forever, level 1','10183 trace name context forever, level 1','10027 trace name context forever, level 1','10028 trace name context forever, level 1','10191 trace name context forever, level 1','10411 trace name context forever, level 1','10629 trace name context forever, level 32','14532 trace name context forever, level 1','10142 trace name context forever, level 1','38068 trace name context forever, level 100','38085 trace name context forever, level 1'

*.filesystemio_options='SETALL'

*.log_archive_format='%t_%s_%r.dbf'

*.log_buffer=1048576

*.log_checkpoints_to_alert=TRUE

*.max_dump_file_size='20000'

*.open_cursors=800

*.optimizer_index_cost_adj=20

*.parallel_execution_message_size=16384

*.parallel_threads_per_cpu=1

*.pga_aggregate_target=3221225472

*.processes=260

*.query_rewrite_enabled='FALSE'

*.recyclebin='off'

*.remote_os_authent=TRUE

*.sessions=520

*.shared_pool_size=620756992

*.star_transformation_enabled='TRUE'

*.undo_management='AUTO'

*.undo_retention=43200

*.undo_tablespace='PSAPUNDO'

thank you for the advice on SGA_TARGET & however when i am tried to increase increase SGA_MAX_SIZE, DB_CAHE_SIZE & PERFORMANCE of system is going down.. Is there any dependency between that 2 parameters..

thanks,

rahul.