All Change Please...

As I mentioned in my last post, on June 1, 2009 I joined the team at Sumner Technologies. Since then, Scott Spendolini and I have been working on Sumner's portfolio to broaden the scope of what we're offering.

New items include SumnerPrint, a PL/SQL-based solution that allows you to easily print APEX reports to PDF, HTML and XLS, and SumnerFramework, an application security and management framework for APEX. We're also working on several new courses and service offerings.

As part of the inevitable website changes, we've introduced a new company blog. We'll be using this blog to provide information about products, services and training classes that we provide as well as unique technical tips and tricks on APEX and other Oracle-related technologies.

You can subscribe to the SumnertechBlog at http://feeds2.feedburner.com/SumnertechBlog.

I'll still be updating my blog, but it will become much more personal, and much less about Oracle and APEX.

And don't forget to come visit us at ODTUG KALAIDOSOPE!

ODTUG Kaleidoscope - The countdown begins...

As many of you know, ODTUG Kaleidoscope runs from June 21-25 in Monterey, California. Topics covered will range from Essbase and Hyperion, BI and Data Warehousing, SOA, BPM to my favorite, Application Express.

This year there are over 40+ APEX related presentations, a number of which will be presented by Oracle ACE and ACE DIRECTORS. Pretty exciting stuff if you're an APEX geek like me. But for me there are several things that will make this particular conference "special".

Of course, the opportunity to attend so many good sessions is high on my list, but I also get to see a number of people that I haven't seen for quite some time. I'm really looking forward to the APEX Meet-Up that John Scott is organizing.

But one thing that will be very different is that it will be my first conference as a partner in Sumner Technologies. As of June 1, 2009 I joined forces with Scott Spendolini to help broaden Sumner's offerings. And, not only will Sumner (Scott) be presenting, but we will also have a booth in the exhibitors area this year.

To coincide with all of this, we've just relaunched our website outlining our full range of course, services and software offerings.

On top of all of that, the 23rd of June is my 42nd Birthday. What a great opportunity to celebrate with all my APEX friends!

So, if you're there, stop by the booth and say "Hi". I look forward to seeing you all there.

The trouble with DBMS_XPLAN...

As some of you may know, I used to be the Product Development Director for Hotsos. Part of my job entailed work the Laredo product, which helps identify potentials performance problem areas when making changes to your database structure, indexes, statistics, init parameters, etc.

Recently I was called back in on a particularly sticky problem that one of their customers was having in using Laredo. Without going into a long and arduous explanation, the problem boiled down to the fact that Laredo was using DBMS_XPLAN to emit formatted explain plans to the user and they were coming out wrong.

I suppose you would have expected me to learn my lesson by now, but I kind of expect the Oracle documentation to be correct for the most part. So when we looked up DBMS_XPLAN.DISPLAY in the PL/SQL Packages and Types Reference for 10g, I kind of expected to be able to take the code example and use it verbatim.

Here is what it said:

DISPLAY Function

This table function displays the contents of the plan table.

In addition, you can use this table function to display any plan (with or without statistics) stored in a table as long as the columns of this table are named the same as columns of the plan table (or V$SQL_PLAN_STATISTICS_ALL if statistics are included). You can apply a predicate on the specified table to select rows of the plan to display.

Syntax

DBMS_XPLAN.DISPLAY(
table_name IN VARCHAR2 DEFAULT ‘PLAN_TABLE’,
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT ‘TYPICAL’,
filter_preds IN VARCHAR2 DEFAULT NULL);

…

statement_id

Specifies the statement_id of the plan to be displayed. This parameter defaults to NULL, which is the default when the EXPLAIN PLAN command is executed without a set statement_id clause.If no statement_id is specified, the function will show you the plan of the most recent explained statement.

And even came with an example that showed how to use it:

To display the plan for a statement identified by ‘foo’, such as statement_id=’foo’:

SELECT * FROM table (DBMS_XPLAN.DISPLAY(‘plan_table’, ‘foo’));

So, when we coded a statement that went into the product that looked like the following, I would have expected to be shown the explain plan output for the correct statement.

select * from table(DBMS_XPLAN.DISPLAY(‘LAR_SCENARIO_PLAN_VW’,’82-2476-34’, ‘BASIC’))

But instead what we got was THIS:

Notice that we get two “0” IDs and two “1” IDs. And they are OBVIOUSLY from two different statements! WHAT THE….

Now I know I specifically told it to get me only the the plan where “82-2476-34” was that STATEMENT_ID. Did I get the ordinal position of the parameters wrong? I tried again with the following statement:

