Oracle Communities

Oracle Analytics and AI

Child Item

OBIEE Logical Query and Physical Query bug or not?

Dje07

Hi Everyone

Can you help me

I take out Query log on BIEE 12.2.1.2

but i get somethin wrong about query on database,

First on logical Query BIEE filter about Date is available beside other filter, but when on Physcal Query on Database filter about date is missing beside other filter.

It's Normal or not?

Thanks

Dje

Find more posts tagged with

Accepted answers

All comments

Robert Angel

Can you show a screenshot or log extract to show exactly what you mean please?

Dje07

[2018-05-23T17:47:07.81+07:00] [OBIS] [TRACE:7] [] [] [ecid: c36b9dc6-d4b5-4ea5-9249-bde95a9cb502-00007987,0:1:1:3] [sik: ssi] [tid: fd565700] [messageid: USER-0] [requestid: d08f0077] [sessionid: d08f0000] [username: weblogic] ############################################## [[

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

9632961d

SET VARIABLE OBIS_REFRESH_CACHE=1,QUERY_SRC_CD='Report',SAW_SRC_PATH='/shared/SA/Report AA/Analysis Report AA';SELECT

0 s_0,

"SA"."Report AA"."Area Code" s_1,

"SA"."Report AA"."Area Name" s_2,

"SA"."Report AA"."Date Time" s_3,

"SA"."Report AA"."Detail" s_4,

"SA"."Report AA"."Category" s_5,

"SA"."Report AA"."Msisdn" s_6,

"SA"."Report AA"."Reg Code" s_7,

"SA"."Report AA"."Reg Name" s_8,

"SA"."Report AA"."Region" s_9,

"SA"."Report AA"."Segment" s_10,

"SA"."Report AA"."SubCategory" s_11

FROM "SA"

WHERE

("Report AA"."Datex" BETWEEN date '2018-05-02' AND date '2018-05-09')

ORDER BY 10 ASC NULLS LAST, 4 ASC NULLS LAST, 7 ASC NULLS LAST, 2 ASC NULLS LAST, 3 ASC NULLS LAST, 8 ASC NULLS LAST, 9 ASC NULLS LAST, 6 ASC NULLS LAST, 12 ASC NULLS LAST, 5 ASC NULLS LAST, 11 ASC NULLS LAST

FETCH FIRST 65001 ROWS ONLY

]]

[2018-05-23T17:47:07.81+07:00] [OBIS] [TRACE:7] [] [] [ecid: c36b9dc6-d4b5-4ea5-9249-bde95a9cb502-00007987,0:1:1:3] [sik: ssi] [tid: fd565700] [messageid: USER-74] [requestid: d08f0077] [sessionid: d08f0000] [username: weblogic] -------------------- Logical query limits: max query time(user, system, session) = (0, 0, 0); max rows(system, session) = (0, 0)

[2018-05-23T17:47:07.81+07:00] [OBIS] [TRACE:7] [] [] [ecid: c36b9dc6-d4b5-4ea5-9249-bde95a9cb502-00007987,0:1:1:3] [sik: ssi] [tid: fd565700] [messageid: USER-23] [requestid: d08f0077] [sessionid: d08f0000] [username: weblogic] -------------------- General Query Info: [[

Repository: ssi, Subject Area: SA, Presentation: SA

]]

