Author Archives: cmartin2

SQL Plan Management – Adding a desired plan

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.

Third International NoCOUG SQL & NoSQL Challenge – Coming Soon

Iggy Fernandez just let me know that the Third International NoCOUG SQL & NoSQL Challenge will be starting on Monday. I really enjoy these challenges and seeing the creative answers people come up with. I am sure this year will be no different.
—-
The Third International NoCOUG SQL & NoSQL Challenge sponsored by Pythian—Love Your Data™ will be revealed on Monday, May 21 at 9 AM PST at http://bit.ly/JvJS46. In this challenge, the Wicked Witch of the West needs help in creating a magic spell to ensure that the Third Annual Witching & Wizarding Ball is a grand success. The winner will receive the August Order of the Wooden Pretzel in keeping with the Steven Feuerstein’s observation that “some people can perform seeming miracles with straight SQL, but the statements end up looking like pretzels created by somebody who is experimenting with hallucinogens.” There are currently four knights of the August Order of the Wooden Pretzel: Alberto Dell’Era (Italy) who won the first challenge in 2009 and Andre Araujo (Australia), Rob van Wijk (Netherlands), and Ilya Chuhnakov (Russia) who won the second challenge in 2011

NEOOUG Oracle Training Seminar – Day 2

NEOOUG wrapped up yesterday, with 2 talks each by Cary Millsap and Rich Niemiec. I have seen Cary speak before, and never get tired of it. He is a natural teacher, making everything so simple to understand. Rich is a very entertaining speaker as well, and it was fun hearing about stories from Oracle’s early days. I had a great time and will definitely be back next year. I look forward to getting my feedback forms to see what else I can improve. Hopefully people were honest, as I enjoy the feedback with the attitude that if you aren’t getting better, you are getting worse 🙂

Here is a picture of the audience from my presentation on Monday as I was waiting to get started:

NEOOUG Oracle Training Seminar – Day 1

Day 1 is complete. The venue at Cleveland State is awesome, and the presentations were all high quality as well. My presentation went through decently, although I stumbled in a few spots. The questions I got were good, and makes me feel I need to add more to the presentation about how to use the information you get from the execution plans. Looking forward to Day 2.

NEOOUG Two Day Oracle Training Seminar

Preparing for the NEOOUG seminar next week, and have come up with my schedule. Luckily for me there is 30 minutes between the prior presentation and when I present, so I will have plenty of time to set up without having to duck out early from the previous session.

Below is my planned schedule for Day 1:











TimeTrackRoomSpeakerTopic
8:00-8:15General SessionBallroom C&DNEOOUGWelcome
8:30-9:30General SessionBallroom C&DJoel KallmanOracle Application Express – How It’s Made
10:00-11:00DBABallroom C&DRic Van DykeJourney to the Center of the Database
11:15-12:15DeveloperBallroom A&BDominic DelmolinoImplementing MapReduced Algorithms in SQL and PL/SQL
12:15-1:15Lunch
1:15-2:15General SessionBallroom C&DStewart BrysonReal-Time DW with OBIEE 11g and the Oracle Database
2:45-3:45FundamentalsSC 313MeExecuting Explan Plans and Explaining Execution Plans
4:00-5:00FundamentalsSC 313Gary MartinIntroduction to Oracle Analytic Functions

Day 2 is just one track, since I am sure nobody really wants to compete with Cary Millsap or Rich Niemiec 🙂

It looks like it will be a great event.

Presentation for NEOOUG – Recap

On Friday, I presented 2 presentations for the Northeast Ohio Users Group and came away feeling pretty good. The presentations were on Execution Plans and Index Myths, and both seemed to be pretty well received. I lost one of my slides in the Explain Plans portion, but other than that it went pretty smoothly (at least for me). If you are interested in the slides they are on my presentations page.

I came away very impressed with the group at NEOOUG. There were probably around 30+ people in attendance and overall the group was very willing to engage and make the presentation into more of a discussion, with not only good questions but also personal insights that added a lot of value to the presentation. I was meaning to take a picture of the group between the sessions, but unfortunately that got lost in me trying not to forget everything I wanted to cover. Hopefully I will be able to present to the group again in the future, as it was a great deal of fun.

Steven Feuerstein High Performance PL/SQL Seminar in Columbus

OOUG is hosting a one day seminar by Steven Feuerstein on April 16. The seminar is titled “Higher Performance PL/SQL and Oracle 11g PL/SQL New Features”. If you are in the Columbus area and work with PL/SQL, you will want to be there. Steven is the world’s leading expert in PL/SQL and by far one of the best presenters I have ever seen. Check out the OOUG website for more details.