Home » RDBMS Server » Performance Tuning » views in 92
views in 92 [message #228679] Tue, 03 April 2007 12:51 Go to next message
moody1
Messages: 15
Registered: March 2007
Junior Member
We have some views that worked fine in 8i but after converting to
92 the sql using these views are dog slow. Any one have any ideas?
Re: views in 92 [message #228682 is a reply to message #228679] Tue, 03 April 2007 13:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Nope.
Unless you provide more useful information, we are the same position as you are Smile
Post the concerned sql.
Post the details on base tables (data/statistics/distribution/whatever).
Someone might throw some light!
Re: views in 92 [message #228686 is a reply to message #228679] Tue, 03 April 2007 13:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Have you collected current statistics since being on V9?
If not, why not?
Re: views in 92 [message #228689 is a reply to message #228682] Tue, 03 April 2007 13:57 Go to previous message
moody1
Messages: 15
Registered: March 2007
Junior Member
Statistics are gathered weekly.
This is the view:
SELECT 
  /*+ index (pd pk_promo_dates) index(p pk_promotions) index(u 1x_upc_id_store_promo) */ p.promo_id, 
  u.upc_id, 
  u.store, 
  pd.beg_date, 
  pd.end_date, 
  p.AD_TYPE, 
  p.AD_QTY, 
  p.AD_AMT, 
  p.AD_MIX_MATCH, 
  p.LQD_MAX_QTY, 
  u.tag_auth_override, 
  p.PURCHASE_REQ, 
  p.PURCHASE_TYPE, 
  u.AD_VEHICLE, 
  u.AD_START, 
  u.AD_END, 
  p.AD_NOTES, 
  p.SURVEY_EXCLUDE_FLAG, 
  u.IMMED_FLAG, 
  u.IMMED_DATE, 
  u.mini_special_buy, 
  p.CONTRACT_ID, 
  p.CONTRACT_NOTES, 
  u.UPDATE_ID, 
  u.UPDATE_DATE 
FROM 
  promo_dates pd, 
  upc_id_store_promo u, 
  store s, 
  promotions p 
WHERE 
  u.PROMO_ID = pd.PROMO_ID and 
  u.promo_id = p.promo_id and 
  u.store = s.store and 
  s.day_code = pd.day_code



This is the procedure being ran against the view:
familyvar VARCHAR2(50);
weekendvar DATE;
datevar DATE;
cntr NUMBER := 0;

--CURSOR c_buyer IS
--SELECT DISTINCT buyer_id FROM BUYER_MASTER;

CURSOR c_upc IS
SELECT *
  FROM improd.upc_master
 WHERE upc=primary_upc
   AND upc_status='A';

CURSOR c_ad_long (upcidparam IN NUMBER) IS
SELECT u.upc_id, u.store,
  	   pd.beg_date, pd.end_date,
  	   p.AD_TYPE, p.AD_QTY, p.AD_AMT,
  	   p.AD_MIX_MATCH, p.LQD_MAX_QTY,
  	   u.tag_auth_override,
  	   p.PURCHASE_REQ, p.PURCHASE_TYPE,
  	   u.AD_VEHICLE, u.AD_START, u.AD_END,
  	   p.AD_NOTES, p.SURVEY_EXCLUDE_FLAG,
  	   u.IMMED_FLAG, u.IMMED_DATE,
  	   u.mini_special_buy,
  	   p.CONTRACT_ID, p.CONTRACT_NOTES,
  	   u.UPDATE_ID, u.UPDATE_DATE,
	   workspace.buyer_id_function(improd.vendor_buyer_function(u.upc_id,u.store)) buyer_id,
	   improd.vendor_pack_function(u.upc_id,u.store,trunc(sysdate)) pack
  FROM improd.promo_dates pd,
  	   improd.upc_id_store_promo u,
  	   improd.store s,
  	   improd.promotions p
 WHERE u.PROMO_ID = pd.PROMO_ID
   AND u.promo_id = p.promo_id
   AND u.store = s.store
   AND s.day_code = pd.day_code
   AND u.upc_id = upcidparam
   AND pd.end_date >= trunc(sysdate)
   AND u.store not in (164,798)
 ORDER BY u.store;

--and end_date-beg_date > 7

CURSOR c_pend_date_long (upcidparam IN NUMBER) IS
SELECT upc_id, week_end_date
  FROM (
    SELECT u.upc_id,
  		   next_day(trunc(pd.beg_date), 'saturday') week_end_date
      FROM improd.promo_dates pd,
  	   	   improd.upc_id_store_promo u,
  	   	   improd.store s,
  	   	   improd.promotions p
     WHERE u.PROMO_ID = pd.PROMO_ID
       AND u.promo_id = p.promo_id
   	   AND u.store = s.store
   	   AND s.day_code = pd.day_code
       AND u.upc_id=upcidparam
       AND pd.end_date-pd.beg_date > 7
	   AND pd.beg_date > trunc(sysdate)
	   AND u.store = (SELECT min(u2.store)
  	   	                FROM improd.promo_dates pd2,
  	   					 	 improd.upc_id_store_promo u2,
  	   					 	 improd.store s2,
  	   					 	 improd.promotions p2
                       WHERE u2.PROMO_ID = pd2.PROMO_ID
   				         AND u2.promo_id = p2.promo_id
   				     	 AND u2.store 	 = s2.store
   				     	 AND s2.day_code = pd2.day_code
                     	 AND u2.upc_id	 = upcidparam
   				     	 AND u2.store not in (164,798)
   					 	 AND pd2.end_date - pd2.beg_date > 7
   					 	 AND pd2.beg_date > trunc(sysdate)))
 group by upc_id, week_end_date;

CURSOR c_pend_long (upcidparam IN NUMBER,dateparam IN DATE) IS
SELECT u.upc_id, u.store,
  	   pd.beg_date, pd.end_date,
  	   p.AD_TYPE, p.AD_QTY, p.AD_AMT,
  	   p.AD_MIX_MATCH, p.LQD_MAX_QTY,
  	   u.tag_auth_override,
  	   p.PURCHASE_REQ, p.PURCHASE_TYPE,
  	   u.AD_VEHICLE, u.AD_START, u.AD_END,
  	   p.AD_NOTES, p.SURVEY_EXCLUDE_FLAG,
  	   u.IMMED_FLAG, u.IMMED_DATE,
  	   u.mini_special_buy,
  	   p.CONTRACT_ID, p.CONTRACT_NOTES,
  	   u.UPDATE_ID, u.UPDATE_DATE,
	   workspace.buyer_id_function(improd.vendor_buyer_function(u.upc_id,u.store)) buyer_id,
	   improd.vendor_pack_function(u.upc_id,u.store,trunc(sysdate)) pack
  FROM improd.promo_dates pd,
  	   improd.upc_id_store_promo u,
  	   improd.store s,
  	   improd.promotions p
 WHERE u.PROMO_ID = pd.PROMO_ID
   AND u.promo_id = p.promo_id
   AND u.store = s.store
   AND s.day_code = pd.day_code
   AND u.upc_id = upcidparam
   AND dateparam BETWEEN pd.beg_date AND pd.end_date
   AND pd.end_date - pd.beg_date > 7
   AND pd.beg_date > trunc(sysdate)
   AND u.store not in (164,798)
 ORDER BY u.store;

CURSOR c_pend_date (upcidparam IN NUMBER) IS
SELECT upc_id, week_end_date
  FROM (
       SELECT u.upc_id,
     		  next_day(trunc(pd.beg_date), 'saturday') week_end_date
         FROM improd.promo_dates pd,
     	   	  improd.upc_id_store_promo u,
     	   	  improd.store s,
     	   	  improd.promotions p
        WHERE u.PROMO_ID = pd.PROMO_ID
          AND u.promo_id = p.promo_id
      	  AND u.store = s.store
      	  AND s.day_code = pd.day_code
	      AND u.upc_id= upcidparam
		  AND pd.beg_date > trunc(sysdate)
		  AND not exists (
		  	  SELECT '1'
                FROM improd.promo_dates pd2,
            	   	 improd.upc_id_store_promo u2,
            	   	 improd.store s2,
            	   	 improd.promotions p2
               WHERE u2.PROMO_ID = pd2.PROMO_ID
                 AND u2.promo_id = p2.promo_id
             	 AND u2.store 	 = s2.store
             	 AND s2.day_code = pd2.day_code
			     AND u2.upc_id 	 = u.upc_id
			     AND u2.store 	 = u.store
				 AND u2.upc_id 	 = upcidparam
				 AND pd2.end_date - pd2.beg_date > 7
				 AND u2.store = (SELECT min(u3.store)
                                   FROM improd.promo_dates pd3,
                               	   	 	improd.upc_id_store_promo u3,
                               	   	 	improd.store s3,
                               	   	 	improd.promotions p3
                                  WHERE u3.PROMO_ID = pd3.PROMO_ID
                                    AND u3.promo_id = p3.promo_id
                                	AND u3.store 	= s3.store
                                	AND s3.day_code = pd3.day_code
								    AND u3.upc_id = upcidparam
								    AND u3.store not in (164,798)
								    AND pd3.end_date - pd3.beg_date > 7
								    AND pd3.beg_date > trunc(sysdate)  )))
 GROUP BY upc_id,week_end_date;



CURSOR c_pend (upcidparam IN NUMBER,dateparam IN DATE) IS
SELECT p.promo_id, u.upc_id, u.store,
  	   pd.beg_date, pd.end_date,
  	   p.AD_TYPE, p.AD_QTY, p.AD_AMT,
  	   p.AD_MIX_MATCH, p.LQD_MAX_QTY,
  	   u.tag_auth_override,
  	   p.PURCHASE_REQ, p.PURCHASE_TYPE,
  	   u.AD_VEHICLE, u.AD_START, u.AD_END,
  	   p.AD_NOTES, p.SURVEY_EXCLUDE_FLAG,
  	   u.IMMED_FLAG, u.IMMED_DATE,
  	   u.mini_special_buy,
  	   p.CONTRACT_ID, p.CONTRACT_NOTES,
  	   u.UPDATE_ID, u.UPDATE_DATE,
	   workspace.buyer_id_function(improd.vendor_buyer_function(u.upc_id,u.store)) buyer_id,
	   improd.vendor_pack_function(u.upc_id,u.store,trunc(sysdate)) pack
  FROM improd.promo_dates pd,
  	   improd.upc_id_store_promo u,
  	   improd.store s,
  	   improd.promotions p
 WHERE u.PROMO_ID = pd.PROMO_ID
   AND u.promo_id = p.promo_id
   AND u.store 	  = s.store
   AND s.day_code = pd.day_code
   AND u.upc_id	  = upcidparam
   AND dateparam BETWEEN pd.beg_date AND pd.end_date
   AND pd.beg_DATE > trunc(sysdate)
   AND u.store not in (164,798)
   AND not exists (
   	   SELECT '1'
         FROM improd.promo_dates pd2,
     	   	  improd.upc_id_store_promo u2,
     	   	  improd.store s2,
     	   	  improd.promotions p2
        WHERE u2.PROMO_ID = pd2.PROMO_ID
          AND u2.promo_id = p2.promo_id
      	  AND u2.store 	  = s2.store
      	  AND s2.day_code = pd2.day_code
		  AND u2.upc_id   = u.upc_id
		  AND u2.store    = u.store
		  AND u2.upc_id  = upcidparam
		  AND dateparam BETWEEN pd2.beg_date AND pd2.end_date
		  AND pd2.beg_DATE > trunc(sysdate)
		  AND pd2.end_date - pd2.beg_date > 7
		  AND pd2.beg_Date = pd.beg_Date
		  AND pd2.end_date = pd.end_date)
 ORDER BY STORE;

BEGIN
dbms_output.enable(1000000);
--FOR v_buyer IN c_buyer LOOP

  FOR v_upc IN c_upc  LOOP

   BEGIN
	    SELECT family_group_name
		  INTO familyvar
		  FROM improd.FAMILY_GROUP
		 WHERE family_group_nbr=v_upc.retail_family_group_nbr;
	  EXCEPTION
	    WHEN NO_DATA_FOUND THEN
		 familyvar := NULL;
	END;
	cntr := 0;
   FOR v_ad_long IN c_ad_long(v_upc.upc_id) LOOP

  IF cntr < 1 THEN
	weekendvar := v_ad_long.beg_Date;
	datevar := v_ad_long.beg_Date;
	cntr := 0;

	WHILE   datevar < v_ad_long.end_date LOOP

	SELECT NEXT_DAY(TRUNC(datevar),'saturday')
	  INTO weekendvar
	  FROM DUAL;

	BEGIN
	  INSERT INTO SURVEY_WORK_TABLE  VALUES (
	  v_ad_long.buyer_id,
	  v_upc.upc,
	  v_upc.upc_id,
	  weekendvar,
	  v_upc.item_div,
	  v_upc.item_nbr,
	  v_upc.product_description,
	  v_ad_long.pack,
	  v_upc.display_size,
	  v_upc.whse_nbr,
	  familyvar,
	  v_ad_long.ad_type,
	  v_ad_long.beg_date,
	  v_ad_long.end_date,
	  v_ad_long.ad_qty,
	  v_ad_long.ad_amt,
	  v_ad_long.ad_vehicle,
	  v_ad_long.survey_exclude_flag,
	  trunc(sysdate),v_upc.department);
	  COMMIT;
	 EXCEPTION
	  WHEN DUP_VAL_ON_INDEX THEN
	    dbms_output.put_line(v_ad_long.buyer_id||','||v_upc.upc||','||
		v_upc.upc_id||','||weekendvar);
	 END;
	 datevar := datevar + 7;
	END LOOP;
	cntr := cntr + 1;
  ELSE
   EXIT ;
  END IF;
  END LOOP;

  FOR v_pend_date_long IN c_pend_date_long (v_upc.upc_id) LOOP
  cntr := 0;
   FOR v_pend_long IN c_pend_long(v_upc.upc_id,v_pend_date_long.week_end_date) LOOP

   IF cntr < 1 THEN
   	  /*
      BEGIN
	    SELECT family_grp_name INTO familyvar FROM FAMILY_GROUP WHERE
		family_grp_nbr=v_upc.family_grp_nbr;
	  EXCEPTION
	    WHEN NO_DATA_FOUND THEN
		 familyvar := NULL;
	  END;
	  */
	weekendvar := v_pend_long.beg_Date;
	datevar := v_pend_long.beg_Date;
	cntr := 0;

	WHILE   datevar < v_pend_long.end_date LOOP

	SELECT NEXT_DAY(TRUNC(datevar),'saturday') INTO weekendvar
	FROM DUAL;

     BEGIN
	  INSERT INTO SURVEY_WORK_TABLE (
	  buyer_id,
	  upc,
	  upc_id,
	  week_ending_date,
	  item_div,
	  item_nbr,
	  description,
	  pack,
	  display_size,
	  whse_nbr,
	  family_group_name,
	  ad_type,
	  ad_beg,
	  ad_end,
	  ad_qty,
	  ad_amt,
	  ad_vehicle,
	  survey_exclude_flag,
	  update_date,department )
	  VALUES (
	  v_pend_long.buyer_id,
	  v_upc.upc,
	  v_upc.upc_id,
	  weekendvar,
	  v_upc.item_div,
	  v_upc.item_nbr,
	  v_upc.product_description,
	  v_pend_long.pack,
	  v_upc.display_size,
	  v_upc.whse_nbr,
	  familyvar,
	  v_pend_long.ad_type,
	  v_pend_long.beg_date,
	  v_pend_long.end_date,
	  v_pend_long.ad_qty,
	  v_pend_long.ad_amt,
	  v_pend_long.ad_vehicle,
	  v_pend_long.survey_exclude_flag,
	  trunc(sysdate),
	  v_upc.department);
	  COMMIT;
	 EXCEPTION
	  WHEN DUP_VAL_ON_INDEX THEN
	    UPDATE SURVEY_WORK_TABLE
		   SET ad_type=v_pend_long.ad_type,
		   	   ad_beg=v_pend_long.beg_date,
			   ad_end=v_pend_long.end_date,
			   ad_qty=v_pend_long.ad_qty,
			   ad_amt=v_pend_long.ad_amt,
			   ad_vehicle=v_pend_long.ad_vehicle,
			   survey_exclude_flag=v_pend_long.survey_exclude_flag
		 WHERE buyer_id=v_pend_long.buyer_id
		   AND upc=v_upc.upc
		   AND upc_id=v_upc.upc_id
		   AND week_ending_date=weekendvar;
		COMMIT;
	  END;

	 datevar := datevar + 7;
	END LOOP;
	cntr := cntr + 1;
  ELSE
   EXIT ;
  END IF;
  END LOOP;
 END LOOP;


 FOR v_pend_date IN c_pend_date (v_upc.upc_id) LOOP
cntr := 0;
   FOR v_pend IN c_pend(v_upc.upc_id,v_pend_date.week_end_date) LOOP

  IF cntr < 1 THEN
   	  /*
      BEGIN
	    SELECT family_grp_name INTO familyvar FROM FAMILY_GROUP WHERE
		family_grp_nbr=v_upc.family_grp_nbr;
	  EXCEPTION
	    WHEN NO_DATA_FOUND THEN
		 familyvar := NULL;
	  END;
	  */
	weekendvar := v_pend.beg_Date;
	datevar := v_pend.beg_Date;
	cntr := 0;

	WHILE   datevar < v_pend.end_date LOOP

	SELECT NEXT_DAY(TRUNC(datevar),'saturday') INTO weekendvar
	FROM DUAL;

     BEGIN
	  INSERT INTO SURVEY_WORK_TABLE (
	  buyer_id,
	  upc,
	  upc_id,
	  week_ending_date,
	  item_div,
	  item_nbr,
	  description,
	  pack,
	  display_size,
	  whse_nbr,
	  family_group_name,
	  ad_type,
	  ad_beg,
	  ad_end,
	  ad_qty,
	  ad_amt,
	  ad_vehicle,
	  survey_exclude_flag,
	  update_date,department )
	  VALUES (
	  v_pend.buyer_id,
	  v_upc.upc,
	  v_upc.upc_id,
	  weekendvar,
	  v_upc.item_div,
	  v_upc.item_nbr,
	  v_upc.product_description,
	  v_pend.pack,
	  v_upc.display_size,
	  v_upc.whse_nbr,
	  familyvar,
	  v_pend.ad_type,
	  v_pend.beg_date,
	  v_pend.end_date,
	  v_pend.ad_qty,
	  v_pend.ad_amt,
	  v_pend.ad_vehicle,
	  v_pend.survey_exclude_flag,trunc(sysdate),
	  v_upc.department);
	  COMMIT;
	 EXCEPTION
	  WHEN DUP_VAL_ON_INDEX THEN
	    UPDATE SURVEY_WORK_TABLE
		  SET ad_type=v_pend.ad_type,
		  	  ad_beg=v_pend.beg_date,
			  ad_end=v_pend.end_date,
			  ad_qty=v_pend.ad_qty,
			  ad_amt=v_pend.ad_amt,
			  ad_vehicle=v_pend.ad_vehicle,
			  survey_exclude_flag=v_pend.survey_exclude_flag
		 WHERE buyer_id=v_pend.buyer_id
		   AND upc=v_upc.upc
		   AND upc_id=v_upc.upc_id
		   AND week_ending_date=weekendvar;
		COMMIT;
	  END;

	 datevar := datevar + 7;
	END LOOP;
	cntr := cntr + 1;
  ELSE
   EXIT ;
  END IF;
  END LOOP;
 END LOOP;

 END LOOP;

--END LOOP;

END;




[EDIT] I've added [CODE] tags for you; next time, please, do it yourself to improve readability.

[Updated on: Tue, 03 April 2007 15:28] by Moderator

Report message to a moderator

Previous Topic: Tuning
Next Topic: Database Performance Issue VS Network Performance Issue
Goto Forum:
  


Current Time: Sat Dec 10 12:53:30 CST 2016

Total time taken to generate the page: 0.08426 seconds