[2018-05-23T17:47:07.84+07:00] [OBIS] [TRACE:7] [] [] [ecid: c36b9dc6-d4b5-4ea5-9249-bde95a9cb502-00007987,0:1:1:3] [sik: ssi] [tid: fd565700] [messageid: USER-2] [requestid: d08f0077] [sessionid: d08f0000] [username: weblogic] -------------------- Logical Request (before navigation): [[

RqList distinct

0 as c1 GB,

A_TABLE_AA.Area Code as c2 GB,

A_TABLE_AA.Area Name as c3 GB,

A_TABLE_AA.Date Time as c4 GB,

A_TABLE_AA.Detail as c5 GB,

A_TABLE_AA.Category as c6 GB,

A_TABLE_AA.Msisdn as c7 GB,

A_TABLE_AA.Reg Code as c8 GB,

A_TABLE_AA.Reg Name as c9 GB,

A_TABLE_AA.Region as c10 GB,

A_TABLE_AA.Segment as c11 GB,

A_TABLE_AA.SubCategory as c12 GB

DetailFilter: A_TABLE_AA.Datex between DATE '2018-05-02' and DATE '2018-05-09'

OrderBy: c10 asc NULLS LAST, c4 asc NULLS LAST, c7 asc NULLS LAST, c2 asc NULLS LAST, c3 asc NULLS LAST, c8 asc NULLS LAST, c9 asc NULLS LAST, c6 asc NULLS LAST, c12 asc NULLS LAST, c5 asc NULLS LAST, c11 asc NULLS LAST

]]

[2018-05-23T17:47:07.97+07:00] [OBIS] [TRACE:7] [] [] [ecid: c36b9dc6-d4b5-4ea5-9249-bde95a9cb502-00007987,0:1:1:5] [sik: ssi] [tid: fd565700] [messageid: USER-53] [requestid: d08f0077] [sessionid: d08f0000] [username: weblogic] -------------------- List of attributes and their LTS sources: [[

Column A_TABLE_AA.SubCategory rendered via fact LTS [Logical table sources (Priority=0, SystemGenerated=False): A_TABLE_AA.A_TABLE_AA]

]]

[2018-05-23T17:47:07.98+07:00] [OBIS] [TRACE:7] [] [] [ecid: c36b9dc6-d4b5-4ea5-9249-bde95a9cb502-00007987,0:1:1:5] [sik: ssi] [tid: fd565700] [messageid: USER-48] [requestid: d08f0077] [sessionid: d08f0000] [username: weblogic] -------------------- The logical query block failed to hit or seed the cache in subrequest level due to [[

only one subrequest

]]

[2018-05-23T17:47:07.99+07:00] [OBIS] [TRACE:7] [] [] [ecid: c36b9dc6-d4b5-4ea5-9249-bde95a9cb502-00007987,0:1:1:5] [sik: ssi] [tid: fd565700] [messageid: USER-53] [requestid: d08f0077] [sessionid: d08f0000] [username: weblogic] -------------------- List of attributes and their LTS sources:

[2018-05-23T17:47:07.100+07:00] [OBIS] [TRACE:7] [] [] [ecid: c36b9dc6-d4b5-4ea5-9249-bde95a9cb502-00007987,0:1:1:5] [sik: ssi] [tid: fd565700] [messageid: USER-51] [requestid: d08f0077] [sessionid: d08f0000] [username: weblogic] -------------------- The logical plan contains non-cacheable table or expression: TABLE_AA AS A_TABLE_AA [[

plan

RqList <<3203194>> distinct

0 as c1 GB,

D1.c11 as c2 GB,

D1.c10 as c3 GB,

D1.c9 as c4 GB,

D1.c8 as c5 GB,

D1.c7 as c6 GB,

D1.c6 as c7 GB,

D1.c5 as c8 GB,

D1.c4 as c9 GB,

D1.c3 as c10 GB,

D1.c2 as c11 GB,

D1.c1 as c12 GB

Child Nodes (RqJoinSpec): <<3203262>>

RqJoinNode <<3203261>> []

(

RqList <<3203201>>

A_TABLE_AA.SUBCategory as c1 GB,

A_TABLE_AA.SEGMENT as c2 GB,

A_TABLE_AA.REGION as c3 GB,

A_TABLE_AA.REG_NAME as c4 GB,

A_TABLE_AA.REG_CODE as c5 GB,

A_TABLE_AA.MSISDN as c6 GB,

A_TABLE_AA.Category as c7 GB,

A_TABLE_AA.DETAIL as c8 GB,

A_TABLE_AA.DATE_TIME as c9 GB,

A_TABLE_AA.AREA_NAME as c10 GB,

A_TABLE_AA.AREA_CODE as c11 GB

Child Nodes (RqJoinSpec): <<3203215>>

RqJoinNode <<3203214>> []

TABLE_AA AS A_TABLE_AA

DetailFilter: not A_TABLE_AA.DATEX < DATE '2018-05-02' and not DATE '2018-05-09' < A_TABLE_AA.DATEX

) as D1

]]

