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: Wed, 13 Oct 2004 05:22:01 -0700 (PDT)
Message-ID: <20041013122201.74031.qmail@web51306.mail.yahoo.com> 





Manoj
 


I checked and founnd the Cursor_sharing is exact and will use the Force. Actually in the trace which is created with Bind and Waits = to true shows that all of the Sql called are having "sql*net message to/from client" Do you think that this has link to any network issue.
 


I am also having some query where the trace shows the binds variable and below is one from the trace file. First is the TKprof output and second is content from trace itself. The tkprof show that Parsing is done for each rows
 

<<<<<<<<<<<<<<<TKPROF>>>>>>>>>>>>>>>>>>>


SELECT NVL(L.T_VALUE,S.R_VALUE)



FROM



 L_TRANSLATIONS L,SUB S  WHERE S.ID = L.SUB_ID (+)    AND

  L.L_TYPE_GR_ID (+)   = :b1  AND S.ID = :b2
 
 


call     count       cpu    elapsed       disk      query    current        rows


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


Parse     3262      0.36       0.38          0          0          0           0
Execute   3262      1.86       2.48          0          0          0           0
Fetch     3262      1.28       1.40          0      36274          0        3262


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

total     9786      3.50       4.27          0      36274          0        3262
 




Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: 722  (SANJAY)
 


Rows     Row Source Operation

-------  ---------------------------------------------------
      1  NESTED LOOPS OUTER
      1   TABLE ACCESS BY INDEX ROWID SUB
      1    INDEX UNIQUE SCAN (object id 50109)
      1   TABLE ACCESS BY INDEX ROWID L_TRANSLATIONS
      1    AND-EQUAL
      5     INDEX RANGE SCAN (object id 38679)
      4     INDEX RANGE SCAN (object id 50945)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   NESTED LOOPS (OUTER)
      1    TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'SUB'
      1     INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'SUB_PK' (UNIQUE)
      1    TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
               'L_TRANSLATIONS'
      1     AND-EQUAL
      5      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'LTR_IDX_3'
                 (NON-UNIQUE)
      4      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'LTR_IDX_2'
                 (NON-UNIQUE)
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       7        0.00          0.00
  SQL*Net message from client                     7        0.00          0.00
********************************************************************************
 
<<<<<<<<<<<<<<<<<<<<TRACE FILE EXCERPTS>>>>>>>>>>>>>>>>>>>>>>>


PARSING IN CURSOR #37 len=174 dep=0 uid=722 oct=3 lid=722 tim=1097075676760072 hv=2539746800 ad='8924d96c'
SELECT NVL(L.T_VALUE,S.R_VALUE)   FROM L_TRANSLATIONS L,SUB S  WHERE S.ID = L.SUB_ID (+)    AND

 L.LANGUAGE_TYPE_GR_ID (+)   = :b1  AND S.ID = :b2
END OF STMT



