OraFAQ Forum: Performance Tuning » How to Tune my SQL (5 merged)

Home » RDBMS Server » Performance Tuning » How to Tune my SQL (5 merged) (10.2.0.3.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to Tune my SQL (5 merged) [message #560622]

Mon, 16 July 2012 02:12

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Hi,

I am tuning one of my query and it is taking long time. So I want to tune the SQL. I have taken the Explain plan as well.
I am attaching the Explain plan and query.

My ABR_SEGEMNT table is having --> 2387472 recs
My ABR_REF_CABLE_TYPE table is having --> 25 recs

See the below query which is calling from View:

Select DIRECT.OBJ_ID,
DIRECT.OBJ_ID As DIRECT_SEGMENT_OBJ_ID,
Null As INDIRECT_SEGMENT_OBJ_ID,
DIRECT.ABR_KBS_VERSION_OBJ_ID,
DIRECT.ABR_DISTRIBUTION_POINT_OBJ_ID,
DIRECT.NET_CD,
DIRECT.KBS_DOC,
DIRECT.SEGMENT_NUMBER_IN_LOOP As DIRECT_SGM_NMBR_IN_LOOP,
Null As INDIRECT_SGM_NMBR_IN_LOOP,
DIRECT.SEGMENT_SEQUENCE_NUMBER As DIRECT_SGMT_SQNC_NMBR,
Null As INDIRECT_SGMT_SQNC_NMBR,
DIRECT.CABLE_TYPE,
CABLE.CABLE_TYPE_VALUE,
DIRECT.CABLE_WIRE_DIAMETER,
DIRECT.CABLE_NUMBER,
DIRECT.AIR_LINE_INDICATOR,
DIRECT.CABLE_CAPACITY,
DIRECT.ABR_LOOP_OBJ_ID,
DIRECT.WRITEABLE_IN_CHECKOUT,
DIRECT.SEGMENT_DIRECTION_L_R As DIRECT_DIRECTION_L_R,
Null As INDIRECT_DIRECTION_L_R,
0 As SYMMETRIC,
DIRECT.SEGMENT_COUNTING_DIRECTION As COUNTING_DIRECTION
From abr_segment DIRECT left join ABR_REF_CABLE_TYPE CABLE on CABLE.CABLE_TYPE = DIRECT.CABLE_TYPE
Where DIRECT.OBJ_ID Not In
(Select distinct DIRECT.OBJ_ID
From abr_segment DIRECT
Inner Join ABR_SEGMENT INDIRECT On DIRECT.ABR_KBS_VERSION_OBJ_ID =
INDIRECT.ABR_KBS_VERSION_OBJ_ID
And DIRECT.ABR_DISTRIBUTION_POINT_OBJ_ID =
INDIRECT.ABR_DISTRIBUTION_POINT_OBJ_ID
And DIRECT.SEGMENT_NUMBER_IN_LOOP =
INDIRECT.SEGMENT_NUMBER_IN_LOOP
And DIRECT.ABR_LOOP_OBJ_ID =
INDIRECT.ABR_LOOP_OBJ_ID
Where DIRECT.START_SEGMENT_INDICATOR = 1
And INDIRECT.START_SEGMENT_INDICATOR = 0)
-- And DIRECT.NET_CD <> '000000'
-- And DIRECT.KBS_DOC <> '0000'
And DIRECT.START_SEGMENT_INDICATOR = 1
Union
Select DIRECT.OBJ_ID,
DIRECT.OBJ_ID As DIRECT_SEGMENT_OBJ_ID,
INDIRECT.OBJ_ID As INDIRECT_SEGMENT_OBJ_ID,
DIRECT.ABR_KBS_VERSION_OBJ_ID,
DIRECT.ABR_DISTRIBUTION_POINT_OBJ_ID,
DIRECT.NET_CD,
DIRECT.KBS_DOC,
DIRECT.SEGMENT_NUMBER_IN_LOOP As DIRECT_SGM_NMBR_IN_LOOP,
INDIRECT.SEGMENT_NUMBER_IN_LOOP As INDIRECT_SGM_NMBR_IN_LOOP,
DIRECT.SEGMENT_SEQUENCE_NUMBER As DIRECT_SGMT_SQNC_NMBR,
INDIRECT.SEGMENT_SEQUENCE_NUMBER As INDIRECT_SGMT_SQNC_NMBR,
DIRECT.CABLE_TYPE,
CABLE.CABLE_TYPE_VALUE,
DIRECT.CABLE_WIRE_DIAMETER,
DIRECT.CABLE_NUMBER,
DIRECT.AIR_LINE_INDICATOR,
DIRECT.CABLE_CAPACITY,
DIRECT.ABR_LOOP_OBJ_ID,
DIRECT.WRITEABLE_IN_CHECKOUT,
DIRECT.SEGMENT_DIRECTION_L_R As DIRECT_DIRECTION_L_R,
INDIRECT.SEGMENT_DIRECTION_L_R As INDIRECT_DIRECTION_L_R,
1 As SYMMETRIC,
DIRECT.SEGMENT_COUNTING_DIRECTION As COUNTING_DIRECTION
From abr_segment DIRECT
Inner Join ABR_SEGMENT INDIRECT On DIRECT.ABR_KBS_VERSION_OBJ_ID =
INDIRECT.ABR_KBS_VERSION_OBJ_ID
And DIRECT.ABR_DISTRIBUTION_POINT_OBJ_ID =
INDIRECT.ABR_DISTRIBUTION_POINT_OBJ_ID
And DIRECT.SEGMENT_NUMBER_IN_LOOP =
INDIRECT.SEGMENT_NUMBER_IN_LOOP
And DIRECT.ABR_LOOP_OBJ_ID =
INDIRECT.ABR_LOOP_OBJ_ID
left join ABR_REF_CABLE_TYPE CABLE on CABLE.CABLE_TYPE = DIRECT.CABLE_TYPE
Where DIRECT.START_SEGMENT_INDICATOR = 1
And INDIRECT.START_SEGMENT_INDICATOR = 0

Please suggest.

[Moderator: Merged 4 - RL]

[Updated on: Mon, 16 July 2012 02:32] by Moderator

Report message to a moderator

Re: How to Tune my SQL [message #560627 is a reply to message #560622]

Mon, 16 July 2012 02:29

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Report message to a moderator

How to Tune my SQL [message #560631 is a reply to message #560622]

Mon, 16 July 2012 02:44

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Report message to a moderator

Re: How to Tune my SQL [message #560694 is a reply to message #560631]

Mon, 16 July 2012 08:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I am attaching the Explain plan and query.
Really?

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Report message to a moderator

Re: How to Tune my SQL [message #560701 is a reply to message #560694]

Mon, 16 July 2012 08:31

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Please suggest

Michel Cadot wrote on Mon, 16 July 2012 09:29

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Report message to a moderator

Previous Topic:	Response very slow
Next Topic:	Oracle process exceed SGA_MAX_TARGET

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 19 13:43:37 CST 2025