Performance tuning..

Hello all,

        Running WEBDB 2.2.0 on a test database Oracle 7.3.4 on Sun SC2000 with 1GB RAM and 2 cpus. I'm getting slow response for the Forms/Reports made through WEBDB. It takes around 3 to 4 second for the forms to appear and a simple "Query/Update" form takes same time to show the results. There are no gifs/jpgs etc. to make it slow. The following are the init parameters.

db_block_buffers=10000
shared_pool_size=25000000
log_buffer=1638400
open_cursors=600
db_block_size=2048 (I tried to make it 8K but due to same strange problems could not succeed).
shared_pool_size = 25000000

        On doing

select a.address,executions,b.sql_text
from v$sqlarea a

    ,v$sqltext b
where parse_calls !=1
and parse_calls = executions
and a.address = b.address
order by executions,a.address,b.piece

I'm getting around 300 entries and this grows on reloading the forms /reports. Also the v$sqlarea.executions increases. More than 90% of the statements are internally generated ones like

select order#,columns,types from access$ where d_obj#=:1

select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#

select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0

Is this continous reparsing the reason for making things slower. If yes, how can I avoid them. I'd increased shared_pool_size from lower values to the present one but there was no improvement.

I've checked and confirmd that there is no contention for RBS, DBBuffer, Dict., Redolog etc.

What other factors could be slowing the process. Please advice. Received on Mon Jun 12 2000 - 07:29:17 CDT