0 Replies Latest reply on Jun 4, 2015 11:49 AM by User538247

    cache not hitting

    User538247

      Hi,

       

      I m seeding all prompts values through another report but dashboard prompt is not hitting cache instead it is sending query to database for fetching the rows.

       

      in session log of dashboard prompt error shown is "node cache seed removed due to multiple plan not cacheable" but i have already marked all the tables as cachable in rpd.

       

      session log is as follows,

       

      [2015-06-04T17:10:21.000+05:30] [OracleBIServerComponent] [TRACE:5] [USER-0] [] [ecid: 11d1def534ea1be0:-fe40e80:14d9b12d987:-8000-00000000000d7d2f,0:1:9:3] [tid: dcd3700] [requestid: b1ee000d] [sessionid: b1ee0000] [username: weblogic] ############################################## [[

      -------------------- SQL Request, logical request hash:

      fabafa39

      SET VARIABLE QUERY_SRC_CD='ValuePrompt';SELECT "Attributes"."State Office Name"||' ('||cast("Attributes"."State Office Code" as varchar(20))||')' saw_0 FROM "Subsidy Not Maintained" ORDER BY saw_0

      FETCH FIRST 65001 ROWS ONLY

       

       

       

       

      ]]

      [2015-06-04T17:10:21.000+05:30] [OracleBIServerComponent] [TRACE:5] [USER-23] [] [ecid: 11d1def534ea1be0:-fe40e80:14d9b12d987:-8000-00000000000d7d2f,0:1:9:3] [tid: dcd3700] [requestid: b1ee000d] [sessionid: b1ee0000] [username: weblogic] -------------------- General Query Info: [[

      Repository: Star, Subject Area: DBTL, Presentation: Subsidy Not Maintained

       

       

      ]]

      [2015-06-04T17:10:21.000+05:30] [OracleBIServerComponent] [TRACE:5] [USER-2] [] [ecid: 11d1def534ea1be0:-fe40e80:14d9b12d987:-8000-00000000000d7d2f,0:1:9:3] [tid: dcd3700] [requestid: b1ee000d] [sessionid: b1ee0000] [username: weblogic] -------------------- Logical Request (before navigation): [[

       

       

      RqList  distinct

          Dim- Ref All Distributors.Ref State Office So Name || ' (' || cast(Dim- Ref All Distributors.State Office Code as  VARCHAR ( 20 ))  || ')' as c1 GB

      OrderBy: c1 asc

       

       

      ]]

      [2015-06-04T17:10:21.000+05:30] [OracleBIServerComponent] [TRACE:5] [USER-51] [] [ecid: 11d1def534ea1be0:-fe40e80:14d9b12d987:-8000-00000000000d7d2f,0:1:9:5] [tid: dcd3700] [requestid: b1ee000d] [sessionid: b1ee0000] [username: weblogic] -------------------- The logical plan contains non-cacheable node, plan  [[

      Child Nodes (RqCache): 1433418021:82:40276557:0x7fe50dcce570

      RqList <<10175267>> distinct  /* FETCH FIRST 65001 ROWS ONLY */

          Dim_REF_STATE_OFFICE.SO_NAME || ' (' || cast(Dim_REF_ALL_DISTRIBUTORS.STATE_OFFICE_CODE as  VARCHAR ( 20 ))  || ')' as c1 GB

      Child Nodes (RqJoinSpec): <<10175278>>

          RqJoinNode <<10175279>> [(InNode:<<10175279>>) (OutNode:<<10175288>>) , (InNode:<<10175279>>) (OutNode:<<10175282>>) , (InNode:<<10175279>>) (OutNode:<<10175291>>) , (InNode:<<10175279>>) (OutNode:<<10175294>>) , (InNode:<<10175279>>) (OutNode:<<10175297>>) ]

              INDSOFTX1PROD.MST_DISTRIBUTOR AS Dim_MST_DISTRIBUTOR

          RqJoinNode <<10175282>> [(InNode:<<10175279>>) (OutNode:<<10175282>>) , (InNode:<<10175291>>) (OutNode:<<10175282>>) ]

              INDSOFTX1PROD.MST_CONSUMER AS Dim_MST_CONSUMER

          RqJoinNode <<10175285>> [(InNode:<<10175285>>) (OutNode:<<10175288>>) , (InNode:<<10175291>>) (OutNode:<<10175285>>) ]

              INDSOFTX1PROD.REF_DISTRICT AS Dim_REF_DISTRICT

          RqJoinNode <<10175288>> [(InNode:<<10175279>>) (OutNode:<<10175288>>) , (InNode:<<10175285>>) (OutNode:<<10175288>>) , (InNode:<<10175291>>) (OutNode:<<10175288>>) ]

              INDSOFTX1PROD.REF_STATE AS Dim_REF_STATE

          RqJoinNode <<10175291>> [(InNode:<<10175279>>) (OutNode:<<10175291>>) , (InNode:<<10175291>>) (OutNode:<<10175282>>) , (InNode:<<10175291>>) (OutNode:<<10175285>>) , (InNode:<<10175291>>) (OutNode:<<10175288>>) , (InNode:<<10175291>>) (OutNode:<<10175294>>) , (InNode:<<10175291>>) (OutNode:<<10175297>>) ]

              INDSOFTX1PROD.REF_ALL_DISTRIBUTORS AS Dim_REF_ALL_DISTRIBUTORS

          RqJoinNode <<10175294>> [(InNode:<<10175279>>) (OutNode:<<10175294>>) , (InNode:<<10175291>>) (OutNode:<<10175294>>) ]

              INDSOFTX1PROD.REF_AREA_OFFICE AS Dim_REF_AREA_OFFICE

          RqJoinNode <<10175297>> [(InNode:<<10175279>>) (OutNode:<<10175297>>) , (InNode:<<10175291>>) (OutNode:<<10175297>>) ]

              INDSOFTX1PROD.REF_STATE_OFFICE AS Dim_REF_STATE_OFFICE

      DetailFilter: Dim_MST_DISTRIBUTOR.DIST_CODE = Dim_REF_ALL_DISTRIBUTORS.DIST_CODE and Dim_MST_DISTRIBUTOR.AREA_OFFICE_CODE = Dim_REF_AREA_OFFICE.AO_CODE and Dim_MST_DISTRIBUTOR.DIST_CODE = Dim_MST_CONSUMER.DIST_CODE and Dim_MST_DISTRIBUTOR.STATE_CODE = Dim_REF_STATE.STATE_CODE and Dim_MST_DISTRIBUTOR.STATE_OFFICE_CODE = Dim_REF_STATE_OFFICE.SO_CODE and Dim_REF_ALL_DISTRIBUTORS.DISTRICT_CODE = Dim_REF_DISTRICT.DISTRICT_CODE and Dim_REF_ALL_DISTRIBUTORS.DIST_CODE = Dim_MST_CONSUMER.DIST_CODE and Dim_REF_ALL_DISTRIBUTORS.STATE = Dim_REF_STATE.STATE_CODE and Dim_REF_ALL_DISTRIBUTORS.AREA_OFFICE_CODE = Dim_REF_AREA_OFFICE.AO_CODE and Dim_REF_ALL_DISTRIBUTORS.STATE_OFFICE_CODE = Dim_REF_STATE_OFFICE.SO_CODE and Dim_REF_DISTRICT.STATE_CODE = Dim_REF_STATE.STATE_CODE

      OrderBy: c1 asc

      node cache seed removed due to multiple plan not cacheable

       

       

      ]]

      [2015-06-04T17:10:21.000+05:30] [OracleBIServerComponent] [TRACE:5] [USER-50] [] [ecid: 11d1def534ea1be0:-fe40e80:14d9b12d987:-8000-00000000000d7d2f,0:1:9:5] [tid: dcd3700] [requestid: b1ee000d] [sessionid: b1ee0000] [username: weblogic] -------------------- The logical query hits the plan cache [[

      plan

      RqList <<10175267>> [for database 3023:67960:Oracle INDSOFTX1DR,57] distinct  /* FETCH FIRST 65001 ROWS ONLY */

          Dim_REF_STATE_OFFICE.SO_NAME || ' (' || cast(Dim_REF_ALL_DISTRIBUTORS.STATE_OFFICE_CODE as  VARCHAR ( 20 ))  || ')' as c1 GB [for database 3023:67960,57]

      Child Nodes (RqJoinSpec): <<10175278>> [for database 3023:67960:Oracle INDSOFTX1DR,57]

          RqJoinNode <<10175279>> [(InNode:<<10175279>>) (OutNode:<<10175288>>) , (InNode:<<10175279>>) (OutNode:<<10175282>>) , (InNode:<<10175279>>) (OutNode:<<10175291>>) , (InNode:<<10175279>>) (OutNode:<<10175294>>) , (InNode:<<10175279>>) (OutNode:<<10175297>>) ]

              INDSOFTX1PROD.MST_DISTRIBUTOR AS Dim_MST_DISTRIBUTOR

          RqJoinNode <<10175282>> [(InNode:<<10175279>>) (OutNode:<<10175282>>) , (InNode:<<10175291>>) (OutNode:<<10175282>>) ]

              INDSOFTX1PROD.MST_CONSUMER AS Dim_MST_CONSUMER

          RqJoinNode <<10175285>> [(InNode:<<10175285>>) (OutNode:<<10175288>>) , (InNode:<<10175291>>) (OutNode:<<10175285>>) ]

              INDSOFTX1PROD.REF_DISTRICT AS Dim_REF_DISTRICT

          RqJoinNode <<10175288>> [(InNode:<<10175279>>) (OutNode:<<10175288>>) , (InNode:<<10175285>>) (OutNode:<<10175288>>) , (InNode:<<10175291>>) (OutNode:<<10175288>>) ]

              INDSOFTX1PROD.REF_STATE AS Dim_REF_STATE

          RqJoinNode <<10175291>> [(InNode:<<10175279>>) (OutNode:<<10175291>>) , (InNode:<<10175291>>) (OutNode:<<10175282>>) , (InNode:<<10175291>>) (OutNode:<<10175285>>) , (InNode:<<10175291>>) (OutNode:<<10175288>>) , (InNode:<<10175291>>) (OutNode:<<10175294>>) , (InNode:<<10175291>>) (OutNode:<<10175297>>) ]

              INDSOFTX1PROD.REF_ALL_DISTRIBUTORS AS Dim_REF_ALL_DISTRIBUTORS

          RqJoinNode <<10175294>> [(InNode:<<10175279>>) (OutNode:<<10175294>>) , (InNode:<<10175291>>) (OutNode:<<10175294>>) ]

              INDSOFTX1PROD.REF_AREA_OFFICE AS Dim_REF_AREA_OFFICE

          RqJoinNode <<10175297>> [(InNode:<<10175279>>) (OutNode:<<10175297>>) , (InNode:<<10175291>>) (OutNode:<<10175297>>) ]

              INDSOFTX1PROD.REF_STATE_OFFICE AS Dim_REF_STATE_OFFICE

      DetailFilter: Dim_MST_DISTRIBUTOR.DIST_CODE = Dim_REF_ALL_DISTRIBUTORS.DIST_CODE and Dim_MST_DISTRIBUTOR.AREA_OFFICE_CODE = Dim_REF_AREA_OFFICE.AO_CODE and Dim_MST_DISTRIBUTOR.DIST_CODE = Dim_MST_CONSUMER.DIST_CODE and Dim_MST_DISTRIBUTOR.STATE_CODE = Dim_REF_STATE.STATE_CODE and Dim_MST_DISTRIBUTOR.STATE_OFFICE_CODE = Dim_REF_STATE_OFFICE.SO_CODE and Dim_REF_ALL_DISTRIBUTORS.DISTRICT_CODE = Dim_REF_DISTRICT.DISTRICT_CODE and Dim_REF_ALL_DISTRIBUTORS.DIST_CODE = Dim_MST_CONSUMER.DIST_CODE and Dim_REF_ALL_DISTRIBUTORS.STATE = Dim_REF_STATE.STATE_CODE and Dim_REF_ALL_DISTRIBUTORS.AREA_OFFICE_CODE = Dim_REF_AREA_OFFICE.AO_CODE and Dim_REF_ALL_DISTRIBUTORS.STATE_OFFICE_CODE = Dim_REF_STATE_OFFICE.SO_CODE and Dim_REF_DISTRICT.STATE_CODE = Dim_REF_STATE.STATE_CODE [for database 3023:67960]

      OrderBy: c1 asc [for database 3023:67960,57]

       

       

      ]]

      [2015-06-04T17:10:21.000+05:30] [OracleBIServerComponent] [TRACE:5] [USER-16] [] [ecid: 11d1def534ea1be0:-fe40e80:14d9b12d987:-8000-00000000000d7d2f,0:1:9:5] [tid: dcd3700] [requestid: b1ee000d] [sessionid: b1ee0000] [username: weblogic] -------------------- Execution plan: [[

       

       

      RqList <<10175267>> [for database 3023:67960:Oracle INDSOFTX1DR,57] distinct  /* FETCH FIRST 65001 ROWS ONLY */

          Dim_REF_STATE_OFFICE.SO_NAME || ' (' || cast(Dim_REF_ALL_DISTRIBUTORS.STATE_OFFICE_CODE as  VARCHAR ( 20 ))  || ')' as c1 GB [for database 3023:67960,57]

      Child Nodes (RqJoinSpec): <<10175278>> [for database 3023:67960:Oracle INDSOFTX1DR,57]

          RqJoinNode <<10175279>> [(InNode:<<10175279>>) (OutNode:<<10175288>>) , (InNode:<<10175279>>) (OutNode:<<10175282>>) , (InNode:<<10175279>>) (OutNode:<<10175291>>) , (InNode:<<10175279>>) (OutNode:<<10175294>>) , (InNode:<<10175279>>) (OutNode:<<10175297>>) ]

              INDSOFTX1PROD.MST_DISTRIBUTOR AS Dim_MST_DISTRIBUTOR

          RqJoinNode <<10175282>> [(InNode:<<10175279>>) (OutNode:<<10175282>>) , (InNode:<<10175291>>) (OutNode:<<10175282>>) ]

              INDSOFTX1PROD.MST_CONSUMER AS Dim_MST_CONSUMER

          RqJoinNode <<10175285>> [(InNode:<<10175285>>) (OutNode:<<10175288>>) , (InNode:<<10175291>>) (OutNode:<<10175285>>) ]

              INDSOFTX1PROD.REF_DISTRICT AS Dim_REF_DISTRICT

          RqJoinNode <<10175288>> [(InNode:<<10175279>>) (OutNode:<<10175288>>) , (InNode:<<10175285>>) (OutNode:<<10175288>>) , (InNode:<<10175291>>) (OutNode:<<10175288>>) ]

              INDSOFTX1PROD.REF_STATE AS Dim_REF_STATE

          RqJoinNode <<10175291>> [(InNode:<<10175279>>) (OutNode:<<10175291>>) , (InNode:<<10175291>>) (OutNode:<<10175282>>) , (InNode:<<10175291>>) (OutNode:<<10175285>>) , (InNode:<<10175291>>) (OutNode:<<10175288>>) , (InNode:<<10175291>>) (OutNode:<<10175294>>) , (InNode:<<10175291>>) (OutNode:<<10175297>>) ]

              INDSOFTX1PROD.REF_ALL_DISTRIBUTORS AS Dim_REF_ALL_DISTRIBUTORS

          RqJoinNode <<10175294>> [(InNode:<<10175279>>) (OutNode:<<10175294>>) , (InNode:<<10175291>>) (OutNode:<<10175294>>) ]

              INDSOFTX1PROD.REF_AREA_OFFICE AS Dim_REF_AREA_OFFICE

          RqJoinNode <<10175297>> [(InNode:<<10175279>>) (OutNode:<<10175297>>) , (InNode:<<10175291>>) (OutNode:<<10175297>>) ]

              INDSOFTX1PROD.REF_STATE_OFFICE AS Dim_REF_STATE_OFFICE

      DetailFilter: Dim_MST_DISTRIBUTOR.DIST_CODE = Dim_REF_ALL_DISTRIBUTORS.DIST_CODE and Dim_MST_DISTRIBUTOR.AREA_OFFICE_CODE = Dim_REF_AREA_OFFICE.AO_CODE and Dim_MST_DISTRIBUTOR.DIST_CODE = Dim_MST_CONSUMER.DIST_CODE and Dim_MST_DISTRIBUTOR.STATE_CODE = Dim_REF_STATE.STATE_CODE and Dim_MST_DISTRIBUTOR.STATE_OFFICE_CODE = Dim_REF_STATE_OFFICE.SO_CODE and Dim_REF_ALL_DISTRIBUTORS.DISTRICT_CODE = Dim_REF_DISTRICT.DISTRICT_CODE and Dim_REF_ALL_DISTRIBUTORS.DIST_CODE = Dim_MST_CONSUMER.DIST_CODE and Dim_REF_ALL_DISTRIBUTORS.STATE = Dim_REF_STATE.STATE_CODE and Dim_REF_ALL_DISTRIBUTORS.AREA_OFFICE_CODE = Dim_REF_AREA_OFFICE.AO_CODE and Dim_REF_ALL_DISTRIBUTORS.STATE_OFFICE_CODE = Dim_REF_STATE_OFFICE.SO_CODE and Dim_REF_DISTRICT.STATE_CODE = Dim_REF_STATE.STATE_CODE [for database 3023:67960]

      OrderBy: c1 asc [for database 3023:67960,57]

       

       

      ]]

      [2015-06-04T17:10:21.000+05:30] [OracleBIServerComponent] [TRACE:5] [USER-18] [] [ecid: 11d1def534ea1be0:-fe40e80:14d9b12d987:-8000-00000000000d7d2f,0:1:9:5] [tid: dcd3700] [requestid: b1ee000d] [sessionid: b1ee0000] [username: weblogic] -------------------- Sending query to database named Oracle INDSOFTX1DR (id: <<10175267>>), connection pool named INDSOFTX1_DR, logical request hash fabafa39, physical request hash 32b1e5a9: [[

       

       

      select D1.c1 as c1 from ( select distinct concat(concat(concat(T77608.SO_NAME, ' ('), cast(T67616.STATE_OFFICE_CODE as  VARCHAR ( 20 ) )), ')') as c1

      from

           INDSOFTX1PROD.MST_DISTRIBUTOR T67294 /* Dim_MST_DISTRIBUTOR */ ,

           INDSOFTX1PROD.MST_CONSUMER T69097 /* Dim_MST_CONSUMER */ ,

           INDSOFTX1PROD.REF_DISTRICT T69160 /* Dim_REF_DISTRICT */ ,

           INDSOFTX1PROD.REF_STATE T69179 /* Dim_REF_STATE */ ,

           INDSOFTX1PROD.REF_ALL_DISTRIBUTORS T67616 /* Dim_REF_ALL_DISTRIBUTORS */ ,

           INDSOFTX1PROD.REF_AREA_OFFICE T74492 /* Dim_REF_AREA_OFFICE */ ,

           INDSOFTX1PROD.REF_STATE_OFFICE T77608 /* Dim_REF_STATE_OFFICE */

      where  ( T67294.DIST_CODE = T67616.DIST_CODE and T67294.AREA_OFFICE_CODE = T74492.AO_CODE and T67294.DIST_CODE = T69097.DIST_CODE and T67294.STATE_CODE = T69179.STATE_CODE and T67294.STATE_OFFICE_CODE = T77608.SO_CODE and T67616.DISTRICT_CODE = T69160.DISTRICT_CODE and T67616.DIST_CODE = T69097.DIST_CODE and T67616.STATE = T69179.STATE_CODE and T67616.AREA_OFFICE_CODE = T74492.AO_CODE and T67616.STATE_OFFICE_CODE = T77608.SO_CODE and T69160.STATE_CODE = T69179.STATE_CODE )

      order by c1 ) D1 where rownum <= 65001

       

       

      ]]