Home » RDBMS Server » Performance Tuning » SQL Tune (Oracle 9i)
SQL Tune [message #327901] |
Wed, 18 June 2008 06:07 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ankitmodicse
Messages: 6 Registered: February 2007 Location: Bangalore
|
Junior Member |
![akmodi143](/forum/theme/orafaq/images/yahoo.png)
|
|
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')
|
|
|
|
Goto Forum:
Current Time: Thu Aug 29 20:40:23 CDT 2024
|