[2018-05-23T17:47:07.110+07:00] [OBIS] [TRACE:7] [] [] [ecid: c36b9dc6-d4b5-4ea5-9249-bde95a9cb502-00007987,0:1:1:5] [sik: ssi] [tid: fd565700] [messageid: USER-50] [requestid: d08f0077] [sessionid: d08f0000] [username: weblogic] -------------------- The logical query disqualifies the plan cache [[

plan

]]

[2018-05-23T17:47:07.111+07:00] [OBIS] [TRACE:7] [] [] [ecid: c36b9dc6-d4b5-4ea5-9249-bde95a9cb502-00007987,0:1:1:5] [sik: ssi] [tid: fd565700] [messageid: USER-16] [requestid: d08f0077] [sessionid: d08f0000] [username: weblogic] -------------------- Execution plan: [[

RqList <<3203194>> [for database 0:0,0] distinct /* FETCH FIRST 65001 ROWS ONLY */

0 as c1 GB [for database 0:0,1],

D1.c11 as c2 GB [for database 0:0,0],

D1.c10 as c3 GB [for database 0:0,0],

D1.c9 as c4 GB [for database 0:0,0],

D1.c8 as c5 GB [for database 0:0,0],

D1.c7 as c6 GB [for database 0:0,0],

D1.c6 as c7 GB [for database 0:0,0],

D1.c5 as c8 GB [for database 0:0,0],

D1.c4 as c9 GB [for database 0:0,0],

D1.c3 as c10 GB [for database 0:0,0],

D1.c2 as c11 GB [for database 0:0,0],

D1.c1 as c12 GB [for database 0:0,0]

Child Nodes (RqJoinSpec): <<3203262>> [for database 0:0,0]

RqJoinNode <<3203261>> []

(

RqList <<3203314>> [for database 0:0,0]

D1.c1 as c1 [for database 0:0,0],

D1.c2 as c2 [for database 0:0,0],

D1.c3 as c3 [for database 0:0,0],

D1.c4 as c4 [for database 0:0,0],

D1.c5 as c5 [for database 0:0,0],

D1.c6 as c6 [for database 0:0,0],

D1.c7 as c7 [for database 0:0,0],

D1.c8 as c8 [for database 0:0,0],

D1.c9 as c9 [for database 0:0,0],

D1.c10 as c10 [for database 0:0,0],

D1.c11 as c11 [for database 0:0,0]

Child Nodes (RqJoinSpec): <<3203353>> [for database 0:0,0]

RqJoinNode <<3203352>> []

(

RqList <<3203201>> [for database 3023:49200:schema1,2]

A_TABLE_AA.SUBCategory as c1 GB [for database 3023:49200:schema1,2],

A_TABLE_AA.SEGMENT as c2 GB [for database 3023:49200:schema1,2],

A_TABLE_AA.REGION as c3 GB [for database 3023:49200:schema1,2],

A_TABLE_AA.REG_NAME as c4 GB [for database 3023:49200:schema1,2],

A_TABLE_AA.REG_CODE as c5 GB [for database 3023:49200:schema1,2],

A_TABLE_AA.MSISDN as c6 GB [for database 3023:49200:schema1,2],

A_TABLE_AA.Category as c7 GB [for database 3023:49200:schema1,2],

A_TABLE_AA.DETAIL as c8 GB [for database 3023:49200:schema1,2],

A_TABLE_AA.DATE_TIME as c9 GB [for database 3023:49200:schema1,2],

A_TABLE_AA.AREA_NAME as c10 GB [for database 3023:49200:schema1,2],

A_TABLE_AA.AREA_CODE as c11 GB [for database 3023:49200:schema1,2],

A_TABLE_AA.DATEX as c12 [for database 3023:49200:schema1,2]

Child Nodes (RqJoinSpec): <<3203215>> [for database 3023:49200:schema1,2]

RqJoinNode <<3203214>> []

TABLE_AA AS A_TABLE_AA

) as D1

DetailFilter: not D1.c12 < DATE '2018-05-02' and not DATE '2018-05-09' < D1.c12 [for database 0:0,0]

) as D1

]]

