back2 basics - a day in the life of an oracle analytics query
TRANSCRIPT
![Page 1: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/1.jpg)
Back2Basics
A day in the life of an Oracle BI query
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 2: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/2.jpg)
• Owner of Dimensionality GmbH in Switzerland
• Hacking OBI since 2001 (nQuire + Peregrin aquisitions by Siebel)
• Oracle ACE Associate BI/DWH
• Speaker at OpenWorld, KScope, regional Oracle User Groups...
• Part-time blogger on Oracle BI (http://dimensionality.ch)
• Full-time IRC (freenode | #obihackers) and OTN addict
• Oracle BI trainer for Oracle University since 2006
• Proud geek and gamer
• Responding to any and all questions 24 / 7 – especially on IRC
Who am I?
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 3: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/3.jpg)
Who am I?
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 4: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/4.jpg)
Outline
• Why this presentation
• Overview / What the packaging says
• VA / DV / DVD / BICS / ASCS
• The actual day in the life of a query
• Query execution details
• Summary
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 5: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/5.jpg)
Outline
• Why this presentation
• Overview / What the packaging says
• VA / DV / DVD / BICS / ASCS
• The actual day in the life of a query
• Query execution details
• Summary
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 6: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/6.jpg)
Why this presentation?
• Oracle BI has matured and is quite wide-spread
• As a result it’s often done worse than ever…
• Problem being: core of a whole range of products
• Hence: Back to basics presentation series
• Not understanding and comprehending query mechanisms is a
cardinal sin for any developer
• And no, “cloud” doesn’t eliminate the need for “comprehension”
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 7: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/7.jpg)
Why this presentation?
• Alternative title “A normal day on the OTN forums”
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 8: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/8.jpg)
Why this presentation?
• Going more into details from my “Worst practices” presentation
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 9: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/9.jpg)
Why this presentation?
• Alleviate your struggle
• How can I get from front-end problems to the actual root cause?
• Why is my report returning the data it does?
• Why do I get old data back? (Spoiler alert: Cache!)
• Where can I find all bits and pieces of information for my investigation?
• Why doesn’t my request hit the source I thought it would?
• Why does it bypass our aggregate tables?
• If I change the RPD – what will be the impact?
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 10: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/10.jpg)
Why this presentation?
Simply put:
Without a solid understanding of how and why
queries function inside the Oracle BI core you will have
a very hard time of analyzing any issue pertaining to
data, aggregations, modelling, performance etc. !
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 11: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/11.jpg)
Outline
• Why this presentation
• Overview / What the packaging says
• VA / DV / DVD / BICS / ASCS
• The actual day in the life of a query
• Query execution details
• Summary
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 12: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/12.jpg)
What the packaging says
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 13: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/13.jpg)
What the packaging says
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 14: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/14.jpg)
What the packaging says
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 15: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/15.jpg)
And “under the covers”?
www.dimensionality.ch @Nephentur #obihackers | freenode
Needs
more
detail…
![Page 16: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/16.jpg)
A little archeology
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 17: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/17.jpg)
Wait, wait…
www.dimensionality.ch @Nephentur #obihackers | freenode
…did you just say “Siebel Analytics”; so what about versions?
![Page 18: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/18.jpg)
Wait, wait…
www.dimensionality.ch @Nephentur #obihackers | freenode
…did you just say “Siebel Analytics”; so what about versions?
![Page 19: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/19.jpg)
Wait, wait…
www.dimensionality.ch @Nephentur #obihackers | freenode
…did you just say “Siebel Analytics”; so what about versions?
![Page 20: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/20.jpg)
Outline
• Why this presentation
• Overview / What the packaging says
• VA / DV / DVD / BICS / ASCS
• The actual day in the life of a query
• Query execution details
• Summary
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 21: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/21.jpg)
VA / DV / DVD / BICS / ASCS?
www.dimensionality.ch @Nephentur #obihackers | freenode
But Christian it’s all about cloud, data visualization and mashups now!
Surely I don’t need to bother with all this query stuff?
![Page 22: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/22.jpg)
VA / DV / DVD / BICS?
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 23: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/23.jpg)
VA / DV / DVD / BICS?
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 24: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/24.jpg)
VA / DV / DVD / BICS?
www.dimensionality.ch @Nephentur #obihackers | freenode
But Christian it’s all about data visualization and mashups now! Surely I
don’t need to bother with all this?
![Page 25: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/25.jpg)
So what about VA / DV / DVD / BICS?
www.dimensionality.ch @Nephentur #obihackers | freenode
Yes DV/ASCS/BICS are way(s) forward, but…
Really? Think about it:
• They all still access Subject Areas (indirectly)
• Performance problems won’t just automagically disappear
– You’ll probably run into even more thanks to mashups and file uploads
• Just because there are nice graphs doesn’t mean there isn’t still a very clever system in the background!
• But yes, there’s a new toy and new possibilities
• Watch out for the next dissection talk!
![Page 26: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/26.jpg)
So what about VA / DV / DVD / BICS?
www.dimensionality.ch @Nephentur #obihackers | freenode
Yes VA, DV and DVD are the way forward, but…
Really? Think about it:
• They all still access Subject Areas (indirectly)
• Performance problems won’t just automagically disappear
– You’ll probably run into even more thanks to mashups and file uploads
• Just because there are nice graphs doesn’t mean there isn’t still a very clever system in the background!
• But yes, there’s a new toy and new possibilities
• Watch out for the next dissection talk!
![Page 27: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/27.jpg)
Outline
• Why this presentation
• Overview / What the packaging says
• VA / DV / DVD / BICS / ASCS
• The actual day in the life of a query
• Query execution details
• Summary
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 28: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/28.jpg)
The workday
www.dimensionality.ch @Nephentur #obihackers | freenode
?
![Page 29: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/29.jpg)
This is where
very few
magic should
happen
This is where
THE magic
happens
We don’t
really care
about this part
This is where
the actual
workload
should happen
“The workday” aka detailed query flow
www.dimensionality.ch @Nephentur #obihackers | freenode
Browser,
SmartView,
other toolsBI Server
WLS
Managed
Server
Presentation
Server
Sources
Browser,
SmartView,
other toolsBI Server
WLS
Managed
Server
Presentation
Server
![Page 30: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/30.jpg)
To this detail architectureMapping these flow details in the
BI Server
“Magic” details
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 31: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/31.jpg)
Your friend the log
www.dimensionality.ch @Nephentur
12c: obis1-query.log
11g: nqquery.log
#obihackers | freenode
![Page 32: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/32.jpg)
Your friend the log
www.dimensionality.ch @Nephentur
12c: obis1-query.log
11g: nqquery.log
-------------------- SQL Request, logical request hash:a180d345SET VARIABLE
OBIS_REFRESH_CACHE=1,QUERY_SRC_CD='Report',SAW_SRC_PATH='/users/web
logic/Preso/Main',PREFERRED_CURRENCY='USD',DISABLE_CACHE=1,LOG_LEVEL=
2;
SELECT
0 s_0,
"D - Sample Federated"."Time"."T05 Per Name Year" s_1,
"D - Sample Federated"."Measures"."1a- Revenue (Orcl Db Tables Only)" s_2
FROM "D - Sample Federated"
ORDER BY 2 ASC NULLS LAST
FETCH FIRST 5000001 ROWS ONLY
#obihackers | freenode
![Page 33: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/33.jpg)
Your friend the log
www.dimensionality.ch @Nephentur
12c: obis1-query.log
11g: nqquery.log
-------------------- SQL Request, logical request hash:a180d345SET VARIABLE
OBIS_REFRESH_CACHE=1,QUERY_SRC_CD='Report',SAW_SRC_PATH='/users/web
logic/Preso/Main',PREFERRED_CURRENCY='USD',DISABLE_CACHE=1,LOG_LEVEL=
2;
SELECT
0 s_0,
"D - Sample Federated"."Time"."T05 Per Name Year" s_1,
"D - Sample Federated"."Measures"."1a- Revenue (Orcl Db Tables Only)" s_2
FROM "D - Sample Federated"
ORDER BY 2 ASC NULLS LAST
FETCH FIRST 5000001 ROWS ONLY
[2016-05-27T13:40:38.29+00:00] [OBIS] [TRACE:3] [] [] [ecid: aa89eaf6-8094-
45b1-8c59-ea46b2a1e14e-00008dd6,0:1:17:5] [sik: ssi] [tid: 996f4700]
[messageid: USER-18] [requestid: e295001d] [sessionid: e2950000] [username:
weblogic] -------------------- Sending query to database named 01 - Sample App
Data (ORCL) (id: <<387385>>), connection pool named Sample Relational
Connection, logical request hash a180d345, physical request hash f5274c5e: [[
WITH
SAWITH0 AS (select sum(T418.Revenue) as c1,
T653.Per_Name_Year as c2
From
BISAMPLE.SAMP_TIME_MTH_D T653 /* D02 Time Month Grain */ ,
BISAMPLE.SAMP_REVENUE_F T418 /* F10 Billed Rev */ where (
T418.Bill_Mth_Key = T653.Mth_Key ) group by T653.Per_Name_Year)select
D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select 0 as c1, D1.c2 as c2, D1.c1
as c3from SAWITH0 D1order by c2 ) D1 where rownum <= 5000001
#obihackers | freenode
![Page 34: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/34.jpg)
Outline
• Why this presentation
• Overview / What the packaging says
• VA / DV / DVD / BICS / ASCS
• The actual day in the life of a query
• Query execution details
• Summary
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 35: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/35.jpg)
Intelligent Request Generation
www.dimensionality.ch @Nephentur #obihackers | freenode
• Receives the queries from the front-end
• Answers, SmartView, Visual Analyzer,
Data Visualization (Desktop)
• Other tools using the Oracle BI Server
ODBC API or Web Servives
![Page 36: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/36.jpg)
Logical Request Generation
www.dimensionality.ch @Nephentur #obihackers | freenode
• Creates the actual logical request to be
processed
• Doesn’t “write the query”
• Doesn’t decide which business models,
LTSs or sources to use
• Preparation stage for Cache Services and
Navigator
![Page 37: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/37.jpg)
Logical Request Generation
www.dimensionality.ch @Nephentur #obihackers | freenode
-------------------- SQL Request, logical request hash:
ee9cf87d
SET VARIABLE
OBIS_REFRESH_CACHE=1,QUERY_SRC_CD='Report',SAW_SRC_PATH='/users/weblogic/Preso/Main',PREFER
RED_CURRENCY='USD',DISABLE_CACHE=1,LOG_LEVEL=5;
SELECT
0 s_0,
"D - Sample Federated"."Time"."T05 Per Name Year" s_1,
"D - Sample Federated"."Measures"."1a- Revenue (Orcl Db Tables Only)" s_2,
"D - Sample Federated"."Measures"."1b- Revenue (Essbase Cube Only)" s_3
FROM "D - Sample Federated"
ORDER BY 2 ASC NULLS LAST
FETCH FIRST 5000001 ROWS ONLY
![Page 38: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/38.jpg)
Cache Services
www.dimensionality.ch @Nephentur #obihackers | freenode
• Optional step: Caching enabled?
• Checks if request can be fulfilled by existing
cache entries
• Cache doesn’t need to match 1:1
– Existing entries can be aggregated
– Subsets can be extracted
• Cache can be forcibly circumvented
![Page 39: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/39.jpg)
Cache Services
www.dimensionality.ch @Nephentur #obihackers | freenode
• Optional step: Caching enabled?
• Checks if request can be fulfilled by existing
cache entries
• Cache doesn’t need to match 1:1
– Existing entries can be aggregated
– Subsets can be extracted
• Cache can be forcibly circumvented
![Page 40: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/40.jpg)
Cache Services
www.dimensionality.ch @Nephentur #obihackers | freenode
• Optional step: Caching enabled?
• Checks if request can be fulfilled by existing
cache entries
• Cache doesn’t need to match 1:1
– Existing entries can be aggregated
– Subsets can be extracted
• Cache can be forcibly circumvented-------------------- SQL Request, logical request hash:
ee9cf87d
SET VARIABLE
OBIS_REFRESH_CACHE=1,QUERY_SRC_CD='Report',SAW_SRC_PATH='/users/weblogic/Preso/Main',PREFER
RED_CURRENCY='USD',DISABLE_CACHE=1,LOG_LEVEL=5;
[…]
ORDER BY 2 ASC NULLS LAST
FETCH FIRST 5000001 ROWS ONLY
![Page 41: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/41.jpg)
Cache Services
www.dimensionality.ch @Nephentur #obihackers | freenode
• All SELECT columns
– Existing in cache entries or
– Derived from cache entries (bins, substring or
other analysis-based formulas/functions)
• WHERE clause must match or be a subset
of cache entries– Query for 10 US states will hit a cache entry containing all
the US states
• Join conditions must match or be a subset
![Page 42: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/42.jpg)
Cache Services
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 43: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/43.jpg)
Cache Services
www.dimensionality.ch @Nephentur #obihackers | freenode
[2016-05-28T15:18:40.151+00:00] [OBIS] [TRACE:3] [] [] [ecid: 86cb6a39-9f56-4305-a0e6-
dd95618de970-000006a9,0:1:23:5] [sik: ssi] [tid: df80700] [messageid: USER-18] [requestid: f7340009]
[sessionid: f7340000] [username: weblogic] -------------------- Sending query to database named 01 -
Sample App Data (ORCL) (id: <<112556>>), connection pool named Sample Relational Connection,
logical request hash e019650e, physical request hash 61dd43b2: [[
WITH
SAWITH0 AS (select distinct T795.PER_NAME_YEAR as c1
from
BISAMPLE.SAMP_TIME_QTR_D T795 /* D03 Time Quarter Grain */ )
select D1.c1 as c1, D1.c2 as c2 from ( select 0 as c1,
D1.c1 as c2
from
SAWITH0 D1
order by c2 ) D1 where rownum <= 5000001
![Page 44: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/44.jpg)
Cache Services
www.dimensionality.ch @Nephentur #obihackers | freenode
[2016-05-28T15:18:40.151+00:00] [OBIS] [TRACE:3] [] [] [ecid: 86cb6a39-9f56-4305-a0e6-
dd95618de970-000006a9,0:1:23:5] [sik: ssi] [tid: df80700] [messageid: USER-18] [requestid: f7340009]
[sessionid: f7340000] [username: weblogic] -------------------- Sending query to database named 01 -
Sample App Data (ORCL) (id: <<112556>>), connection pool named Sample Relational Connection,
logical request hash e019650e, physical request hash 61dd43b2: [[
WITH
SAWITH0 AS (select distinct T795.PER_NAME_YEAR as c1
from
BISAMPLE.SAMP_TIME_QTR_D T795 /* D03 Time Quarter Grain */ )
select D1.c1 as c1, D1.c2 as c2 from ( select 0 as c1,
D1.c1 as c2
from
SAWITH0 D1
order by c2 ) D1 where rownum <= 5000001
[2016-05-28T15:18:40.182+00:00] [OBIS] [TRACE:3] [] [] [ecid:
005D_Wap^cJFw000jzwkno0001Qq000EG0,0] [sik: ssi] [tid: 6fe3700] [messageid: USER-27] [requestid:
f7340009] [sessionid: f7340000] [username: weblogic] -------------------- Total Cache elapsed time 0.028
(seconds)
[2016-05-28T15:18:40.183+00:00] [OBIS] [TRACE:3] [] [] [ecid:
005D_Wap^cJFw000jzwkno0001Qq000EG0,0] [sik: ssi] [tid: 6fe3700] [messageid: USER-42] [requestid:
f7340009] [sessionid: f7340000] [username: weblogic] -------------------- Query Result Cache: [59124] The
query for user 'weblogic' was inserted into the query result cache. The filename is
'/app/oracle/biee/user_projects/domains/bi/servers/obis1/cache/NQS__736145_55120_0.TBL'.
[2016-05-28T15:18:40.183+00:00] [OBIS] [TRACE:3] [] [] [ecid:
005D_Wap^cJFw000jzwkno0001Qq000EG0,0] [sik: ssi] [tid: 6fe3700] [messageid: USER-50] [requestid:
f7340009] [sessionid: f7340000] [username: weblogic] -------------------- The logical query seeds the plan
cache [[
plan
RqList <<307721>> [for database 0:0,9]
T1.1 as c1 [for database 0:0,9],
T1.2 as c2 [for database 0:0,9]
Child Nodes (RqJoinSpec): <<307730>> [for database 0:0,1]
RqJoinNode <<307729>> []
CacheTable T1
![Page 45: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/45.jpg)
Cache Services
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 46: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/46.jpg)
Cache Services
www.dimensionality.ch @Nephentur #obihackers | freenode
[2016-05-28T15:31:22.112+00:00] [OBIS] [TRACE:3] [] [] [ecid: 86cb6a39-9f56-4305-a0e6-dd95618de970-00000820,0:1:24:5] [sik: ssi] [tid: de7b700]
[messageid: USER-21] [requestid: f7340014] [sessionid: f7340000] [username: weblogic] -------------------- Cache Hit on query: [[
Matching Query: SET VARIABLE
OBIS_REFRESH_CACHE=1,QUERY_SRC_CD='Report',SAW_DASHBOARD='/users/weblogic/_portal',SAW_DASHBOARD_PG='page
1',SAW_SRC_PATH='/users/weblogic/Preso/Cache Hit',PREFERRED_CURRENCY='USD';SELECT
0 s_0,
"A - Sample Sales"."Time"."T05 Per Name Year" s_1
FROM "A - Sample Sales"
ORDER BY 2 ASC NULLS LAST
FETCH FIRST 5000001 ROWS ONLY
Created by: weblogic
]]
[2016-05-28T15:31:22.114+00:00] [OBIS] [TRACE:3] [] [] [ecid: 86cb6a39-9f56-4305-a0e6-dd95618de970-00000820,0:1:24:5] [sik: ssi] [tid: de7b700]
[messageid: USER-55] [requestid: f7340014] [sessionid: f7340000] [username: weblogic] -------------------- Order By Safely Removed [[
'OrderBy: D1.c2 asc NULLS LAST [ added for join ] ' above the cache hit plan is safely dropped due to the preserved order by ' OrderBy: c2 asc NULLS LAST'
from cache entry
![Page 47: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/47.jpg)
Cache Services
www.dimensionality.ch @Nephentur #obihackers | freenode
[2016-05-28T15:31:22.114+00:00] [OBIS] [TRACE:3] [] [] [ecid: 86cb6a39-9f56-4305-a0e6-dd95618de970-00000820,0:1:24:5] [sik: ssi] [tid: de7b700]
[messageid: USER-50] [requestid: f7340014] [sessionid: f7340000] [username: weblogic] -------------------- The logical query seed the plan cache [[
plan
RqList <<120136>> [for database 0:0,0] /* FETCH FIRST 5000001 ROWS ONLY */
0 as c1 [for database 0:0,1],
D1.c2 as c2 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<120142>> [for database 0:0,0]
RqJoinNode <<120143>> []
(
RqList <<120146>> [for database 0:0,9]
T1.2 as c2 [for database 0:0,9],
T1.2 as c3 [for database 0:0,9]
Child Nodes (RqJoinSpec): <<120152>> [for database 0:0,9]
RqJoinNode <<120153>> []
CacheTable T1
) as D1
DetailFilter: not D1.c3 < '2012' [for database 0:0,0]
![Page 48: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/48.jpg)
Cache Services
www.dimensionality.ch @Nephentur #obihackers | freenode
[2016-05-28T15:31:22.139+00:00] [OBIS] [TRACE:3] [] [] [ecid: 86cb6a39-9f56-4305-a0e6-dd95618de970-00000820,0:1:28] [sik: ssi] [tid: de7b700] [messageid:
USER-34] [requestid: f7340014] [sessionid: f7340000] [username: weblogic] -------------------- Query Status: Successful Completion
[2016-05-28T15:31:22.139+00:00] [OBIS] [TRACE:3] [] [] [ecid: 86cb6a39-9f56-4305-a0e6-dd95618de970-00000820,0:1:28] [sik: ssi] [tid: de7b700] [messageid:
USER-29] [requestid: f7340014] [sessionid: f7340000] [username: weblogic] -------------------- Physical Query Summary Stats: Number of physical queries 1,
Cumulative time 0.000, DB-connect time 0.000 (seconds)
[2016-05-28T15:31:22.139+00:00] [OBIS] [TRACE:3] [] [] [ecid: 86cb6a39-9f56-4305-a0e6-dd95618de970-00000820,0:1:28] [sik: ssi] [tid: de7b700] [messageid:
USER-24] [requestid: f7340014] [sessionid: f7340000] [username: weblogic] -------------------- Rows returned to Client 2
[2016-05-28T15:31:22.139+00:00] [OBIS] [TRACE:3] [] [] [ecid: 86cb6a39-9f56-4305-a0e6-dd95618de970-00000820,0:1:28] [sik: ssi] [tid: de7b700] [messageid:
USER-33] [requestid: f7340014] [sessionid: f7340000] [username: weblogic] -------------------- Logical Query Summary Stats: Elapsed time 0.030, Total time in BI
Server 0.029, Execution time 0.008, Response time 0.029, Compilation time 0.007 (seconds), logical request hash 48047dfa
![Page 49: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/49.jpg)
Navigator
www.dimensionality.ch @Nephentur #obihackers | freenode
• The true brain of the operation
• Parses BI Server metadata
• Decides on actual logical tables, logical
table sources etc.
• Uses 3 components to decide on details
– Multi-pass / sub-request
– Fragmentation
– Aggregates
![Page 50: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/50.jpg)
Multi-pass / sub-request
www.dimensionality.ch @Nephentur #obihackers | freenode
[2016-05-27T14:28:54.799+00:00] [OBIS] [TRACE:3] [] [] [ecid: aa89eaf6-8094-45b1-8c59-ea46b2a1e14e-000092ae,0:1:9:3] [sik: ssi] [tid: 996f4700] [messageid: USER-23] [requestid:
af740029] [sessionid: af740000] [username: weblogic] -------------------- General Query Info: [[
Repository: ssi, Subject Area: 06 - Federated Sources, Presentation: D - Sample Federated
]]
[2016-05-27T14:28:54.800+00:00] [OBIS] [TRACE:3] [] [] [ecid: aa89eaf6-8094-45b1-8c59-ea46b2a1e14e-000092ae,0:1:9:3] [sik: ssi] [tid: 996f4700] [messageid: USER-2] [requestid:
af740029] [sessionid: af740000] [username: weblogic] -------------------- Logical Request (before navigation): [[
RqList [1]
0 as c1 GB,
D0 Time.T05 Per Name Year as c2 GB,
1a- Revenue (Orcl Db Tables Only):[DAggr(F0 Revenue Base Measures.1a- Revenue (Orcl Db Tables Only) by [ D0 Time.T05 Per Name Year] )] as c3 GB,
1b- Revenue (Essbase Cube Only):[DAggr(F0 Revenue Base Measures.1b- Revenue (Essbase Cube Only) by [ D0 Time.T05 Per Name Year] )] as c4 GB
OrderBy: c2 asc NULLS LAST
![Page 51: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/51.jpg)
Multi-pass / sub-request
www.dimensionality.ch @Nephentur #obihackers | freenode
RqBreakFilter <<390156>>[2] [for database 0:0,0] /* FETCH FIRST 5000001 ROWS ONLY */
RqList <<390157>> [for database 0:0,0]
0 as c1 GB [for database 0:0,1],
D901.c5 as c2 GB [for database 0:0,0],
D901.c1 as c3 GB [for database 3023:28:01 - Sample App Data (ORCL),78],
D901.c2 as c4 GB [for database 0:0,0]
Child Nodes (RqJoinSpec): <<390167>> [for database 0:0,0]
RqJoinNode <<390168>> []
(
RqList <<390171>> [for database 0:0,0]
D1.c1 as c1 GB [for database 3023:28:01 - Sample App Data (ORCL),78],
D2.c1 as c2 GB [for database 0:0,0],
coalesce( D1.c2 , D2.c2) as c5 GB [for database 0:0,0]
Child Nodes (RqJoinSpec): <<390181>> [for database 0:0,0]
RqJoinNode <<390182>> []
(
RqList <<390186>> [for database 3023:28:01 - Sample App Data (ORCL),78]
sum(F10 Billed Rev.Revenue by [ D02 Time Month Grain.Per_Name_Year] ) as c1 GB [for database 3023:28:01 - Sample App Data (ORCL),78],
D02 Time Month Grain.Per_Name_Year as c2 GB [for database 3023:28:01 - Sample App Data (ORCL),78]
Child Nodes (RqJoinSpec): <<390195>> [for database 3023:28:01 - Sample App Data (ORCL),78]
RqJoinNode <<390196>> [(InNode:<<390196>>) (OutNode:<<390199>>) ]
SAMP_TIME_MTH_D AS D02 Time Month Grain
RqJoinNode <<390199>> [(InNode:<<390196>>) (OutNode:<<390199>>) ]
SAMP_REVENUE_F AS F10 Billed Rev
DetailFilter: F10 Billed Rev.Bill_Mth_Key = D02 Time Month Grain.Mth_Key [for database 3023:28:01 - Sample App Data (ORCL),78]
GroupBy: [ D02 Time Month Grain.Per_Name_Year] [for database 3023:28:01 - Sample App Data (ORCL),78]
OrderBy: c2 asc [for database 3023:28:01 - Sample App Data (ORCL),78]
) as D1 FullOuterStitchJoin <<390183>> On D1.c2 = D2.c2; actual join vectors: [ 1 ] = [ 1 ]
(
RqList <<390215>> [for database 0:0,0] distinct
D1.c1 as c1 [for database 0:0,0],
D1.c2 as c2 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<390221>> [for database 0:0,0]
RqJoinNode <<390222>> []
(
RqList <<390225>> [for database 3023:10:03 - Essbase Sample E1 Flat,84]
sum(Sample Flattened.Gross Revenue by [ Sample Flattened.Years] ) as c1 GB [for database 3023:10:03 - Essbase Sample E1 Flat,84],
Sample Flattened.Years as c2 GB [for database 3023:10:03 - Essbase Sample E1 Flat,84]
Child Nodes (RqJoinSpec): <<390234>> [for database 3023:10:03 - Essbase Sample E1 Flat,84]
RqJoinNode <<390235>> []
Sample T12016
GroupBy: [ Sample Flattened.Years] [for database 3023:10:03 - Essbase Sample E1 Flat,84]
) as D1
OrderBy: c2 asc [for database 0:0,0]
) as D2
OrderBy: c5 asc NULLS LAST [for database 0:0,0]
) as D901
![Page 52: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/52.jpg)
Multi-pass / sub-request
www.dimensionality.ch @Nephentur #obihackers | freenode
RqBreakFilter <<390156>>[2] [for database 0:0,0] /* FETCH FIRST 5000001 ROWS ONLY */
RqList <<390157>> [for database 0:0,0]
0 as c1 GB [for database 0:0,1],
D901.c5 as c2 GB [for database 0:0,0],
D901.c1 as c3 GB [for database 3023:28:01 - Sample App Data (ORCL),78],
D901.c2 as c4 GB [for database 0:0,0]
Child Nodes (RqJoinSpec): <<390167>> [for database 0:0,0]
RqJoinNode <<390168>> []
(
RqList <<390171>> [for database 0:0,0]
D1.c1 as c1 GB [for database 3023:28:01 - Sample App Data (ORCL),78],
D2.c1 as c2 GB [for database 0:0,0],
coalesce( D1.c2 , D2.c2) as c5 GB [for database 0:0,0]
Child Nodes (RqJoinSpec): <<390181>> [for database 0:0,0]
RqJoinNode <<390182>> []
(
RqList <<390186>> [for database 3023:28:01 - Sample App Data (ORCL),78]
sum(F10 Billed Rev.Revenue by [ D02 Time Month Grain.Per_Name_Year] ) as c1 GB [for database 3023:28:01 - Sample App Data (ORCL),78],
D02 Time Month Grain.Per_Name_Year as c2 GB [for database 3023:28:01 - Sample App Data (ORCL),78]
Child Nodes (RqJoinSpec): <<390195>> [for database 3023:28:01 - Sample App Data (ORCL),78]
RqJoinNode <<390196>> [(InNode:<<390196>>) (OutNode:<<390199>>) ]
SAMP_TIME_MTH_D AS D02 Time Month Grain
RqJoinNode <<390199>> [(InNode:<<390196>>) (OutNode:<<390199>>) ]
SAMP_REVENUE_F AS F10 Billed Rev
DetailFilter: F10 Billed Rev.Bill_Mth_Key = D02 Time Month Grain.Mth_Key [for database 3023:28:01 - Sample App Data (ORCL),78]
GroupBy: [ D02 Time Month Grain.Per_Name_Year] [for database 3023:28:01 - Sample App Data (ORCL),78]
OrderBy: c2 asc [for database 3023:28:01 - Sample App Data (ORCL),78]
) as D1 FullOuterStitchJoin <<390183>> On D1.c2 = D2.c2; actual join vectors: [ 1 ] = [ 1 ]
(
RqList <<390215>> [for database 0:0,0] distinct
D1.c1 as c1 [for database 0:0,0],
D1.c2 as c2 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<390221>> [for database 0:0,0]
RqJoinNode <<390222>> []
(
RqList <<390225>> [for database 3023:10:03 - Essbase Sample E1 Flat,84]
sum(Sample Flattened.Gross Revenue by [ Sample Flattened.Years] ) as c1 GB [for database 3023:10:03 - Essbase Sample E1 Flat,84],
Sample Flattened.Years as c2 GB [for database 3023:10:03 - Essbase Sample E1 Flat,84]
Child Nodes (RqJoinSpec): <<390234>> [for database 3023:10:03 - Essbase Sample E1 Flat,84]
RqJoinNode <<390235>> []
Sample T12016
GroupBy: [ Sample Flattened.Years] [for database 3023:10:03 - Essbase Sample E1 Flat,84]
) as D1
OrderBy: c2 asc [for database 0:0,0]
) as D2
OrderBy: c5 asc NULLS LAST [for database 0:0,0]
) as D901
RqBreakFilter <<390156>>[2] [for database 0:0,0] /* FETCH FIRST 5000001 ROWS ONLY */
(
(
RqList <<390186>> [for database 3023:28:01 - Sample App Data (ORCL),78]
sum(F10 Billed Rev.Revenue by [ D02 Time Month Grain.Per_Name_Year] ) as c1 GB [for database 3023:28:01 - Sample App Data (ORCL),78],
D02 Time Month Grain.Per_Name_Year as c2 GB [for database 3023:28:01 - Sample App Data (ORCL),78]
Child Nodes (RqJoinSpec): <<390195>> [for database 3023:28:01 - Sample App Data (ORCL),78]
RqJoinNode <<390196>> [(InNode:<<390196>>) (OutNode:<<390199>>) ]
SAMP_TIME_MTH_D AS D02 Time Month Grain
RqJoinNode <<390199>> [(InNode:<<390196>>) (OutNode:<<390199>>) ]
SAMP_REVENUE_F AS F10 Billed Rev
DetailFilter: F10 Billed Rev.Bill_Mth_Key = D02 Time Month Grain.Mth_Key [for database 3023:28:01 - Sample App Data (ORCL),78]
GroupBy: [ D02 Time Month Grain.Per_Name_Year] [for database 3023:28:01 - Sample App Data (ORCL),78]
OrderBy: c2 asc [for database 3023:28:01 - Sample App Data (ORCL),78]
) as D1 FullOuterStitchJoin <<390183>> On D1.c2 = D2.c2; actual join vectors: [ 1 ] = [ 1 ]
(
RqList <<390215>> [for database 0:0,0] distinct
D1.c1 as c1 [for database 0:0,0],
D1.c2 as c2 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<390221>> [for database:0,0]
RqJoinNode <<390222>> []
(
RqList <<390225>> [for database 3023:10:03 - Essbase Sample E1 Flat,84]
sum(Sample Flattened.Gross Revenue by [ Sample Flattened.Years] ) as c1 GB [for database 3023:10:03 - Essbase Sample E1 Flat,84],
Sample Flattened.Years as c2 GB [for database 3023:10:03 - Essbase Sample E1 Flat,84]
Child Nodes (RqJoinSpec): <<390234>> [for database 3023:10:03 - Essbase Sample E1 Flat,84]
RqJoinNode <<390235>> []
Sample T12016
GroupBy: [ Sample Flattened.Years] [for database 3023:10:03 - Essbase Sample E1 Flat,84]
) as D1
) as D2
) as D901
![Page 53: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/53.jpg)
Multi-pass / sub-request
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 54: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/54.jpg)
Multi-pass / sub-request
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 55: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/55.jpg)
Multi-pass / sub-request
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 56: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/56.jpg)
Multi-pass / sub-request
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 57: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/57.jpg)
Multi-pass / sub-request
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 58: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/58.jpg)
Multi-pass / sub-request
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 59: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/59.jpg)
Multi-pass / sub-request
www.dimensionality.ch @Nephentur #obihackers | freenode
[2016-05-28T15:54:26.741+00:00] [OBIS] [TRACE:3] [] [] [ecid: 86cb6a39-9f56-4305-a0e6-dd95618de970-00000be7,0:1:1:5] [sik: ssi] [tid: de7b700] [messageid: USER-18] [requestid: f7340057] [sessionid: f7340000] [username: weblogic] -------------------- Sending query to database named 01 - Sample App Data (ORCL) (id: <<128861>>), connection pool named Sample Relational Connection, logical request hash c0e3e3eb, physical request hash f6e07fd4: [[ select sum(T432.Revenue_Usd) as c1,
T795.PER_NAME_YEAR as c2
from
BISAMPLE.SAMP_TIME_QTR_D T795 /* D03 Time Quarter Grain */ ,
BISAMPLE.SAMP_REVENUE_CURR_F T432 /* F19 Rev. (Converted) */
where ( T432.Bill_Qtr_Key = T795.QTR_KEY )
group by T795.PER_NAME_YEAR
order by c2
]]
[2016-05-28T15:54:26.742+00:00] [OBIS] [TRACE:3] [] [] [ecid: 86cb6a39-9f56-4305-a0e6-dd95618de970-
00000be7,0:1:1:5] [sik: ssi] [tid: de7b700] [messageid: USER-18] [requestid: f7340057] [sessionid: f7340000] [username:
weblogic] -------------------- Sending query to database named 01 - Sample App Data (ORCL) (id: <<128903>>), connection
pool named Sample Relational Connection, logical request hash c0e3e3eb, physical request hash 343379a9: [[
select sum(T216.Revenue) as c1,
T795.PER_NAME_YEAR as c2
from
BISAMPLE.SAMP_TIME_QTR_D T795 /* D03 Time Quarter Grain */ ,
BISAMPLE.SAMP_REVENUE_FA2 T216 /* F21 Rev. (Aggregate 2) */
where ( T216.Bill_Qtr_Key = T795.QTR_KEY )
group by T795.PER_NAME_YEAR
order by c2
![Page 60: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/60.jpg)
Fragmentation
www.dimensionality.ch @Nephentur #obihackers | freenode
• Are fragmented logical table sources
being used?
![Page 61: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/61.jpg)
Fragmentation
www.dimensionality.ch @Nephentur #obihackers | freenode
• Are fragmented logical table sources
being used?
[...]
RqJoinNode <<406134>> []
((
RqList <<406138>> [for database 3023:16:Y01 - Custom Demo Datasets,78]
WEATHER_HAIL.STATE as c2 [for database 3023:16:Y01 - Custom Demo Datasets,78],
'Hail' as c3 [for database 3023:16:Y01 - Custom Demo Datasets,78],
WEATHER_HAIL.ID as c4 [for database 3023:16:Y01 - Custom Demo Datasets,78]
Child Nodes (RqJoinSpec): <<406146>> [for database 3023:16:Y01 - Custom Demo Datasets,78]
RqJoinNode <<406147>> []
WEATHER_HAIL T403
RqUnion All <<406137>> [for database 3023:16:Y01 - Custom Demo Datasets,78]
RqList <<406150>> [for database 3023:16:Y01 - Custom Demo Datasets,78]
WEATHER_TORNADOES.STATE as c2 [for database 3023:16:Y01 - Custom Demo Datasets,78],
'Tornado' as c3 [for database 3023:16:Y01 - Custom Demo Datasets,78],
WEATHER_TORNADOES.ID as c4 [for database 3023:16:Y01 - Custom Demo Datasets,78]
Child Nodes (RqJoinSpec): <<406158>> [for database 3023:16:Y01 - Custom Demo Datasets,78]
RqJoinNode <<406159>> []
WEATHER_TORNADOES T525
RqUnion All <<406137>> [for database 3023:16:Y01 - Custom Demo Datasets,78]
RqList <<406162>> [for database 3023:16:Y01 - Custom Demo Datasets,78]
WEATHER_WIND.STATE as c2 [for database 3023:16:Y01 - Custom Demo Datasets,78],
'Wind' as c3 [for database 3023:16:Y01 - Custom Demo Datasets,78],
WEATHER_WIND.ID as c4 [for database 3023:16:Y01 - Custom Demo Datasets,78]
Child Nodes (RqJoinSpec): <<406170>> [for database 3023:16:Y01 - Custom Demo Datasets,78]
RqJoinNode <<406171>> []
WEATHER_WIND T375)
) as D6
[...]
![Page 62: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/62.jpg)
Fragmentation
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 63: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/63.jpg)
Fragmentation
www.dimensionality.ch @Nephentur #obihackers | freenode
• Are fragmented logical table sources
being used?
![Page 64: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/64.jpg)
Aggregate Navigator
www.dimensionality.ch @Nephentur #obihackers | freenode
• Which LTS contains data on a level of
aggregation to fulfill the query with the
least amount of effort?
• Hierarchy logical level # of elements
• LTS priority group
• LTS order
![Page 65: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/65.jpg)
Aggregate Navigator
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 66: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/66.jpg)
Aggregate Navigator
www.dimensionality.ch @Nephentur #obihackers | freenode
WITH SAWITH0 AS (select sum(T216.Revenue) as c1,T795.PER_NAME_YEAR as c2from BISAMPLE.SAMP_TIME_QTR_D T795 /* D03 Time Quarter Grain */ ,BISAMPLE.SAMP_TIME_QTR_D T795 /* D03 Time Quarter Grain */ ,BISAMPLE.SAMP_TIME_QTR_D T795 /* D03 Time Quarter Grain */ ,BISAMPLE.SAMP_TIME_QTR_D T795 /* D03 Time Quarter Grain */ ,BISAMPLE.SAMP_REVENUE_FA2 T216 /* F21 Rev. (Aggregate 2) */ where ( T216.Bill_Qtr_Key = T795.QTR_KEY ) group by T795.PER_NAME_YEAR)select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select 0 as c1,D1.c2 as c2,D1.c1 as c3from SAWITH0 D1order by c2 ) D1 where rownum <= 5000001
![Page 67: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/67.jpg)
Aggregate Navigator
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 68: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/68.jpg)
Aggregate Navigator
www.dimensionality.ch @Nephentur #obihackers | freenode
WITH SAWITH0 AS (select sum(T418.Revenue) as c1,T42.Calendar_Date as c2,T42.Per_Name_Year as c3from BISAMPLE.SAMP_TIME_DAY_D T42 /* D01 Time Day Grain */ ,BISAMPLE.SAMP_TIME_DAY_D T42 /* D01 Time Day Grain */ ,BISAMPLE.SAMP_TIME_DAY_D T42 /* D01 Time Day Grain */ ,BISAMPLE.SAMP_TIME_DAY_D T42 /* D01 Time Day Grain */ ,BISAMPLE.SAMP_REVENUE_F T418 /* F10 Billed Rev */ where ( T42.Calendar_Date = T418.Bill_Day_Dt and T42.Per_Name_Year = '2012' ) group by T42.Calendar_Date, T42.Per_Name_Year)select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select 0 as c1,D1.c2 as c2,D1.c3 as c3,D1.c1 as c4from SAWITH0 D1order by c3, c2 ) D1 where rownum <= 5000001
![Page 69: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/69.jpg)
Optimized Query Rewrites
www.dimensionality.ch @Nephentur #obihackers | freenode
• Writes the effective query sent against a
source
• Source type
• Source features
• Native function shipping driven by these
configurations (and version)
![Page 70: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/70.jpg)
Optimized Query Rewrites
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 71: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/71.jpg)
Optimized Query Rewrites
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 72: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/72.jpg)
Optimized Query Rewrites
www.dimensionality.ch @Nephentur #obihackers | freenode
• Results are retrieved from sources
• Combined and post-calculated as needed
• Written into the cache
• Passed to the initially calling interface
![Page 73: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/73.jpg)
Outline
• Why this presentation
• Overview / What the packaging says
• VA / DV / DVD / BICS / ASCS
• The actual day in the life of a query
• Query execution details
• Summary
www.dimensionality.ch @Nephentur #obihackers | freenode
![Page 74: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/74.jpg)
Summary
www.dimensionality.ch @Nephentur #obihackers | freenode
1. Know the flow
This is where
very few
magic should
happen
This is where
THE magic
happens
We don’t
really care
about this part
This is where
the actual
workload
should happen
Browser,
SmartView,
other toolsBI Server
WLS
Managed
Server
Presentation
Server
Sources
Browser,
SmartView,
other toolsBI Server
WLS
Managed
Server
Presentation
Server
![Page 75: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/75.jpg)
Summary
2. Understand the magic
![Page 76: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/76.jpg)
Summary
3. You’ll be fine!
![Page 77: Back2 basics - A Day In The Life Of An Oracle Analytics Query](https://reader031.vdocuments.us/reader031/viewer/2022021500/58758a9c1a28ab901c8b5981/html5/thumbnails/77.jpg)
www.dimensionality.ch @Nephentur #obihackers | freenode