Home » RDBMS Server » Performance Tuning » SQL tuning (11.2.0.2)
| SQL tuning [message #530196] |
Sun, 06 November 2011 15:08  |
 |
singh09
Messages: 8
Registered: October 2011
|
Junior Member |
|
|
SELECT C.*,
SUBSTR (D.AlertFactIDSix, (INSTR (D.AlertFactIDSix, 'X') + 1), 9)
AS SixAlertStatus,
SUBSTR (D.AlertFactIDSeven,
(INSTR (D.AlertFactIDSeven, 'X') + 1),
9)
AS SevenAlertStatus,
SUBSTR (D.AlertFactIDEight,
(INSTR (D.AlertFactIDEight, 'X') + 1),
9)
AS EightAlertStatus,
SUBSTR (D.AlertFactIDSix, 1, (INSTR (D.AlertFactIDSix, 'X') - 1))
AS AlertFactIDSix,
SUBSTR (B.AlertFactIDSeven,
1,
(INSTR (D.AlertFactIDSeven, 'X') - 1))
AS AlertFactIDSeven,
SUBSTR (B.AlertFactIDEight,
1,
(INSTR (D.AlertFactIDEight, 'X') - 1))
AS AlertFactIDEight,
C.Date_DM_ID AS AlertCVDate
FROM (SELECT DISTINCT
MasterPlan.Master_Plan_Dm_Id AS MasterPlanDmId,
MasterPlan.Master_Plan_Code AS MasterPlanCode,
MasterPlan.Master_Plan_Name AS MasterPlanName,
Emp.Employer_Code AS EmprCode,
Emp.Employer_Name AS EmployerName,
'All' AS PlanCode,
'Consolidated' AS PlanName,
Emp.Employee_Dm_ID AS EmpDmId,
EmpBalFact.Date_Dm_Id,
Vendor.Vendor_dm_ID,
Emp.Employee_Last_Name AS LName,
Emp.Employee_First_Name AS FName,
SUBSTR (TRIM (Emp.Employee_Middle_Name), 1, 1) AS MII,
Emp.Employee_SSN AS SSN,
Emp.Employee_EE_ID AS EEIDNo,
EmpBalFact.Employee_Account_Num AS EmpAcctNum,
CASE
WHEN EmpBalFact.Method_Of_Reporting_Loan_Data = 'M'
THEN
'N/A'
WHEN EmpBalFact.Method_Of_Reporting_Loan_Data = 'C'
THEN
EmpLoanBalFact.Vendor_Loan_Num
ELSE
NULL
END
AS LoanNo,
Vendor.Vendor_Code AS Vendor,
CASE
WHEN EmpBalFact.Method_Of_Reporting_Loan_Data = 'M'
THEN
'S'
WHEN EmpBalFact.Method_Of_Reporting_Loan_Data = 'C'
THEN
'D'
ELSE
NULL
END
AS DetailOrSummary,
(CASE
WHEN EmpBalFact.Method_Of_Reporting_Loan_Data = 'M'
THEN
EmpBalFact.Num_Of_Loans_Outstanding
WHEN EmpLoanBalFact.Loan_Status != 'P'
THEN
1
ELSE
0
END)
AS NoOfOutStandingLoans,
CASE
WHEN EmpBalFact.Method_Of_Reporting_Loan_Data = 'M'
THEN
'N/A'
WHEN EmpBalFact.Method_Of_Reporting_Loan_Data = 'C'
THEN
TO_CHAR (EmpLoanBalFact.Loan_Initiation_Date,
'mm/dd/yyyy')
END
AS LoanInitiationDt,
CASE
WHEN EmpBalFact.Method_Of_Reporting_Loan_Data = 'M'
THEN
'Z'
ELSE
CASE
WHEN EmpLoanBalFact.Loan_Status = 'D'
THEN
'DEFAULT'
WHEN EmpLoanBalFact.Loan_Status = 'A'
THEN
'ACTIVE'
WHEN EmpLoanBalFact.Loan_Status = 'P'
THEN
'PAID UP'
ELSE
NULL
END
END
AS LoanStatus,
CASE
WHEN EmpBalFact.Method_Of_Reporting_Loan_Data = 'M'
THEN
'N/A'
ELSE
CASE
WHEN EmpLoanBalFact.Loan_Type_Indicator = 'R'
THEN
'Residential'
WHEN EmpLoanBalFact.Loan_Type_Indicator = 'G'
THEN
'General'
ELSE
NULL
END
END
AS LoanTypeIndicator,
CASE
WHEN EmpBalFact.Method_Of_Reporting_Loan_Data = 'M'
THEN
'0.00'
WHEN EmpBalFact.Method_Of_Reporting_Loan_Data = 'C'
THEN
TO_CHAR (EmpLoanBalfact.Original_Loan_Amount)
ELSE
NULL
END
AS OriginalLoanAmt,
EmpBalSumm.Highest_Outstanding_Loan_Bal
AS HighestOutLoanBalLast12Mths,
EmpBalSumm.SUM_OF_LOANS_OUTSTANDING
AS CurrOutstandingLoanAmt,
EmpBalSumm.Est_Vested_Account_Balance AS EstVestedAccBal,
EmpBalSumm.Est_Available_Loan_Balance AS EstAvailLoanBal
FROM Employee_Cashvalue_Dt_Lkp LastCashValue,
Employee_Loan_Balance_Fact EmpLoanBalFact,
Employee_Balance_Summary EmpBalSumm,
Employee_Balance_Fact EmpBalFact,
Employee_Dim Emp,
Plan_Dim Plan,
Master_Plan_Dim MasterPlan,
Vendor_Dim Vendor
WHERE MasterPlan.Employer_Code = Emp.Employer_Code
AND MasterPlan.Master_Plan_Dm_Id = Plan.Master_Plan_Dim_Id
AND EmpLoanBalFact.Employee_DM_ID(+) =
EmpBalFact.Employee_DM_ID
AND EmpLoanBalFact.Plan_DM_ID(+) = EmpBalFact.Plan_DM_ID
AND EmpLoanBalFact.Vendor_DM_ID(+) =
EmpBalFact.Vendor_DM_ID
AND EmpLoanBalFact.Date_DM_ID(+) = EmpBalFact.Date_DM_ID
AND EmpLoanBalFact.Employee_Account_Num(+) =
EmpBalFact.Employee_Account_Num
AND EmpBalFact.Employee_DM_ID = Emp.Employee_DM_ID
AND EmpBalFact.Plan_DM_ID = Plan.Plan_DM_ID
AND EmpBalFact.Vendor_DM_ID = Vendor.vendor_DM_ID
AND LastCashValue.Master_Plan_Dm_ID =
MasterPlan.Master_Plan_Dm_ID
AND LastCashValue.Plan_Dm_ID = EmpBalFact.Plan_Dm_ID
AND LastCashValue.Plan_Dm_ID = Plan.Plan_DM_ID
AND LastCashValue.Employee_Dm_ID = Emp.Employee_DM_ID
AND LastCashValue.Employee_Dm_ID =
EmpBalFact.Employee_Dm_ID
AND LastCashValue.Last_Cash_Value_Date =
EmpBalFact.Date_Dm_ID
AND LastCashValue.Employee_Account_Num =
EmpBalFact.Employee_Account_Num
AND EmpBalSumm.Master_Plan_Dm_ID =
MasterPlan.Master_Plan_Dm_ID
AND EmpBalSumm.Employee_Dm_ID =
LastCashValue.Employee_Dm_ID
AND EmpBalSumm.Date_DM_ID =
LastCashValue.Last_Cash_Value_Date
AND Plan.Master_Plan_Dim_Id =
LastCashValue.Master_Plan_Dm_ID
AND Plan.Master_Plan_Dim_Id = EmpBalSumm.Master_Plan_Dm_ID
AND LastCashValue.Master_Plan_Dm_ID =
EmpBalSumm.Master_Plan_Dm_ID
AND LastCashValue.Date_Dm_Id = 20111130
AND MasterPlan.Master_Plan_DM_ID = 1106
AND LastCashValue.Master_Plan_DM_ID = 1106) C,
( SELECT a.Master_Plan_DM_ID,
a.Employee_DM_ID,
MAX(CASE
WHEN a.Alert_Type_ID = 6
THEN
a.Alert_Fact_ID || 'X' || a.Alert_Status
END)
AS AlertFactIDSix,
MAX(CASE
WHEN a.Alert_Type_ID = 7
THEN
a.Alert_Fact_ID || 'X' || a.Alert_Status
END)
AS AlertFactIDSeven,
MAX(CASE
WHEN a.Alert_Type_ID = 8
THEN
a.Alert_Fact_ID || 'X' || a.Alert_Status
END)
AS AlertFactIDEight
FROM Employee_Cashvalue_Dt_Lkp b, Employee_Alert_Fact a
WHERE a.Master_Plan_DM_ID = b.Master_Plan_DM_ID
AND a.Employee_DM_ID = b.Employee_DM_ID
AND a.Alert_Type_ID IN (6, 7, 8)
AND b.Date_DM_ID = 20111130
AND a.Master_Plan_DM_ID = 1106
AND b.Master_Plan_DM_ID = 1106
GROUP BY a.Master_Plan_DM_ID, a.Employee_DM_ID) D
WHERE C.MasterPlanDmId = D.Master_Plan_DM_ID(+)
AND C.EmpDmId = D.Employee_DM_ID(+)
ORDER BY C.SSN, C.Date_Dm_ID DESC
explain plan....
------------------------------------------------------------------------------------------------------------------------------------- ----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------- ----------
| 0 | SELECT STATEMENT | | 1 | 415 | 28 (8)| 00:00:01 | | |
| 1 | SORT ORDER BY | | 1 | 415 | 28 (8)| 00:00:01 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 415 | 27 (4)| 00:00:01 | | |
| 3 | VIEW | | 1 | 330 | 21 (5)| 00:00:01 | | |
| 4 | HASH UNIQUE | | 1 | 386 | 21 (5)| 00:00:01 | | |
| 5 | NESTED LOOPS | | | | | | | |
| 6 | NESTED LOOPS | | 1 | 386 | 20 (0)| 00:00:01 | | |
| 7 | NESTED LOOPS | | 1 | 370 | 19 (0)| 00:00:01 | | |
| 8 | NESTED LOOPS | | 1 | 278 | 17 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS OUTER | | 1 | 209 | 14 (0)| 00:00:01 | | |
| 10 | NESTED LOOPS | | 1 | 133 | 11 (0)| 00:00:01 | | |
| 11 | NESTED LOOPS | | 1 | 88 | 8 (0)| 00:00:01 | | |
| 12 | NESTED LOOPS | | 1 | 43 | 4 (0)| 00:00:01 | | |
| 13 | TABLE ACCESS BY INDEX ROWID | MASTER_PLAN_DIM | 1 | 31 | 2 (0)| 00:00:01 | | |
|* 14 | INDEX UNIQUE SCAN | MASTER_PLAN_DIM_XPK | 1 | | 1 (0)| 00:00:01 | | |
| 15 | TABLE ACCESS BY INDEX ROWID | PLAN_DIM | 1 | 12 | 2 (0)| 00:00:01 | | |
|* 16 | INDEX RANGE SCAN | PLAN_DIM_IDX01 | 1 | | 1 (0)| 00:00:01 | | |
|* 17 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE_CASHVALUE_DT_LKP | 1 | 45 | 4 (0)| 00:00:01 | | |
|* 18 | INDEX RANGE SCAN | EMPLOYEE_CASHVALUE_DT_LKP_XPK | 1 | | 3 (0)| 00:00:01 | | |
| 19 | PARTITION RANGE ITERATOR | | 1 | 45 | 3 (0)| 00:00:01 | KEY | KEY |
|* 20 | TABLE ACCESS BY LOCAL INDEX ROWID| EMPLOYEE_BALANCE_FACT | 1 | 45 | 3 (0)| 00:00:01 | KEY | KEY |
|* 21 | INDEX RANGE SCAN | EMPLOYEE_BALANCE_FACT_IDX02 | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
|* 22 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE_LOAN_BALANCE_FACT | 1 | 76 | 3 (0)| 00:00:01 | | |
|* 23 | INDEX RANGE SCAN | EMPLOYEE_LOAN_BALANCE_FACT_XPK | 1 | | 2 (0)| 00:00:01 | | |
| 24 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE_BALANCE_SUMMARY | 1 | 69 | 3 (0)| 00:00:01 | | |
|* 25 | INDEX UNIQUE SCAN | EMPLOYEE_BALANCE_SUMMARY_XPK | 1 | | 2 (0)| 00:00:01 | | |
|* 26 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE_DIM | 1 | 92 | 2 (0)| 00:00:01 | | |
|* 27 | INDEX UNIQUE SCAN | EMPLOYEE_DIM_XPK | 1 | | 1 (0)| 00:00:01 | | |
|* 28 | INDEX UNIQUE SCAN | VENDOR_DIM_DM_ID_XPK | 1 | | 0 (0)| 00:00:01 | | |
| 29 | TABLE ACCESS BY INDEX ROWID | VENDOR_DIM | 1 | 16 | 1 (0)| 00:00:01 | | |
| 30 | VIEW PUSHED PREDICATE | | 1 | 85 | 6 (0)| 00:00:01 | | |
| 31 | SORT GROUP BY | | 1 | 43 | 6 (0)| 00:00:01 | | |
|* 32 | FILTER | | | | | | | |
| 33 | NESTED LOOPS | | | | | | | |
| 34 | NESTED LOOPS | | 1 | 43 | 6 (0)| 00:00:01 | | |
|* 35 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE_ALERT_FACT | 1 | 26 | 2 (0)| 00:00:01 | | |
|* 36 | INDEX RANGE SCAN | EMPLOYEE_ALERT_FACT_IDX01 | 1 | | 1 (0)| 00:00:01 | | |
|* 37 | INDEX RANGE SCAN | EMPLOYEE_CASHVALUE_DT_LKP_XPK | 1 | | 3 (0)| 00:00:01 | | |
|* 38 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE_CASHVALUE_DT_LKP | 1 | 17 | 4 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------- ----------
the sql above is taking 7 sec but should be taking 3secs. Need help!!!
* <code_tags> Added by BlackSwan - Please do so yourself in the future
[Updated on: Sun, 06 November 2011 17:53] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: SQL tuning [message #530210 is a reply to message #530198] |
Mon, 07 November 2011 01:17  |
 |
LNossov
Messages: 318
Registered: July 2011
Location: Germany
|
Senior Member |
|
|
Could you please provide some additional information:
1. make the following settings
set linesize 1000
set pagesize 1000
2. then run
alter session set statistics_level=all;
3. then run your sql,
4. after that run the following select:
select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));
5. upload the last formatted output.
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 23:50:50 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
