5 Replies Latest reply on Jan 14, 2020 7:05 PM by Christian Berg

    very slow report

    John305

      Hello , i am using OBIEE 12

       

      In my fact table i have millions of rows ( almost 1 million row per year  )

       

       

      For this graph , i calculate number of total licences , total licences for a specific bank ( from the filter ) and the average of licence for each bank.

       

      i dont know why this graph take almost 30s  to be generated , and how can i make it faster ? ( My fact table is already partitionned by year )

       

      Thank you

        • 1. Re: very slow report
          Christian Berg

          What does the query log say?

          • 2. Re: very slow report
            John305

            Do you want me to send the log ? its a big query

            • 3. Re: very slow report
              Gianni Ceresa

              John305 wrote:

               

              i dont know why this graph take almost 30s to be generated , and how can i make it faster ? ( My fact table is already partitionned by year )

               

              So you have to find out where time is spent....

               

              https://www.rittmanmead.com/blog/2013/03/performance-and-obiee-summary-and-faq/

              OBIEE Performance Analytics: Analysing the Impact of Suboptimal Report Design

              1 person found this helpful
              • 4. Re: very slow report
                John305

                My query is loosing 19s in this part reporting to the query log

                 

                [2020-01-14T11:07:28.701+00:00] [OBIS] [TRACE:3] [] [] [ecid: 0e2c7a86-183b-4684-b3db-6dfd67a39db7-0002afaf,0:1:104:5] [sik: ssi] [tid: 41] [messageid: USER-18] [requestid: 2e7900ad] [sessionid: 2e790000] [username: weblogic] -------------------- Sending query to database named PORTNET (id: <<2083076>>), connection pool named BITEST, logical request hash fe68ea41, physical request hash ca7a27b2: [[

                WITH

                SAWITH0 AS (select count(distinct T36172.INTEGRATION_ID) as c1,

                  T30871.NOM_DEVISE as c2,

                  T30871.INTEGRATION_ID as c3

                from

                  W_DEVISE_D T30871 /* Dim_DEVISE_D */ ,

                  W_TI_MREGLE_F T36172 /* Fact_TI_MREGLE_F */

                where ( T30871.ROW_WID = T36172.DEVISE_WID )

                group by T30871.INTEGRATION_ID, T30871.NOM_DEVISE),

                SAWITH1 AS (select count(distinct T30985.INTEGRATION_ID) as c1,

                  sum(distinct T30985.MONTANT_D_MAD) as c2,

                  sum(T30985.MONTANT_DOMICILIE) as c3,

                  T30871.NOM_DEVISE as c4,

                  T30871.INTEGRATION_ID as c5

                from

                  W_BANQUE_DOM_D T59238 /* Dim_W_BANQUE_Domiciliation2_D */ ,

                  W_DEVISE_D T30871 /* Dim_DEVISE_D */ ,

                  W_TI_MDOMICILIE_F T30985 /* Fact_TI_MDOMICILIE_F */

                where ( T30871.ROW_WID = T30985.DEVISE_WID and T30985.BANQUE_DELAI_WID = T59238.ID_BANQUE and T59238.BANQUE_REGIONAL = 'CDM' )

                group by T30871.INTEGRATION_ID, T30871.NOM_DEVISE),

                SAWITH2 AS (select coalesce( D1.c2, D2.c4) as c2,

                  D2.c3 as c3,

                  D2.c2 as c4,

                  D2.c1 as c5,

                  D1.c1 as c6,

                  coalesce( D1.c3, D2.c5) as c8,

                  D1.c2 as c10,

                  D2.c4 as c11,

                  D1.c3 as c12,

                  D2.c5 as c13,

                  ROW_NUMBER() OVER (PARTITION BY case when D1.c2 is not null then D1.c2 when D2.c4 is not null then D2.c4 end , case when D1.c3 is not null then D1.c3 when D2.c5 is not null then D2.c5 end ORDER BY case when D1.c2 is not null then D1.c2 when D2.c4 is not null then D2.c4 end DESC, case when D1.c3 is not null then D1.c3 when D2.c5 is not null then D2.c5 end DESC) as c14

                from

                  SAWITH0 D1 full outer join SAWITH1 D2 On SYS_OP_MAP_NONNULL(D1.c2) = SYS_OP_MAP_NONNULL(D2.c4) and SYS_OP_MAP_NONNULL(D1.c3) = SYS_OP_MAP_NONNULL(D2.c5) ),

                SAWITH3 AS (select D1.c2 as c2,

                  D1.c3 as c3,

                  D1.c4 as c4,

                  D1.c5 as c5,

                  D1.c6 as c6,

                  D1.c8 as c8,

                  Case when case D1.c14 when 1 then D1.c5 else NULL end is not null then Rank() OVER ( ORDER BY case D1.c14 when 1 then D1.c5 else NULL end DESC NULLS LAST ) end as c9,

                  D1.c10 as c10,

                  D1.c11 as c11,

                  D1.c12 as c12,

                  D1.c13 as c13

                from

                  SAWITH2 D1),

                SAWITH4 AS (select D1.c2 as c2,

                  D1.c3 as c3,

                  D1.c4 as c4,

                  D1.c5 as c5,

                  D1.c6 as c6,

                  D1.c8 as c8,

                  min(D1.c9) over (partition by case when D1.c10 is not null then D1.c10 when D1.c11 is not null then D1.c11 end , case when D1.c12 is not null then D1.c12 when D1.c13 is not null then D1.c13 end ) as c9

                from

                  SAWITH3 D1

                order by c2, c8)

                select 0 as c1,

                  D1.c2 as c2,

                  D1.c3 as c3,

                  D1.c4 as c4,

                  D1.c5 as c5,

                  D1.c6 as c6,

                  D1.c8 as c8,

                  D1.c9 as c9

                from

                  SAWITH4 D1

                order by c2, c8

                • 5. Re: very slow report
                  Christian Berg

                  Ok and what does the DB say? OBI is only as good as the DB underneath. How does the explain plan look?