Message-Id: <10526.108667@fatcity.com> From: "Nguyen, Tu" Date: Mon, 12 Jun 2000 10:00:42 -0500 Subject: RE: Performance tuning.. This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BFD47E.FA7F5580 Content-Type: text/plain; charset="iso-8859-1" Vikas, Your SGA size is too small. You probably use less than 50M for your SGA. Try db_block_buffers = 80000 shared_pool_size = 250000000 -----Original Message----- From: Vikas S [mailto:svikas@bom5.vsnl.net.in] Sent: Monday, June 12, 2000 8:35 AM To: Multiple recipients of list ORACLE-L Subject: 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. Thanks in advance, Vikas -- Author: Vikas S INET: svikas@bom5.vsnl.net.in Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01BFD47E.FA7F5580 Content-Type: text/html; charset="iso-8859-1" RE: Performance tuning..

Vikas,

Your SGA size is too small. You probably use less than 50M for your SGA.
Try db_block_buffers = 80000
    shared_pool_size = 250000000

-----Original Message-----
From: Vikas S [mailto:svikas@bom5.vsnl.net.in]
Sent: Monday, June 12, 2000 8:35 AM
To: Multiple recipients of list ORACLE-L
Subject: 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.

Thanks in advance,
Vikas




--
Author: Vikas S
  INET: svikas@bom5.vsnl.net.in

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may