PARSE #37:c=0,e=2137,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1097075676760058
WAIT #37: nam='SQL*Net message to client' ela= 3 p1=675562835 p2=1 p3=0
WAIT #37: nam='SQL*Net message from client' ela= 931 p1=675562835 p2=1 p3=0
BINDS #37:



 bind 0: dty=1 mxl=2000(255) mal=00 scl=00 pre=00 oacflg=03 oacfl2=10 size=2000 offset=0
   bfp=fc8df0e0 bln=2000 avl=03 flg=05


   value="291"


 bind 1: dty=2 mxl=23(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=24 offset=0
   bfp=fc8e7bfc bln=23 avl=04 flg=05


   value=10103

EXEC #37:c=10000,e=4855,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1097075676766310
WAIT #37: nam='SQL*Net message to client' ela= 4 p1=675562835 p2=1 p3=0
WAIT #37: nam='SQL*Net message from client' ela= 432 p1=675562835 p2=1 p3=0
WAIT #37: nam='SQL*Net message to client' ela= 3 p1=675562835 p2=1 p3=0


FETCH #37:c=0,e=487,p=0,cr=13,cu=0,mis=0,r=1,dep=0,og=4,tim=1097075676767676

WAIT #37: nam='SQL*Net message from client' ela= 369 p1=675562835 p2=1 p3=0
WAIT #37: nam='SQL*Net message to client' ela= 4 p1=675562835 p2=1 p3=0
WAIT #37: nam='SQL*Net message from client' ela= 311 p1=675562835 p2=1 p3=0
STAT #36 id=1 cnt=1 pid=0 pos=0 obj=0 op='NESTED LOOPS '
STAT #36 id=2 cnt=1 pid=1 pos=1 obj=31796 op='TABLE ACCESS BY INDEX ROWID O_C_RELATIONS '
STAT #36 id=3 cnt=1 pid=2 pos=1 obj=50077 op='INDEX UNIQUE SCAN '
STAT #36 id=4 cnt=1 pid=1 pos=2 obj=31755 op='TABLE ACCESS BY INDEX ROWID CNCTS '
STAT #36 id=5 cnt=1 pid=4 pos=1 obj=50058 op='INDEX UNIQUE SCAN '
WAIT #36: nam='SQL*Net message to client' ela= 3 p1=675562835 p2=1 p3=0
WAIT #36: nam='SQL*Net message from client' ela= 287 p1=675562835 p2=1 p3=0
STAT #37 id=1 cnt=1 pid=0 pos=0 obj=0 op='NESTED LOOPS OUTER '
STAT #37 id=2 cnt=1 pid=1 pos=1 obj=31854 op='TABLE ACCESS BY INDEX ROWID SUB '
STAT #37 id=3 cnt=1 pid=2 pos=1 obj=50109 op='INDEX UNIQUE SCAN '
STAT #37 id=4 cnt=1 pid=1 pos=2 obj=38677 op='TABLE ACCESS BY INDEX ROWID L_TRANSLATIONS '
STAT #37 id=5 cnt=1 pid=4 pos=1 obj=0 op='AND-EQUAL '
STAT #37 id=6 cnt=5 pid=5 pos=1 obj=38679 op='INDEX RANGE SCAN '
STAT #37 id=7 cnt=4 pid=5 pos=2 obj=50945 op='INDEX RANGE SCAN '
WAIT #37: nam='SQL*Net message to client' ela= 5 p1=675562835 p2=1 p3=0
WAIT #37: nam='SQL*Net message from client' ela= 769 p1=675562835 p2=1 p3=0
WAIT #36: nam='SQL*Net message to client' ela= 3 p1=675562835 p2=1 p3=0
WAIT #36: nam='SQL*Net message from client' ela= 413 p1=675562835 p2=1 p3=0






This query is seen a few times in the Trace output.
 


Thanks



manojk.jha_at_tcs.com wrote:



HI All, 



The high no. of parsing ( in ur case no. of parse =no. of execute = no. of fetch) is due to cursor are not being shared. 
Please supply SQL_text and explain for further analysis. 
What I hopes is application is using hard coded value , thus limiting the cursor_sharing. 
In ur system check the cursor_sharing parameter. If the its value is similar or exact change to force for forms applications. 



Thanks & Regards,


Manoj Kumar Jha


Tata Consultancy Services Limited


Mailto: manojk.jha_at_tcs.com


Website: http://www.tcs.com 




Sanjay Mishra <smishra_97_at_yahoo.com> 


Sent by: oracle-l-bounce_at_freelists.org 


10/13/04 02:50 AM 


Please respond to


smishra_97_at_yahoo.com




To


Juan Carlos Reyes Pacheco <jreyes_at_dazasoftware.com>, mrothouse_at_gmail.com, oracle-l_at_freelists.org cc
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

ForwardSourceID:NT00010512     
DISCLAIMER: The information contained in this message is intended only and solely for the addressed individual or entity indicated in this message and for the exclusive use of the said addressed individual or entity indicated in this message (or responsible for delivery of the message to such person) and may contain legally privileged and confidential information belonging to Tata Consultancy Services Limited. It must not be printed, read, copied, disclosed, forwarded, distributed or used (in whatsoever manner) by any person other than the addressee. Unauthorized use, disclosure or copying is strictly prohibited and may constitute unlawful act and can possibly attract legal action, civil and/or criminal. The contents of this message need not necessarily reflect or endorse the views of Tata Consultancy Services limited on any subject matter.
Any action taken or omitted to be taken based on this message is entirely at your risk and neither the originator of this message nor Tata Consultancy Services Limited takes any responsibility or liability towards the same. Opinions, conclusions and any other information contained in this message that do not relate to the official business of Tata Consultancy Services limited shall be understood as neither given nor endorsed by Tata Consultancy Services Limited or any affiliate of Tata Consultancy Services Limited. If you have received this message in error, you should destroy this message and may please notify the sender by e-mail. 
Thank you.

		
---------------------------------
Do you Yahoo!?
vote.yahoo.com - Register online to vote today!

--
http://www.freelists.org/webpage/oracle-l


Received on Wed Oct 13 2004 - 07:17:45 CDT












Original text of this message













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