Home » RDBMS Server » Performance Tuning » Response time (12.1.0.1.0, solaris)
Response time [message #640057] |
Tue, 21 July 2015 11:27  |
 |
kapilavastu
Messages: 9 Registered: July 2015
|
Junior Member |
|
|
here is the query I am running ( have changed the table names so that it can be posted here) and from the tkprof output, I see that it takes 7.31 seconds. but it takes 2 minutes for the data to appear completely to the front end ( DB Artisan ) - same is the case for the web based application. can anything been done to improve this?
SELECT z.tablea_sid ,
z.invalid_yn ,
NVL(z.hsid, z.cpcsid) AS CDETER_SID ,
NVL(d.hcode, e.pcode) AS cd_code,
b.gnum_code ,
c.lnum_code
FROM tablea z ,
tablea_component a ,
pcg b ,
pcg_line c,
hb d ,
cheprodc e
WHERE a.tablea_sid = 100300
AND z.tablea_sid = a.tablea_sid
AND a.pcg_sid = b.pcg_sid
AND a.pcgline_sid = c.pcgline_sid (+)
AND z.hsid = d.hsid (+)
AND z.cpcsid = e.cpcsid (+)
ORDER BY z.tablea_sid,
b.gnum_code ,
c.lnum_code
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.06 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 20737 4.57 7.24 2245 3478 0 311040
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20739 4.63 7.31 2245 3478 0 311040
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 157
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
311040 311040 311040 SORT ORDER BY (cr=3478 pr=2245 pw=0 time=6875410 us cost=10290 size=25816320 card=311040)
311040 311040 311040 NESTED LOOPS OUTER (cr=3478 pr=2245 pw=0 time=6545661 us cost=4256 size=25816320 card=311040)
311040 311040 311040 HASH JOIN RIGHT OUTER (cr=3478 pr=2245 pw=0 time=5939691 us cost=4110 size=20528640 card=311040)
1400 1400 1400 TABLE ACCESS FULL pcg_LINE (cr=16 pr=1 pw=0 time=1440 us cost=6 size=14000 card=1400)
311040 311040 311040 HASH JOIN (cr=3462 pr=2244 pw=0 time=5071398 us cost=4100 size=17418240 card=311040)
200 200 200 TABLE ACCESS FULL pcg (cr=4 pr=0 pw=0 time=122 us cost=3 size=1600 card=200)
311040 311040 311040 HASH JOIN RIGHT OUTER (cr=3458 pr=2244 pw=0 time=4696642 us cost=4093 size=14929920 card=311040)
65 65 65 VIEW index$_join$_006 (cr=8 pr=3 pw=0 time=18475 us cost=2 size=1105 card=65)
65 65 65 HASH JOIN (cr=8 pr=3 pw=0 time=18407 us)
65 65 65 INDEX FAST FULL SCAN PK_cheprodc (cr=4 pr=1 pw=0 time=1346 us cost=1 size=1105 card=65)(object id 1427165)
65 65 65 INDEX FAST FULL SCAN UK1_cheprodc (cr=4 pr=2 pw=0 time=12922 us cost=1 size=1105 card=65)(object id 1427166)
311040 311040 311040 HASH JOIN (cr=3450 pr=2241 pw=0 time=4321108 us cost=4087 size=9642240 card=311040)
233265 233265 233265 NESTED LOOPS (cr=1067 pr=962 pw=0 time=227852 us)
233265 233265 233265 NESTED LOOPS (cr=1067 pr=962 pw=0 time=175254 us cost=4087 size=9642240 card=311040)
233265 233265 233265 STATISTICS COLLECTOR (cr=1067 pr=962 pw=0 time=123296 us)
233265 233265 233265 TABLE ACCESS FULL tablea (cr=1067 pr=962 pw=0 time=61686 us cost=305 size=2565915 card=233265)
0 0 0 INDEX RANGE SCAN PK_tablea_COMPONEN (cr=0 pr=0 pw=0 time=0 us cost=712 size=0 card=317295)(object id 1427267)
0 0 0 TABLE ACCESS BY INDEX ROWID tablea_COMPONENT (cr=0 pr=0 pw=0 time=0 us cost=3044 size=20 card=1)
311040 311040 311040 TABLE ACCESS BY INDEX ROWID BATCHED tablea_COMPONENT (cr=2383 pr=1279 pw=0 time=3301193 us cost=3044 size=6220800 card=311040)
311040 311040 311040 INDEX RANGE SCAN FK_IDX3_tablea_COMPO (cr=654 pr=344 pw=0 time=151200 us cost=712 size=0 card=317295)(object id 1427265)
0 0 0 TABLE ACCESS BY INDEX ROWID hb (cr=0 pr=0 pw=0 time=405575 us cost=1 size=17 card=1)
0 0 0 INDEX UNIQUE SCAN PK_hb (cr=0 pr=0 pw=0 time=158278 us cost=0 size=0 card=1)(object id 1427192)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
311040 SORT (ORDER BY)
311040 NESTED LOOPS (OUTER)
311040 HASH JOIN (RIGHT OUTER)
1400 TABLE ACCESS MODE: ANALYZED (FULL) OF
'pcg_LINE' (TABLE)
311040 HASH JOIN
200 TABLE ACCESS MODE: ANALYZED (FULL) OF
'pcg' (TABLE)
311040 HASH JOIN (RIGHT OUTER)
65 VIEW OF 'index$_join$_006' (VIEW)
65 HASH JOIN
65 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'PK_cheprodc' (INDEX (UNIQUE))
65 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'UK1_cheprodc' (INDEX (UNIQUE))
311040 HASH JOIN
233265 NESTED LOOPS
233265 NESTED LOOPS
233265 STATISTICS COLLECTOR
233265 TABLE ACCESS MODE: ANALYZED (FULL) OF
'tablea' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_tablea_COMPONEN' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'tablea_COMPONENT' (TABLE)
311040 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID
BATCHED) OF 'tablea_COMPONENT' (TABLE)
311040 INDEX MODE: ANALYZED (RANGE SCAN) OF
'FK_IDX3_tablea_COMPO' (INDEX)
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'HB'
(TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_HB' (INDEX
(UNIQUE))
|
|
|
|
|
Re: Response time [message #640693 is a reply to message #640063] |
Mon, 03 August 2015 22:44   |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Do this.
create table kevtemp1
nologging
as
SELECT z.tablea_sid ,
z.invalid_yn ,
NVL(z.hsid, z.cpcsid) AS CDETER_SID ,
NVL(d.hcode, e.pcode) AS cd_code,
b.gnum_code ,
c.lnum_code
FROM tablea z ,
tablea_component a ,
pcg b ,
pcg_line c,
hb d ,
cheprodc e
WHERE a.tablea_sid = 100300
AND z.tablea_sid = a.tablea_sid
AND a.pcg_sid = b.pcg_sid
AND a.pcgline_sid = c.pcgline_sid (+)
AND z.hsid = d.hsid (+)
AND z.cpcsid = e.cpcsid (+)
ORDER BY z.tablea_sid,
b.gnum_code ,
c.lnum_code
/
Let us assume that this take 10 seconds. What does that tell you about your 2 minutes? Given an answer to this question whatever it is, what does the answer tell you about how to improve performance?
Kevin
[Updated on: Mon, 03 August 2015 22:45] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Wed Feb 19 13:47:09 CST 2025
|