Home » RDBMS Server » Performance Tuning » How to reduce cost of query ...Give Indexing on FT Scan (Oracle 10g)
How to reduce cost of query ...Give Indexing on FT Scan [message #580169] Thu, 21 March 2013 01:52 Go to next message
swapnil_naik
Messages: 268
Registered: December 2009
Location: Mumbai
Senior Member

Hi Friend,

I have Following wjich takes some minutes to executes i want to be tune
so this query Executes fast.

Query :
 SELECT a.CHDR_EXCH_CD ,TMHR_EXCH_TM_CD,'S' Sec_type,
        round(SUM (Decode(csdt_Depo_Typ,'I',(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty)-
        (Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty* (CSDT_HAIRCUT/ 100) ),0)),2)im_AMT,
        Round(SUM (Decode(csdt_Depo_Typ,'A',(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty)-
        (Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty* (CSDT_HAIRCUT/ 100) ),0)),2)Aim_AMT,
        round(SUM (Decode(csdt_Depo_Typ,'M',(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty)-
        (Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty* (CSDT_HAIRCUT/ 100) ),0)),2)Mtm_AMT,
        Round(SUM (Decode(csdt_Depo_Typ,'T',(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty)-
        (Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty* (CSDT_HAIRCUT/ 100) ),0)),2)AMTM_AMT,
        0 REFUND_AMT, 0 wITHDRAWL_AMT
        FROM MG_COLL_HDR a, mS_TM_HCUT_LINK C ,MG_COLL_SCP_DTL B
        WHERE A.CHDR_CLNT_CD=C.tmhr_tm_cd 
        AND C.tmhr_tm_cd  = B.CSDT_CHDR_CLNT_CD
         AND a.CHDR_TRANS_NUM = B.CSDT_CHDR_TRANS_NUM
        AND a.CHDR_CLNT_CD=DECODE(:P_TM_CD,'ALL', A.CHDR_CLNT_CD, :P_TM_CD)
        AND a.CHDR_EXCH_CD=decode(:P_EXCH,'ALL',a.CHDR_EXCH_CD,:P_exch)
        AND a.CHDR_TRANS_DATE <=:P_DT_FR
        GROUP BY a.CHDR_EXCH_CD,'S',TMHR_EXCH_TM_CD
 





Explain Plan Result :

 
Plan	   
SELECT STATEMENT  ALL_ROWS
Cost: 1,669  Bytes: 67  Cardinality: 1  	   
	15 HASH GROUP BY  
	Bytes: 67  Cardinality: 1  	   
		14 CONCATENATION  	   
			6 FILTER  	   
				5 HASH JOIN  
				Cost: 1,647  Bytes: 1,139  Cardinality: 17  	   
					3 HASH JOIN  
					Cost: 167  Bytes: 88,040  Cardinality: 2,840  	   
						1 TABLE ACCESS FULL TABLE TM.MS_TM_HCUT_LINK Object Instance: 2  
						Cost: 4  Bytes: 10,406  Cardinality: 946  	   
						2 INDEX FAST FULL SCAN INDEX TM.CHDR_MTM 
						Cost: 163  Bytes: 56,620  Cardinality: 2,831  	   
					4 TABLE ACCESS FULL TABLE TM.MG_COLL_SCP_DTL Object Instance: 3  
					Cost: 1,477  Bytes: 14,554,044  Cardinality: 404,279  	   
			13 FILTER  	   
				12 TABLE ACCESS BY INDEX ROWID TABLE TM.MG_COLL_SCP_DTL Object Instance: 3  
				Cost: 3  Bytes: 36  Cardinality: 1  	   
					11 NESTED LOOPS  
					Cost: 21  Bytes: 67  Cardinality: 1  	   
						9 HASH JOIN  
						Cost: 9  Bytes: 124  Cardinality: 4  	   
							7 INDEX RANGE SCAN INDEX TM.CHDR_MTM 
							Cost: 4  Bytes: 80  Cardinality: 4  	   
							8 TABLE ACCESS FULL TABLE TM.MS_TM_HCUT_LINK Object Instance: 2  
							Cost: 4  Bytes: 10,406  Cardinality: 946  	   
						10 INDEX RANGE SCAN INDEX (UNIQUE) TM.CSDT_PK 
						Cost: 2  Cardinality: 1  	 



After i see result , no 4 in explain plan result gives TABLE ACCESS FULL . i want to be indexing on that how to do this..

This table MG_COLL_SCP_DTL have index like this

CREATE UNIQUE INDEX CSDT_PK ON MG_COLL_SCP_DTL
(CSDT_CHDR_TRANS_NUM, CSDT_PROD_TYP, CSDT_TRAN_SR_NO, CSDT_CHDR_CDTL_COLL_TYP, CSDT_CHDR_CDTL_COLL_TYP_CD,
CSDT_STSC_CD, CSDT_CHDR_CLNT_CD, CSDT_CHDR_CLNT_TM_CD)


CREATE INDEX INDX_CSDT_VW ON MG_COLL_SCP_DTL
(CSDT_CHDR_TRANS_NUM, CSDT_STATUS, CSDT_PROD_TYP, CSDT_STSC_CD)


CREATE INDEX INDX_TRANS_NUM ON MG_COLL_SCP_DTL
(CSDT_CHDR_TRANS_NUM)


CREATE INDEX INDX_VIEW_MTM ON MG_COLL_SCP_DTL
(CSDT_CHDR_TRANS_NUM, CSDT_PROD_TYP, CSDT_CHDR_CDTL_COLL_TYP, CSDT_CHDR_CDTL_COLL_TYP_CD, CSDT_STSC_CD,
CSDT_STATUS)


CREATE INDEX MG_COLL_SCP_DTL_I_CSDT_STSC_CD ON MG_COLL_SCP_DTL
(CSDT_STSC_CD)

How to Reduce cost ???
Re: How to reduce cost of query ...Give Indexing on FT Scan [message #580191 is a reply to message #580169] Thu, 21 March 2013 03:46 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use the following method to generate explain plans, the output is easier to read:
[code]
SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> 


If you remove all the functions from the select clause, how long does it take to run?
Re: How to reduce cost of query ...Give Indexing on FT Scan [message #580248 is a reply to message #580191] Thu, 21 March 2013 23:22 Go to previous messageGo to next message
swapnil_naik
Messages: 268
Registered: December 2009
Location: Mumbai
Senior Member

Thanks for Reply.


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

----------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |     1 |    67 |  1669 |
|   1 |  HASH GROUP BY                 |                 |     1 |    67 |       |
|   2 |   CONCATENATION                |                 |       |       |       |
|   3 |    FILTER                      |                 |       |       |       |
|   4 |     HASH JOIN                  |                 |    17 |  1139 |  1647 |
|   5 |      HASH JOIN                 |                 |  2840 | 88040 |   167 |
|   6 |       TABLE ACCESS FULL        | MS_TM_HCUT_LINK |   946 | 10406 |     4 |
|   7 |       INDEX FAST FULL SCAN     | CHDR_MTM        |  2831 | 56620 |   163 |
|   8 |      TABLE ACCESS FULL         | MG_COLL_SCP_DTL |   404K|    13M|  1477 |
|   9 |    FILTER                      |                 |       |       |       |
|  10 |     TABLE ACCESS BY INDEX ROWID| MG_COLL_SCP_DTL |     1 |    36 |     3 |
|  11 |      NESTED LOOPS              |                 |     1 |    67 |    21 |
|  12 |       HASH JOIN                |                 |     4 |   124 |     9 |
|  13 |        INDEX RANGE SCAN        | CHDR_MTM        |     4 |    80 |     4 |
|  14 |        TABLE ACCESS FULL       | MS_TM_HCUT_LINK |   946 | 10406 |     4 |
|  15 |       INDEX RANGE SCAN         | CSDT_PK         |     1 |       |     2 |
----------------------------------------------------------------------------------
Re: How to reduce cost of query ...Give Indexing on FT Scan [message #580279 is a reply to message #580248] Fri, 22 March 2013 03:42 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
You didn't answer my question.
Re: How to reduce cost of query ...Give Indexing on FT Scan [message #580332 is a reply to message #580169] Fri, 22 March 2013 16:10 Go to previous message
michael_bialik
Messages: 602
Registered: July 2006
Senior Member
1. As cookiemonster already asked - What happens when you remove call to Cal_Scheme_Rate function from select?

2. The query contains:

 ...AND a.CHDR_CLNT_CD=DECODE(:P_TM_CD,'ALL', A.CHDR_CLNT_CD, :P_TM_CD)
    AND a.CHDR_EXCH_CD=decode(:P_EXCH,'ALL',a.CHDR_EXCH_CD,:P_exch)


as the same column(s) appear on both sides of = operator(a.CHDR_CLNT_CD,a.CHDR_EXCH_CD ) - optimizer has difficulties using index, so
it may use full table scan (even if you are apssing values for both parameters).

Try using dynamic SQL or a number of different SQL statements.

HTH
Previous Topic: Full table scan
Next Topic: Why would you want to avoid and how do you limit oracle redo?
Goto Forum:
  


Current Time: Sun Sep 21 22:07:43 CDT 2014

Total time taken to generate the page: 0.07393 seconds