The day started early so I could get ready and attend breakfast to do some networking, and then it was off to the main room for the conference to start!
8:30 AM - Keynote Address: An Introduction to the New Oracle Database In-Memory Option - Maria Colgan - @db_inmemory
First off, Maria told us that she can't be held accountable for anything she tells us as this is not PROD code but after that she said that 12.1.0.2 should be coming this calendar year! She also made us re-think what ad-hoc report running really is, especially with objects such as Materialized Views existing sometimes to help speed reporting up.
What's the secret sauce of In-Memory? Both row and columnar formats are in memory at the same time and the Optimizer is smart enough to go to the correct data store to make the queries better.
The new pool in the SGA for this is called "In-Memory Area" and cannot be changed from the size determined at creation.
How do you put objects into memory? ALTER TABLE foo INMEMORY
The new memory-optimized compression format does a 90 degree pivot of your data.
The table V$IM_SEGMENTS shows what is in-memory.
At this point Maria was prepared to do a demo, but it didn't work which was unexpected and she showed her professionalism by having prepared to talk about the material anyways.
Based on the data available for the OpenWorld 2013, 12c was 100x faster with the column store!
SIMD vector processing vs. Tom Kyte's row by row, slow by slow.
This is a great example of how closer talks within Oracle, and between teams, is getting them better results. What teams should talk or work together for you?
Another new table: V$MYSTAT
This uses bloom filters which wasn't new for Exadata, but existed in 10g.
The Optimizer is sophisticated enough to apply multiple bloom filters so chances of the query going to disk are greatly reduced because of the data reduction applied originally.
A new vector group by shows up to replace the old hash group by.
This solution gives us extreme capacity and cost effectiveness while not being limited by memory.
Neither is this limited by RAC, as each instance has their own caches for in-memory.
In-Memory uses direct-to-wire InfiniBand speeds for messaging in Exadata systems bypasses all of the OS speeds/interfaces.
If tables are kept in all RAC nodes, the column stores will be kept in sync across the cluster.
This is deeply embedded in the DB, and is transparent to the application as all applications are compatible because the on disk format has not changed.
9:45 AM - General Session: SQL Gone Bad - But Plan not Changed? - Kerry Osborne - @KerryOracleGuy
A reminder about the Exadata E4 conference, sign up at www.Enkitec.com/e4!
The presentation is summed up by "good news, bad news" and "that which doesn't kill us makes us stronger".
This is keyed on the fact that plan hash values (PHV) can change instead of staying static.
Kerry reminds us that plan stability is a hard thing to deal with, first the CBO was introduced, then hints, and then outlines.
There are several different "automagic troublemakers" so you need to keep your eyes open.
What is missing from the PHV calculation?
Live demo!
AWR doesn't capture predicates, neither does statspack or SQL monitor.
SQL Plan Management does work in 12c to capture the plan, but older versions don't work to capture it!
Nobody in the room is using 12c in production…or at least that they would admit to! I'm concerned!
First time an online question was taken, and even though it didn't get answered, it was awesome to get that out there!
The presentation may have been short, but I thought it was really valuable because it helped to break some ideas people may have had and introduced the idea of not seeing the same thing everything the same way.
11:00 AM - Real-Time SQL Statement Monitoring - Tim Gorman - @timothyjgorman
Tim was a victim of the rooms being broken down, so he lost probably a good 5-10 minutes but he kept it together and ended up knocking it out of the park! Seeing gaps with ASH and DBMS_XPLAN, which they could each fill for each other, he created a package called ASH_XPLAN!
A good reminder that indexes are disabled on function usage.
An introduction (for me at least) that INDEX SKIP SCAN not a good thing in general just as going REMOTE over database links are typically not good.
This was a lot like Kerry's presentation about bad instead of good, but these are the things we need to face and learn about instead of running from.
Tim also indicated that V$SQL_MONITOR is added to automatically when more than 5 seconds of time has elapsed for the current SQL.
He shared output from SQL tracing, but time isn't included and that's what you should be tuning for.
Another new table: V$SQL_PLAN_MONITOR has a lot of stuff in it including I/O info.
What is the workarea and why does it spill?
Hidden ASH parameters _ash_disk_filter_ratio and _ash_eflush_trigger control how ASH acts!
Tim suggests a 45 day retention of AWR due to monthly business cycle.
I thought it was also brilliant to wrap up his references to a phone book (dated) and Olympics (current) in the same breath.
1:00 PM - Three Approaches to Shared Pool Monitoring - John Hurley - @GrumpyOldDBA
A reminder for the GLOC conference, sign up at https://www.neooug.org/gloc/!
Some key questions that John asks us are what size should the shared pool be and how much is in the shared pool?
This stems from an ORA-04031 error crash resulting from ASM dying in his main database.
Chapters 2 and 4 from Tom Kyte's book and Jonathan Lewis's Oracle Core, chapter 7 are main references for him including most of what Tanel Poder puts up on his blog.
The shared pool was introduced with Version 7 as a metadata cache which was only 3.5MB!
Now the shared pool size has expanded from 9 -> 90 -> 900 -> 9000 MBs.
He gives us several different ways to look at SQL in your system with a good script!
An outline of the difference between ORA-04030 and ORA-04031 errors.
Great visuals to make his point!!
Sub sub pools? My head is getting dizzy!
These might not be new tables to everybody, but you should know about: V$SGA, V$SGAINFO and V$SGASTAT (ASM info here).
If you see "KGH: NO ACCESS" this is a bad thing.
Another new table: V$DB_OBJECT_CACHE
How to chew the shared pool up? There are many ways!
Who is using OSwatcher?
Another new new table: V$SQL_SHARED_CURSOR
There were many example SQL scripts which can be setup as alerts in my system!
MOS note 296377.1 for SQL statements with children.
ASM uses space in the shared pool, which is why it was able to throw an ORA-04031 error!
2:15 PM - Five Things You Might not Have Known about the Optimizer - Tom Kyte - @OracleAskTom
It's Tom Kyte. Come on, I HAVE to go see this one! I didn't get to because the room was full, and I didn't feel right on taking a seat from the paying attendees, so I went to see Julian Doncheff give his presentation "DBA Best Practices for Performance Tuning in a Pluggable World" to learn something new!
I really liked how he opened up the presentation with quizzing us by throwing oddities at us, that's a welcome change!
Bryn Llewellyn was in the audience talking about the difference between hint returns if it has the choice not to return an error, which was news to me.
Julian shared a query that had a value of 6 Exabytes, which is an Exabyte more than all the words spoken in the human language to date. I think the query is off by a few factors!
A recent study indicated that 20% of DBAs admit to doing nothing to address performance issues. The scary thought is that this is only the ones that admitted it! The same study indicated that the current ratio of instances a DBA manages is around 45:1. That's insane!
He indicates that it is not possible to upgrade 11g to a 12c CDB, as it has to be upgraded to 12c and then made a PDB.
I thought it was really interesting how CDB and PDB are changing the game, but in several instances Oracle is taking steps back from "support" aspects like 1 log file for up to 254 PDBs.
Did you know that statspack still exists even in this area?
In-Database Archiving - This is a new feature that essentially masks information from being returned.
Don't like the supportability of 12c? Blame the Commonwealth Bank of Australia! I guess they did the cost/benefit ratio on aggregating databases, and Oracle was sold!
3:30 PM - Compression and Performance in Oracle Database 12c - Gregg Christman with Oracle
Two compression presentations walk into the Thunderdome, but only one compression presentation can be attended!
First off, I thought it was interesting that Gregg came from a "Competitive Intelligence" group within Oracle that analyzed their competition. That sounds like a fun job! Anyways, his presentation went over many reasons for data growth and also that Oracle provides basic compression for free. Free! Well, only for bulk loads which is best suited for a Data Warehousing situation but at least it's better than nothing!
The RMAN/backup compression options really strike home for me right now because we've been fighting long running backups for a few weeks now.
Everything Gregg went over was existing prior to 12c, but Advanced Network Compression was created just for 12c and obviously is for network traffic.
He also talked about a Flashback Data Archive which seems odd since flashback data usually ages out within a day or two.
All of these options are 100% transparent to application just like In-Memory was Maria talked about earlier in the day.
There is another free product called Index Key Compression that is included with EE!
There is a tool called ANALYZE INDEX which mimics the different products to run against your system so you can see what benefits you will get.
Lastly, he went over Hybrid Columnar Compression (HCC) for Exadata.
4:45 PM - Evolution of Histograms in Oracle - Amit Poddar
This was difficult because George Srdanov was giving his presentation at the same time, but since his was "Extreme Performance without Extreme Hardware" and already have Exadata, I felt this would benefit me a lot more because histograms are something I'm very weak on. Amit went on to cover the following in detail:
Selectivity of predicates
Uniform and linear distributions
Zipfian distribution
Frequency histogram in 11g limited to 256 values, 2048(right?) in 12c
Top-N histogram
Equi-width histogram
Equi-height histogram
Hybrid histogram
A lot of this presentation went right over my head and it really wasn't what I thought it would be based on the abstract, as it was a deep dive into the theory behind histograms instead of anything I could take back home to apply in order to address performance issues with a script. Some of theory I've obviously seen or dealt with, but in particular I wanted to learn about how to determine what types of histograms work best in certain situations and I came away empty on that count.
No comments:
Post a Comment