Home » RDBMS Server » Performance Tuning » SQL Tune (Oracle 9i)
SQL Tune [message #327901] Wed, 18 June 2008 06:07 Go to next message
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')
Re: SQL Tune [message #327902 is a reply to message #327901] Wed, 18 June 2008 06:10 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Performance issues
Next Topic: Hit Rate.
Goto Forum:
  


Current Time: Thu Dec 08 10:31:51 CST 2016

Total time taken to generate the page: 0.13814 seconds