Updated WP and new theme

I upgraded to WordPress 2.5 tonight and also found a new theme that I like better than my dull old one (hat tip to BAAG and Alex for the theme). I’ll be working out the kinks with the word wrapping and updating some tags (I was foolish and used spaces instead of commas, so need to update those posts to fix the tag cloud). Hopefully, in the next couple of days, I’ll get things updated and hopefully the site will look and work better. Then I’ll hopefully continue working through my blog article backlog. Thanks for your patience while I remodel.

Future Oracle events

This is a quick note about upcoming major events on my Oracle event calendar.

In between these events (hopefully, I’ll get to attend and/or participate in all of them), I’ll also be planning to present at some regional user group events in Charlotte (CLTOUG), Chicago (COUG), Northern California (NoCOUG) and others as time allows.

If you’re going to be at any of these events, please get in touch so we can meet up there. I’m hoping to make more time this year for attending these events and be a little less involved in them so that I can make more time for talking with individuals and/or customers. Hope to see you in the next year at one or more of these venues!

Collaborate 08 thoughts

I took a few extra days to decompress from last week’s events at Collaborate 08 before writing up a summary of my thoughts. I didn’t think it was really possible to be more busy than I have been in previous years, but I think I matched my previous year’s schedule pretty easily. This year, instead of spending a lot of time in the Piocon exhibit hall booth (we had an excellent location this time), I replaced much of the time I usually spend carrying out the corporate duties with volunteer activities for the IOUG Collaborate 08 Conference Committee.

I was one of the three IOUG DBA Track Managers (along with Bonnie Bizzaro and Nick Marcovecchio) whose responsibilities were to select the DBA sessions for the conference, review the whitepapers and presentations for each speaker (with the help of our excellent reviewers), ensure that the speakers made it to their sessions, and provide feedback to speakers after their sessions (especially first-time speakers). These duties, along with coordinating 3 RAC SIG events, giving 2 expert technical sessions, one technical session, and assembling and moderating a panel discussion made for a busy week. I can say, though, that being on the conference committee is a rewarding experience and something that volunteers should aspire to as they become more involved with the IOUG. Anyone can apply for the Collaborate 09 conference committee when the application becomes available later this summer.

As mentioned, I had some extra duties this year that made it almost impossible for me to sit still through an entire session. However, I did get to sit in on parts of many sessions. We had about 400 total sessions in the IOUG Forum this year, up from about 250 last year. So, as expected, we had over 80 first-time IOUG speakers. I was delighted to see parts of many new speaker sessions and found most of them to be very informative, clear, and concise. Some of the speakers were a little nervous and a few finished a little early (due to speeding through their material a little too fast), but that’s about the worst I saw (which isn’t bad at all!).

My own sessions went well and other than the 8:30am session (on Oracle Adaptive Access Manager), they were attended pretty well too. I think my favorite session was the panel discussion I moderated on Thursday morning titled “To RAC or Not To RAC: What’s Best For HA?” The panelists were great and we had a lively discussion and dispelled several myths about RAC. I only wish we had more time to take questions from the audience, but several attendees still commented that they enjoyed the session and the panelists. Everyone seemed to enjoy the Saturday Night Live skits I played prior to the start of my presentations. My whitepapers and presentations are now available here.

There was a great RAC hands-on session put together at the last minute by Jeremy Schneider. Jeremy heard about an opening in the hands-on lab during our new speaker briefing on Sunday and by Wednesday, he had built several complete recipe procedures for installation and configuration as well as 3 sets of RAC virtual machines. Attendees could choose from different options for starting points to make it easier for each skill level to do something productive in the relatively short 90-minute hands-on session. The only unfortunate part of the RAC hands-on session was that it was scheduled concurrently with the RAC Birds of a Feather session and another RAC technical session as well. With 400+ sessions, scheduling two sessions from the same topic area such that they don’t overlap is next to impossible. Even with the scheduling conflict, there were about 15 RAC hands-on attendees and about 15 people attending the RAC Birds of a Feather session.

