OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » SQL Tune (Oracle 9i)

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

SQL Tune [message #327901]

Wed, 18 June 2008 06:07

ankitmodicse
Messages: 6
Registered: February 2007
Location: Bangalore

Junior Member

hi All

how to tune this query its taking long time to execute more than 3 hr.

select
MembMaster.PasMemberID,
MembSIN.SIN,
SystPlan.PlanNumber,
SystGroup.GroupNumber,
CalcIdentifierDescHistoData.DescF,
MAX(MembHistoData.YearEnd),
MembHistoData.DateFrom,
MembHistoData.DateTo,
MembHistoSal.SalaryRate,
MembHistoSal.Salary,
MembHistoData.IsReleased,
ScReceivedType.LongDescF,
MembHistoData.DateRecord,
MembPensionStatusHisto.ScPensionStatusID,
MembPensionStatusHisto.StatusStartDate,
ScHistoryActivityType.LongDescF,
ScHistoDataType.LongDescF,
CalcIdentifierDescHistoData.CalcIdentifierDescID,
CalcIdentifierDescHistoData.ProgramName
,MembMaster.MembMasterID
from
MembBasic inner join MembMaster on (MembBasic.MembMasterID = MembMaster.MembMasterID and MembBasic.IsDeleted = 0 )
inner join MembSIN on (MembSIN.MembBasicID = MembBasic.MembBasicID )
inner join MembPlanInfo on (MembPlanInfo.MembMasterID = MembBasic.MembMasterID and MembPlanInfo.IsDeleted = 0 )
inner join MembPlanGroupHisto on (MembPlanGroupHisto.MembPlanInfoID = MembPlanInfo.MembPlanInfoID and MembPlanGroupHisto.IsDeleted = 0 )
inner join MembPensionStatusHisto on (MembPensionStatusHisto.MembPlanGroupHistoID = MembPlanGroupHisto.MembPlanGroupHistoID and MembPensionStatusHisto.IsDeleted = 0 )
inner join ScHistoryActivityType on (ScHistoryActivityType.ScHistoryActivityTypeID = MembPensionStatusHisto.ScHistoryActivityTypeID )
inner join MembHistoData on (MembHistoData.MembPlanGroupHistoID = MembPlanGroupHisto.MembPlanGroupHistoID and MembHistoData.IsDeleted = 0 )
inner join ScHistoDataType on (ScHistoDataType.ScHistoDataTypeID = MembHistoData.ScHistoDataTypeID )
inner join ScReceivedType on (ScReceivedType.ScReceivedTypeID = MembHistoData.ScReceivedTypeID )
inner join CalcIdentifierDesc CalcIdentifierDescHistoData on (CalcIdentifierDescHistoData.CalcIdentifierDescID = MembHistoData.CalcIdentifierDescID )
inner join MembHistoSal on (MembHistoSal.MembHistoDataID = MembHistoData.MembHistoDataID )
inner join SystGroup on (SystGroup.SystGroupID = MembPlanGroupHisto.SystGroupID )
inner join SystPlan on (SystPlan.SystPlanID = MembPlanInfo.SystPlanID )
where
(MembMaster.SystClientID = 1380 ) and
(MembMaster.ScDbEnvirID = 0) and
(MembMaster.IsDeleted = 0 ) and
(MembBasic.ScBasicRecordTypeID IN (0 ) ) and
(TO_CHAR (MembMaster.MembMasterID ) LIKE TO_CHAR ('%' ) )
and
(MembPensionStatusHisto.ScHistoryActivityTypeID = 0 )
group by
MembMaster.PasMemberID,
MembSIN.SIN,
SystPlan.PlanNumber,
SystGroup.GroupNumber,
CalcIdentifierDescHistoData.DescF,
MembHistoData.DateFrom,
MembHistoData.DateTo,
MembHistoSal.SalaryRate,
MembHistoSal.Salary,
MembHistoData.IsReleased,
ScReceivedType.LongDescF,
MembHistoData.DateRecord,
MembPensionStatusHisto.ScPensionStatusID,
MembPensionStatusHisto.StatusStartDate,
ScHistoryActivityType.LongDescF,
ScHistoDataType.LongDescF,
CalcIdentifierDescHistoData.CalcIdentifierDescID,
CalcIdentifierDescHistoData.ProgramName
,MembMaster.MembMasterID
order by
NLSSORT(MembMaster.PasMemberID,'NLS_SORT = FRENCH')

Report message to a moderator

Re: SQL Tune [message #327902 is a reply to message #327901]

Wed, 18 June 2008 06:10

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

Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel

Report message to a moderator

Previous Topic:	Performance issues
Next Topic:	Hit Rate.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 30 01:11:12 CST 2024