I have recently been doing presentations about SQL tuning, but haven’t really written anything on the topic here. Therefore, I have decided to put together a series of posts over the next few weeks on the process I use for tuning SQL. I plan to cover:
– Viewing and interpreting the current execution plan for a query
– Using a visual approach to find an optimal execution plan
– Various methods that can be used to go from the current execution plan to the optimal one
– Review of some tools that can help with these tasks
I finally got around to getting a few pictures I took during my time at GLOC uploaded so I thought I would share.
First, the hotel that the conference had a discount for was the Hyatt Regency Cleveland at The Arcade. Now, typically I don’t care much about what hotel I stay in, but this one was awesome. The Arcade was one of the first indoor shopping malls in the US, opening in 1890. It really is an impressive building for being so old, and the Hyatt did a good job of integrating the hotel without taking away from the buildings appeal.
I also managed to get some pictures during the speakers dinner the night before the conference. Here I am with Chris Date:
And myself with Craig Shallahamer:
Finally a couple shots I took of the room as I was getting ready to present:
It ended up getting about 20 more people, so was a lot more crowded than I expected. Also, note Carlos Sierra and Abel Macias sitting at the front table in the 2nd picture. They were joined by Kyle Hailey before I got started as well. So that was enough to make me a little nervous, but the good news is that I knew if I said something incorrect, I had the right people there to correct me!
Again, a great event and thanks again to everyone at NEOOUG for making it such a success.
The conference was a great event. I learned about several new things, and now I have some homework to do to get further into the details. My favorite sessions were the 2 from Carlos Sierra on SQLT and Adaptive Cursor Sharing as well as Craig Shallahamer’s session on Child Cursors. It was great to hear exactly how child cursors and ACS works. I had looked at SQLT about 5 years ago, and amazed at how much has been added since. I will definitely be spending some time seeing what all it contains now, and will be posting what I find.
I had a great time catching up with people I haven’t seen in a while and making some new friends. Once again, the venue was awesome. I will definitely be back next year.
Arrived in Cleveland today for the Great Lakes Oracle Conference pre-conference workshops. I attended Craig Shallahamer‘s presentation on Oracle Performance. A lot of great information packed into 2 hours. More than anything, I like that from this quick presentation I was able to get a sense of how Craig does his performance troubleshooting. He clearly has a systematic methodology for finding the root cause of performance issues, and then works to understand why the issue is occurring. It appears to be a very scientific method approach.. observe, measure, hypothesize, experiment, repeat until you find the solution. Some of the things he presented on graphing performance metrics will be immediately applicable.. I just need to go figure out some of the math behind how it works 🙂
After the workshop, the NEOOUG was nice enough to have all the speakers attend Lola Bistro, the restaurant owned by Iron Chef Michael Symon). The food was delicious, and the company was even better. I got a chance to talk more with Craig and his wife, meet CJ Date, and have some great conversation with Lyson Ludvic, Dhan Patel, and Rich Schoustra. Makes me very much look forward to getting the presentations started tomorrow.
I have finally caught up on my backlog of blog posts I have been meaning to read, and am quite glad I did. One of the posts I ran across was by the Oracle Optimizer team about SQL Patching. This allows you to have Oracle apply a hint to a query without having to modify the query text. This is similar to what you would do with SQL Profiles or SQL Plan Management, however, where those specify the entire execution plan to use, SQL Patching allows you to just apply a hint and still give Oracle the flexibility to adapt to changes. Very cool feature, and doesn’t require any additional tuning packs or anything, just 11g Enterprise Edition.
Read more about this undocumented (but supported) procedure in the following 2 articles:
I have finished my presentations at DAY-O and think they went fairly well. I got a good amount of participation and even better questions. Their members do a good job talking with each other about new features or issues they are having, which is really what Users Groups should be about. I look forward to returning in the future, and thank their board for putting together a well run event. The presentations are available in to my presentations section. If you attended and have any feedback for how I can improve in the future, please share in the comments or via email.
I will be presenting twice at the Dayton Oracle Users Group on Thursday, April 25. My presentations will be on reading execution plans and SQL Tuning. I have never presented at Dayton before, so I look forward to meeting the people there and seeing what kind of group they have going. Check out the full meeting details or join their LinkedIn Group.
I am also very excited that I have been selected to present at the Great Lakes Oracle Conference in Cleveland again this year. I will be presenting my SQL Tuning presentation there as well. This conference was great last year, and looks significantly better this year. The NEOOUG is doing a great job at putting together the best conference in the Midwest… just check out the presentations! Also, Tom Kyte and CJ Date will be there giving the Keynote Addresses. I have never had the chance to see CJ Date in person, but have heard nothing but great things. The conference is May 14 and 15th with pre-conference workshops by Tom Kyte, Craig Shallahamer, and Joel Kallman on May 13. If you are interested in attending (and you should be), use the code “ATTENDGLOC” to get an additional $25 off.
If you happen to be at either one of these events, make sure you stop me and say hello!
I was reading Rene Antunez’s post about some things you can do with tools you use to help make you aware when you are in Production. The idea is that you want to do everything you can to prevent yourself from getting confused when you are working with several different windows open at the same time. Running that TRUNCATE command in the PROD window instead of the ALPHA window by accident is not what you want to find yourself staring at.
This reminded me of a trick one of my old colleagues taught me.. Set up one role that provides “safe” privileges (e.g. select-only) and one that provides elevated privs (e.g. insert,update,alter, etc). The key behind this is the little known fact that not all roles have to be enabled by default. If you check the *_role_privs views, you will see the column “DEFAULT_ROLE”. This indicates whether each role is enabled by default (when the user logs in). The trick is to set up the “safe privilege” role as being enabled by default and the “elevated privilege” role as not being enabled by default on Production. You can do this with the ALTER USER command:
ALTER USER cmartin2 DEFAULT ROLE read_only_role;
Now on Prod, when you log in you are restricted to only doing selects and such until you intentionally enable your other roles:
SET ROLE ALL;
The idea is that your user still has all the privs that you need to do your job, but it ensures there is a conscious decision being made that you do indeed want to use your higher privs at this time. Obviously this isn’t fool-proof either, but if it helps to prevent one statement from accidentally running in Prod, it will be more than worth it.
There are a couple other options (like specifying multiple roles, ALL, NONE, or ALL EXCEPT) that you can read about in the ALTER documentation and SET documentation.
I didn’t do very well on my goals for 2012. I didn’t read the books or get my certification. However, I did do the 2 users group presentations as well as my first conference presentation, all which was very enjoyable. I also got a new job as an Applications DBA, which I am quite enjoying. Not to mention things in my personal life couldn’t have gone too much better. So all in all, 2012 was a good year for me. Now bring on 2013!
My goals this year are pretty much to accomplish what I set out to do last year and didn’t, plus keep improving my presentation skills.
- Present 2 more times – I really enjoy presenting and am hoping to be able to do so at least 2 more times again this year. It is something that I can’t get enough practice at doing.
- Read! – My Oracle reading really got off-track last year. I read more books than I have in a long time, but only got through half of an Oracle book. Time to make up for lost time and get 5 books read.
- Get Certified – Again, picking up what I didn’t do last year, I would like to continue to get certifications. Not that I feel they are that meaningful, but they do keep me focused on learning new things.
- Blog! – My goal is to post at least once per week.
Well, that is all. I will try to do a mid-year assessment as well to make sure I stay on track 🙂
I ran across this post today with good information about some differences in how to reset a password in 11g using the hash values, and didn’t want to lose it: http://blog.flimatech.com/2011/07/17/changing-oracle-password-in-11g-using-alter-user-identified-by-values/
Short and to the point 🙂