Home » RDBMS Server » Performance Tuning » Need help in tuning query (11i)
Need help in tuning query [message #601874] Tue, 26 November 2013 10:19 Go to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
There is one sql that is taking time.
SELECT PLL.po_header_id, 
       PLL.po_line_id, 
       PLL.po_release_id, 
       PLL.line_location_id 
FROM   po_headers POH, 
       po_line_locations PLL, 
       po_releases PR 
WHERE  POH.segment1 = :B3 
       AND POH.po_header_id = PLL.po_header_id 
       AND POH.po_header_id = PR.po_header_id 
       AND PLL.po_release_id = PR.po_release_id 
       AND PR.release_num = :B2 
       AND PLL.shipment_num = :B1 ;

---------------------------------------------------------------------------------------------------
 Id  | Operation                      | Name                  | Rows  | Bytes | Cost (%CPU)| Time  
---------------------------------------------------------------------------------------------------
   0 | SELECT STATEMENT               |                       |     1 |    68 |     9   (0)| 00:00:
   1 |  NESTED LOOPS                  |                       |       |       |            |       
   2 |   NESTED LOOPS                 |                       |     1 |    68 |     9   (0)| 00:00:
   3 |    NESTED LOOPS                |                       |     1 |    48 |     8   (0)| 00:00:
   4 |     TABLE ACCESS BY INDEX ROWID| PO_HEADERS_ALL        |     1 |    20 |     4   (0)| 00:00:
*  5 |      INDEX RANGE SCAN          | PO_HEADERS_U2         |     1 |       |     3   (0)| 00:00:
*  6 |     TABLE ACCESS BY INDEX ROWID| PO_LINE_LOCATIONS_ALL |     1 |    28 |     4   (0)| 00:00:
*  7 |      INDEX RANGE SCAN          | PO_LINE_LOCATIONS_N2  |     5 |       |     2   (0)| 00:00:

*  8 |    INDEX UNIQUE SCAN           | PO_RELEASES_U1        |     1 |       |     0   (0)| 00:00:
*  9 |   TABLE ACCESS BY INDEX ROWID  | PO_RELEASES_ALL       |     1 |    20 |     1   (0)| 00:00:
---------------------------------------------------------------------------------------------------

*BlackSwan added {code} tags. Please do so yourself in the future.

[Updated on: Tue, 26 November 2013 11:44] by Moderator

Report message to a moderator

Re: Need help in tuning query [message #601875 is a reply to message #601874] Tue, 26 November 2013 10:22 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  NESTED LOOPS  (cr=4580 pr=3033 pw=0 time=50360454 us)
       772        772        772   NESTED LOOPS  (cr=3809 pr=2337 pw=0 time=39918762 us cost=9 size=68 card=1)
       772        772        772    NESTED LOOPS  (cr=3034 pr=1954 pw=0 time=36152789 us cost=8 size=48 card=1)
         1          1          1     TABLE ACCESS BY INDEX ROWID PO_HEADERS_ALL (cr=4 pr=4 pw=0 time=162977 us cost=4 size=20 card=1)
         1          1          1      INDEX RANGE SCAN PO_HEADERS_U2 (cr=3 pr=3 pw=0 time=137638 us cost=3 size=0 card=1)(object id 45053)
       772        772        772     TABLE ACCESS BY INDEX ROWID PO_LINE_LOCATIONS_ALL (cr=3030 pr=1950 pw=0 time=35987008 us cost=4 size=28 card=1)
      3311       3311       3311      INDEX RANGE SCAN PO_LINE_LOCATIONS_N2 (cr=17 pr=17 pw=0 time=335751 us cost=2 size=0 card=5)(object id 45131)
       772        772        772    INDEX UNIQUE SCAN PO_RELEASES_U1 (cr=775 pr=383 pw=0 time=3758739 us cost=0 size=0 card=1)(object id 45175)
         1          1          1   TABLE ACCESS BY INDEX ROWID PO_RELEASES_ALL (cr=771 pr=696 pw=0 time=10437762 us cost=1 size=20 card=1)

*BlackSwan added {code} tags. Please do so yourself in the future.

[Updated on: Tue, 26 November 2013 11:02] by Moderator

Report message to a moderator

Re: Need help in tuning query [message #601879 is a reply to message #601875] Tue, 26 November 2013 10:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>There is one sql that is taking time.
PLAN shows 00:00 seconds to return single row.

I don't see any problem here.
Re: Need help in tuning query [message #601880 is a reply to message #601879] Tue, 26 November 2013 11:19 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
If you can see the trace file which i have attached show you that it has taken 30 minutes
Re: Need help in tuning query [message #601881 is a reply to message #601880] Tue, 26 November 2013 11:34 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
what trace file?
I doubt that trace file & EXPLAIN PLAN were produced on the same database.
Previous Topic: Performance degrade
Next Topic: Automatic SQL Tuning
Goto Forum:
  


Current Time: Thu Mar 28 12:51:46 CDT 2024