hi,
this is my spfile.ora that i have changed.
Are there any other parameters that to be changed for better performance?

*.cursor_sharing='SIMILAR'
*.cursor_space_for_time=TRUE
*.db_16k_cache_size=83886080
*.db_block_size=8192
*.db_cache_advice='OFF'
*.db_cache_size=134217728
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_keep_cache_size=524288000
*.java_pool_size=20971520
*.job_queue_processes=10
*.large_pool_size=8388608
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=250
*.session_cached_cursors=50
*.sga_max_size=1073741824
*.shared_pool_size=75M

>> cursor_sharing='SIMILAR'

NOT Good, unless you are on 11g:

http://www.dba-oracle.com/t_cusror_sharing.htm

************************************************
>> *.db_16k_cache_size=83886080

Why are you using a mix of 16k and 8k blocksizes?

>> db_file_multiblock_read_count=8

Have you optimized this?

http://www.dba-oracle.com/t_db_file_multib...atic_tuning.htm

************************************************
>> *.shared_pool_size=75M

This seems very small to me.

Are you getting library cache contention?

************************************************

>> Are there any other parameters that to be changed for better performance?

Sure, dozens of others:

http://www.dba-oracle.com/art_orafaq_tune_parms.htm

And, specific parms for improving SQL execution performance:

http://www.dba-oracle.com/art_otn_cbo_p1.htm

If you want the details (it's very complex), see my book "Oracle Tuning: The Definitive Reference":

http://www.rampant-books.com/book_2005_1_a...tive_tuning.htm

Is the ASSM is enabled, if yes, then the it is automatic. db_block_size dpends on the application type whether it OLTP or DSS. For OLTP kind of application 8192 is OK.

Thank you...

We are using 10g.
So what option i can put for cursor_sharing?

----------------------------------------------------
to create tablespaces with 16kb block size, i set that parameter. We moved the tables in 8kb table space into 16kb tablespace.

-----------------------------------------------------

i changed the multiblock_read_count parameter from 16 to 8

-----------------------------------------------------
I don't know about library cache contention?
but the library cache hit ratio is 93-98%.
On what basis we can set the shared_pool parameter?