Archive for the ‘Training’ Category
Exadata Optimizations and SQLTXPLAIN Courses
I will be delivering a couple of courses soon. One in January and the second in February. I will keep posting upcoming Training and Conferences on a new link at the right margin of this blog.
Exadata Optimizations Jan 13-14
This 2-days “Exadata Optimizations” course is for Developers and DBAs new to Exadata and in need to ramp-up quickly. As the name implies, its focus is on Exadata Optimizations. We talk about Smart Scans, Storage Indexes, Smart Flash Cache, Hybrid Columnar Compression (HCC) and Parallel Execution (PX). This course is hands-on, with a fair amount of demos and labs.
SQLTXPLAIN (SQLT) Feb 20-21
This “SQL Tuning with SQLTXPLAIN” 2-days course shows how to use SQLT to actually do SQL Tuning. We go over the ying-yang of the CBO, meaning: Plan Flexibility versus Plan Stability. We use SQLT for labs and we also go over some real-life SQL Tuning cases. If you are currently using SQLT, you are welcome to bring a SQLT Report to class and we could review it there.
Conclusion
New year, new resolutions. I will be investing part of my time sharing knowledge through formal courses and conferences. These days it is hard to find the time and budget to keep our knowledge on the edge, but again and again I see that many of our daily struggles could be mitigated by some concise technical training. So I encourage you to add some training to your list of resolutions for this new year; or at the very least, to get and read some fresh books.
Happy New Year 2014!
SQL Tuning with SQLTXPLAIN 2-days Workshop
SQLTXPLAIN is a SQL Tuning tool widely used by the Oracle community. Available through My Oracle Support (MOS) under document 215187.1, this free tool is available for download and use to anyone with MOS access. It has helped thousands of times to expedite the resolution of SQL Tuning issues, and many Oracle DBAs and Developers benefit of its use on a daily basis.
Stelios Charalambides has done an excellent job writing a book on this topic. In his book Stelios covers many aspects about SQLTXPLAIN and some related topics. I highly recommend to get a copy of this book if you want to learn more about SQLTXPLAIN. It is available at Amazon and many other retailers.
The new 2-days SQLTXPLAIN Workshop offered by Enkitec (an Oracle Platinum business partner and my employer) is a completely new course that interleaves “how to use effectively SQLTXPLAIN” with important and related SQL Tuning Topics such as Plan Flexibility and Plan Stability. This hands-on workshop offers participants the unique opportunity to fully understand the contents of SQLTXPLAIN and its vast output through an interactive session. About half the time is dedicated to short guided labs, while the other half uses presentations and demos. This workshop is packed with lots of content. It was a real challenge packaging so much info in only two days, but I am very pleased with the result. It became a 2-days intensive knowledge transfer hands-on workshop on SQLTXPLAIN and SQL Tuning!
The first session of this workshop is scheduled for November 7-8 in Dallas, Texas. I expect this pilot session to fill out fast. Other sessions and onsite ones will be offered during 2014. I hope to meet many of you face to face on November 7!
Carlos Sierra is joining Enkitec soon
In a few days I will be joining Enkitec. Sure I will miss Oracle after 17 good years there. I made some really good friends and I learned a lot. I had the opportunity to build tools I thought were needed by the Oracle community, and I am glad they are been deployed all over the world. I am also pleased to know all these tools will continue helping our SQL Tuning community with no disruption.
A question I am often asked is: WHY Enkitec? I could elaborate on an answer but to keep it short, I would simply say “because I like the idea of contributing to an expanding team of outstanding professionals in the Oracle space“. I also value the close relationship between Enkitec and Oracle. I see this as a win-win.
At Enkitec I will be doing a lot of stuff. My main focus at the beginning will be database performance in general and SQL Tuning in particular. I foresee the possibility to open some SQL Tuning hands-on workshops using SQLTXPLAIN, SQLHC and some other cool tools and techniques. I also expect some additional duties and new areas of expertise to develop, which I embrace as new challenges and opportunities.
Overall, I feel very excited and ready to start walking this new career path. What can I say… Just when I though life was good it simply got better!
You know how to reach me (here) or by email.
Cheers — Carlos
SQL Tuning 101 and Sushi
A question that I hear often is: “how to tune a query?”. It comes in several flavors, but what I usually read between lines is: I am new to this “sql tuning” thing, and I have been asked to improve the performance of this query, but I have no clue where to start.
Knowing about nothing on SQL Tuning is quite common for most DBAs. Even seasoned DBAs may stay away from SQL Tuning, mostly because they feel out of their comfort zone. In addition, I think SQL Tuning is like Sushi: You either love it or hate it! And same like Sushi, most would avoid it simply because they haven’t tried it. By the way, I am a Sushi lover but that is another story…
SQL Tuning 101
So, if you are like in square 1, and you are serious about learning SQL Tuning, where do you start? There are about a couple dozens of well recognized names on this space of SQL Tuning. Look at my blog roll to get some idea. The problem with this list is that most of the “gurus” walk on water and their very simple explanations require like tons of knowledge in related topics. Not bad if you are traveling the road from intermediate to advanced, but a newbie gets lost like in 5 seconds. There is also the risk of falling for all the misinformation you find in the internet written by some adventurous self-proclaimed “experts”.
I strongly suggest to start by reading the Concepts reference manual for your Oracle release, followed by the SQL Reference. Feeling comfortable writing SQL is a must. You don’t know SQL? Then, learn it first then move into SQL Tuning. If you have some Development experience that helps a lot. If you are a seasoned Developer you are half the way there already.
After reading the Concepts reference manual and becoming proficient in SQL, then get yourself a copy of the “Oracle SQL High-Performance Tuning (2nd Edition)” book written by Guy Harrison more than 10 years ago. Disregard the fact that the book is old. I like this book because it starts with the basics. It assumes nothing. I learned a lot from it when I first read it long time ago. If you search for it in Amazon just be aware the front-cover picture is incorrect, but the actual book is correct.
Another book I suggest is the recent one written by Stelios Charalambides. The title is “Oracle SQL Tuning with Oracle SQLTXPLAIN”. I like this one because it introduces SQLTXPLAIN like you would do with Sushi: Starting with a California Roll, then Sushi and last Sashimi. Also, you would get to learn of SQLTXPLAIN and how this FREE tool can help you to digest your dinner better… I mean your SQL Tuning.
Once you gain some experience doing some real SQL Tuning, then you can move to start reading endorsed blogs and books from the Sushi Masters. I hope you give it a try, and I hope you also get to love it!
Hotsos Symposium 2013
Today is the 3rd and last day of the Hotsos Symposium 2013. I will stay the extra day so I can attend Maria Colgan’s 1-day class. I am delighted to be here. This annual event is awesome. I will certainly apply to speak next year!
In my opinion, most sessions have been of good quality. Topics are interesting, and having 2 sessions at the same time allows me to pick one out of two instead of many. Of course there are times where I would like to attend the two!
Since this is my second year, I feel like coming back home. I know more people than last year, including other speakers. My kudos to Hotsos for keeping up with the quality of this event.
I would love to see more Oracle attendees, specially Support managers so they can better sense what the Oracle community needs… Anyways, I am happy to see some Oracle faces here… Since many of us work from home these days, participating in events like this helps to reestablish relationships, that would otherwise fade over time.
Yesterday, we paid a visit to Enkitec‘s offices. Veronica showed us the office and we ate some pizza there. The office is modern, stylish and cool. It invites to work! One thing I noticed is the natural synergy among employees. I find this intimate networking of smart guys so important to achieve better results. It encourages constant collaboration and kind of explains me the success of this firm. It brought to my memory my first years at Oracle, when we used to gathered in the aisles and talk about anything, including work! Anyways, it was nice to go and visit…
My to-do list after this Symposium grows:
- I noticed some misconceptions about statistics gathering. I have to chase down some Metalink notes which are causing this trouble…
- Without knowing it, some speakers have created some “enhancement requests” to SQLT, so I have to incorporate some of these soon…
- I need to review some SQL tuning complex cases that some participants want to discuss with me. I will review and provide some advice…
- I expect to receive some emails with enhancements to either the CBO or the database. I will log them to corresponding groups…
- Oracle Support would benefit of some refreshment in “diagnostics collection” for SQL tuning issues. That means another round of internal training. Maybe short remote sessions…
- I have a list of at least half a dozen topics that could be of interest for future conferences. I need to polish this list and draft some abstracts. Then I will review with some OUG’s key players to sense interest.
- I need to purchase (and read) like 3-4 books.
- I need to evaluate the possibility of writing a new tool. It would be a holistic review of CBO statistics (including schema, system, data dictionary and fixed objects). The tool would perform many health-checks and return a set of observations. A natural component could be the dynamic generation of a second script to actually implement those observations. Statistics is still a hot issue and I noticed very diverse opinions. Some are good and some are simply wrong.
- We need to investigate if “STATISTICS_LEVEL to ALL” can be improved in terms of performance in EXADATA.
- I need to learn STRACE and determine its real value for Support. Then review existing Metalink notes and update them accordingly.
- Big data and fussy data mining seem to be hot. Performance of data mining may become a concern… I have to research.
- Method-R and Delphix have excellent products. I need to learn more about them since I expect their adoption by the Oracle community will make them more visible to all of us. I may write a Metalink note on the former so Support can make better sense of it.
- I have to document in this Blog more about the many ramifications of SQLT, specially in the area of Plan Stability and Plan Migration. It seems to be an eternal concern. SQLT provides a lot of help in this area but I think we need better explanations on how to exploit these capabilities.
Ok. Time for a coffee and my daily email race… So far, email is winning by far…
SQLTXPLAIN capabilities – free webinar for Oracle partners (Wednesday 27)
This Wednesday, February 27 at 8 am Pacific Time (11 am ET), I will deliver a one hour webinar to Oracle partners. The topic is about SQLTXPLAIN capabilities and how this tool helps to diagnose SQL statements performing poorly. Details are here. Format is 45 minutes of presentation and demo, followed by 15 minutes for a Q&A session.
SQL Tuning Workshop at the NoCOUG
I am delivering a two-days SQL Tuning Workshop at the Norther California Oracle Users Group (NoCOUG) in Pleasanton, California. Today is the 2nd and last day of this class. We discussed yesterday: Cost-based Optimizer (CBO) Statistics, Event 10053, Adaptive Cursor Sharing (ACS) and SQL Plan Management (SPM). We will discuss today some SQL Tuning tools: Event 10046, Trace Analyzer TRCANLZR, SQLTXPLAIN (SQLT), SQL Health-Check (SQLHC) and SQLT Test Case (TC). There are around 35 participants and I would say the level of the class is between intermediate to advanced. Most participants are seasoned DBAs and some have a good level of expertise as Developers. Abel Macias is participating in this session as my co-instructor. He is bringing to class his extensive expertise in SQL Tuning. It is always a challenge to keep the attention of such a large and diverse group, but I would say the level of participation has been quite high. I appreciate all the effort from several contributors of the NoCOUG who have made this training possible. My special thanks to Gwen Shapira, Randy Samberg, Iggy Fernandez and Eric Hutchinson. It is very rewarding to see how Oracle Users Groups are putting together events like this where we can openly share knowledge with our Oracle users community.
I will speak at the actual NoCOUG Conference this Thursday. I will talk about the content of SQLTXPLAIN (SQLT) main report, which is quite comprehensive. I will go over some samples and I will allocate plenty of time for questions during and after my presentation. These days SQLT is widely used within Oracle and now by several enterprise Oracle customers.
Stelios Charalambides book about SQLT Tuning and SQLTXPLAIN will be released on March 20. The title is Oracle SQL Tuning with Oracle SQLTXPLAIN. This book can now be pre-ordered in Amazon. If you want to learn more about how to use SQLTXPLAIN to diagnose SQL Tuning issues you may want to get a copy of this book.
After NoCOUG I will spend one week at the office, where I will deliver a live Webinar in SQL Tuning to our Oracle Partners. Then my next conference is the popular Hotsos Symposium in March 3-7. In my humble opinion, this annual Hotsos event is the single most relevant in the area of Oracle Performance. Thus I am very excited to have the opportunity to speak there. A real honor!
RMOUG Training Days February 12 (day 2)
Second day is over now. I attended several interesting sessions. The one I liked the most was about the role of women in technology. I am so glad RMOUG provides now this kind of session! I think we need to encourage women to fill positions that are still dominated by men, like speaking at conferences. If you consider that 40% of DBAs are women while only 11% are speakers, you get to understand something can be improved here…
Today, I will deliver two one-hour sessions:
- How to improve SQL Performance with new SQL Health Check Tool. Wednesday, February 13 at 8:30 am. Room 4a.
- How to create in 5 minutes a SQL Tuning Test Case using SQLTXPLAIN. Wednesday, February 13 at 1:30 pm. Room 403.
I am so happy to participate as a speaker at the RMOUG! My reward is simple: to see participants nodding and smiling when a concept clicks in, or when they get an answer to a question that has been boiling in their heads for some time. After 17 years at Oracle and having learned from so many smart guys, I am lucky to be in a position where I can answer many questions. Still I say “I do not know” when asked something I do not know, but when it comes to SQL Tuning, I feel pretty comfortable with most subtopics. I notice a great interest on Plan Stability and SQL Plan Management in particular. I will be offering sessions in this area within SQL Tuning…
Looking forward to a great third and last day of this event today!
2-days open SQL Tuning Class in Pleasanton, CA this month!
I will be delivering a two-days SQL Tuning class on February 19-20 in Pleasanton, CA. It is available to registered Oracle Users thanks to the help from NoCOUG. Space is limited, so if you want to attend you need to hurry up.
This class covers:
- CBO statistics (fundamentals and good practices);
- Tracing with Events 10046 and 10053;
- Plan Stability with Adaptive Cursor Sharing (ACS) and SQL Plan Management (SPM);
- SQL Tuning tools: Trace Analyzer TRCANLZR (TRCA), SQLTXPLAIN (SQLT) and SQL Health-Check (SQLHC); and
- SQL Tuning Test Cases (creation and implementation).
This is the first time this 2-days class is offered outside Oracle. Its content is an extract of the internal SQL Tuning Workshops I have developed and delivered to over 700 Oracle Engineers around the globe.
This is not an Oracle University (OU) class, which means the scope is more into the actual trades of the day-to-day life of SQL Tuning and less into product features. So if you have taken a SQL Tuning OU class, then this 2-days session would be a nice addition.
Another thing that you should expect from this class is lots of participation. In my sessions I always encourage participants to bring to the table their current SQL Tuning issues or concerns, so we can openly analyze and discuss them during class. The environment is friendly, welcoming and open.
I hope you can join us on February 19-20.
Deep-dive on SQLTXPLAIN offered externally for the first time
The Rocky Mountain Oracle Users Group (RMOUG) Training Days is approaching soon: February 11-13, 2013. I hope you are attending this Oracle relevant event. There are so many good sessions that it is hard to decide which ones to attend!
For the first time, I will be conducting a 4-hours deep-dive external-session on SQLTXPLAIN (SQLT). The title is Using SQLTXPLAIN (SQLT) to Diagnose SQL Statements Performing Poorly. If you want to know SQLT from the basics to an intermediate level, consider attending this 4-hrs session. It will be offered on Monday, February 11 from 1 to 5 pm at the Colorado Convention Center in Denver, Colorado (USA). I love interactive sessions, so I will encourage voluntary participation and open discussions.
In addition to the 4-hrs deep-dive on SQLT, I will also speak on Wednesday, February 13 at 8:30 am about How to Improve SQL Performance with New SQL Health Check Tool. This is a 1-hour seminar with plenty of time for Q&A.
If you attend the event and want to discuss any issue with regard to SQLTXPLAIN (SQLT), SQL Health-Check (SQLHC) or simply SQL Tuning, please approach me. I will be there to help, share, listen, learn, discuss, etc.
Carlos Sierra's Tools and Tips 