select *
from table(DBMS_XPLAN.DISPLAY(TABLE_NAME =>’LAR_SCENARIO_PLAN_VW’,
STATEMENT_ID =>;’82-2476-34’,
FORMAT=>;’BASIC’))

Unfortunately, I received the same result. SO, I decided to go digging into the underlying plan table to see what was going on. I decided to select all rows where the STATEMENT_ID was equal to ‘82-2476-34’ to see if there was a duplicate STATEMENT_ID in the table. No luck.

Hmm... That doesn't make any sense! There has to be something going on that makes DBMS_XPLAN bring back extraneous rows. My search then turned to the extraneous rows to try to find what they had in common with the rows that should have been coming back. So I started looking at the expanded output of DBMS_XPLAN and trudged through the base table examining the individual attributes to see where DMBS_XPLAN might have been losing it's mind.

The search finally lead me to PLAN_ID. If I selected all the records from the base table where the PLAN_ID = 7191, the same PLAN_ID as my original statement, here is what I got.

EUREKA! But WHY would DBMS_XPLAN bring back other statement ID's when I specifically passed in statement id "82-2476-34”? Luckily DBMS_XPLAN is not wrapped so I was able to use SQL-DEVELOPER's debug feature to walk through the execution of DMBS_XPLAN and grab the explain plan SQL.

SELECT
/*+ opt_param('parallel_execution_enabled','false') */
/* EXEC_FROM_DBMS_XPLAN */
id,
position,
depth,
operation,
OPTIONS,
object_name,
cardinality,
bytes,
temp_space,
cost,
io_cost,
cpu_cost,
TIME,
partition_start,
partition_stop,
object_node,
other_tag,
distribution,
projection,
access_predicates,
filter_predicates,
NULL,
qblock_name,
object_alias,
NVL( other_xml, remarks ) other_xml,
NULL sql_profile,
NULL sql_plan_baseline,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
FROM
LAREDO.LAR_SCENARIO_PLAN_VW
WHERE
plan_id =
(SELECT
MAX( plan_id )
FROM
LAREDO.LAR_SCENARIO_PLAN_VW
WHERE id =0
AND statement_id = :stmt_id)
ORDER BY id

The problem shows up very clearly in the generated SQL. The WHERE clause is taking the STATEMENT_ID I passed and getting the MAX(PLAN_ID) and selecting ALL records that have that PLAN_ID. But what if, as in my case, there are multiple statements with the same plan ID?

OK. Well, since DBMS_XPLAN provides the ability to add filter predicates, I'll try call the procedure as follows:

select * from table(DBMS_XPLAN.DISPLAY(‘LAR_SCENARIO_PLAN_VW’,’82-2476-34’, ‘BASIC’, 'STATEMENT_ID= ''82-2476-34'''))

Adding the filter predicate changed the where clause, but not as I thought it might.

...

FROM
LAREDO.LAR_SCENARIO_PLAN_VW
WHERE
1=1
AND statement_id = :stmt_id
AND STATEMENT_ID = '82-2476-34'
ORDER BY id

WHAT THE %*@#? ...

Ok... I walked through the code again a little more carefully and came to the conclusion that this is just a run of the mill code error. Oracle are building pieces of the where clause and pasting them together based on certain criteria, mixing and matching based on what your PLAN_TABLE looks like. When it comes to the part where they paste in the STATEMENT_ID that you pass in, if you haven't passed anything to FILTER_PRED, they forget to paste the predicates limiting the selection to your chosen STATEMENT ID in all the necessary places.

While the resulting WHERE CLAUSE you get by actually passing something in to FILTER_PRED is not technically correct, it does work.

I hope this might save some people some time, if they run into something similar. In the mean time I'll be looking into METALINK to see it the bug is logged, and if not I'll log it.

Happy Coding!

JavaScript and Dates.

If you've ever tried to work with date manipulation and formatting in JavaScript, then you know what a hassle it can be. While trying to solve a particularly itchy problem with dates, I ran across this post by Steven Levithan that everyone should be aware of.

He's put together a very nice little Java Script library that lets you format a JavaScript Date object very easily. The available date format masks have been abstracted in such a way that it is quite easy to make them match what you're used to as an Oracle developer.

So, if you ever have to deal with Date formatting in Javascript, then this is definitely something to check out.

Of Rescessions and Research...

It's been a long while since I last wrote about our grand 5 year plan and a lot has changed; in the world as well as with us.

As for the world, I think we all know what's been going on there; Housing Bubbles bursting, Bank Failures, Bailouts, Elections, the confirmation of the dreaded 'R' word (a year after the fact, I might add) and most recently even the car companies have stepped out of their private Leers with tin cup in hand.

