Re: Sincere Advice on Sql Plan - Thanks -Please Help in Understanding
- From: Sanjay Mishra <smishra_97@xxxxxxxxx>
- To: Juan Carlos Reyes Pacheco <jreyes@xxxxxxxxxxxxxxxx>, mrothouse@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 12 Oct 2004 14:20:22 -0700 (PDT)
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@xxxxxxxxxxxxxxxx> wrote:
Hi why don't you paste the raw trace file
Juan Carlos Reyes Pacheco
OCP
-------Original Message-------
From: smishra_97@xxxxxxxxx
Date: 10/12/04 14:46:26
To: oracle-l@xxxxxxxxxxxxx
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!
--
//www.freelists.org/webpage/oracle-l
---------------------------------
Do you Yahoo!?
vote.yahoo.com - Register online to vote today!
--
//www.freelists.org/webpage/oracle-l
Other related posts:
