Need tips to fine tune quey which is running for long

Database

Need tips to fine tune quey which is running for long

SELECT

 ACCT_I

,EVNT_I

,MSUR_NODE_C

,PERD_TYPE_C

,PERD_D

,ACCT_LINK_D

,MSUR_A

FROM

tablename

The above select query is running for more than 40 minutes.

Can we have anyway to bring this time down.

Your responses are welcome !!!

5 REPLIES
Supporter

Re: Need tips to fine tune quey which is running for long

You didn't poste any valuable info to support you here. So it ends up in guessing...

Is the table really a table? Or is it a view?

Run a show select * from tablename; 

Share the ddl(s)

How many rows does the table contain?

Is your query blocked? 

Try a lock row for access in front of your query

Is your query delayed - check in viewpoint, ask you DBAs...

Re: Need tips to fine tune quey which is running for long

Hi,

The query which is trying to retrive is a view.

It needs 4 TB of spool space to retreive the rows.

So i cant able to give the no of rows presect currently.

Supporter

Re: Need tips to fine tune quey which is running for long

But without sharing DDLs of the view, explain and stats info etc. nobody will be able to help you...

Re: Need tips to fine tune quey which is running for long

DDL of the view

 Request Text

 REPLACE VIEW PVMSUR.GRD_INT_PAID_BMASAP

     (

       ACCT_I

      ,EVNT_I

      ,MSUR_NODE_C

      ,PERD_TYPE_C

      ,PERD_D

      ,ACCT_LINK_D

      ,MSUR_A

     )

     AS

     SELECT

       ACCT_I

      ,EVNT_I

      ,MSUR_NODE_C

      ,'MNTH'      AS PERD_TYPE_C

      ,MSUR_PERD_D AS PERD_D

      ,ACCT_LINK_D

      ,MSUR_A

     FROM (

       SELECT

         FINEVNT1.ACCT_I                                        AS ACCT_I

        ,FINEVNT1.EVNT_I                                        AS EVNT_I

        ,EVNT1.EVNT_ACTV_TYPE_C

        ,CASE

           WHEN ANCS_GL_ACCT_NODE_C = 'NETINTONASSETS_08'

             THEN 'INT_INC_PAID'

           ELSE 'INT_EXP_PAID'

         END                                                    AS MSUR_NODE_C

      ------------------------------------------------

      --  THE FOLLOWING COLUMN INCLUDES TRANSACTION DATES ALONG WITH BACK DATED

      --  TRANSACTIONS FOR THE GIVEN MONTH

      ------------------------------------------------

        ,CASE

     WHEN CALRPROS1.MNTHS1900 - CALREFFT1.MNTHS1900 > 1

                ------------------------------------------------

                 -- AND POST_DATE IS WITHIN 4TH WORKING DAYS OF THE NEXT MONTH

                ------------------------------------------------

                 THEN

               CASE

                 WHEN FINEVNT1.FNCL_ACCT_EVNT_PROS_D

              BETWEEN DT1.CURR_RPRT_PERD_STRT_D AND DT1.CURR_RPRT_PERD_FRTH_DAY_D

                ------------------------------------------------

                    -- SET POST_DATE AS MONTH START DATE OF

                    -- THE PREVIOUS MONTH TO THE REPORTING PERIOD

                ------------------------------------------------

                       THEN DT1.PREV_RPRT_PERD_STRT_D

                ------------------------------------------------

                        -- ELSE POST_DATE AS MONTH START DATE OF POST_DATE

                ------------------------------------------------

                   ELSE FINEVNT1.FNCL_ACCT_EVNT_PROS_D

                    - FINEVNT1.FNCL_ACCT_EVNT_PROS_D MOD 100 + 1

               END

            ------------------------------------------------

                  -- WHEN POST_DATE > VALUE_DATE BY  A MNTH

            ------------------------------------------------

           WHEN CALRPROS1.MNTHS1900 - CALREFFT1.MNTHS1900 = 1

            ------------------------------------------------

                  -- IF POST_DATE IS WITHIN THE 1ST WORKING DAY AND 3RD

                  -- WORKING DAY OF THE REPORT MNTH

            ------------------------------------------------

             THEN

               CASE

                 WHEN FINEVNT1.FNCL_ACCT_EVNT_PROS_D

              BETWEEN DT1.CURR_RPRT_PERD_STRT_D AND DT1.CURR_RPRT_PERD_FRTH_DAY_D

            ------------------------------------------------

                  -- THEN SET THE DATE AS THE MONTH START DATE OF VALUE_DATE

            ------------------------------------------------

                   THEN  FINEVNT1.FNCL_ACCT_EVNT_EFFT_D

                 ELSE  FINEVNT1.FNCL_ACCT_EVNT_PROS_D

               END

           ELSE FINEVNT1.FNCL_ACCT_EVNT_PROS_D

         END                                                    AS POST_DATE

        ,ADD_MONTHS(POST_DATE - POST_DATE MOD 100 + 1, 1)-1     AS MSUR_PERD_D

        ,FINEVNT1.FNCL_ACCT_EVNT_PROS_D AS ACCT_LINK_D

        ,GLEVNT1.PDCT_I

        ,GLEVNT1.GL_OFST_ACCT_I

        ,FINEVNT1.FNCL_ACCT_EVNT_A                              AS MSUR_A

       FROM PVTECH.FNCL_ACCT_EVNT FINEVNT1

       JOIN PVTECH.EVNT EVNT1

         ON FINEVNT1.EVNT_I = EVNT1.EVNT_I

       JOIN PVTECH.FNCL_GL_EVNT GLEVNT1

         ON FINEVNT1.EVNT_I =  GLEVNT1.EVNT_I

      JOIN PVTECH.GRD_GL_ACCT_MNGE_MEMB MEMB1

         ON GLEVNT1.GL_OFST_ACCT_I = MEMB1.GL_ACCT_I

       JOIN PVTECH.GRD_GL_ACCT_MNGE_ANCS ANCS1

         ON MEMB1.GL_ACCT_I = ANCS1.GL_ACCT_I

       JOIN ( -- START OF DT1

         SELECT

         PERD_CURR1.PREV_RPRT_PERD_STRT_D AS PREV_RPRT_PERD_STRT_D

            ,PERD_CURR1.FRTH_BUS_DAY_D        AS CURR_RPRT_PERD_FRTH_DAY_D

            ,PERD_FWD1.FWD_RPRT_PERD_END_D    AS CURR_RPRT_PERD_END_D        

            ,PERD_CURR1.CURR_RPRT_PERD_STRT_D AS CURR_RPRT_PERD_STRT_D

            ,PERD_FWD1.FWD_FRTH_BUS_DAY_D     AS FWD_RPRT_PERD_FRTH_D

     FROM (

     SELECT

            CALR_YEAR_N

           ,CALR_MNTH_N

           ,CALR_CALR_D AS FWD_FRTH_BUS_DAY_D

           ,CALR_CALR_D - EXTRACT(DAY FROM CALR_CALR_D) + 1 AS FWD_FRST_OF_MNTH_D

           ,FWD_FRST_OF_MNTH_D -1 AS FWD_RPRT_PERD_END_D

           ,ADD_MONTHS(FWD_FRST_OF_MNTH_D, -1) AS FWD_RPRT_PERD_STRT_D

     FROM PVTECH.GRD_RPRT_CALR_CLYR

     WHERE CALR_WEEK_DAY_N NOT IN (1,7)

      AND CALR_NON_WORK_DAY_F = 'N'

      AND CALR_CALR_D BETWEEN CURRENT_DATE - 1096

      AND CURRENT_DATE + 31

     QUALIFY ROW_NUMBER() OVER (PARTITION BY CALR_YEAR_N, CALR_MNTH_N

     ORDER BY CALR_CALR_D) = 4

              ) PERD_FWD1

     LEFT JOIN (

     SELECT

             CALR_YEAR_N

            ,CALR_MNTH_N

            ,CALR_CALR_D AS FRTH_BUS_DAY_D

            ,CALR_CALR_D - EXTRACT(DAY FROM CALR_CALR_D) + 1 AS FRST_OF_MNTH_D

            ,CALR_MNTH_LAST_DAY_D AS CURR_RPRT_PERD_END_D

            ,CALR_MNTH_FRST_DAY_D AS CURR_RPRT_PERD_STRT_D

            ,ADD_MONTHS (CURR_RPRT_PERD_STRT_D, -1)  AS PREV_RPRT_PERD_STRT_D

     FROM PVTECH.GRD_RPRT_CALR_CLYR

     WHERE CALR_WEEK_DAY_N NOT IN (1,7)

      AND CALR_NON_WORK_DAY_F = 'N'

      AND CALR_CALR_D BETWEEN CURRENT_DATE - 1096

      AND CURRENT_DATE + 31

     QUALIFY ROW_NUMBER() OVER (PARTITION BY CALR_YEAR_N, CALR_MNTH_N

     ORDER BY CALR_CALR_D) = 4

              ) PERD_CURR1

    ON PERD_FWD1.FWD_RPRT_PERD_STRT_D = PERD_CURR1.CURR_RPRT_PERD_STRT_D

       ) AS DT1

         ON FINEVNT1.FNCL_ACCT_EVNT_PROS_D

           BETWEEN DT1.CURR_RPRT_PERD_STRT_D AND DT1.FWD_RPRT_PERD_FRTH_D

     -- TO EXCLUDE MASTER CONTRACTS  - USE NEW VIEW THAT CONTAINS EVENTS FOR

     -- BOTH MC ACCOUNTS AND PARTICIPATING ACCOUNTS

     LEFT OUTER JOIN

     ( SELECT EVNT_I

             ,FNCL_ACCT_EVNT_PROS_D

         FROM PVMSUR.SUBQ_MSTR_CNCT_PRTP_ACCT_EVNT PRTP

         JOIN PVTECH.ACCT_BASE BASE1

           ON PRTP.ACCT_I = BASE1.ACCT_I

         JOIN PVTECH.GRD_GNRC_MAP_CURR GGM

           ON BASE1.ACCT_CLAS_TYPE_C = GGM.SRCE_CHAR_1_C

          WHERE GGM.MAP_TYPE_C = 'SAP_MC_ACCT_CLAS_TYPE_C'

      ) REL1   

           ON FINEVNT1.EVNT_I =  REL1.EVNT_I

          AND REL1.FNCL_ACCT_EVNT_PROS_D

      BETWEEN DT1.CURR_RPRT_PERD_STRT_D AND DT1.CURR_RPRT_PERD_END_D

       INNER JOIN (

       -- TO ALLOW CALCULATION OF MONTHS DIFFERENCE BETWEEN FNCL_ACCT_EVNT_PROS_D

       -- AND FNCL_ACCT_EVNT_EFFT_D

         SELECT

           CALR_CALR_D

          ,CALR_MNTH_SNCE_1900_N AS MNTHS1900

         FROM PVTECH.GRD_RPRT_CALR_CLYR

         WHERE CALR_CALR_D BETWEEN CURRENT_DATE - 1096 AND CURRENT_DATE + 31

       ) CALRPROS1

         ON FINEVNT1.FNCL_ACCT_EVNT_PROS_D = CALRPROS1.CALR_CALR_D

       INNER JOIN (

       -- TO ALLOW CALCULATION OF MONTHS DIFFERENCE BETWEEN FNCL_ACCT_EVNT_PROS_D

       -- AND FNCL_ACCT_EVNT_EFFT_D

         SELECT

           CALR_CALR_D

          ,CALR_MNTH_SNCE_1900_N AS MNTHS1900

         FROM PVTECH.GRD_RPRT_CALR_CLYR

         WHERE CALR_CALR_D BETWEEN CURRENT_DATE - 1096 AND CURRENT_DATE + 31

       ) CALREFFT1

         ON FINEVNT1.FNCL_ACCT_EVNT_EFFT_D = CALREFFT1.CALR_CALR_D

       WHERE FINEVNT1.SRCE_SYST_C = 'SAP'

       AND ANCS1.ANCS_GL_ACCT_NODE_C IN('NETINTONASSETS_08', 'NETINTONLIABS_08')

         -- TO EXCLUDE MASTER CONTRACTS AND PARTICIPATION ACCOUNTS

         AND REL1.EVNT_I IS NULL

     ) DT2

     QUALIFY ROW_NUMBER()

             OVER (PARTITION BY ACCT_I, EVNT_I ORDER BY POST_DATE ASC) = 1

 ;

Supporter

Re: Need tips to fine tune quey which is running for long

So funny - instead of a plain select x,y,z from table you have a complicated view with 231 lines of code accessing >10 tables/views using derived tables and qualify filters etc.

did you try to run 

show select * from PVMSUR.GRD_INT_PAID_BMASAP;

?

How many database objects are returned?

Beside 

1. Check the stats for all the tables involed

2. Review the join conditions in respect to the PI / PPI definitions.

not much can be said.

If this is not helping break the SQL into smaller peaces and use work tables as interim steps.

Ask and pay a consultent to review the process / design ;-).