Re: SQL*Net message from client

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Fri, 04 Jun 2004 11:58:42 -0600
Message-id: <40C0B852.90107@sun.com>

Stalin,

This is a great example of a 'chatty' application. If all of this code was placed into a stored procedure, the sql*net calls between statement execution/data fetch would probably be eliminated (and subsequently reduce response time). In one application I looked at, over 60% of the response time was in sql*net messages. A look at the trace file, compiling individual statements into a repeating transaction and sampling several transactions showed that the inner-tx sql*net round trips comprised roughly 50% of the total response time.

I tested this type of scenario (get count, if 0 insert else update) in a pl/sql loop and there were NO sql*net messages inside the execution of the block.

IIRC, I posted an illustration of this using beer. Most developers can understand this concept rather quickly!

Daniel

Subbiah, Stalin wrote:
> PARSING IN CURSOR #6 len=336 dep=0 uid=19 oct=6 lid=19 tim=1248377797
> hv=3885370321 ad='a7027a74'
> UPDATE warehouse.summary_hits_fact shf
> SET (hit_count) =
> (SELECT count(*)
> FROM hits_fact hf
> WHERE shf.page_hit_key = hf.page_hit_key
> AND shf.hit_date_key = hf.hit_date_key)
> WHERE shf.page_hit_key = :p1
> AND shf.hit_date_key = :p2
> END OF STMT
> EXEC #6:c=0,e=0,p=0,cr=6,cu=2,mis=0,r=1,dep=0,og=4,tim=1248377797
> WAIT #6: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
> WAIT #6: nam='SQL*Net message from client' ela= 46 p1=1413697536 p2=1 p3=0
> EXEC #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1248377843
> WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
> FETCH #5:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=1248377843

> SELECT count (*) from summary_hits_fact shf where
> shf.page_hit_key = :p1 AND shf.hit_date_key = :p2
> INSERT INTO summary_hits_fact
> (page_hit_key, hit_date_key, hit_count)
> SELECT hf.page_hit_key, hf.hit_date_key, count(*)
> FROM hits_fact hf
> WHERE hf.page_hit_key = :p1
> AND hf.hit_date_key = :p2
> GROUP BY hf.page_hit_key, hf.hit_date_key
> UPDATE summary_hits_fact shf
> SET (hit_count) =
> (SELECT count(*)
> FROM hits_fact hf
> WHERE shf.page_hit_key = hf.page_hit_key
> AND shf.hit_date_key = hf.hit_date_key)
> WHERE shf.page_hit_key = :p1
> AND shf.hit_date_key = :p2

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Jun 04 2004 - 12:58:23 CDT