how aso works - ntuplentuple.net/images/kscope14_steve_and_dan_go_to_k... · memory – bso –...
TRANSCRIPT
![Page 1: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/1.jpg)
Kumar and Dan
Go to Kscope
How ASO Works
(Based on Your Questions)
Seattle, WA Jun 25, 2014
Steve
And Hybrid
![Page 2: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/2.jpg)
Please envision the standard safe
harbor statement from Oracle here
to protect Steve Job
![Page 3: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/3.jpg)
• A Quick Review of ASO
• A Bit About Memory Management
• Speculation About How Hybrid
Works
• Your Questions
Today’s Session
![Page 4: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/4.jpg)
A Quick Review of ASO
A Bit About Memory Management
Speculation About How Hybrid Works
Your Questions
Today’s Session
![Page 5: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/5.jpg)
Really? You’ve looked inside the computer
and seen an ASO cube?
Who Has Seen an ASO Cube?
![Page 6: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/6.jpg)
Still made and used for field identification of minerals.
Similar guides are available for Trees and Birds.
An ASO Cube You Can Hold in Your Hand
![Page 7: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/7.jpg)
Holes represent metadata presence; notches represent absence.
Note notches for all levels (67510, Abbyville, Kansas and Central).
Query for Check and Adult = ((Check) AND Adult)
Card Used With Sorting-Needle
![Page 8: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/8.jpg)
Cards with holes are pulled up and used for next part of query; cards with notches fall out. Check and adult = ((Check) AND (Adult))
Data Is Queried Using a “Sorting-Needle”
![Page 9: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/9.jpg)
Patented Multi-Processor Sorting-Needle
![Page 10: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/10.jpg)
“You can never be too rich or too thin.”
Wallis Simpson
Disk Speed, CPU Speed and Ram
![Page 11: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/11.jpg)
Both Unix and Windows cache files in memory
Separate (and somewhat redundant) vs.
familiar Data and Index caches
During data load, both input file and output pag,
ind or dat files are cached
Called memory mapped IO
Seen in Windows Resource Manager or on
UNIX in top or nmon:
Memory Management
![Page 12: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/12.jpg)
Memory - Resource Manager
Green is program memory including explicit caches
Dark blue is memory mapped IO
Light blue is memory not being used
![Page 13: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/13.jpg)
top
Memory – Unix: top or nmon
nmon with m (memory) option
![Page 14: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/14.jpg)
Memory – Just for Fun Load = Physical Drive 125 eff threads
Load = SSD Drive 125 eff threads
![Page 15: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/15.jpg)
Memory & CPU – Just for Fun
CPU % = 8,069.7 Cached = 200GB
![Page 16: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/16.jpg)
To see which files are being cached, look at the File
Summary tab of RamMap:
Memory - RamMap
RamMap is part of Microsoft’s SysInternals, and is available for free at:
http://technet.microsoft.com/en-us/sysinternals
![Page 17: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/17.jpg)
… take longer to run sometimes?
Very often, it’s memory-mapped IO
● A copied input file may still be in RAM
● After a calc/query, the dat/pag file is still in RAM
Can be seen in Resource Manager and RamMap
when a file is unzipped and loaded into a cube:
Why Does My Query/Load/Calc…
![Page 18: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/18.jpg)
After Unzip and before load:
Memory – BSO – Unzip and Load
Both zipped and unzipped files remain in
memory
● Unzipped not needed, but remains in memory
![Page 19: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/19.jpg)
About ¼ way through load:
Memory – BSO – Unzip and Load
The .pag files remain in memory even after
they’re written
● Great, for augmentation or querying later
● Might be in data cache if there’s room
![Page 20: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/20.jpg)
About ½ way through load:
Memory – BSO – Unzip and Load
Still room for all .pag files created so far
and both zipped and unzipped input files
![Page 21: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/21.jpg)
About ¾ way through load:
Memory – BSO – Unzip and Load
No more free space
● Unzipped file no longer wholly in memory
● Previous slides showed it at 1,013,624 K
● We didn’t need it again
![Page 22: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/22.jpg)
After load:
Memory – BSO – Unzip and Load
None of unzipped file in memory
Part of ess00001.pag has been forced out
of memory
Running Calc All now will be faster than
after application restart
● Almost all .pag files are in memory
![Page 23: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/23.jpg)
Unix: echo 3 > /proc/sys/vm/drop_caches
Windows: EmptyStandbyList.exe
● From RamMap directory, or
● Interactively, using RamMap:
Memory – How to Clean out Mapped IO
Before: After:
![Page 24: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/24.jpg)
Load ran faster because unzipped was in
memory: 192 seconds
Memory – Clear and Load
Proved by clearing memory mapped IO
rerunning load: 233 seconds 21% longer
41 seconds may not seem long
● SSD’s used – physical would be slower
![Page 25: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/25.jpg)
RamMap at same points in run:
Memory – Clear and Load
![Page 26: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/26.jpg)
RamMap at same points in run (continued):
Memory – Clear and Load
![Page 27: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/27.jpg)
Query Analyzed
Subset of Blocks are Read
Data is Aggregated on-the-fly to a View that will
satisfy the query
Resulting .dat file is written to ASO Cache
and/or disk
Query is processed
.dat file logically discarded
Speculation About How Hybrid Works
![Page 28: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/28.jpg)
ASO Cache size is more critical
● “Pure” ASO query performance not terribly sensitive
to ASO cache size
● Hybrid uses ASO cache and temp tablespace to
create a temporary dat file for every query
● Physical size on disk in hybrid\temp of ess0001.dat
represents the “High-Water” mark of ASO cache size
● Use “High-Water” mark for decisions on cache size
● As always, use the fastest disk (SSD) you have for
the temp tablespace
Speculation About How Hybrid Works
![Page 29: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/29.jpg)
Finally, Your Questions
Seattle, WA Jun 25, 2014
![Page 30: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/30.jpg)
How much of Dan’s
Speculations about Hybrid
were just plain B(&*#@^ ?
Tweeted At: 10 am June 25th 2010
From Twitter
![Page 31: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/31.jpg)
Tell me more
● Can I turn it off?
● Can I change where it goes?
● Can I create the different “new” members on-the-fly?
Renegade Members
![Page 32: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/32.jpg)
How do my queries actually get processed
I want to learn more about how much various
design decisions cost me
Explain Plan
![Page 33: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/33.jpg)
Which Scales better Hybrid or ASO?
![Page 34: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/34.jpg)
I want them to “survive” restructures when
possible
Can I edit them logically instead of by those
weird numbers?
I want help editing my Agg Scripts
![Page 35: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/35.jpg)
Today
Next Release (PS4? – what is a PS4 anyway?)
When does Hybrid make Sense vs ASO
![Page 36: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/36.jpg)
When do I do witch (sic)
ForceCalc vs Fix Parallel
![Page 37: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/37.jpg)
When will I be able to change them without
restarting Essbase?
ASO config settings
![Page 38: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/38.jpg)
When will 64bit ODBC Interface support Data
Export Batch Insert Mode?
What does this question mean?
Why should I care?
64bit ODBC
![Page 39: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/39.jpg)
Such as:
● Actuals updated thru mm/dd/yyy
● Why did you submit that stupid query?
● Have you actually used this data you “had to have”
last week?
I want to send my user’s messages
![Page 40: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/40.jpg)
Restructure Warnings – Long vs Short and
maybe time to complete?
Progress msg or estimate of how much longer
query will take?
Estimates and Warnings
![Page 41: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/41.jpg)
Will I still be able to buy it in the future or only
as part of fill-in-the blank
Where Forth will Essbase Go?
![Page 42: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/42.jpg)
![Page 43: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/43.jpg)
![Page 44: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/44.jpg)
![Page 45: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/45.jpg)
![Page 46: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/46.jpg)
![Page 47: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/47.jpg)
![Page 48: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/48.jpg)
![Page 49: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/49.jpg)
![Page 50: How ASO Works - nTuplentuple.net/images/KScope14_Steve_and_Dan_Go_to_K... · Memory – BSO – Unzip and Load Both zipped and unzipped files remain in memory Unzipped not needed,](https://reader036.vdocuments.us/reader036/viewer/2022062611/6127d5c8d6a25e217c1dc022/html5/thumbnails/50.jpg)