My Possible Pasts

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:

https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a
https://blogs.oracle.com/optimizer/entry/additional_information_on_sql_patches

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

Check out the winner of the Third International NoCOUG SQL & NoSQL Challenge as well as some great articles in the newest edition of the NoCOUG Journal.

I have just started looking into SQL Plan Management, so the next few posts will probably be focused around it. As a quick background, SQL Plan Management lets you “lock in” a plan for Oracle to use for a specific SQL statement. This way when stats change, or data is added, Oracle will continue to use the same plan as before keeping consistency.

The issue I had today is that we had a poorly performing query that had an accepted plan that I didn’t like. The query was part of application code and hints couldn’t be easily added. My hope was to use hints to create the plan that I wanted, and then apply that plan to the statement and mark it as accepted. I would then disable the existing plan and Oracle would start using the new plan for the poorly performing query. Seems easy enough.

However, adding the hints made the query different, so Oracle didn’t add my new plan to the old query. It stored this new query with the new plan that I wanted, and left the old query alone. So this didn’t work as I wanted. We were able to get the plan I wanted by creating extended statistics on correlated columns (but that is for a different post). I knew that there had to be a way to apply the plan from one query to another though, so I kept researching. I am glad I did, because it is extremely easy to do. Following is an example from an Oracle white paper:

DECLARE
   v_cnt PLS_INTEGER;
BEGIN
    v_cnt := dbms_spm.load_plans_from_cursor_cache(
                 sql_id => 'djkqjd0kvgmb5',  -- new SQL
                 plan_hash_value => 3074207202,  -- new Plan
                 sql_handle => 'SYS_SQL_bf5c9b08f72bde3e'  -- Poorly Performing SQL handle
             );
END;

This gets the plan from the cursor cache for the new query, and adds it to the Baseline of the poor performing query. The SQL_ID and PLAN_HASH_VALUE of the new hinted query can be found from V$SQL. The SQL_HANDLE can be found by looking at DBA_SQL_PLAN_BASELINES.

Once the plan is added to the Baseline, it can be accpeted, and the old plan can be rejected.