The RAC Birds of a Feather session was a lively discussion where I was able to gather some good ideas for future RAC SIG events and topics for possible hands-on sessions as well as webcasts. If you have more ideas, please send them to me.
My non-conference highlight of the week was when a group of 16 Pioteam members and friends of Piocon attended a playoff game in the quest for the Stanley Cup (that’s NHL for those that don’t know hockey). The Colorado Avalanche defeated the Minnesota Wild 5-1 with lots of action to make the match entertaining as well as victorious for the home team. It was a good time for all and allowed for some good discussions with our partners and customers that attended. Other than that, we also had a good time at the customer appreciation event on Wednesday night. They had some rock climbing, DJ music, pool tables, and (fake) gambling tables. I played fake Texas Hold ‘Em Poker for about an hour or so.

Of course, probably the most important and rewarding part of the Collaborate conferences for me is the mini-reunion of all the individuals that I generally only see once a year at these events (aside from “seeing” them online). From what I remember, I saw and/or hung out with Jake K, Matt, Jeremy, Bex, Michelle, Vinod, Venkat, Jake V, Paul, Tony, Vikki, George, Alex, Logan, and that doesn’t include all the IOUG volunteers that I worked so closely with since October. To all those that I forgot to mention, I enjoyed seeing you too! And especially to those I met for the first time, I hope to see you again online as well as at future conference events. Thanks to everyone that took the time to say hello and/or introduce yourself.

See you next year in Orlando, May 3-7, 2009 for Collaborate 09!

Collaborate 08, RAC SIG & other sessions of interest

Collaborate 08 is here and I’m in Denver already to prepare for the start of the conference. This year, I won’t be blogging a day-by-day accounting of my whereabouts and happenings. Instead, I’ll save up for a summary of what I saw, heard, and vibes I felt during the week.

If you’re still one of the many that hasn’t quite finalized your itinerary for the week, check out these events from the RAC SIG:

  • Sun, 13-Apr, 7:30pm, Hyatt Granite ABC: IOUG SIG Reception (this is all SIGs, not just RAC SIG)
  • Mon, 14-Apr, 10:30am, Korbel 1F: RAC SIG Customer Panel
  • Tue, 15-Apr, 12:15pm, Korbel 4C: RAC SIG Experts Panel
  • Wed, 16-Apr, 1:30pm, Korbel 4F: RAC SIG Birds of a Feather

Also check out all the great RAC-related sessions happening this week. For convenience, the RAC SIG has assembled a summary of all the Collaborate 08 sessions related to RAC on their website for download.

Of course, I also would love to see you in my own sessions this week. Here is my presentation list this week:

  • Mon, 14-Apr, 9:15am, Korbel 3B: SSL, Load Balancers, Rewrite, Redirect, and More Advanced Configuration (2-hour expert session co-presentation with Matt Topper)
  • Tue, 15-Apr, 3:30pm, Four Seasons 2/3: Building a RAC Test Environment On VMWare For Free (2-hour expert session)
  • Wed, 16-Apr, 8:30am, Korbel 3B: Oracle Adaptive Access Manager: What, Why, How
  • Thu, 17-Apr, 9:45am, Korbel 2A: To RAC or Not To RAC: What’s Best For HA? Panel debate session (I’m the moderator)

When I’m not at one of those sessions or a RAC SIG session, I may be attending other sessions and will also be spending some time at the Piocon Piobooth #1619 in the exhibit hall (I actually have a real job!). My Piocon team members are presenting quite a few interesting sessions as well, so if RAC and IdM aren’t your things, check out these sessions as well.

If you’re at the conference and want to meet up, tweet me using twitter.com (I’m dannorris).

PIPELINED PL/SQL function performance

I recently had an opportunity to do a deeper dive into a performance issue related to the performance of a PIPELINED function. The results weren’t quite what I had expected, so I thought they may be of general interest. As with many performance issues, the issues encountered here are likely data- and database-specific. This was testing performed on 10.2.0.3 64-bit non-RAC on Solaris 9.

The PIPELINED function was called like this:

select * from TABLE(pkg1.f1(arg1,arg2,arg3));

And pkg1 is defined like this:

CREATE OR REPLACE PACKAGE pkg1 AS
TYPE coll_table IS TABLE OF custom_persistent_obj_type;
FUNCTION f1 (
p1        IN   NUMBER,
p2             IN   NUMBER,
p3        IN   NUMBER
) RETURN coll_table PIPELINED;
END;
/

The function pkg1.f1 returns a collection of object types, is a little more than 1400 lines long, and contains more than 10 distinct, separate queries, each of them significantly complex involving at least 2 tables, many of them with multiple levels of subqueries. Anyhoo, my chore was to open the idea bin and see if there were some possible enhancements that hadn’t been considered yet.

