Realtime Realization From EBS Views Through BIEE RPD

Question

Hi Everyone,I meet the performance problem from obiee 11g. I was using a complex view in RPD from EBS, this is a gl account balance,about 40 thousands rows. I seperately query the view in client tool is very fast(1s),but run the report in web cost almost (47s) and I using the sql that biee generated in client is also fast.this report pass through three layer from rpd to request db.I used to try using hints to change the query optimizer execution plan but didn't work. please provide me your advise. thanks so much.I'm looking forward to your reply! is there any a good idea to realize a realtime report using biee rpd.

[Deleted User] · Answer

Ok @rmoff beat me to it this time :-)

But he's right. It's the amount of information you're pulling back (40k rows with 2GB...are you retrieving MP3s or what? ;-) ) which has to be processed by the OBI Server that slows down things.

You said you were doing transactional modeling on realtime sources. While this is totally valid and a normal use case it is also totally possible that you're modeling things which just aren't going to fly since transactional real-time sources do not support or contain things in an analytical way and hence the BI server has to do huge amounts of number crunching.

Once more: transactional modeling, yes. Will work for everything without an analytical access / performance layer? Nope.

rmoff · Answer

select T48352.ACC_CODE as c3, T48352.ACC_DESC as c4, T48352.CODE_COMBINATION_ID as c5, concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(T48352.COM_CODE, '.'), T48352.DEPT_CODE), '.'), T48352.ACC_CODE), '.'), T48352.SUBACC_CODE), '.'), T48352.PRODUCT_CODE), '.'), T48352.PROJECT_CODE), '.'), T48352.SPECIAL_CODE), '.'), T48352.INTERCOM_CODE), '.'), T48352.RES1_CODE), '.'), T48352.RES2_CODE) as c6, concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(T48352.COM_DESC, ','), T48352.DEPT_DESC), ','), T48352.ACC_DESC), ','), T48352.INTERCOM_DESC), ','), T48352.PRODUCT_DESC), ','), T48352.PROJECT_DESC), ','), T48352.RES1_DESC), ','), T48352.SPECIAL_DESC), ','), T48352.SUBACC_DESC) as c7, T48352.CURRENCY_CODE as c8, T48352.PERIOD_NAME as c9, T48352.BEGIN_BEQ_QTY as c11, T48352.BEGIN_BEQ_AMOUNT as c12from CUX_BI_GL_BALANCES_V T48352 /* AL_CUX_BI_GL_BALANCES_V */where ( T48352.PERIOD_NAME = '2016-09' and (T48352.COM_CODE in ('') or 'WEBLOGIC' in (upper('weblogic')) or 0 < instr(upper('') , 'RT_ORG_ALL')) ) So this query is what's going to your database. From the query log we can see : Rows 45440, bytes 2128409600 retrieved from database query id: <<2501833>>Physical query response time 41.719 (seconds), id <<2501833>>Physical Query Summary Stats: Number of physical queries 1, Cumulative time 41.719, DB-connect time 0.000 (seconds)Rows returned to Client 1055Logical Query Summary Stats: Elapsed time 317.982, Total time in BI Server 70.079, Response time 70.075, Compilation time 0.096 (seconds), Logical hash 6a985b29 So the SQL query that your OBIEE RPD is generating is pulling back 45,440 rows from the database, and an impressive 2GB of data. The BI Server then processes this down to just 1055 rows that go back to the client (Presentation Services, which generates the report that you see in the web browser). You can see from the timings that the BI Server takes over a minute (70 seconds) doing this. So the reason, at least on first pass, that your performance is so bad, is that OBIEE is having to do all this extra work, on top of 41 seconds spent in the database too. Others may well want to chime in here, but generally, cramming logic into views and trying to 'trick' OBIEE into rendering some report that you've built out in pure SQL alone, ends badly. Either model the physical tables in the RPD with a logical model on top correctly -- or use Direct Database Requests to just get the job done (albeit in a very unmaintainable, unscaleable, way).

Carl wan · Answer

