Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Sincere Advice on Sql Plan - Thanks -Please Help in Understanding

Re: Sincere Advice on Sql Plan - Thanks -Please Help in Understanding

From: Juan Carlos Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Tue, 12 Oct 2004 18:22:39 -0400
Message-Id: <416C592F.000001.02664@DAZA-MGEJCA5J7T> 





Hi Sanjay if you see 2419 neither 291 are not binded, 
sendme your full trace file.


The first question, can't you try to bind that variables or you have
histograms in that columns.
 


 Please showme your library cache hit ratio to see if your shared pool is
not too small


The following query


SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES", 

ROUND(SUM(RELOADS)*100/SUM(PINS),2) "% RATIO"

FROM V$LIBRARYCACHE



Shows you the amount of reloads needed from the library because there were
not enough memory if the ratio is more that 1% you can think in add the
shared pool size.



Juan Carlos Reyes Pacheco


OCP



-------Original Message-------

 


From: Sanjay Mishra


Date: 10/12/04 17:22:53


To: Juan Carlos Reyes Pacheco; mrothouse_at_gmail.com; oracle-l_at_freelists.org
Subject: Re: Sincere Advice on Sql Plan - Thanks -Please Help in
Understanding
 


Juan
 


There are several query like this and I am giving one of the recent trace.
First is the TKPROF output and second is Trace Data
 

<<<<<<<<<<<<<<<<<First>>>>>>>>>>>>>>>>>>>>>>>>


SELECT DECODE(lt.t_value, NULL, gr.r_value,
lt.t_value) description, TO_CHAR(gr.id) id
FROM



g_regions gr, l_translations lt WHERE sub_type = 2419 AND
lt.geo_id (+) = gr.id AND lt.l_type_gr_id (+) = 291 ORDER BY
description
 
 


call count cpu elapsed disk query current rows


------- ------ -------- ---------- ---------- ---------- ----------





Parse 10 0.01 0.00 0 0 0 0


Execute 5 0.00 0.00 0 0 0 0


Fetch 60 0.29 0.31 0 1240 0 1155


------- ------ -------- ---------- ---------- ---------- ----------





total 75 0.30 0.32 0 1240 0 1155
 


Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: 722 (SMISHRA)
 


Rows Execution Plan


------- ---------------------------------------------------

0 SELECT STATEMENT GOAL: CHOOSE



0 SORT (ORDER BY)



0 HASH JOIN (OUTER)



0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF

'G_REGIONS'



0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'GR_IFK'

(NON-UNIQUE)



0 TABLE ACCESS GOAL: ANALYZED (FULL) OF



'L_TRANSLATIONS'

 
 
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited


---------------------------------------- Waited ---------- ------------

SQL*Net message to client 1 0.00 0.00


SQL*Net message from client 1 0.00 0.00
 
 
 

<<<<<<<<<<<<<<<Trace Data>>>>>>>>>>>>>>>>>>>>>>>>>>


PARSING IN CURSOR #2 len=273 dep=0 uid=722 oct=3 lid=722
tim=1097075480899747 hv=179724349 ad='8dab9050'
SELECT DECODE(lt.t_value, NULL, gr.r_value, lt.t_value) description,
TO_CHAR(gr.id) id FROM g_regions gr, l_translations lt WHERE sub_type = 2419
AND lt.geo_id (+) = gr.id AND lt.l_type_gr_id (+) =
291 ORDER BY description


END OF STMT



PARSE #2:c=10000,e=7344,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4
tim=1097075480899731

WAIT #2: nam='SQL*Net message to client' ela= 4 p1=675562835 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 4103 p1=675562835 p2=1 p3=0
=====================




PARSING IN CURSOR #2 len=273 dep=0 uid=722 oct=3 lid=722
tim=1097075480905248 hv=179724349 ad='8dab9050'
SELECT DECODE(lt.t_value, NULL, gr.r_value, lt.t_value) description,
TO_CHAR(gr.id) id FROM g_regions gr, l_translations lt WHERE sub_type = 2419
AND lt.geo_id (+) = gr.id AND lt.l_type_gr_id (+) =
291 ORDER BY description


END OF STMT



PARSE #2:c=0,e=99,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1097075480905234
BINDS #2:



EXEC #2:c=0,e=188,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1097075480905637
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=675562835 p2=1 p3=0
FETCH #2:c=60000,e=59729,p=0,cr=248,cu=0,mis=0,r=20,dep=0,og=4
tim=1097075480965588


