Home » RDBMS Server » Performance Tuning » Performance problem (Merged)
Performance problem (Merged) [message #379157] |
Mon, 05 January 2009 03:14 |
mamalik
Messages: 268 Registered: November 2008 Location: Pakistan
|
Senior Member |
|
|
DEAR I HAVE FOLLOWING TABLES.
SQL> desc emp_Atn_03;
Name Null? Type
------------------------------- -------- ----
TRN_IDE NOT NULL NUMBER
BAS_COD NOT NULL NUMBER
ATN_DTE NOT NULL DATE
UNT_COD NOT NULL NUMBER
DPT_COD NOT NULL NUMBER
SFT_COD NOT NULL NUMBER
ATN_TYP NUMBER
INN_TIM DATE
CR_ID NUMBER
CR_DT DATE
AP_ID NUMBER
AP_DT DATE
SQL> desc emp_Atn_04
Name Null? Type
------------------------------- -------- ----
TRN_IDE NOT NULL NUMBER
BAS_COD NOT NULL NUMBER
EMP_COD NOT NULL NUMBER
DSG_COD NUMBER
EMP_ATN NOT NULL CHAR(3)
EMP_LEV CHAR(10)
TOT_ATN NOT NULL CHAR(3)
CHG_DSG NUMBER
CHG_SFT NUMBER
ORD_COD NUMBER
LIN_NUM NUMBER
SRL_NUM NUMBER
(TRN_IDE,BAS_COD) In Emp_Atn_03 Is Primary Key.
(Trn_Ide,Bas_Cod) In Emp_ATn_04 Is Foreign Key References Emp_Atn_03(emp_Cod,Bas_Cod
(Trn_Ide,Bas_Cod,Emp_Cod) Is Primary Key In Emp_Atn_04
INDEXES ON EMP_ATN_03
Index Name Unique? Column Name Order Position Index Owner
EMP_ATN_03_PK Y TRN_IDE Asc 1 SAL
EMP_ATN_03_PK Y BAS_COD Asc 2 SAL
EMP_ATN_03_UK Y ATN_DTE Asc 1 SAL
EMP_ATN_03_UK Y ATN_TYP Asc 2 SAL
EMP_ATN_03_UK Y UNT_COD Asc 3 SAL
EMP_ATN_03_UK Y DPT_COD Asc 4 SAL
EMP_ATN_03_UK Y SFT_COD Asc 5 SAL
INSEXES OF EMP_ATN_04
Index Name Unique? Column Name Order Position Index Owner
EMP_ATN_04_PK Y TRN_IDE Asc 1 SAL
EMP_ATN_04_PK Y BAS_COD Asc 2 SAL
EMP_ATN_04_PK Y EMP_COD Asc 3 SAL
Emp_Atn_03 table has 119753 Record;
Emp_ATn_04 table has 2338873 Record.
When i run follwoing query it takes too time.
SELECT DISTINCT TO_CHAR (atn_dte, 'dd') atn_dte, a4.emp_cod, emp_nam, emp_fnm,
dsg_des, tot_atn, a4.ord_cod, a4.lin_num, a3.unt_cod, unt_des,
dpt_des,
DECODE (sft_cod,
1, 'A',
2, 'B',
3, 'C',
4, 'G',
5, 'R'
) sft_des,
apt_dte, a4.bas_cod, sft_cod,
DECODE (RTRIM (tot_atn),
'P', 1,
'G', sal.val_guz_pay (a4.emp_cod,
a4.bas_cod,
a3.atn_dte,
a4.tot_atn
),
'P/2', 0.50,
'ML', 1,
'CL', 1,
'AL', 1,
0
) pay_dys,
DECODE (NVL (chg_dsg, 0), 0, 0, 1) chg_dsg, a3.dpt_cod
FROM sal.emp_atn_03 a3,
sal.emp_atn_04 a4,
sal.emp_fil_01 emp_fil,
sal.emp_dsg_01 emp_dsg,
sal.dsg_00_01 dsg,
sal.unt_00_01 u,
sal.dpt_00_01 d
WHERE a3.trn_ide = a4.trn_ide
AND a3.bas_cod = a4.bas_cod
AND emp_fil.emp_cod = a4.emp_cod
AND emp_fil.bas_cod = a4.bas_cod
AND emp_dsg.emp_cod = a4.emp_cod
AND emp_dsg.bas_cod = a4.bas_cod
AND emp_fil.cat_cod =1
AND dsg.dsg_cod = emp_dsg.dsg_cod
AND u.unt_cod = a3.unt_cod
AND d.dpt_cod = a3.dpt_cod
AND atn_dte BETWEEN emp_dsg.frm_dte
AND DECODE (emp_dsg.too_dte,
NULL, TRUNC (SYSDATE),
too_dte
)
AND a3.unt_cod BETWEEN 8 AND 40
AND a4.emp_cod BETWEEN 0 AND 99999999
AND atn_dte BETWEEN '01-DEC-2008' AND '31-DEC-2008'
ORDER BY a3.unt_cod,
sft_cod,
atn_dte,
dpt_cod,
a4.lin_num,
a4.ord_cod,
apt_dte
How can we improve it?
|
|
|
|
|
Re: WHAT MAY PROBLEM [message #379168 is a reply to message #379157] |
Mon, 05 January 2009 03:58 |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
I didn't realise you are using 8.1.7. Are you using RBO or CBO. Either way post the explain plan output ? Probably somebody will be able to guide you in the right directions.
Regards
Raj
P.S : On a side note it's time to upgrade to atleast 10.2.0.4
[Updated on: Mon, 05 January 2009 04:00] Report message to a moderator
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Dec 05 15:56:34 CST 2024
|