Home » RDBMS Server » Performance Tuning » Performance problem (Merged)
Performance problem (Merged) [message #379157] Mon, 05 January 2009 03:14 Go to next message
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 #379159 is a reply to message #379157] Mon, 05 January 2009 03:19 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Quote:
AND atn_dte BETWEEN '01-DEC-2008' AND '31-DEC-2008'

'01-DEC-2008' is a string, not a date.
You should not rely on implicit datatype conversions, but use TO_DATE.

[Updated on: Mon, 05 January 2009 03:19]

Report message to a moderator

Re: WHAT MAY PROBLEM [message #379165 is a reply to message #379159] Mon, 05 January 2009 03:39 Go to previous messageGo to next message
mamalik
Messages: 268
Registered: November 2008
Location: Pakistan
Senior Member

Dear Thank for response.

Actually i am running query by a report and date is passed by Parameters whose datatype is Date.

Best Regards.
Muhammad Asif.

Re: WHAT MAY PROBLEM [message #379168 is a reply to message #379157] Mon, 05 January 2009 03:58 Go to previous messageGo to next message
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

Re: WHAT MAY PROBLEM [message #379169 is a reply to message #379168] Mon, 05 January 2009 04:02 Go to previous messageGo to next message
mamalik
Messages: 268
Registered: November 2008
Location: Pakistan
Senior Member

how can i get execution plan???
Re: WHAT MAY PROBLEM [message #379173 is a reply to message #379169] Mon, 05 January 2009 04:14 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

http://www.orafaq.com/forum/t/84315/94420/

Go through it. It explains it in detail how to identify the performance bottleneck.

Hope this helps.

Regards

Raj
Re: WHAT MAY PROBLEM [message #379188 is a reply to message #379157] Mon, 05 January 2009 05:25 Go to previous messageGo to next message
mamalik
Messages: 268
Registered: November 2008
Location: Pakistan
Senior Member

I have excution plan in plan table how can i put it here.


Regards.
Asif.
Re: WHAT MAY PROBLEM [message #379193 is a reply to message #379157] Mon, 05 January 2009 05:35 Go to previous messageGo to next message
mamalik
Messages: 268
Registered: November 2008
Location: Pakistan
Senior Member

Select lpad(' ',2*(level-1))||operation||' '||options||' '||object_name 
     ||' '||decode(id,0,'Cost = '||position) "Query Plan" 
   From plan_table 
   Start with id = 0  
   connect by prior id = parent_id 

Query Plan

SELECT STATEMENT   Cost = 
  SORT GROUP BY  
    NESTED LOOPS   
      TABLE ACCESS BY INDEX ROWID EMP_ATN_03 
        INDEX RANGE SCAN EMP_ATN_03_UK 
      TABLE ACCESS BY INDEX ROWID EMP_ATN_04 
        INDEX RANGE SCAN EMP_ATN_04_PK 
how can we change execution plan of a query [message #379200 is a reply to message #379157] Mon, 05 January 2009 05:51 Go to previous messageGo to next message
mamalik
Messages: 268
Registered: November 2008
Location: Pakistan
Senior Member

Dear

How can we change execution plan of a query???.


Best Regards.
Asif
Re: how can we change execution plan of a query [message #379201 is a reply to message #379200] Mon, 05 January 2009 06:00 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Here's one link:
http://www.orafaq.com/tuningguide

There are some others posted on the sticky note at the top of the Performance Tuning Forum on this site.

Ross Leishman
Previous Topic: Db writer Process
Next Topic: Primary key index not working proper after import
Goto Forum:
  


Current Time: Thu Dec 05 15:56:34 CST 2024