While reviewing overall performance metrics for the instance, I noted that the DBMS_PICKLER package appeared in the top 10 resource-intensive SQL statements on a regular basis. More digging identified this package as being the primary workhorse behind the TABLE function. So, then the test was clear. Let’s see what happens if we remove PIPELINED, remove TABLE and call the function in a more conventional way. So, my version of the pkg.f would create a collection, add records to it throughout the function’s execution, then return the whole collection at the end with a RETURN statement. The calling function would then need to process this collection into some form to make it more usable by the calling application (similar to the functionality that TABLE provides). For this task, I wrote a stand alone function called DAN.

The new package, pkg2, is defined like this:

CREATE OR REPLACE PACKAGE pkg2 AS
  TYPE coll_table IS TABLE OF custom_persistent_obj_type;
  FUNCTION f2 (
    p1        IN   NUMBER,
    p2             IN   NUMBER,
    p3        IN   NUMBER
  )  RETURN coll_table;
END;
/

DAN is defined as:

CREATE OR REPLACE FUNCTION dan (p_b_tab  IN  pkg2.coll_table,
    p_delimiter     IN  VARCHAR2 DEFAULT ',')
  RETURN VARCHAR2 IS
    l_string     VARCHAR2(32767);
  BEGIN
    for i in p_b_tab.FIRST .. p_b_tab.LAST LOOP
      l_string := l_string || to_char(p_b_tab(i).id1)  ||p_delimiter
                           ||         p_b_tab(i).id2   ||p_delimiter
                           || to_char(p_b_tab(i).array_id)   ||p_delimiter
                           ||         p_b_tab(i).value
      ;
    END LOOP;
  RETURN l_string;
END dan;
/

So, my new units would be called like this:

DECLARE
  l_string      VARCHAR2(32767);
BEGIN
  l_string := dan(pkg2.f2(arg1,arg2,arg3));
  DBMS_OUTPUT.PUT_LINE(l_string);
END;
/

Of course, performance is our primary objective in this case, so timing these two examples side-by-side is the primary chore. I was satisfied that they performed equivalent work and provided equivalent results using different methods. The calling program (custom java, in this case) would have to be modified to handle the changes required, but that may be worth it depending on the time difference.

So, I devised a script to do some very rough testing. The script looked like this:

set timing on echo on termout on pages 0 lines 450 feedb on serveroutput on size 100000
select * from table(pkg1.f1(1095482041,60983,1));
DECLARE
  l_string      VARCHAR2(32767);
BEGIN
  l_string := dan(pkg2.f2(1095482041,60983,1));
  DBMS_OUTPUT.PUT_LINE(l_string);
END;
/

The results were somewhat surprising. Keep in mind that all I’m changing is the way in which data is returned and processed from the function. The data returned, though in slightly different formats, is exactly the same and is not filtered further after it is returned (so there’s no benefit in having it available to run SQL against it).

SELECT (sec) PL/SQL (sec) Difference (sec) Percent Improvement
0.15 0.09 0.06 40.00%
0.14 0.09 0.05 35.71%
0.14 0.10 0.04 28.57%
0.16 0.09 0.07 43.75%
0.15 0.09 0.06 40.00%
0.14 0.12 0.02 14.29%
0.18 0.12 0.06 33.33%
0.18 0.10 0.08 44.44%
0.25 0.12 0.13 52.00%
0.14 0.10 0.04 28.57%
0.18 0.10 0.08 44.44%
0.15 0.10 0.05 33.33%
0.17 0.10 0.07 41.18%
0.18 0.10 0.08 44.44%
0.14 0.10 0.04 28.57%
0.17 0.11 0.06 35.29%
0.17 0.10 0.07 41.18%
0.16 0.10 0.06 37.01% Average
0.14 0.09 0.02 14.29% Min
0.25 0.12 0.13 52.00% Max
8.84% Standard Deviation

As you can see, the results are pretty clear that there is a performance improvement in this case when we avoid using PIPELINED functions. I will also say that the result set from the function body is only about 30 rows. From my review of common uses for PIPELINED functions, it appears that larger collections (result sets) may benefit more from PIPELINED functions as they can likely take more advantage of the parallelism allowed with PIPELINED functions.