[2018-05-23T17:47:07.114+07:00] [OBIS] [TRACE:7] [] [] [ecid: c36b9dc6-d4b5-4ea5-9249-bde95a9cb502-00007987,0:1:1:5] [sik: ssi] [tid: fd565700] [messageid: USER-18] [requestid: d08f0077] [sessionid: d08f0000] [username: weblogic] -------------------- Sending query to database named schema1 (id: <<3203201>>), connection pool named schema1, logical request hash 9632961d, physical request hash 5d572a0b: [[

select T56411."SUBCategory" as c1,

T56411."SEGMENT" as c2,

T56411."REGION" as c3,

T56411."REG_NAME" as c4,

T56411."REG_CODE" as c5,

T56411."MSISDN" as c6,

T56411."Category" as c7,

T56411."DETAIL" as c8,

T56411."DATE_TIME" as c9,

T56411."AREA_NAME" as c10,

T56411."AREA_CODE" as c11,

T56411."DATEX" as c12

from

"TABLE_AA" T56411

]]

[2018-05-23T17:48:05.458+07:00] [OBIS] [TRACE:7] [] [] [ecid: c36b9dc6-d4b5-4ea5-9249-bde95a9cb502-0000744e,0:1:1:5:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1] [sik: ssi] [tid: 62c36700] [messageid: USER-73] [requestid: d08f0077] [sessionid: d08f0000] [username: weblogic] -------------------- Physical query limits: max time = 0; max rows = 0; max data size = 0

Dje07

This Full Query on One analysis. Its Normal? Thanks DJe

Robert Angel

Without knowing your rpd modelling I cannot comment on whether it is correct, but yes, the logical model is typically massively simpler than the physical model, I would not see what you are seeing as a bug, merely as an indication of the underlying complexity of the physical model.

Dje07

its like flat table join with time dimension.

Robert Angel

I think I need more than your sentence there, but reading between the lines you are saying 'Why is the OBIEE sql so complex when you would think it would just be select ..... from fact f, time_dimension td where fk.tkey = td.key' - then yes, partly I agree with you (all of that select from select from ... stuff seems wholly pointless), partly OBIEE does add value and optimises, and dependent on the exact nature of the query and view used some of the 'work' may also be done on the client.

This is somewhat dated (depending on what version you are on) but you might find this interesting; -

https://www.rittmanmead.com/blog/2010/03/inside-the-oracle-bi-server-part-2-how-is-a-query-processed/

[Deleted User]

Nice reference and nice write-up, Robert!

@Dje07 OBI lives and dies with its models. So basically if they are done sub-optimally or just...wrong...then it can never produce any meaningful or optimized queries.

Already if I see something called "SA."Report AA" personally I have serious doubts as to the modelling in the RPD!

Robert Angel

lol re "SA."Report AA"

Robert Angel

And thanks for the compliment - feels better than all of the 'badges' you get on the forum!

#HashTagReceivedAComplimentFromChristianBergShouldBeABadge

[Deleted User]

Should it? Be careful what you wish for ;-)