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: Sanjay Mishra <smishra_97_at_yahoo.com>
Date: Tue, 12 Oct 2004 14:20:22 -0700 (PDT)
Message-ID: <20041012212023.19701.qmail@web51307.mail.yahoo.com> 





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 - 16:16:06 CDT












Original text of this message













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