RE: Performance tuning..

From: Nguyen, Tu <TuNguyen_at_houston.reed-hycalog.slb.com>
Date: Mon, 12 Jun 2000 10:00:42 -0500
Message-Id: <10526.108667@fatcity.com>

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_at_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_at_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_at_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"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2651.75">
<TITLE>RE: Performance tuning..</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=2>Vikas,</FONT>
</P>

<P><FONT SIZE=2>Your SGA size is too small. You probably use less than 50M for your SGA.</FONT>
<BR><FONT SIZE=2>Try db_block_buffers = 80000</FONT>
<BR><FONT SIZE=2>&nbsp;&nbsp;&nbsp; shared_pool_size = 250000000</FONT>
</P>

<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: Vikas S [<A HREF="mailto:svikas_at_bom5.vsnl.net.in">mailto:svikas_at_bom5.vsnl.net.in</A>]</FONT>
<BR><FONT SIZE=2>Sent: Monday, June 12, 2000 8:35 AM</FONT>
<BR><FONT SIZE=2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=2>Subject: Performance tuning..</FONT>
</P>
<BR>
<BR>

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

<P><FONT SIZE=2>db_block_buffers=10000</FONT>
<BR><FONT SIZE=2>shared_pool_size=25000000</FONT>
<BR><FONT SIZE=2>log_buffer=1638400</FONT>
<BR><FONT SIZE=2>open_cursors=600</FONT>
<BR><FONT SIZE=2>db_block_size=2048&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (I tried to make it 8K but due to same strange</FONT>
<BR><FONT SIZE=2>problems could not succeed).</FONT>
<BR><FONT SIZE=2>shared_pool_size = 25000000</FONT>
</P>

<P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=2>On doing </FONT>
</P>

<P><FONT SIZE=2>select a.address,executions,b.sql_text</FONT>
<BR><FONT SIZE=2>from v$sqlarea a</FONT>
<BR><FONT SIZE=2>&nbsp;&nbsp;&nbsp; ,v$sqltext b</FONT>
<BR><FONT SIZE=2>where parse_calls !=1</FONT>
<BR><FONT SIZE=2>and&nbsp;&nbsp; parse_calls = executions</FONT>
<BR><FONT SIZE=2>and&nbsp;&nbsp; a.address = b.address</FONT>
<BR><FONT SIZE=2>order by executions,a.address,b.piece</FONT>
</P>

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

<P><FONT SIZE=2>select order#,columns,types from access$ where d_obj#=:1</FONT>
</P>

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

<P><FONT SIZE=2>select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where</FONT>
<BR><FONT SIZE=2>t.ts#=f.ts# and t.dflextpct!=0</FONT>
</P>

<P><FONT SIZE=2>Is this continous reparsing the reason for making things slower. If</FONT>
<BR><FONT SIZE=2>yes, how can I avoid them. I'd increased shared_pool_size from lower</FONT>
<BR><FONT SIZE=2>values to the present one but there was no improvement. </FONT>
</P>

<P><FONT SIZE=2>I've checked and confirmd that there is no contention for RBS,</FONT>
<BR><FONT SIZE=2>DBBuffer, Dict., Redolog etc. </FONT>
</P>

<P><FONT SIZE=2>What other factors could be slowing the process. Please advice.</FONT>
</P>

<P><FONT SIZE=2>Thanks in advance,</FONT>
<BR><FONT SIZE=2>Vikas</FONT>
</P>
<BR>
<BR>
<BR>

<P><FONT SIZE=2>-- </FONT>
<BR><FONT SIZE=2>Author: Vikas S</FONT>
<BR><FONT SIZE=2>&nbsp; INET: svikas_at_bom5.vsnl.net.in</FONT>
</P>

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

Received on Mon Jun 12 2000 - 10:00:42 CDT