Home » RDBMS Server » Performance Tuning » Response time (12.1.0.1.0, solaris)
Response time [message #640057] Tue, 21 July 2015 11:27 Go to next message
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 #640061 is a reply to message #640057] Tue, 21 July 2015 11:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I conclude that no problem exists in the database or DB Server, but only at client or application server.
Re: Response time [message #640063 is a reply to message #640057] Tue, 21 July 2015 12:04 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I do notice that you are doing a lot of fetches: on average about 15 rows per fetch. You may want to try raising the array fetch size. I've no idea how to do that in Artisan. In SQL*Plus you would do it with (for example)

set arraysize 5000

Re: Response time [message #640693 is a reply to message #640063] Mon, 03 August 2015 22:44 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Do this.

set timing on


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

Re: Response time [message #641809 is a reply to message #640693] Mon, 24 August 2015 19:22 Go to previous messageGo to next message
kapilavastu
Messages: 9
Registered: July 2015
Junior Member
Thank you for your responses. Java developer also noticed that it is the screen rendering that took time and not the response time from DB. So they are implementing thru pagination techniques.
Re: Response time [message #641810 is a reply to message #641809] Mon, 24 August 2015 21:02 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thus we see an example of diagnosis in action, and being able to determine that in fact the problem was not the SQL at all, but rested elsewhere.

Thank you for getting back to us with the resolution. Kevin
Previous Topic: Interesting performance bottle neck issue
Next Topic: Oracle noforce view creates force view
Goto Forum:
  


Current Time: Thu Mar 28 14:48:01 CDT 2024