Hi Christian the below is query log: [2016-09-22T10:54:58.338+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014bfe3,0:1:9:3] [tid: 66a03940] [messageid: USER-0] [requestid: 86780009] [sessionid: 86780000] [username: weblogic] ############################################## [[ -------------------- SQL Request, logical request hash: 6a985b29 SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/users/weblogic/PerformanceTest';SELECT 0 s_0, "MDBI Realtime"."Dim BI GL Balances V"."Acc Code" s_1, "MDBI Realtime"."Dim BI GL Balances V"."Acc Desc" s_2, "MDBI Realtime"."Dim BI GL Balances V"."Code Combination Id" s_3, "MDBI Realtime"."Dim BI GL Balances V"."Com Code"||'.'||"MDBI Realtime"."Dim BI GL Balances V"."Dept Code"||'.'||"MDBI Realtime"."Dim BI GL Balances V"."Acc Code"||'.'||"MDBI Realtime"."Dim BI GL Balances V"."Subacc Code"||'.'||"MDBI Realtime"."Dim BI GL Balances V"."Product Code"||'.'||"MDBI Realtime"."Dim BI GL Balances V"."Project Code"||'.'||"MDBI Realtime"."Dim BI GL Balances V"."Special Code"||'.'||"MDBI Realtime"."Dim BI GL Balances V"."Intercom Code"||'.'||"MDBI Realtime"."Dim BI GL Balances V"."Res1 Code"||'.'||"MDBI Realtime"."Dim BI GL Balances V"."Res2 code" s_4, "MDBI Realtime"."Dim BI GL Balances V"."Com Desc"||','||"MDBI Realtime"."Dim BI GL Balances V"."Dept Desc"||','||"MDBI Realtime"."Dim BI GL Balances V"."Acc Desc"||','||"MDBI Realtime"."Dim BI GL Balances V"."Intercom Desc"||','||"MDBI Realtime"."Dim BI GL Balances V"."Product Desc"||','||"MDBI Realtime"."Dim BI GL Balances V"."Project Desc"||','||"MDBI Realtime"."Dim BI GL Balances V"."Res1 Desc"||','||"MDBI Realtime"."Dim BI GL Balances V"."Special Desc"||','||"MDBI Realtime"."Dim BI GL Balances V"."Subacc Desc" s_5, "MDBI Realtime"."Dim BI GL Balances V"."Currency Code" s_6, "MDBI Realtime"."Dim BI GL Balances V"."Period Name" s_7, "MDBI Realtime"."Fact BI GL Balances V Test"."Begin Beq Amount" s_8, "MDBI Realtime"."Fact BI GL Balances V Test"."Begin Beq Qty" s_9 FROM "MDBI Realtime" WHERE ("Dim BI GL Balances V"."Period Name" = '2016-09') ORDER BY 1, 4 ASC NULLS LAST, 7 ASC NULLS LAST, 8 ASC NULLS LAST, 2 ASC NULLS LAST, 3 ASC NULLS LAST, 5 ASC NULLS LAST, 6 ASC NULLS LAST FETCH FIRST 65001 ROWS ONLY ]] [2016-09-22T10:54:58.339+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014bfe3,0:1:9:3] [tid: 66a03940] [messageid: USER-23] [requestid: 86780009] [sessionid: 86780000] [username: weblogic] -------------------- General Query Info: [[ Repository: Star, Subject Area: MDBI Realtime, Presentation: MDBI Realtime ]] [2016-09-22T10:54:58.349+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014bfe3,0:1:9:3] [tid: 66a03940] [messageid: USER-2] [requestid: 86780009] [sessionid: 86780000] [username: weblogic] -------------------- Logical Request (before navigation): [[ RqList 0 as c1 GB, Dim Bi Gl Balances V.Acc Code as c2 GB, Dim Bi Gl Balances V.Acc Desc as c3 GB, Dim Bi Gl Balances V.Code Combination Id as c4 GB, Dim Bi Gl Balances V.Com Code || '.' || Dim Bi Gl Balances V.Dept Code || '.' || Dim Bi Gl Balances V.Acc Code || '.' || Dim Bi Gl Balances V.Subacc Code || '.' || Dim Bi Gl Balances V.Product Code || '.' || Dim Bi Gl Balances V.Project Code || '.' || Dim Bi Gl Balances V.Special Code || '.' || Dim Bi Gl Balances V.Intercom Code || '.' || Dim Bi Gl Balances V.Res1 Code || '.' || Dim Bi Gl Balances V.Res2 code as c5 GB, Dim Bi Gl Balances V.Com Desc || ',' || Dim Bi Gl Balances V.Dept Desc || ',' || Dim Bi Gl Balances V.Acc Desc || ',' || Dim Bi Gl Balances V.Intercom Desc || ',' || Dim Bi Gl Balances V.Product Desc || ',' || Dim Bi Gl Balances V.Project Desc || ',' || Dim Bi Gl Balances V.Res1 Desc || ',' || Dim Bi Gl Balances V.Special Desc || ',' || Dim Bi Gl Balances V.Subacc Desc as c6 GB, Dim Bi Gl Balances V.Currency Code as c7 GB, Dim Bi Gl Balances V.Period Name as c8 GB, Begin Beq Amount:[DAggr(Fact BI GL Balances V Test.Begin Beq Amount by [ Dim Bi Gl Balances V.Code Combination Id, Dim Bi Gl Balances V.Currency Code, Dim Bi Gl Balances V.Period Name, Dim Bi Gl Balances V.Acc Code, Dim Bi Gl Balances V.Acc Desc, Dim Bi Gl Balances V.Com Desc || ',' || Dim Bi Gl Balances V.Dept Desc || ',' || Dim Bi Gl Balances V.Acc Desc || ',' || Dim Bi Gl Balances V.Intercom Desc || ',' || Dim Bi Gl Balances V.Product Desc || ',' || Dim Bi Gl Balances V.Project Desc || ',' || Dim Bi Gl Balances V.Res1 Desc || ',' || Dim Bi Gl Balances V.Special Desc || ',' || Dim Bi Gl Balances V.Subacc Desc, Dim Bi Gl Balances V.Com Code || '.' || Dim Bi Gl Balances V.Dept Code || '.' || Dim Bi Gl Balances V.Acc Code || '.' || Dim Bi Gl Balances V.Subacc Code || '.' || Dim Bi Gl Balances V.Product Code || '.' || Dim Bi Gl Balances V.Project Code || '.' || Dim Bi Gl Balances V.Special Code || '.' || Dim Bi Gl Balances V.Intercom Code || '.' || Dim Bi Gl Balances V.Res1 Code || '.' || Dim Bi Gl Balances V.Res2 code] )] as c9 GB, Begin Beq Qty:[DAggr(Fact BI GL Balances V Test.Begin Beq Qty by [ Dim Bi Gl Balances V.Code Combination Id, Dim Bi Gl Balances V.Currency Code, Dim Bi Gl Balances V.Period Name, Dim Bi Gl Balances V.Acc Code, Dim Bi Gl Balances V.Acc Desc, Dim Bi Gl Balances V.Com Desc || ',' || Dim Bi Gl Balances V.Dept Desc || ',' || Dim Bi Gl Balances V.Acc Desc || ',' || Dim Bi Gl Balances V.Intercom Desc || ',' || Dim Bi Gl Balances V.Product Desc || ',' || Dim Bi Gl Balances V.Project Desc || ',' || Dim Bi Gl Balances V.Res1 Desc || ',' || Dim Bi Gl Balances V.Special Desc || ',' || Dim Bi Gl Balances V.Subacc Desc, Dim Bi Gl Balances V.Com Code || '.' || Dim Bi Gl Balances V.Dept Code || '.' || Dim Bi Gl Balances V.Acc Code || '.' || Dim Bi Gl Balances V.Subacc Code || '.' || Dim Bi Gl Balances V.Product Code || '.' || Dim Bi Gl Balances V.Project Code || '.' || Dim Bi Gl Balances V.Special Code || '.' || Dim Bi Gl Balances V.Intercom Code || '.' || Dim Bi Gl Balances V.Res1 Code || '.' || Dim Bi Gl Balances V.Res2 code] )] as c10 GB DetailFilter: Dim Bi Gl Balances V.Period Name = '2016-09' OrderBy: c1 asc, c4 asc NULLS LAST, c7 asc NULLS LAST, c8 asc NULLS LAST, c2 asc NULLS LAST, c3 asc NULLS LAST, c5 asc NULLS LAST, c6 asc NULLS LAST ]] [2016-09-22T10:54:58.374+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014bfe3,0:1:9:5] [tid: 66a03940] [messageid: USER-48] [requestid: 86780009] [sessionid: 86780000] [username: weblogic] -------------------- The logical query block fail to hits or seed the cache in subrequest level due to [[ only one subrequest ]] [2016-09-22T10:54:58.417+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014bfe3,0:1:9:5] [tid: 66a03940] [messageid: USER-50] [requestid: 86780009] [sessionid: 86780000] [username: weblogic] -------------------- The logical query seed the plan cache [[ plan Child Nodes (RqCache): 1474512898:380:289575323:0x2b1466a010c0 /* FETCH FIRST 65001 ROWS ONLY */ RqList <<2502363>> [for database 0:0,0] D1.c1 as c1 [for database 0:0,0], D1.c2 as c2 [for database 3023:46526:MDBI Realtime,57], D1.c3 as c3 [for database 3023:46526:MDBI Realtime,57], D1.c4 as c4 [for database 3023:46526:MDBI Realtime,57], D1.c5 as c5 [for database 3023:46526:MDBI Realtime,57], D1.c6 as c6 [for database 3023:46526:MDBI Realtime,57], D1.c7 as c7 [for database 3023:46526:MDBI Realtime,57], D1.c8 as c8 [for database 3023:46526:MDBI Realtime,57], D1.c9 as c9 [for database 0:0,0], D1.c10 as c10 [for database 0:0,0] Child Nodes (RqJoinSpec): <<2502385>> [for database 0:0,0] RqJoinNode <<2502386>> [] ( RqBreakFilter <<2502389>>[2,3,4,7,8] [for database 0:0,0] RqList <<2502390>> [for database 0:0,0] 0 as c1 GB [for database 0:0,1], D1.c3 as c2 GB [for database 3023:46526:MDBI Realtime,57], D1.c4 as c3 GB [for database 3023:46526:MDBI Realtime,57], D1.c5 as c4 GB [for database 3023:46526:MDBI Realtime,57], D1.c6 as c5 GB [for database 3023:46526:MDBI Realtime,57], D1.c7 as c6 GB [for database 3023:46526:MDBI Realtime,57], D1.c8 as c7 GB [for database 3023:46526:MDBI Realtime,57], D1.c9 as c8 GB [for database 3023:46526:MDBI Realtime,57], D1.c2 as c9 GB [for database 0:0,0], D1.c1 as c10 GB [for database 0:0,0] Child Nodes (RqJoinSpec): <<2502412>> [for database 0:0,0] RqJoinNode <<2502413>> [] ( RqList <<2502416>> [for database 0:0,0] sum(D1.c11 by [ D1.c5, D1.c8, D1.c9, D1.c3, D1.c4, D1.c7, D1.c6] ) as c1 [for database 0:0,0], sum(D1.c12 by [ D1.c5, D1.c8, D1.c9, D1.c3, D1.c4, D1.c7, D1.c6] ) as c2 [for database 0:0,0], D1.c3 as c3 [for database 3023:46526:MDBI Realtime,57], D1.c4 as c4 [for database 3023:46526:MDBI Realtime,57], D1.c5 as c5 [for database 3023:46526:MDBI Realtime,57], D1.c6 as c6 [for database 3023:46526:MDBI Realtime,57], D1.c7 as c7 [for database 3023:46526:MDBI Realtime,57], D1.c8 as c8 [for database 3023:46526:MDBI Realtime,57], D1.c9 as c9 [for database 3023:46526:MDBI Realtime,57] Child Nodes (RqJoinSpec): <<2502454>> [for database 0:0,0] RqJoinNode <<2502455>> [] ( RqList <<2502458>> [for database 0:0,0] D1.c3 as c3 [for database 3023:46526:MDBI Realtime,57], D1.c4 as c4 [for database 3023:46526:MDBI Realtime,57], D1.c5 as c5 [for database 3023:46526:MDBI Realtime,57], D1.c6 as c6 [for database 3023:46526:MDBI Realtime,57], D1.c7 as c7 [for database 3023:46526:MDBI Realtime,57], D1.c8 as c8 [for database 3023:46526:MDBI Realtime,57], D1.c9 as c9 [for database 3023:46526:MDBI Realtime,57], D1.c11 as c11 [for database 3023:46526:MDBI Realtime,57], D1.c12 as c12 [for database 3023:46526:MDBI Realtime,57] Child Nodes (RqJoinSpec): <<2502478>> [for database 3023:46526:MDBI Realtime,57] RqJoinNode <<2502479>> [] ( RqList <<2502482>> [for database 3023:46526:MDBI Realtime,57] AL_CUX_BI_GL_BALANCES_V.ACC_CODE as c3 GB [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.ACC_DESC as c4 GB [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.CODE_COMBINATION_ID as c5 GB [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.COM_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.DEPT_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.ACC_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.SUBACC_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.PRODUCT_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.PROJECT_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.SPECIAL_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.INTERCOM_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.RES1_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.RES2_CODE as c6 GB [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.COM_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.DEPT_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.ACC_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.INTERCOM_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.PRODUCT_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.PROJECT_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.RES1_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.SPECIAL_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.SUBACC_DESC as c7 GB [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.CURRENCY_CODE as c8 GB [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.PERIOD_NAME as c9 GB [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.BEGIN_BEQ_QTY as c11 [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.BEGIN_BEQ_AMOUNT as c12 [for database 3023:46526:MDBI Realtime,57] Child Nodes (RqJoinSpec): <<2502570>> [for database 3023:46526:MDBI Realtime,57] RqJoinNode <<2502571>> [] CUX_BI_GL_BALANCES_V AS AL_CUX_BI_GL_BALANCES_V DetailFilter: AL_CUX_BI_GL_BALANCES_V.PERIOD_NAME = '2016-09' and (AL_CUX_BI_GL_BALANCES_V.COM_CODE = '' or upper('weblogic') = 'WEBLOGIC' or 0 < position('RT_ORG_ALL' in upper(''))) [for database 3023:46526:MDBI Realtime,57] ) as D1 OrderBy: c5, c8, c9, c3, c4, c7, c6 [for database 0:0,0] ) as D1 OrderBy: c3, c4, c5, c8, c9 [for database 0:0,0] ) as D1 ) as D1 OrderBy: c1 asc, c4 asc NULLS LAST, c7 asc NULLS LAST, c8 asc NULLS LAST, c2 asc NULLS LAST, c3 asc NULLS LAST, c5 asc NULLS LAST, c6 asc NULLS LAST [for database 0:0,0] ]] [2016-09-22T10:54:58.419+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014bfe3,0:1:9:5] [tid: 66a03940] [messageid: USER-16] [requestid: 86780009] [sessionid: 86780000] [username: weblogic] -------------------- Execution plan: [[ Child Nodes (RqCache): 1474512898:380:289575323:0x2b1466a010c0 /* FETCH FIRST 65001 ROWS ONLY */ RqList <<2502051>> [for database 0:0,0] D1.c1 as c1 [for database 0:0,0], D1.c2 as c2 [for database 3023:46526:MDBI Realtime,57], D1.c3 as c3 [for database 3023:46526:MDBI Realtime,57], D1.c4 as c4 [for database 3023:46526:MDBI Realtime,57], D1.c5 as c5 [for database 3023:46526:MDBI Realtime,57], D1.c6 as c6 [for database 3023:46526:MDBI Realtime,57], D1.c7 as c7 [for database 3023:46526:MDBI Realtime,57], D1.c8 as c8 [for database 3023:46526:MDBI Realtime,57], D1.c9 as c9 [for database 0:0,0], D1.c10 as c10 [for database 0:0,0] Child Nodes (RqJoinSpec): <<2502076>> [for database 0:0,0] RqJoinNode <<2502075>> [] ( RqBreakFilter <<2502050>>[2,3,4,7,8] [for database 0:0,0] RqList <<2501826>> [for database 0:0,0] 0 as c1 GB [for database 0:0,1], D1.c3 as c2 GB [for database 3023:46526:MDBI Realtime,57], D1.c4 as c3 GB [for database 3023:46526:MDBI Realtime,57], D1.c5 as c4 GB [for database 3023:46526:MDBI Realtime,57], D1.c6 as c5 GB [for database 3023:46526:MDBI Realtime,57], D1.c7 as c6 GB [for database 3023:46526:MDBI Realtime,57], D1.c8 as c7 GB [for database 3023:46526:MDBI Realtime,57], D1.c9 as c8 GB [for database 3023:46526:MDBI Realtime,57], D1.c2 as c9 GB [for database 0:0,0], D1.c1 as c10 GB [for database 0:0,0] Child Nodes (RqJoinSpec): <<2502027>> [for database 0:0,0] RqJoinNode <<2502026>> [] ( RqList <<2502162>> [for database 0:0,0] sum(D1.c11 by [ D1.c5, D1.c8, D1.c9, D1.c3, D1.c4, D1.c7, D1.c6] ) as c1 [for database 0:0,0], sum(D1.c12 by [ D1.c5, D1.c8, D1.c9, D1.c3, D1.c4, D1.c7, D1.c6] ) as c2 [for database 0:0,0], D1.c3 as c3 [for database 3023:46526:MDBI Realtime,57], D1.c4 as c4 [for database 3023:46526:MDBI Realtime,57], D1.c5 as c5 [for database 3023:46526:MDBI Realtime,57], D1.c6 as c6 [for database 3023:46526:MDBI Realtime,57], D1.c7 as c7 [for database 3023:46526:MDBI Realtime,57], D1.c8 as c8 [for database 3023:46526:MDBI Realtime,57], D1.c9 as c9 [for database 3023:46526:MDBI Realtime,57] Child Nodes (RqJoinSpec): <<2502195>> [for database 0:0,0] RqJoinNode <<2502194>> [] ( RqList <<2502252>> [for database 0:0,0] D1.c3 as c3 [for database 3023:46526:MDBI Realtime,57], D1.c4 as c4 [for database 3023:46526:MDBI Realtime,57], D1.c5 as c5 [for database 3023:46526:MDBI Realtime,57], D1.c6 as c6 [for database 3023:46526:MDBI Realtime,57], D1.c7 as c7 [for database 3023:46526:MDBI Realtime,57], D1.c8 as c8 [for database 3023:46526:MDBI Realtime,57], D1.c9 as c9 [for database 3023:46526:MDBI Realtime,57], D1.c11 as c11 [for database 3023:46526:MDBI Realtime,57], D1.c12 as c12 [for database 3023:46526:MDBI Realtime,57] Child Nodes (RqJoinSpec): <<2502285>> [for database 3023:46526:MDBI Realtime,57] RqJoinNode <<2502284>> [] ( RqList <<2501833>> [for database 3023:46526:MDBI Realtime,57] AL_CUX_BI_GL_BALANCES_V.ACC_CODE as c3 GB [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.ACC_DESC as c4 GB [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.CODE_COMBINATION_ID as c5 GB [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.COM_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.DEPT_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.ACC_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.SUBACC_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.PRODUCT_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.PROJECT_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.SPECIAL_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.INTERCOM_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.RES1_CODE || '.' || AL_CUX_BI_GL_BALANCES_V.RES2_CODE as c6 GB [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.COM_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.DEPT_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.ACC_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.INTERCOM_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.PRODUCT_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.PROJECT_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.RES1_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.SPECIAL_DESC || ',' || AL_CUX_BI_GL_BALANCES_V.SUBACC_DESC as c7 GB [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.CURRENCY_CODE as c8 GB [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.PERIOD_NAME as c9 GB [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.BEGIN_BEQ_QTY as c11 [for database 3023:46526:MDBI Realtime,57], AL_CUX_BI_GL_BALANCES_V.BEGIN_BEQ_AMOUNT as c12 [for database 3023:46526:MDBI Realtime,57] Child Nodes (RqJoinSpec): <<2501984>> [for database 3023:46526:MDBI Realtime,57] RqJoinNode <<2501983>> [] CUX_BI_GL_BALANCES_V AS AL_CUX_BI_GL_BALANCES_V DetailFilter: AL_CUX_BI_GL_BALANCES_V.PERIOD_NAME = '2016-09' and (AL_CUX_BI_GL_BALANCES_V.COM_CODE = '' or upper('weblogic') = 'WEBLOGIC' or 0 < position('RT_ORG_ALL' in upper(''))) [for database 3023:46526:MDBI Realtime,57] ) as D1 OrderBy: c5, c8, c9, c3, c4, c7, c6 [for database 0:0,0] ) as D1 OrderBy: c3, c4, c5, c8, c9 [for database 0:0,0] ) as D1 ) as D1 OrderBy: c1 asc, c4 asc NULLS LAST, c7 asc NULLS LAST, c8 asc NULLS LAST, c2 asc NULLS LAST, c3 asc NULLS LAST, c5 asc NULLS LAST, c6 asc NULLS LAST [for database 0:0,0] ]] [2016-09-22T10:54:58.424+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014bfe3,0:1:9:5] [tid: 66a03940] [messageid: USER-18] [requestid: 86780009] [sessionid: 86780000] [username: weblogic] -------------------- Sending query to database named MDBI Realtime (id: <<2501833>>), connection pool named Connection Pool APPS, logical request hash 6a985b29, physical request hash ad89e871: [[ select T48352.ACC_CODE as c3, T48352.ACC_DESC as c4, T48352.CODE_COMBINATION_ID as c5, concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(T48352.COM_CODE, '.'), T48352.DEPT_CODE), '.'), T48352.ACC_CODE), '.'), T48352.SUBACC_CODE), '.'), T48352.PRODUCT_CODE), '.'), T48352.PROJECT_CODE), '.'), T48352.SPECIAL_CODE), '.'), T48352.INTERCOM_CODE), '.'), T48352.RES1_CODE), '.'), T48352.RES2_CODE) as c6, concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(T48352.COM_DESC, ','), T48352.DEPT_DESC), ','), T48352.ACC_DESC), ','), T48352.INTERCOM_DESC), ','), T48352.PRODUCT_DESC), ','), T48352.PROJECT_DESC), ','), T48352.RES1_DESC), ','), T48352.SPECIAL_DESC), ','), T48352.SUBACC_DESC) as c7, T48352.CURRENCY_CODE as c8, T48352.PERIOD_NAME as c9, T48352.BEGIN_BEQ_QTY as c11, T48352.BEGIN_BEQ_AMOUNT as c12 from CUX_BI_GL_BALANCES_V T48352 /* AL_CUX_BI_GL_BALANCES_V */ where ( T48352.PERIOD_NAME = '2016-09' and (T48352.COM_CODE in ('') or 'WEBLOGIC' in (upper('weblogic')) or 0 < instr(upper('') , 'RT_ORG_ALL')) ) ]] [2016-09-22T10:56:11.501+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014bfe3,0:1:9:5:1] [tid: 5fa3a940] [messageid: USER-42] [requestid: 86780009] [sessionid: 86780000] [username: weblogic] -------------------- Query Result Cache: [59124] The query for user 'weblogic' was inserted into the query result cache. The filename is '/u02/biee/instances/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/cache/NQS__736231_39298_291.TBL'. [2016-09-22T10:56:11.540+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014bfe3,0:1:9:5:1] [tid: 5fa3a940] [messageid: USER-50] [requestid: 86780009] [sessionid: 86780000] [username: weblogic] -------------------- The logical query seeds the plan cache [[ plan RqList <<2372377>> [for database 0:0,9] /* FETCH FIRST 65001 ROWS ONLY */ 1 as c1 [for database 0:0,9], 2 as c2 [for database 0:0,9], 3 as c3 [for database 0:0,9], 4 as c4 [for database 0:0,9], 5 as c5 [for database 0:0,9], 6 as c6 [for database 0:0,9], 7 as c7 [for database 0:0,9], 8 as c8 [for database 0:0,9], 9 as c9 [for database 0:0,9], 10 as c10 [for database 0:0,9] Child Nodes (RqJoinSpec): <<2372402>> [for database 0:0,1] RqJoinNode <<2372401>> [] CacheTable T1 ]] [2016-09-22T11:00:16.310+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014c275,0:1:1] [tid: 67627940] [messageid: USER-34] [requestid: 86780009] [sessionid: 86780000] [username: weblogic] -------------------- Query Status: Successful Completion [2016-09-22T11:00:16.310+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014c275,0:1:1] [tid: 67627940] [messageid: USER-26] [requestid: 86780009] [sessionid: 86780000] [username: weblogic] -------------------- Rows 45440, bytes 2128409600 retrieved from database query id: <<2501833>> [2016-09-22T11:00:16.310+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014c275,0:1:1] [tid: 67627940] [messageid: USER-28] [requestid: 86780009] [sessionid: 86780000] [username: weblogic] -------------------- Physical query response time 41.719 (seconds), id <<2501833>> [2016-09-22T11:00:16.310+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014c275,0:1:1] [tid: 67627940] [messageid: USER-29] [requestid: 86780009] [sessionid: 86780000] [username: weblogic] -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 41.719, DB-connect time 0.000 (seconds) [2016-09-22T11:00:16.310+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014c275,0:1:1] [tid: 67627940] [messageid: USER-24] [requestid: 86780009] [sessionid: 86780000] [username: weblogic] -------------------- Rows returned to Client 1055 [2016-09-22T11:00:16.310+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014c275,0:1:1] [tid: 67627940] [messageid: USER-33] [requestid: 86780009] [sessionid: 86780000] [username: weblogic] -------------------- Logical Query Summary Stats: Elapsed time 317.982, Total time in BI Server 70.079, Response time 70.075, Compilation time 0.096 (seconds), Logical hash 6a985b29 [2016-09-22T11:00:16.416+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014c275,0:1:4] [tid: 67627940] [messageid: USER-34] [requestid: 66b0005] [sessionid: 66b0000] [username: weblogic] -------------------- Query Status: Successful Completion [2016-09-22T11:00:16.416+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014c275,0:1:4] [tid: 67627940] [messageid: USER-26] [requestid: 66b0005] [sessionid: 66b0000] [username: weblogic] -------------------- Rows 45436, bytes 2128585728 retrieved from database query id: <<2500014>> [2016-09-22T11:00:16.416+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014c275,0:1:4] [tid: 67627940] [messageid: USER-28] [requestid: 66b0005] [sessionid: 66b0000] [username: weblogic] -------------------- Physical query response time 46.886 (seconds), id <<2500014>> [2016-09-22T11:00:16.416+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014c275,0:1:4] [tid: 67627940] [messageid: USER-29] [requestid: 66b0005] [sessionid: 66b0000] [username: weblogic] -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 46.886, DB-connect time 0.000 (seconds) [2016-09-22T11:00:16.416+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014c275,0:1:4] [tid: 67627940] [messageid: USER-24] [requestid: 66b0005] [sessionid: 66b0000] [username: weblogic] -------------------- Rows returned to Client 1052 [2016-09-22T11:00:16.416+08:00] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b3d01a373daea899:-18ff00a:156fa30e3d3:-8000-000000000014c275,0:1:4] [tid: 67627940] [messageid: USER-33] [requestid: 66b0005] [sessionid: 66b0000] [username: weblogic] -------------------- Logical Query Summary Stats: Elapsed time 373.240, Total time in BI Server 48.694, Response time 48.687, Compilation time 0.092 (seconds), Logical hash 1fa5b61b but I used this physical sql to run in plsql developer is almost 3s. the total row is 40 thousand almost. what is difference between them! I'm urgently to kown this and want to solve the performance from it. @Christian