8:30 AM - General Session: Optimizer Ying and Yang - Tom Kyte - @oracleAskTom
Tom started with a great tip following up from his session yesterday, and that is don't program to fail! Great tip! I know it sounds odd, but trust me he made it work!
This leads into being able to abuse functions which leads to cardinality estimation and CPU usage issues.
This really reminded me of what I saw yesterday about working with functions.
ALTER SESSION SET PLSQL_WARNINGS = 'error:all' - allows the Optimizer to warns you about "suboptimal" code such as "when others null" and it has been there since 10g but doesn't have a lot of adoption from what Tom sees.
Another tip is to think about NLS settings on how your data/programming may turn out if it gets changed.
What happens when partition elimination is eliminated?
A reminder about what happens when you have bad stats, which is something I've seen before, temp tables being empty or full when gathered. Choices are maybe dynamic sampling or filling with representative data. A new feature in 12c is "session private statistics" that can lead to excessive hard parsing because everybody is doing the same thing.
He shared DBMS_STATS.COPY_TABLE_STATS() which makes me think about his example about gathering at 9AM vs 3PM with my next purging project.
When do you have to gather stats more often?
Is 10% right?
Underscored that you need to have knowledge of data pattern and data in your system.
Hints are often a bad idea, unless it gives the optimizer good information.
Valid hints can be ignored!
If you can hint it, you can baseline it.
Most important stats are NUM_ROWS and NDV!
AUTO_SAMPLE_SIZE is another option.
X is bad - is something always bad? or good?
Live tuning by a volunteer!
Idea of "key preserved" helps to eliminate table joins.
Count could change after retrieval! Great reason to not code that in as a logic check!
10053 trace shows the details from his live tuning, so another tool in the toolbox.
He made the comment that if we think that he and Maria Colgan (@db_inmemory) are really unique, the actual Optimizer developers are really unique individuals!
Fake values = fear of nulls
Using the year 9999 makes the system think it has a lot of data so throws the Optimizer off.
Great example used of license plates people picked of NO TAGS and XXXXXXX which caused problems for them because the users/system had those "reserved" for unidentified cars!
First off, bravo! She kept her cool and was able to rush through her presentation because the hotel had a problem partitioning the two rooms and she was left with only 30 minutes of time after a 30 minute delay.
The theme of her presentation was staying on the right road, and not letting ASH and AWR take you down a rabbit hole of tuning. How do you prevent that? Only tune for time, and nothing else.
Great reminder that one shouldn't assume, as data must be gathered to prove any point.
ASH and AWR came out due to needing "always on" performance metrics, but it does require a license from Oracle to run.
I really liked how she was running ASH from 12c EM, and giving us very relevant hints such as how the tool shows HTML output which is useful for explaining or proving things to executives.
Kellyn introduced a new feature called Compare ADDM which uses 2 AWR snapshots to tell you what the differences are for several key metrics.
12c also shows you regression SQL as well for what has decreased in performance.
Resource Comparisons is another feature that compares memory and I/O.
She believes (strongly) that DBAs should share tools with development, and that is a great idea that needs to be shared more!
SQL Monitor in 12c also shows Exadata cell offloading!
Search SQL is another new feature in 12c that lets you find out what has happened to that SQL previously in your system.
Another tool in the toolbox is: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(sql_id))
A few keys from the ASH table is that user_id is same as in DBA_USERS and WAIT_TIME is on previous event.
The best part of the session for me were the scripts that Kellyn shared with us! There's easily 5 or 6 that I'm bouncing around in my head for how we can build it into our proactive monitoring platform! This includes a query that originated with Tyler Muth from Oracle and has been tweaked for several years.
Ending her presentation with the idea that there are RAC global ASH reports got me excited for where we can take this in the future.
CONNECT BY refresher was good for me, because I really don't use it or see it used a whole lot.
Bug 17834663 / MOS note ID 17834663.8 for the associated bug
Two options:
With Fibonacci
Factorial
What's the difference between SYS_CONNECT_BY_PATH vs CONNECT_BY_ROOT? Not much, but it's there.
I think it is concerning when a speaker says "this is hard to understand, and I've spent a lot of time on this but it's not second nature to me yet". So…why am I here trying to learn if you say it's still confusing? Probably why I didn't get a whole lot out of it, (either that or I was focused on what I wanted to say in my presentation next) but I'm going to try and use this stuff when I get home anyways to try something new…when I understand it better.
Sure, I wanted to go see Tanel Poder (@TanelPoder) present "Troubleshooting a Yet Another Complex Performance Issue" too, but the show had to go on and I appreciate everybody that attended my session over his. Especially since he got to have the second room opened because of the delay Kellyn endured, and we were banished to the basement. :} I did rush through the presentation a bit, since I was thrown off some from the changes, and I think I hit every high point that I wanted but if you want a little more in-depth you can visit my Whitepaper that Hotsos has on their portal or the copy Oracle has hosted.
After my presentation I didn't get to attend the next sessions that I had planned on, so I'll cover these in a future blog post after I get access to the audio! Sorry about that folks!
This was a difficult choice for me, but after reading the abstract for Luca Canali's "A Latency Picture Is Worth a Thousand Storage Metrics" I realized I had blogged about it almost a year ago!
No comments:
Post a Comment