Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Archive for the ‘Conferences’ Category

Great Lakes Oracle Conference GLOC May 12-14

leave a comment »

Each Oracle Users Group has its own personality. This week I had the honor to co-speak with Mauro Pagano at GLOC, and the experience has been great! We delivered a 4 hours workshop on “Oracle Performance Tuning 101”. We talked about fundamentals and tools of the trade for both Database and SQL Tuning. It was really nice to have over 50 attendees and it was a challenge to keep them engaged for such a long time! Still the feedback was: we want more!

Anyways, as I am listening to Tom Kyte speaking about the new Oracle In-memory Database, I take a moment to write some words about this GLOC. What can I say? In short: It has been awesome! This Oracle Users Group is well organized and growing slowly for surely (over 300 attendees). This is my second year speaking at GLOC and I see it bigger, better and very welcoming. As far as I understand, membership is inexpensive and still manages to attract such good speakers as Tom Kyte, Alex Gorbachev, Scott Spendolini, Tim Gorman, Kyle Hailey, Steven Feuerstein, Carol Dacko, Ric Van Dyke and many others. My kudos to GLOC. Way to go! Looking forward to contribute again next year, and hoping to see more speakers and attendees traveling from other Regions.

Tom Kyte at GLOC!

Written by Carlos Sierra

May 13, 2014 at 9:33 am

About AWR, SQLT and DB360

leave a comment »

This blog posting is about answering this first question below, which I often get asked:

Can I use SQLTXPLAIN (SQLT) instead of AWR?

The answer is: it depends. If you are doing SQL Tuning and you already know the SQL_ID, then you may want to use SQLT XTRACT (MOS 215187.1) directly on the SQL_ID of concern. But even in that case, keep in mind that SQLT accesses AWR data, so your environment must have a valid license to use the Oracle Diagnostics Pack. In fact, when you install SQLT, it asks if you have the Oracle Tuning Pack, the Oracle Diagnostics Pack or none. Depending how you respond, SQLT access or not the set of views and APIs included on those Oracle Packs. That means you can configure SQLT to access or not AWR data.

What is the difference between AWR and SQLT?

In short, the difference between these two is the scope. You use AWR to diagnose database performance, while you use SQLT to diagnose one SQL. Sometimes I explain this difference by saying: “AWR is to an entire database to what SQLT is to one SQL”. One is for system-wide performance, the other is very centric to one SQL at a time.

Why SQLT exists?

I envisioned SQLT on the late 90’s when I was a road-warrior fighting fires in the area of performance, and in particular SQL performance. I found back then that Oracle-provided tools like TKPROF were excellent, but I always needed something more, like knowing the state of the CBO Statistics, Tables and Indexes, etc.

These days, my good friend Mauro Pagano from Oracle, is keeping the ball rolling. He is keeping SQLT in constant motion, making it a better tool on every new version. So, I would say this: SQLT is filling up some gaps that me, and many others, consider important in order to be diligent on root cause analysis for cases were a SQL performs poorly.

What is DB360?

As SQLT brings to the table several pieces of information that we need for SQL Tuning, and which are not available using out-of-the-box tools like TKPROF or SQL Monitoring, the new DB360 tool is doing something similar for the entire database: It complements  what AWR provides by producing a report with meaningful information about an entire database. This DB360 is a tool that installs nothing on the database, and produces an HTML report with sections such as Configuration, Sizing, CBO Statistics, Performance Trends, etc.

Is DB360 a licensed product?

No. This DB360 tool belongs to Enkitec. It is not yet available to the Oracle community, but it will be soon. Same as SQLT, if you have an Oracle Tuning or Diagnostics Pack, then when you execute DB360 you would get to see in your DB360 report some pieces of information generated out of views and APIs covered by those Oracle Packs, else you get only the pieces which require no Oracle Pack license. Besides the restriction to limit your use of DB360 as per your Oracle Pack license, DB360 itself will be available to the Oracle community for free, and with no strings attached, same as SQLT.

Why are SQLT and DB360 free to download and use?

These tools are simply a contribution to the Oracle community. “Sharing tools is like sharing knowledge: it makes our IT experience more pleasurable”. The only payback comes when you share back with the Oracle community some of your knowledge, or some of your tools and scripts. If you have been a speaker in an Oracle Users Groups, then you may relate to this gratifying experience to share with others what you know. At RMOUG these past 3 days, I have had the opportunity to experience once again this special sense of community, that is always eager to share and to learn from each other.


