Home » SQL & PL/SQL » SQL & PL/SQL » Performance issue (oracle 10g,windows)
Performance issue [message #320232] Wed, 14 May 2008 06:54 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi,

In my Query with using the index on Plcy_phase table

SELECT /*+ INDEX(A)INDEX(C) INDEX(D) */
                           NVL(SUM(CASE WHEN PLCY_STATUS_CD = 'A' AND PHASE_ID = '18' THEN A.PLCY_CNT ELSE 0 END),0) AS ISSUED,
                           NVL(SUM(CASE WHEN PLCY_STATUS_CD = 'TE' AND PHASE_ID >= '56' AND PHASE_ID <= '58' THEN A.PLCY_CNT ELSE 0 END),0) AS DECLINED,
                           NVL(SUM(CASE WHEN PLCY_STATUS_CD = 'P' AND PHASE_ID = '18' THEN A.PLCY_CNT ELSE 0 END),0) AS DECISION,
                           NVL(SUM(CASE WHEN PLCY_STATUS_CD = 'P' AND (PHASE_ID = '22' OR PHASE_ID = '55') THEN A.PLCY_CNT ELSE 0 END),0) AS MAILED
                    INTO   V_RCNT_ISSUED,
                           V_RCNT_DECLINED,
                           V_RCNT_DECISION,
                           V_RCNT_MAILED
                    FROM   CLEAR_DAILY_FACT_VW A,
                           PLCY_PHASE          C,
                           AGNT_HIER_DIM       D
                    WHERE  A.PLCY_NBR = C.PLCY_NBR AND
                           C.PHASE_DT BETWEEN ACTVTY_START_DT_IN AND ACTVTY_END_DT_IN AND
                           A.AGNT_HIER_DIM_ID = D.AGNT_HIER_DIM_ID AND
                           ((D.HIER_LVL_2_AGNT_NBR IN
                           (SELECT AGNT_NBR FROM TABLE(CAST(V_AGNT_LVL_8_TAB AS DASH_BRD_PCN_NBR_OBJ_TBL_TYPE)))) OR
                           (D.HIER_LVL_1_AGNT_NBR IN
                           (SELECT AGNT_NBR FROM TABLE(CAST(V_AGNT_LVL_12_TAB AS DASH_BRD_PCN_NBR_OBJ_TBL_TYPE)))) OR
                           (A.AGNT_NBR IN
                           (SELECT AGNT_NBR FROM TABLE(CAST(V_AGNT_LVL_99_TAB AS DASH_BRD_PCN_NBR_OBJ_TBL_TYPE)))) OR
                           (A.AGNT_LVL_6_NBR IN
                           (SELECT AGNT_NBR FROM TABLE(CAST(V_AGNT_LVL_6_TAB AS DASH_BRD_PCN_NBR_OBJ_TBL_TYPE)))))


and the explain plan for the above is
SELECT STATEMENT, GOAL = ALL_ROWS			Cost=3782	Cardinality=1	Bytes=95
 SORT AGGREGATE				Cardinality=1	Bytes=95
  FILTER					
   FILTER					
    HASH JOIN			Cost=3782	Cardinality=6074	Bytes=577030
     HASH JOIN			Cost=3380	Cardinality=6074	Bytes=413032
      TABLE ACCESS BY INDEX ROWID	Object owner=CLEAR_S01	Object name=PLCY_PHASE	Cost=2995	Cardinality=2224	Bytes=48928
     INDEX FULL SCAN	Object owner=CLEAR_S01	Object name=PLCY_PHASE_XPK	Cost=1317	Cardinality=889451	
      TABLE ACCESS BY INDEX ROWID	Object owner=CLEAR_S01	Object name=CLEAR_DAILY_FACT	Cost=385	Cardinality=28590	Bytes=1315140
       INDEX FULL SCAN	Object owner=CLEAR_S01	Object name=CLEAR_DAILY_FACT_XPK	Cost=27	Cardinality=28593	
     TABLE ACCESS BY INDEX ROWID	Object owner=CLEAR_S01	Object name=AGNT_HIER_DIM	Cost=401	Cardinality=36041	Bytes=973107
      INDEX FULL SCAN	Object owner=CLEAR_S01	Object name=AGENT_HIER_DIM_XPK	Cost=41	Cardinality=36041	
   COLLECTION ITERATOR PICKLER FETCH					
   COLLECTION ITERATOR PICKLER FETCH					
   COLLECTION ITERATOR PICKLER FETCH					
   COLLECTION ITERATOR PICKLER FETCH	


Query without using the index on Plcy_phase table

SELECT /*+ INDEX(A)  INDEX(D) */
                           NVL(SUM(CASE WHEN PLCY_STATUS_CD = 'A' AND PHASE_ID = '18' THEN A.PLCY_CNT ELSE 0 END),0) AS ISSUED,
                           NVL(SUM(CASE WHEN PLCY_STATUS_CD = 'TE' AND PHASE_ID >= '56' AND PHASE_ID <= '58' THEN A.PLCY_CNT ELSE 0 END),0) AS DECLINED,
                           NVL(SUM(CASE WHEN PLCY_STATUS_CD = 'P' AND PHASE_ID = '18' THEN A.PLCY_CNT ELSE 0 END),0) AS DECISION,
                           NVL(SUM(CASE WHEN PLCY_STATUS_CD = 'P' AND (PHASE_ID = '22' OR PHASE_ID = '55') THEN A.PLCY_CNT ELSE 0 END),0) AS MAILED
                    INTO   V_RCNT_ISSUED,
                           V_RCNT_DECLINED,
                           V_RCNT_DECISION,
                           V_RCNT_MAILED
                    FROM   CLEAR_DAILY_FACT_VW A,
                           PLCY_PHASE          C,
                           AGNT_HIER_DIM       D
                    WHERE  A.PLCY_NBR = C.PLCY_NBR AND
                           C.PHASE_DT BETWEEN ACTVTY_START_DT_IN AND ACTVTY_END_DT_IN AND
                           A.AGNT_HIER_DIM_ID = D.AGNT_HIER_DIM_ID AND
                           ((D.HIER_LVL_2_AGNT_NBR IN
                           (SELECT AGNT_NBR FROM TABLE(CAST(V_AGNT_LVL_8_TAB AS DASH_BRD_PCN_NBR_OBJ_TBL_TYPE)))) OR
                           (D.HIER_LVL_1_AGNT_NBR IN
                           (SELECT AGNT_NBR FROM TABLE(CAST(V_AGNT_LVL_12_TAB AS DASH_BRD_PCN_NBR_OBJ_TBL_TYPE)))) OR
                           (A.AGNT_NBR IN
                           (SELECT AGNT_NBR FROM TABLE(CAST(V_AGNT_LVL_99_TAB AS DASH_BRD_PCN_NBR_OBJ_TBL_TYPE)))) OR
                           (A.AGNT_LVL_6_NBR IN
                           (SELECT AGNT_NBR FROM TABLE(CAST(V_AGNT_LVL_6_TAB AS DASH_BRD_PCN_NBR_OBJ_TBL_TYPE)))))


and the explain plan for the above is

SELECT STATEMENT, GOAL = ALL_ROWS			Cost=1574	Cardinality=1	Bytes=95
 SORT AGGREGATE				Cardinality=1	Bytes=95
  FILTER					
   FILTER					
    HASH JOIN			Cost=1574	Cardinality=6074	Bytes=577030
     HASH JOIN			Cost=1172	Cardinality=6074	Bytes=413032
      TABLE ACCESS FULL	Object owner=CLEAR_S01	Object name=PLCY_PHASE	Cost=786	Cardinality=2224	Bytes=48928
      TABLE ACCESS BY INDEX ROWID	Object owner=CLEAR_S01	Object name=CLEAR_DAILY_FACT	Cost=385	Cardinality=28590	Bytes=1315140
       INDEX FULL SCAN	Object owner=CLEAR_S01	Object name=CLEAR_DAILY_FACT_XPK	Cost=27	Cardinality=28593	
     TABLE ACCESS BY INDEX ROWID	Object owner=CLEAR_S01	Object name=AGNT_HIER_DIM	Cost=401	Cardinality=36041	Bytes=973107
      INDEX FULL SCAN	Object owner=CLEAR_S01	Object name=AGENT_HIER_DIM_XPK	Cost=41	Cardinality=36041	
   COLLECTION ITERATOR PICKLER FETCH					
   COLLECTION ITERATOR PICKLER FETCH					
   COLLECTION ITERATOR PICKLER FETCH					
   COLLECTION ITERATOR PICKLER FETCH				


My question is without Plcy_phase index HINT the Table access Full,cose = 786 and cardinality=2224

and with PLcy_phase Index HINT Index FULL SCAN,COST=1317 and CARDINALITY=889451.

So,should I use the index on plcy_phase or Not ?
Please advice,


[Updated on: Wed, 14 May 2008 07:18]

Report message to a moderator

Re: Performance issue [message #320259 is a reply to message #320232] Wed, 14 May 2008 07:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you think you need to "help" the optimizer?
Re: Performance issue [message #320267 is a reply to message #320232] Wed, 14 May 2008 08:35 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Because in my query i am using collection and if the collection types are used all the indexes on the respective columns are not going to use.So,we should help the optimizer by giving the hint.
Please advice should I use the Plcy_phase index or not.
Because if i use the plcy_phase index hint cost= 1317
without plcy_phase index the cost=786.

Thanks,
Re: Performance issue [message #320269 is a reply to message #320232] Wed, 14 May 2008 08:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If I were you, I'd rewrite the query like this (assuming that v_agnt_lvl_8_tab is a local variable):
SELECT NVL(SUM(CASE WHEN PLCY_STATUS_CD = 'A' AND PHASE_ID = '18' THEN A.PLCY_CNT ELSE 0 END),0) AS ISSUED,
       NVL(SUM(CASE WHEN PLCY_STATUS_CD = 'TE' AND PHASE_ID >= '56' AND PHASE_ID <= '58' THEN A.PLCY_CNT ELSE 0 END),0) AS DECLINED,
       NVL(SUM(CASE WHEN PLCY_STATUS_CD = 'P' AND PHASE_ID = '18' THEN A.PLCY_CNT ELSE 0 END),0) AS DECISION,
       NVL(SUM(CASE WHEN PLCY_STATUS_CD = 'P' AND (PHASE_ID = '22' OR PHASE_ID = '55') THEN A.PLCY_CNT ELSE 0 END),0) AS MAILED
INTO   V_RCNT_ISSUED,
       V_RCNT_DECLINED,
       V_RCNT_DECISION,
       V_RCNT_MAILED
FROM   CLEAR_DAILY_FACT_VW A,
       PLCY_PHASE          C,
       AGNT_HIER_DIM       D,
       (SELECT AGNT_NBR FROM TABLE(CAST(V_AGNT_LVL_8_TAB AS DASH_BRD_PCN_NBR_OBJ_TBL_TYPE))) E
WHERE  A.PLCY_NBR = C.PLCY_NBR 
AND    C.PHASE_DT BETWEEN ACTVTY_START_DT_IN AND ACTVTY_END_DT_IN 
AND    A.AGNT_HIER_DIM_ID = D.AGNT_HIER_DIM_ID
AND    (D.HIER_LVL_2_AGNT_NBR = E.AGNT_NBR 
     OR D.HIER_LVL_1_AGNT_NBR = E.AGNT_NBR 
     OR A.AGNT_NBR = E.AGNT_NBR
     OR A.AGNT_LVL_6_NBR = E.AGNT_NBR)


Then I'd repeat the Tom Kyte tuning mantra 'Full Table Scans are not always slower, Indexes are not always quicker' 500 times, strip all the hints out of the query, regenerate the statistics on your database and see what the plan looked like then.
Removing the additional SELECT FROM TABLE clauses won't afect the plan, but it will probably improve the performance.

[Add 3 missing = in the where clause ]

[Updated on: Wed, 14 May 2008 09:44]

Report message to a moderator

Re: Performance issue [message #320270 is a reply to message #320267] Wed, 14 May 2008 08:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What this is telling you is that not using the index is probably going to be quicker.
Re: Performance issue [message #320272 is a reply to message #320232] Wed, 14 May 2008 08:48 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
which table/view contains PLCY_STATUS_CD & PHASE_ID
Re: Performance issue [message #320285 is a reply to message #320232] Wed, 14 May 2008 09:34 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
PLCY_status_cd is clear_daily_fact_vw
phase_id is in plcy_phase table
Re: Performance issue [message #320286 is a reply to message #320232] Wed, 14 May 2008 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
AGNT_HIER_DIM D,
(SELECT AGNT_NBR FROM TABLE(CAST(V_AGNT_LVL_8_TAB AS DASH_BRD_PCN_NBR_OBJ_TBL_TYPE))) E

neither contribute any data to the SELECT clause & should be subordinated into the WHERE clause.
Re: Performance issue [message #320287 is a reply to message #320286] Wed, 14 May 2008 09:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, if you can show me a way to put that TABLE select into the WHERE clause without having it execute 4 times, then you've spotted something I didn't. I'm pretty convinced that having it in the FROM list (where it is executed once) will be quicker than having 4 copies of it executed in the WHERE clause.

Out of curiosity, why do you assume that it will always be quicker to have tables that return no rows only present as part of the WHERE clause?
Re: Performance issue [message #320288 is a reply to message #320232] Wed, 14 May 2008 09:49 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>why do you assume that it will always be quicker
I do/did not claim it will ALWAYS be quicker.
On the flip side, I have never observed where it was slower; but there always is the 1st time for everything.
It should be worth testing, at a minimum IMO.
Re: Performance issue [message #320293 is a reply to message #320232] Wed, 14 May 2008 10:13 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
though AGNT_HIER_DIM is not used in the select clause it is used to filter the databased on some input values along with the other tables.
So,I am decided not to use plcy_phase index in the HINT clause to reduce the COST.

If,any one have any suggestions welcome.

First Time [message #320294 is a reply to message #320288] Wed, 14 May 2008 10:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
create table t_par (col_1  number, col_2 number);

create table t_chil (col_1 number);

insert into t_par (col_1,col_2) (select level, mod(level,100) from dual connect by level <= 100000);

insert into t_chil (col_1) (select level from dual connect by level <= 200);

DECLARE
  v_time  pls_integer;
  v_count pls_integer;
  v_loop  pls_integer := 1000;
BEGIN
  v_time := dbms_utility.get_time;

  for i in 1..v_loop loop
    select count(*)
    into   v_count
    from (select t_par.col_1
          from   t_par, t_chil
          where  t_par.col_2 = t_chil.col_1);
  end loop;
  
  dbms_output.put_line('Version 1 '||to_char(dbms_utility.get_time - v_time));
  
  v_time := dbms_utility.get_time;
  
  for i in 1..v_loop loop    
    select count(*)
    into   v_count
    from  (select col_1
           from   t_par
           where  col_2 in (select col_1 from t_chil));
  end loop;

  dbms_output.put_line('Version 2 '||to_char(dbms_utility.get_time - v_time));
end;
/


set serveroutput on
Version 1 18829
Version 2 27819


Using EXISTS rather than IN was slower still.
Re: Performance issue [message #320295 is a reply to message #320293] Wed, 14 May 2008 10:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've got one 3 part suggestion:

1) Rewrite the Query as I suggested above.
2) Check your stats are up to date
3) remove ALL the hints
Re: Performance issue [message #320384 is a reply to message #320295] Wed, 14 May 2008 23:23 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I've got a better suggestion:
1. Follow JR's suggestion. It's good advice.

Ross Leishman
Previous Topic: SQL query question
Next Topic: Exclude column in select
Goto Forum:
  


Current Time: Sat Dec 03 11:59:31 CST 2016

Total time taken to generate the page: 0.09939 seconds