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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