SQLT complements TKPROF and SQL Monitor. DB360 complements AWR. When it comes to diagnostics, either for one SQL or for an entire Database, having to our disposal additional diagnostics in the context of our analysis, improves our chances to do a diligent job, while reducing the time that it would take to assembly those pieces manually; all with the extra benefit of having these extra diagnostics pieces consistent to a point in time. So, I hope you continue enjoying SQLT and in the near future DB360!

Written by Carlos Sierra

February 7, 2014 at 6:07 pm

Exadata Optimizations and SQLTXPLAIN Courses

leave a comment »

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.


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.


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!

Written by Carlos Sierra

December 27, 2013 at 1:24 pm

Carlos Sierra’s shared Scripts and Presentations

with 2 comments

I recently delivered 3 sessions at the East Coast Oracle Users Group (ECO). During these sessions I offered to share the actual Presentations and some of the Scripts I used during the 3rd session. I plan to keep updating and expanding both scripts and presentations. They also show now on the right side of this page. Feel free to use, share and recycle any of my scripts and presentations.

Written by Carlos Sierra

November 12, 2013 at 7:23 am

Speaking at the SLOUG tomorrow

leave a comment »

Kerry and I will be speaking at the Saint Louis Oracle Users Group. The name of the event is Big Bash 2013. We will talk about problem solving, plan stability and plan flexibility. We expect to have a large audience and we look forward to meet old friends and make new ones. Oracle Users Groups are nice gatherings, and every group has its one charisma. For me, this is my first time speaking at SLOUG, and actually the first time in Saint Louis. Flying today and retuning tomorrow late at night. Feeling excited!

Written by Carlos Sierra

October 17, 2013 at 7:32 am

Posted in Conferences, OUG

Speaking at Oaktable World

with 6 comments

Some of you may not know what is the Oaktable.  I quote here a very precise description right from the Oaktable’s main page: <<< The OakTable network is a network for the Oracle scientist, who believes in better ways of administering and developing Oracle based systems. The network is an informal organisation, which will get together at various Oracle events. >>> To me, it is a lot more, since its members are a very collaborative bunch of smart and dedicated folks. A common denominator I have perceived so far is the honest desire to apply scientific methods to complex issues we encounter in the Oracle world. If you are serious about learning Oracle and its intricacies, you may want to do as I do:  “feast from the crumbles of the Oaktable“.

The Oaktable World is a conference that happens to coincide with OOW.  This year I have the honor to speak at Oaktable World. According to the agenda, I will speak on Tuesday, September 24 at 10am. My session is at the same time than Jonathan Lewis, thus I am prepared to speak to myself (unless lucky me you happen to not know who is JL). Anyways, I will be speaking about Adaptive Cursor Sharing (ACS) and how it actually works. I will do some live demos and my goal is that all participants (if any) can actually fully understand how and when ACS produces multiple optimal execution plans for a given SQL.

If you plan to attend any of the Oaktable World sessions, please be sure you register. Space is very limited.

I will be attending myself as many of the other Oaktable World sessions as I can, else I will be there at the Enkitec booth #501 in Moscone South in case you want to stop-by and say hello.

Written by Carlos Sierra

September 16, 2013 at 5:13 pm

E4 Day 2

leave a comment »

Second day of E4 as great. Look at the agenda. What can I tell? Great event! I learned a lot and I feel quite motivated to dive into all this Exadata ecosystem. Anyways, I have to pack and move to my 3rd hotel 😉

Some pictures from E4 day 2:

Doug Cutting - Big Data Keynote

Doug Cutting – Big Data Keynote

Gwen Shapira - Is Hadoop for You?

Gwen Shapira – Is Hadoop for You?

Tyler Muth - Bloom Filters on Exadata

Tyler Muth – Bloom Filters on Exadata

Alex Fatkulin - DBFS Performance

Alex Fatkulin – DBFS Performance

Kerry Osborne - Oracle Database 12c on Exadata

Kerry Osborne – Oracle Database 12c on Exadata

Ferhat Sengonul - Exadata Ecosystem in Turkcell

Ferhat Sengonul – Exadata Ecosystem in Turkcell

Sue Lee - Resource Management

Sue Lee – Resource Management

Written by Carlos Sierra

August 7, 2013 at 6:48 am

Posted in Conferences, E4