Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> performance needed!!! init.ora param fast=true??? ;-)

performance needed!!! init.ora param fast=true??? ;-)

From: Giovanni Azua <bravegag_at_hotmail.com>
Date: Mon, 27 Jan 2003 16:15:48 +0100
Message-ID: <b13iiv$uvid7$1@ID-114658.news.dfncis.de> 





Hello all,



I created a PL/SQL Procedure for generating "2 level" time
serie aggregations from a Bank data transactions table in order to
prepare data for later DM analysis. The problem is that this procedure
takes incredibly long time and I need to improve its implementation
for getting better execution time.



There are three tables involved in the main computation:



KDPAEAGGACCK_SMON:



aggregations from transactions group by account, months.



KDPAEAGGTSACCK_SMONACCK_SDAY:



aggregations from transactions group by account, day.



KDPAEAGGTSACCK_TESTDAY:



temporary table created for keeping 31 rows days
placeholders in which each row(x)=x. (you will se later).
This table helps in enforcing an outer join in  a way that
even though day X has no transactions it will be anyway
included in the output as null.



The main purpose is to populate some month aggregation columns
taking as input the aggregations computed on the days belonging
to that month, smoothing the days (using Analytic functions and
a sliding window) in a way that I can have as result a continuous
function of the variable for all days in the month:



this is table KDPAEAGGACCK_SMON


ACCOUNT    MONTH    AVG_INCOM    AVG_INCOM_DAY1    AVG_INCOM_DAY2


1                     1                896CHF               654CHF


692CHF


... AVG_INCOM_DAY31



    764CHF


where:


AVG_INCOM_DAY1=sum(avg_incomday1, avg_incomday2)
AVG_INCOM_DAY2=sum(avg_incomday1, avg_incomday2, avg_incomday3)
AVG_INCOM_DAY3=sum(avg_incomday2, avg_incomday3, avg_incomday4)


...


AVG_INCOM_DAY31=sum(avg_incomday30, avg_incomday31)



The current settings and indexes:






ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED

/



ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS -- Cost based right?
/



CREATE INDEX DAYDATE_INDX ON



KDPAEAGGTSACCK_SMONACCK_SDAY(TO_NUMBER(TO_CHAR(DAYDATE, 'DD')))

COMPUTE STATISTICS



/



CREATE INDEX ACCID_INDX ON KDPAEAGGTSACCK_SMONACCK_SDAY(ACCOUNT_ID)

COMPUTE STATISTICS



/



CREATE INDEX DAYDATE_INDX2 ON



KDPAEAGGTSACCK_SMONACCK_SDAY(TRUNC(DAYDATE,'MM'))

COMPUTE STATISTICS



/



CREATE INDEX MONTHDATE_INDX ON KDPAEAGGACCK_SMON(MONTHDATE)

COMPUTE STATISTICS



/



ANALYZE TABLE KDPAEAGGTSACCK_SMONACCK_SDAY COMPUTE STATISTICS

FOR TABLE



FOR ALL INDEXED COLUMNS



FOR ALL INDEXES



/



Any obvious way to improve this monster SP performance?



CREATE OR REPLACE PROCEDURE KDPAE_TIMESERIETEST2

AS


  pvArray_ACCOUNT_ID INT_VARRAY;


  pvArray_MONTH DATE_VARRAY;