WAIT #2: nam='SQL*Net message from client' ela= 1229 p1=675562835 p2=1 p3=0
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=675562835 p2=1 p3=0
FETCH #2:c=0,e=132,p=0,cr=0,cu=0,mis=0,r=20,dep=0,og=4,tim=1097075480967325
WAIT #2: nam='SQL*Net message from client' ela= 1003 p1=675562835 p2=1 p3=0
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=675562835 p2=1 p3=0
FETCH #2:c=0,e=122,p=0,cr=0,cu=0,mis=0,r=20,dep=0,og=4,tim=1097075480968657
WAIT #2: nam='SQL*Net message from client' ela= 1035 p1=675562835 p2=1 p3=0
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=675562835 p2=1 p3=0
FETCH #2:c=0,e=123,p=0,cr=0,cu=0,mis=0,r=20,dep=0,og=4,tim=1097075480970021
WAIT #2: nam='SQL*Net message from client' ela= 1015 p1=675562835 p2=1 p3=0
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=675562835 p2=1 p3=0
FETCH #2:c=0,e=122,p=0,cr=0,cu=0,mis=0,r=20,dep=0,og=4,tim=1097075480971366
WAIT #2: nam='SQL*Net message from client' ela= 998 p1=675562835 p2=1 p3=0
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=675562835 p2=1 p3=0
FETCH #2:c=0,e=121,p=0,cr=0,cu=0,mis=0,r=20,dep=0,og=4,tim=1097075480972691
WAIT #2: nam='SQL*Net message from client' ela= 1071 p1=675562835 p2=1 p3=0
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=675562835 p2=1 p3=0
FETCH #2:c=0,e=121,p=0,cr=0,cu=0,mis=0,r=20,dep=0,og=4,tim=1097075480974128
WAIT #2: nam='SQL*Net message from client' ela= 1095 p1=675562835 p2=1 p3=0
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=675562835 p2=1 p3=0
FETCH #2:c=0,e=122,p=0,cr=0,cu=0,mis=0,r=20,dep=0,og=4,tim=1097075480975554
WAIT #2: nam='SQL*Net message from client' ela= 1030 p1=675562835 p2=1 p3=0
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=675562835 p2=1 p3=0
FETCH #2:c=0,e=120,p=0,cr=0,cu=0,mis=0,r=20,dep=0,og=4,tim=1097075480976911
WAIT #2: nam='SQL*Net message from client' ela= 1033 p1=675562835 p2=1 p3=0
WAIT #2: nam='SQL*Net message to client' ela= 2 p1=675562835 p2=1 p3=0
FETCH #2:c=0,e=124,p=0,cr=0,cu=0,mis=0,r=20,dep=0,og=4,tim=1097075480978300
WAIT #2: nam='SQL*Net message from client' ela= 1033 p1=675562835 p2=1 p3=0
WAIT #2: nam='SQL*Net message to client' ela= 4 p1=675562835 p2=1 p3=0
FETCH #2:c=0,e=121,p=0,cr=0,cu=0,mis=0,r=20,dep=0,og=4,tim=1097075480979790
WAIT #2: nam='SQL*Net message from client' ela= 1051 p1=675562835 p2=1 p3=0
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=675562835 p2=1 p3=0
FETCH #2:c=0,e=513,p=0,cr=0,cu=0,mis=0,r=11,dep=0,og=4,tim=1097075480981567

WAIT #2: nam='SQL*Net message from client' ela= 9504 p1=675562835 p2=1 p3=0
STAT #2 id=1 cnt=231 pid=0 pos=0 obj=0 op='SORT ORDER BY '
STAT #2 id=2 cnt=231 pid=1 pos=1 obj=0 op='HASH JOIN OUTER '
STAT #2 id=3 cnt=231 pid=2 pos=1 obj=31778 op='TABLE ACCESS BY INDEX ROWID


G_REGIONS '



STAT #2 id=4 cnt=231 pid=3 pos=1 obj=34964 op='INDEX RANGE SCAN '
STAT #2 id=5 cnt=239 pid=2 pos=2 obj=38677 op='TABLE ACCESS FULL
L_TRANSLATIONS '

 
Please I am not a Good Tuning expert and really facinf big application
slowness issue.


Do you think that it is due to network
 


tHanks in Advance for all observation , time and consideration.
 


Sanjay


Juan Carlos Reyes Pacheco <jreyes_at_dazasoftware.com> wrote:
Hi why don't you paste the raw trace file 
 


Juan Carlos Reyes Pacheco


OCP



-------Original Message-------

 


From: smishra_97_at_yahoo.com


Date: 10/12/04 14:46:26


To: oracle-l_at_freelists.org


Subject: Sincere Advice on Sql Plan - Thanks
 


I had one FOrms application running using Oracle 9iAS Releas1 and 9i Rel 1DB
I got user complaining that one forms is very slow and after tracing I got
the following kind of TKPROF output for lot of queries
 


call count cpu elapsed disk query current rows


------- ------ -------- ---------- ---------- ---------- ----------





Parse 3262 0.32 0.41 0 0 0 0


Execute 3262 0.97 1.19 0 0 0 0


Fetch 3262 0.84 2.17 120 26472 0 3262


------- ------ -------- ---------- ---------- ---------- ----------





total 9786 2.13 3.78 120 26472 0 3262


I don't understand as why there is so much of Parsing. Can somebody advice
as what is this and why it is so.
 


TIA

 
 




Do you Yahoo!?


vote.yahoo.com - Register online to vote today!
 

--
http://www.freelists.org/webpage/oracle-l
 
 
 
Do you Yahoo!?
vote.yahoo.com - Register online to vote today! 
 
--
http://www.freelists.org/webpage/oracle-l


Received on Tue Oct 12 2004 - 17:18:47 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US