I think everyone will agree that we are living in interesting times. I don't know many people that have not been affected in some way by the recession that we are currently experiencing. I've got friends who have lost or are in danger of loosing their jobs, employees that I've seen let go, and small businesses that I know are fighting what looks like a losing battle.

The economy is sucking wind and that specific fact has made us look very hard at both our short and long term plans. Don't get me wrong, the long term plans for getting out of IT and into the business of being a professional host are still on the table. It just may take a little longer to get there.

You see, a large amount of the capitol for this plan was supposed to come out of one of 3 places; 401k/Investments, House Equity, Cash Savings. If you have any view into the markets, you know that 401k's are now 201k's, home equity is in the basement, and cash savings are something to hold on to for dear life in fear that one might end up as an unemployment statistic. It's fair to say that life as we know it has gone into a deep hibernation and we're all in what my friend and current employer, Gary Goodman, calls "hunker-down mode".

That having been said, that has not stopped me from doing more and more research. I've read more and more about Costa Rica, and it is still as tantalizing on paper as it has always been. One particularly interesting piece of literature I've picked up is How to Buy Costa Rica Real Estate Without Losing Your Camisa by Scott Oliver. Very useful and well presented, it gives you the benefit of experience and research that would be hard to gather together in a short time. Scott's web site ( www.WeLoveCostaRica.com ) is dedicated to helping people make the right decisions about moving to Costa Rica.

Another avenue of research has been in the realm of what it costs to run a small hotel in CR. I've been working with a very nice young man by the name of Kent Thompson from 2CostaRica Real Estate who has helped me get about 3 years worth of running financial data for a small 12 room hotel in our preliminary target location of Quepos/Manuel Antonio on the west coast of Costa Rica.

With this information and information on their occupancy rates for the same period I've managed to create a dynamic model that allows me to adjust the number of rooms, the occupancy rate, the average room rate, and see what type of break even/profitability a given hotel might have based on history and projections. It also takes into account putting in place a Mortgage on the Hotel and paying that off as part of the cash-flow plan.

The bottom line is that after doing the math and understanding what we would likely be left with as "take home" after paying the bills, we are in need of more research. We need to understand what the real cost of living is in Costa Rica. We need to get a handle on not only the cost of day to day things, but exactly what those day to day things really are in when living in Costa Rica.

Moving anywhere like this is not only a change of location, but will constitute a huge change in lifestyle. Are we ready for that? Is it the lifestyle we want? Will we actually like the country once we see it in person. All questions to be answered.

So, the next steps? More research! This time fun research! We plan to visit Costa Rica sometime in the spring of 2009 and spend about a week there traveling the country and seeking what we can of the different areas. If we like what we see, then we'll likely plan more fact finding missions and experience more of what it has to offer. If not, then we'll have to think long and hard about what next.

I'll keep you posted.

ApExPOSED 2008 - T-1 day and counting or "Don't mock the afflicted... They suffer so!"

I'm sitting in the lobby of the Chicago O'Hare Wyndham hotel, waiting for the receptionist to signal that my room has been cleaned and is ready for occupancy. In the mean time I thought I would have a little rant about bus drivers.

I have noticed that, an inordinate amount of the time, shuttle bus drivers share a particularly obscure and annoying disease. This disease lies frighteningly dormant right up to the point they turn the key and begin to accelerate. Then some kind of bus driver specific epilepsy seems to grasp hold of them sending their right leg into convulsions. These seemingly uncontrollable convulsions causing their use of the accelerator to become spasmodic and pulsing, sending the bus into mild thrusting shudders similar to that of a boat bouncing mild waves.

In some drivers the epilepsy is so severe that the bus lurches forward every three seconds, bringing your lunch up to your throat and making you wish you'd packed the Dramamine you usually only bring when heading on a cruise. God help those that are easily affected by motion sickness, which thankfully I am not!

My shuttle driver from O'Hare to the hotel had one of the most pronounced cases I have ever experienced. From my seat in the rear of the bus, I could actually see not only his foot, but his whole leg twitching and bouncing on the accelerator. I was not the only one that seemed to notice. Shortly after the bus left the relative safety of it's parking space, the slight framed woman sitting a few seats ahead of me grabbed the back of the seat directly in front of her and remained white-knuckled until we arrived at the hotel. As I departed the bus her husband was gingerly trying to reassure her as he dug her fingernails out of the back of the seat.

I don't know what this disease is called, but its spreading and it must be stopped. I'll be talking to my doctor friends once I get home to see if there is a foundation to which I can direct these poor souls to for help.

Carl Bacskstrom

I did not know Carl personally as I only met him once or twice, but I have dealt with him via e-mail and online for quite some time.

His loss is tragic and he will be sorely missed by the Oracle and APEX Community.

All of our thoughts are with his family.