BEGIN


  SELECT ACCOUNT_ID, MONTHDATE



  BULK COLLECT INTO pvArray_ACCOUNT_ID, pvArray_MONTH
  FROM KDPAEAGGACCK_SMON;


  FORALL accIndx IN pvArray_ACCOUNT_ID.FIRST .. pvArray_ACCOUNT_ID.LAST
  UPDATE KDPAEAGGACCK_SMON



   SET (SUM_INCOMING_AMOUNT_DAY1, SUM_INCOMING_AMOUNT_DAY2

      , SUM_INCOMING_AMOUNT_DAY3, SUM_INCOMING_AMOUNT_DAY4



   , SUM_INCOMING_AMOUNT_DAY5, SUM_INCOMING_AMOUNT_DAY6


   , SUM_INCOMING_AMOUNT_DAY7, SUM_INCOMING_AMOUNT_DAY8


   , SUM_INCOMING_AMOUNT_DAY9, SUM_INCOMING_AMOUNT_DAY10


   , SUM_INCOMING_AMOUNT_DAY11, SUM_INCOMING_AMOUNT_DAY12


      , SUM_INCOMING_AMOUNT_DAY13, SUM_INCOMING_AMOUNT_DAY14



   , SUM_INCOMING_AMOUNT_DAY15, SUM_INCOMING_AMOUNT_DAY16


   , SUM_INCOMING_AMOUNT_DAY17, SUM_INCOMING_AMOUNT_DAY18


   , SUM_INCOMING_AMOUNT_DAY19, SUM_INCOMING_AMOUNT_DAY20


   , SUM_INCOMING_AMOUNT_DAY21, SUM_INCOMING_AMOUNT_DAY22


      , SUM_INCOMING_AMOUNT_DAY23, SUM_INCOMING_AMOUNT_DAY24



   , SUM_INCOMING_AMOUNT_DAY25, SUM_INCOMING_AMOUNT_DAY26


   , SUM_INCOMING_AMOUNT_DAY27, SUM_INCOMING_AMOUNT_DAY28


   , SUM_INCOMING_AMOUNT_DAY29, SUM_INCOMING_AMOUNT_DAY30


   , SUM_INCOMING_AMOUNT_DAY31) = (SELECT MAX(CASE WHEN DAYNUM = 1  THEN


DAYNUM END) SUM_INCOMING_AMOUNT_DAY1,


                  MAX(CASE WHEN DAYNUM = 2  THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY2,
               MAX(CASE WHEN DAYNUM = 3  THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY3,
               MAX(CASE WHEN DAYNUM = 4  THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY4,
               MAX(CASE WHEN DAYNUM = 5  THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY5,
               MAX(CASE WHEN DAYNUM = 6  THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY6,
               MAX(CASE WHEN DAYNUM = 7  THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY7,
               MAX(CASE WHEN DAYNUM = 8  THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY8,
               MAX(CASE WHEN DAYNUM = 9  THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY9,
               MAX(CASE WHEN DAYNUM = 10 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY10,
               MAX(CASE WHEN DAYNUM = 11 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY11,
               MAX(CASE WHEN DAYNUM = 12 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY12,
               MAX(CASE WHEN DAYNUM = 13 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY13,
               MAX(CASE WHEN DAYNUM = 14 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY14,
               MAX(CASE WHEN DAYNUM = 15 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY15,
               MAX(CASE WHEN DAYNUM = 16 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY16,
               MAX(CASE WHEN DAYNUM = 17 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY17,
               MAX(CASE WHEN DAYNUM = 18 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY18,
               MAX(CASE WHEN DAYNUM = 19 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY19,
               MAX(CASE WHEN DAYNUM = 20 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY20,
               MAX(CASE WHEN DAYNUM = 21 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY21,
               MAX(CASE WHEN DAYNUM = 22 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY22,
               MAX(CASE WHEN DAYNUM = 23 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY23,
               MAX(CASE WHEN DAYNUM = 24 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY24,
               MAX(CASE WHEN DAYNUM = 25 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY25,
               MAX(CASE WHEN DAYNUM = 26 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY26,
               MAX(CASE WHEN DAYNUM = 27 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY27,
               MAX(CASE WHEN DAYNUM = 28 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY28,
               MAX(CASE WHEN DAYNUM = 29 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY29,
               MAX(CASE WHEN DAYNUM = 30 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY30,
               MAX(CASE WHEN DAYNUM = 31 THEN DAYNUM END)
SUM_INCOMING_AMOUNT_DAY31
                FROM (SELECT A.DAYNUM, AVG_INCOMING_AMOUNT
             FROM KDPAEAGGTSACCK_TESTDAY A LEFT JOIN
                                     (
                 SELECT TO_NUMBER(TO_CHAR(DAYDATE, 'DD')) AS DAYNUM
                   , AVG(AVG_INCOMING_AMOUNT) OVER (order by DAYDATE RANGE


BETWEEN INTERVAL '2' DAY PRECEDING AND INTERVAL '2' DAY FOLLOWING) AS

AVG_INCOMING_AMOUNT


                                      FROM KDPAEAGGTSACCK_SMONACCK_SDAY
                 WHERE ACCOUNT_ID=pvArray_ACCOUNT_ID(accIndx) AND
                       TRUNC(DAYDATE,'MM')=pvArray_MONTH(accIndx)
                 ) B ON A.DAYNUM=B.DAYNUM
             ORDER BY A.DAYNUM)
           )
  WHERE ACCOUNT_ID=pvArray_ACCOUNT_ID(accIndx) AND
        MONTHDATE=pvArray_MONTH(accIndx);


END;



/



TIA,



Best Regards,


Giovanni
Received on Mon Jan 27 2003 - 09:15:48 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US