title: Query taking more time when executed in OBIEE Weblogic than on database
body: <p>I have just started working on Oracle BI, I have a scenario where a dashboard when is loaded takes quite a while. At first I thought that the culprit behind such slow performance was database queries. But when the logs were viewed, queries which took hardly a second when executed on database (MDS) are showing 6 seconds as their execution time in Weblogics. </p>
  
  <p>Below is the log of a simple query which takes almost 6s to execute on Weblogics and takes 897 milliseconds when executed on database.</p>
  
  <pre><code>[2016-04-04T05:11:58.000-07:00] [OracleBIServerComponent] [TRACE:4] [USER-0] [] [ecid: 77bb740f357d872f:52e5f112:1522a219085:-8000-00000000004961ca,0:1:9:3] [tid: dbf7c700] [requestid: 157d0024] [sessionid: 157d0000] [username: weblogic] ############################################## [[
  -------------------- SQL Request, logical request hash:
  44507272
  SET VARIABLE QUERY_SRC_CD='ValuePrompt';SELECT "Project"."Project Type#1" saw_0 FROM "PLM PPM - Project Summary" ORDER BY saw_0
  FETCH FIRST 65001 ROWS ONLY
  
  
  ]]
  [2016-04-04T05:11:58.000-07:00] [OracleBIServerComponent] [TRACE:4] [USER-23] [] [ecid: 77bb740f357d872f:52e5f112:1522a219085:-8000-00000000004961ca,0:1:9:3] [tid: dbf7c700] [requestid: 157d0024] [sessionid: 157d0000] [username: weblogic] -------------------- General Query Info: [[
  Repository: Star, Subject Area: PLMA, Presentation: PLM PPM - Project Summary
  
  ]]
  [2016-04-04T05:11:58.000-07:00] [OracleBIServerComponent] [TRACE:4] [USER-2] [] [ecid: 77bb740f357d872f:52e5f112:1522a219085:-8000-00000000004961ca,0:1:9:3] [tid: dbf7c700] [requestid: 157d0024] [sessionid: 157d0000] [username: weblogic] -------------------- Logical Request (before navigation): [[
  
  RqList  distinct 
      Dim - Project.Project Type as c1 GB
  OrderBy: c1 asc
  
  ]]
  [2016-04-04T05:11:58.000-07:00] [OracleBIServerComponent] [TRACE:4] [USER-48] [] [ecid: 77bb740f357d872f:52e5f112:1522a219085:-8000-00000000004961ca,0:1:9:5] [tid: dbf7c700] [requestid: 157d0024] [sessionid: 157d0000] [username: weblogic] -------------------- The logical query block fail to hits or seed the cache in subrequest level due to   [[
   only one subrequest 
  
  
  ]]
  [2016-04-04T05:11:58.000-07:00] [OracleBIServerComponent] [TRACE:4] [USER-51] [] [ecid: 77bb740f357d872f:52e5f112:1522a219085:-8000-00000000004961ca,0:1:9:5] [tid: dbf7c700] [requestid: 157d0024] [sessionid: 157d0000] [username: weblogic] -------------------- The logical plan contains non-cacheable node, plan  [[
  RqList &lt;&lt;82402988&gt;&gt; distinct 
      D1.c1 as c1 GB
  Child Nodes (RqJoinSpec): &lt;&lt;82403017&gt;&gt;
      RqJoinNode &lt;&lt;82403016&gt;&gt; []
          (
              RqList &lt;&lt;82402995&gt;&gt;
                  Dim_PPM_PROJECT_D.PRJ_USRDEF_LIST0 as c1 GB
              Child Nodes (RqJoinSpec): &lt;&lt;82403009&gt;&gt;
                  RqJoinNode &lt;&lt;82403008&gt;&gt; []
                      PPM_PROJECT_D AS Dim_PPM_PROJECT_D
              DetailFilter: Dim_PPM_PROJECT_D.DELETE_FLG = 0
          ) as D1
  OrderBy: c1 asc
  node cache seed not inserted  due to multiple plan not cacheable
  
  ]]
  [2016-04-04T05:11:58.000-07:00] [OracleBIServerComponent] [TRACE:4] [USER-50] [] [ecid: 77bb740f357d872f:52e5f112:1522a219085:-8000-00000000004961ca,0:1:9:5] [tid: dbf7c700] [requestid: 157d0024] [sessionid: 157d0000] [username: weblogic] -------------------- The logical query seed the plan cache [[
  plan 
  RqList &lt;&lt;82403049&gt;&gt; [for database 3023:57:PLMA,57] distinct  /* FETCH FIRST 65001 ROWS ONLY */ 
      Dim_PPM_PROJECT_D.PRJ_USRDEF_LIST0 as c1 GB [for database 3023:57,57]
  Child Nodes (RqJoinSpec): &lt;&lt;82403053&gt;&gt; [for database 3023:57:PLMA,57]
      RqJoinNode &lt;&lt;82403054&gt;&gt; []
          PPM_PROJECT_D AS Dim_PPM_PROJECT_D
  DetailFilter: Dim_PPM_PROJECT_D.DELETE_FLG = 0 [for database 3023:57]
  OrderBy: c1 asc [for database 3023:57,57]
  
  ]]
  [2016-04-04T05:11:58.000-07:00] [OracleBIServerComponent] [TRACE:4] [USER-16] [] [ecid: 77bb740f357d872f:52e5f112:1522a219085:-8000-00000000004961ca,0:1:9:5] [tid: dbf7c700] [requestid: 157d0024] [sessionid: 157d0000] [username: weblogic] -------------------- Execution plan: [[
  
  RqList &lt;&lt;82402995&gt;&gt; [for database 3023:57:PLMA,57] distinct  /* FETCH FIRST 65001 ROWS ONLY */ 
      Dim_PPM_PROJECT_D.PRJ_USRDEF_LIST0 as c1 GB [for database 3023:57,57]
  Child Nodes (RqJoinSpec): &lt;&lt;82403009&gt;&gt; [for database 3023:57:PLMA,57]
      RqJoinNode &lt;&lt;82403008&gt;&gt; []
          PPM_PROJECT_D AS Dim_PPM_PROJECT_D
  DetailFilter: Dim_PPM_PROJECT_D.DELETE_FLG = 0 [for database 3023:57]
  OrderBy: c1 asc [for database 3023:57,57]
  
  ]]
  [2016-04-04T05:11:58.000-07:00] [OracleBIServerComponent] [TRACE:4] [USER-18] [] [ecid: 77bb740f357d872f:52e5f112:1522a219085:-8000-00000000004961ca,0:1:9:5] [tid: dbf7c700] [requestid: 157d0024] [sessionid: 157d0000] [username: weblogic] -------------------- Sending query to database named PLMA (id: &lt;&lt;82402995&gt;&gt;), connection pool named Connection Pool, logical request hash 44507272, physical request hash f82d8b03: [[
  
  select D1.c1 as c1 from ( select distinct T286689.PRJ_USRDEF_LIST0 as c1
  from 
       PPM_PROJECT_D T286689 /* Dim_PPM_PROJECT_D */ 
  where  ( T286689.DELETE_FLG = 0 ) 
  order by c1 ) D1 where rownum &lt;= 65001
  
  ]]
  [2016-04-04T05:12:04.000-07:00] [OracleBIServerComponent] [TRACE:4] [USER-34] [] [ecid: 77bb740f357d872f:52e5f112:1522a219085:-8000-00000000004961ca,0:1:11] [tid: dbf7c700] [requestid: 157d0024] [sessionid: 157d0000] [username: weblogic] -------------------- Query Status: Successful Completion [[
  
  ]]
  [2016-04-04T05:12:04.000-07:00] [OracleBIServerComponent] [TRACE:4] [USER-26] [] [ecid: 77bb740f357d872f:52e5f112:1522a219085:-8000-00000000004961ca,0:1:11] [tid: dbf7c700] [requestid: 157d0024] [sessionid: 157d0000] [username: weblogic] -------------------- Rows 5, bytes 20060 retrieved from database query id: &lt;&lt;82402995&gt;&gt; [[
  
  ]]
  [2016-04-04T05:12:04.000-07:00] [OracleBIServerComponent] [TRACE:4] [USER-28] [] [ecid: 77bb740f357d872f:52e5f112:1522a219085:-8000-00000000004961ca,0:1:11] [tid: dbf7c700] [requestid: 157d0024] [sessionid: 157d0000] [username: weblogic] -------------------- Physical query response time 5 (seconds), id &lt;&lt;82402995&gt;&gt; [[
  
  ]]
  [2016-04-04T05:12:04.000-07:00] [OracleBIServerComponent] [TRACE:4] [USER-29] [] [ecid: 77bb740f357d872f:52e5f112:1522a219085:-8000-00000000004961ca,0:1:11] [tid: dbf7c700] [requestid: 157d0024] [sessionid: 157d0000] [username: weblogic] -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 5, DB-connect time 0 (seconds) [[
  
  ]]
  [2016-04-04T05:12:04.000-07:00] [OracleBIServerComponent] [TRACE:4] [USER-24] [] [ecid: 77bb740f357d872f:52e5f112:1522a219085:-8000-00000000004961ca,0:1:11] [tid: dbf7c700] [requestid: 157d0024] [sessionid: 157d0000] [username: weblogic] -------------------- Rows returned to Client 5 [[
  
  ]]
  [2016-04-04T05:12:04.000-07:00] [OracleBIServerComponent] [TRACE:4] [USER-33] [] [ecid: 77bb740f357d872f:52e5f112:1522a219085:-8000-00000000004961ca,0:1:11] [tid: dbf7c700] [requestid: 157d0024] [sessionid: 157d0000] [username: weblogic] -------------------- Logical Query Summary Stats: Elapsed time 6, Response time 5, Compilation time 0 (seconds) [[
  
  ]] 
  </code></pre>
  
  <p>Can anyone tell me how can I find the root cause of this issue. </p>