Friday, April 29, 2011

ORCAN event

Yesterday I attended an event organised by the Swedish Oracle User Group (ORCAN). They picked a real nice venue for this meeting: the top floor (54) of the Turning Torso. From this top floor you have a stunning view over the Oresund (all the way to Copenhagen) and Malmö and it's surroundings. Might be a little bit distracting from the presentations, but nevertheless...
There were two concurrent presentations. The first one I attended was by Moren Egan about Edition Based Redefinitions. As I presented about the same subject at OOW and UKOUG, I was just curious about how he would present this material. More SQL*Plus oriented as I did it, but I think the message came across just fine. The second one was by Randolf Geist, called "Advanced Oracle Troubleshooting". In fact it was just the first half (the second half was after lunch). Although I could grasp most of he said, some details were a little bit over my head. So I skipped the second part and went to SQL Developer New Features by Sue Harper. I was already familiar with most of the features, but it's still worthwhile to renew your insight about this product. It is growing with every release!
Then it was my turn wih APEX 4 New Features. To my surprise 75% of the audience had no experience at all with APEX - so all features are new to them! But I emphasized and spent most of the time demoing the cool Dynamic Actions and Plug-ins. Just to show how you can create stunning results with almost no coding! After a short coffee break I presented about Charts & Dashboards. This time I had alomst all attendees in my class, as the "other side" only had 4 (sorry Sten). All went pretty well I think, but I have to admit, doing two presentations back to back is a little bit exhausting.
Then it was time to leave for dinner in the center of Malmö. When I got my jacket I noticed my pockets were empty - and I was pretty sure I left my car keys and phone in there (which isn't that smart, I have to admit)! So a little panic broke out. We contacted the reception, searched the whole place - but nothing found. But then I noticed that there was something else missing from my jacket: It had just the Oracle logo, and mine had Oracle ACE Director logo! So it wasn't mine! A quick thought whose jacket it could be...and it had to be Sten Vesterli. And Sten was on his way to Copenhagen... We phoned him, but got his voice mail. So we went to the restaurant...and there was Sten with my jacket. While walking to the station he noticed there was a phone in his jacket (or my jacket) and another one in his pocket. So he also concluded he had taken the wrong one - and decided to go to the restaurant to exchange jackets. A total relief for me. Thanks Sten - not for taking the wrong jacket, but for returning it ;-) !
We had a nice dinner outside on the square. This little square is packed with restaurants and everyone is sitting outside. So they have a lot of heaters (and I mean really a lot!) and blankets for everyone. So I think global heating is for a major part caused by the Swedish... ;-)
All in all a nice event and I am looking forward to the next one!

Saturday, April 16, 2011

Travelling is not always fun!

At Orlando airport my original flight via Detroit was delayed by three hours, due to "technical issues". So they rebooked me via Minneapolis. But that one was delayed too, due to "weather circumstances". But as that would affect all planes, the advice was to keep that route. And indeed the initial delay of 1,5 hours (I had a layover of 1,5 hours...), was reduced to 45 minutes when departing. But when the plane rolled back from the gate, the right engine didn't started. They had to replace the fuel filter. So it went back at the gate, they did the replacement and a half hour or so later we where again ready to depart. So rolled back again, but at that moment all electricity went down, because the third engine (used for electricity when the other engines aren't running) went down. Do they had to reboot the plane or something like that (did you know the in flight entertainment system runs on Linux?). So, again 30 minutes later we departed. At that time I had a scheduled layover in Minneapolis of -30 minutes. During the flight we could check the flight statuses, using the on board wifi, and the plane to Amsterdam was scheduled with a ... 30 minutes delay! Arriving in Minneapolis we had to wait for 10 minutes for a free gate. I got out of the plane as one of the first passengers, and ran all the way from gate G18 to G4 - which is a pretty nice exercise, even with the help of the moving sidewalk. Arrived at the gate on the exact (re)scheduled departure time. I got in the plane as last. They literally closed the door right behind me.
But then the plane had to be de-iced (hard to imagine when you're coming from Orlando!). But there seemed to be only one de-icing thing available at that time, 11:30PM, so we had to wait for nearly an hour...
Fell asleep very quick and stayed that way all across the Atlantic. Landed in Amsterdam just 2,5 hours later than my original schedule. So that was not too bad.
So now I am back in The Netherlands...but where is my bag???

- Posted using BlogPress from my iPad


Friday, April 15, 2011

Collaborate 2011 - Day 5 and Wrap Up

The last (half day) of the conference. Makes me wonder why most (if not all) of the conferences add an additional half day after throwing "the" big party. Why not make it a full day and throw the party at that night - or just end with the Wednesday night party. That will keep everyone at the show. Because these last half days are usually not very well attended anymore, which is not very kind to the speakers.
Anyway, the first session of today was Obscure Tools of the Trade for Tuning SQL. The speaker - after handing out, or throwing, candies, discussed tools that are freely available to get insight in how your SQL runs. The first was not that obscure: DBMS_XPLAN. This package can show all kinds of details on your SQL and can even show AWR reports. And if you use it with the gather_plan_statistics hint, you can also show the expected rows vs the actual rows (so your statistics may be wrong). The next one wasTrace Analyzer, a.k.a. TRCA, available via Metalin. It is like TKPROF on steroids. The main advantage is, you can provide your developers access to this information without access to the udump directory or bothering the DBA. It generates nice HTML readable and navigable output.  Another one was SQLT (SQLTXPLAIN), also available from Metalink This tool understands DB-links, while TRCA does not. It has multiple methods, like xtract, xecute etc The output is like the TRCA output, but TRCA needs SQL, this one needs trace files. TRCA gives a broader view, SQLT more detailed. The last one discussed was Oracle Active Report for Real Time SQL Monitoring. You can use this by using the dbms_sqltune (11gr2) package. It returns HTML, containing Flash or not. So it generates very nice, OEM like, output (with or without using OEM).
Then Rich Niemiec did a very nice presentation about Trends in de Database World. Did you know one Oracle Database is capable of storing all, yes all, information on the entire planet if we only had the hardware? So, that's cloud computing to the max! And another remarkable thing (I've heard before, but still): The development of Fusion Applications is the largest project in the world! The three major trends Rich discussed where Consolidation, the lower cost grid using Linux and Globalisation. Of course they all come together at the end. An nice to know : Oracle's original name was SDL.
The last (real) one of the conference was about SQL Anti Patterns. It appeared to be a MySQL focussed session, but still a lot of the common mistakes made in the MySQL world also apply to the Oracle world, like these:
1. Check if a row exists before insert. You can end up with a race condition, two people might try to do the same thing (apart from that, it is an extra database action). So instead of that, just do insert and catch the error.
2. All string columns have the same, to large, size. Might take too much space for tables and indexes and arrays. So right size your strings. Another solution for reducing your indexes: use the first x characters of a column.
3. The assumption that a select without order by returns rows in order of primary key. Not true, not in MySQL, not in Oracle. Can be true if it is an "index only" query. There is no "natural" order. So if you need a specific ordering, specify that.
4. Commit and rollback in lower level functions. These may lead to unexpected results like half transactions. Ue these only on the highest calling level - the service layer, thus in the application itself.
The last session of this Collaborate was Iron Application Throw-down. Two teams had set up a solution for creating a session planner. The most striving thing was, both uses Oracle Application Express as the preferred front-end! I wonder why....

All in all, Collaborate is for 80% an applications event. So most of the session are aimed at EBS, Peoplesoft, JDEdwards etc. The same holds for the exhibitors. There is some DBA stuff going on (around 10% I guess) and the other 10% is Development, split by regular SQL and PL/SQL and APEX. No Forms, Designer and - even more remarkable, hardly no ADF or other Fusion Middleware!  So if you are a developer, the first event I would recommend, is ODTUG's KScope - see you all there!

Collaborate 2011 - Day 4

The fourth day already...starting of with APEX, Tales from the Trenches by Paul Dorsey. Paul presented the results from a number of interviews (via email) he did with APEX users. He picked the people that presented on APEX somewhere (like me) and asked everyone things like, what do you like about the tool, what not, what's easy, what's not etcetera. And he presented the results of all this with his own opinions on top of it. One remarkeble quote of Paul (so don't shoot me, I am just the messenger): "APEX has succeeded, ADF has failed". This based on the "facts" (as Paul sees it) that most ADF projects fail and almost all APEX projects are succesful. Apart from that: The APEX community is growing, while the ADF community stays on the same level - but that probably will change when Fusion Apps will take off. The bottom line of his talk was: there is something way better than all of this and that's my product "Formspider". But alas for Paul, his product fills in some disadvantages of APEX that nobody sees (or even bothers)...but Paul himself.
After this session, Scott Spendolini, David Peake and myself had a talk about it - and some enhancement requests for APEX 4.x. So I missed the session I originally had planned to attend.
Next I went to a session about Oracle VM. With Oracle VM you can run multiple different guests, containing different operating systems and/or database versions on the same hardware. Good for short term labs situations: Easy and quick to set up, run your tests and destroy the VM. The VM can be managed using Oracle Enterprise Manager. O, yeah, did I already mention it was free? It is not similar to something like VirtualBox, because when you use Oracle VM, that VM is the Operating System (and Windows or Unix is just a guest). Great for training, testing, patching, trying out new technology and products. As a result you can apply patches faster and more frequently in production, just because it is easier to test them..
Just before lunch there was a 30 minute session about Dynamic SQL in APEX. Usually only used for reporting. Tip: Use apex_application.debug( ) within the dynamic part to show in debug mode what's been generated. Can make your life much easier. Alas, dynamic SQL doesn't work in Interactive Reports. A solution is using collections (another solution is using pipedlined functions, but the presenter didn't mention that. Getting the column names right might be a bit nasty though, but you can even use a function that returns those column names.
Scott Spendolin presented on Security in APEX. I think I've seen it before, because it sounded all very familiar - but you can't here it often enoough! Scott talked about multiple security issues and several ways to protect you against it: Be aware that the danger comes from within... First, create a "shadow" schema with read only views on views owned by the actual "data" schema. All DML should be executed using procedure calls, so you have to change the processes in the pages (another solution is: create a regular view with instead of triggers that use the API calls). The framework for these calls can be generated automatically within APEX. Be sure to revoke all privileges from the shadow schema (maybe not at first, but certainly in production) - as a side effect you can't use supporting objects anymore, as the schema has no rights to install them. And add system level triggers to prevent dropping objects. Another tip is : Reduce data access using context settings in every query. 
Back to back, Scott had another session about APEX Maturity. He walked us trough the whole history of APEX, from conception until where it is now. Interesting and especially entertaining.
The last timeslot was for my own presentation on building a Plugin for Google Visualizations. As I had noticed that the audience wasn't very mature yet, I had to change it a little on the fly - as only one person in the audience had ever used a plug-in, let alone build one! Afterwards I think I should have taken a complete different direction, by just showing them what a Plug-in can do for you - and skip the build stuff all together. But that's aftertalk. But they where amazed by the updateable Organizational Chart of course...sub-zero!
After all that, and some chilling down, Universal's Island of Adventures was at our disposal! So with no lines at all (instead of the regular 2, 3 + hour waiting lines), we could enter any attraction. And the most spectacular one, without any doubt, is the Harry Potter ride (check it out on YouTube). It's a sort of rollercoaster ride, with movie projection and a ghost house. Never seen something like that. Spiderman was also good (a little "lighter" than Potter), but a combination of a 3D movie and a rollercoaster like ride has it's effects...

Wednesday, April 13, 2011

Collaborate 2011 - Day 3

Day 3 started out with a 8 AM (!) presentation on Mobile Application Development. Didn't cover any APEX specific details, more about the general things to consider when developing these kinds of apps. Like how to handle multiple platforms, like IOS and Android - and over 10 others. Native apps are preferred above browser apps (and any APEX app will be a browser app). The solution for handling all these different platforms is to use a framework (like jQuery Mobile). For data integration, you should use webservices in order to keep the bandwith usage as small as possible.
The next session had the (lomg) title Enhancing your Data Warehouse Data Completeness using APEX. The presenter showed a (very simple) application to authorize and monitor Excel uploads and changes made to the master data of a data warehouse. Nothing very new...
Then David Peake presented on APEX integrating with E-Business Suite. Since v12, EBS doesn't support mod-plsql extensions anymore. But, luckily, there is a solution for that. You have to run EBS 12.1.3 and an additional patch, but then you're there.Recommended is you should set up a separate webserver and listener for APEX (using Glassfish and APEX Listener), and not use the APPS scheme for the workspace, but create a separate one with access to only the APPS objects needed for the customizations. All the details are in this whitepaper.
Last before lunch, there was a 30 minute session about APEX for DBA's. The message here was to kill all the spreadsheets and bring them over to APEX. Isn't always easy, but still worthwhile. The funny thing is, once the spreadsheet is turned into an Interactive Report, users downloaded that one Excel..sigh.
Then there was a 3 hour break. Meant to spend time in the exhibition hall, but I thought the pool was a better place to be!
After that, I attended the session Developing Multi-client applications with APEX. In the presented solution multiple applications existed that are used by multiple clients - without seeing eachothers data. One master table (with the "client" info) was the parent to all tables - or the grandparent. And add an addtional line to the where clause of all queries. (I would have implemented the "client-id" in all tables to make it easier and use the VPD-properties built into APEX). Some smart customization stuff though: Using replacement strings (like &XYZ.) for customizing Tab labels, URL's and even colors and region widths in the templates!
Last session of today was the APEX Panel. Together with Scott Spendolini and David Peake, we tried to answer the questions from the audience. Good questions came up and we even ran 15 minutes out of time.
After the exhibition hall drinks, I attended the traditional ACE dinner. Good food and pleasant company!

Monday, April 11, 2011

Collaborate 2011 - Day 2

The second day of Collaborate started with a opening general session by Dan Thurmon. Dan is a so called "motivational speaker". And he sure knows is job. In a very American style he promoted his dogma:"Off Balance, On Purpose", demonstrated with juggling (even with axes on a one-wheeler). A very funny start of this day!
The first "real" session I attended was SQL Techniques by Tom Kyte. It was all about Clustering, setting up Index Organized Tables (IOT's) and Partitioning. The goal of all these techniques is to reduce IO. A nice metaphore he used was: You can put your clothes in a closet by just dumping them on the first free spot you see. So inserts are fast, but then retrieval trakes a full scan of your closet. By clustering pants, sweaters and socks together, inserts may be slower, but retrieval is way faster! But not only picking the right storage approach is important, also the retrieval - like using bulk/array fetching - are both important to reduce resources and increase performance and thus scalability. Don't tune queries, tune your approach!
Next on the agenda was Analytic Functions - Revisited. I am already familiar with this technique, but there still are some hidden gems to explore, like the package dbms_frequent_itemset. This package contains functions to find correlated data. I have to check that once. Also a warning: If you use analytic functions in a view, that might prevent the use of indexes.
The last before lunch was called Why BPEL when I can PL/SQL?. It was a 30 minute power session, clarifying when BPEL might be a better choice over PL/SQL. If you need advanced Fault Managemenrt, using multiple non-Oracle techniques (like SQL Server, DB2 etc) or Human Interaction, BPEL has advantages over PL/SQL.
The first presentation after lunch was Tips and Techniques Integrating Oracle XML DB. Due to some (or a lot of) technical difficulties the presenter couldn't do his demo in SQL Developer and had to stick to the slides - which contained lots of code samples. Couldn't bring his point that way. 
Metadata Matters was originally a Tom Kyte presentation (I think I've seen it before), but now done by one of his Oracle colleagues. Very well done I must say. The point was: Put as much (meta)information into the database as you can, and let the optimizer decide what to do with it. The more it knows, the better it can do it's job! So put in all constraints, and even datatypes are constraints! And also "size does matter". If a developer defines all strings as varchar2(4000) - just to be sure - then the amount of memory needed for returning a result set is way bigger than when you specify the real lenght. Check constraints can be used by the optimizer to rewrite queries - and reduce IO and speed up performance. If you define columns as NOT NULL you create more access paths for the optimizer, like using and index scan instead of a table scan. You can even "tweak" this for columns that can be NULL, by appending a fixed value to thee column value when creating the index - just to be sure every row is represented in the index.
Referential constraints can be used to remove tables from the query plan. And constraints can also be used for applying a query rewrite (so a materialized view can be used for delivering the result set instead of scanning the original table(s)). 
Most important: Java applications shouldn't handle all integrity and rules, because they can't do it as fast and as good as the Oracle database does that job. Don't try to reinvent that wheel, Oracle has over 25 years of experience in that field and it's all at your disposal! So don't think you can do better within the timeframe of your project!
The last presentation of today was Giving Winning Presentations by Rich Niemiec. Funny, entertaining and with lost of tips on how to make your point during a presentation. I don't like his slides though.... ;-)
Now off to the Welcome Reception!

Collaborate 2011 - Day 1

Travelling to Orlando went smooth. Very smooth even. The first leg to Boston arrived about a half hour early and as I was sitting right in front of the Economy class, I could leave the plane as one of the first. So I arrived at the border securty as the third non-US citizen. So checkin in the US costed about two minutes. I even arrived at the luggage belt before it actually started running - usually my bag is already off the belt when I arrive there. After an almost two hour layover, in the second plane I managed to get two seats at the exit row. So I could move wherever I liked. And at Orlando airport a taxi was immediately available so I arrived at the hotel around 6:30 AM. After some real American food (burger, beer & fries) I hit the sack around 10. 
The next morning I managed to stay more or less asleep until 7. From 9 to 12 I sat at the pool until it was getting too hot and went to the registration of Collaborate.The conference is in the Orlando Conference Centre. I thought Moscone in San Francisco was a big place, but this is way bigger. The staff rides around on Segways...
Today there was only one session on my schedule : "Fusion, Soup to Nuts". The whole idea was to show all the technical details that's used for building Fusion Apps, by a number of different presenters. I think the idea is quite good, but the operation could be better. Alongside that, the projector screen was way too small for that big room. And one of the speakers couldn't resize his resolution, so his demo was unreadable for everyone who wasn't at the front row. The sequel ended with a presentation of Fusion Applications (by Debra, who else). It even contained a semi-live demo - a.k.a. a viewlet. As Fusion Apps isn't available yet, they weren't allowed to do a live demo. But, I have to admit, the user interface looks very, very slick!
This first day of Collaborate ended with a reception, with some food (pizza slices etc) and drinks. I bumped into Borkur and his colleague Peter (from RittmanMead) and also met Gwen and a colleague of hers (from Pythian).
All in all a nice first day - looking forward to tomorrow as the "real" conference starts!

Tuesday, April 05, 2011

My Schedule for Collaborate 2011

When there is no eruption of an Icelandic vulcano with an unspeakable name, I will be flying out to Collaborate 2011 in Orlando next Saturday. I am really looking forward to this, as it is (still) my first Collab event!
Using the Schedule Builder I also created a (preliminary) schedule for next week. There are a lot of good sessions to choose from. I first picked all (or almost all) APEX sessions, and then added the other promising sessions. A lot of SQL and PL/SQL and some Fusion stuff. My own session is schedule at Wednesday afternoon, just before the big party at Universal!
I will (try to) write a daily blog post covering the details of the sessions - especially for all those who can't make it to Florida...

Friday, April 01, 2011

This years best gift: The Expert Oracle Application Express book

Last year, during ODTUG's Kaleidoscope in Washington, John Scott asked me if I was interested in co-authoring a book on APEX. And it wouldn't be just another APEX book, but a book written by the top presenters/bloggers on APEX. So I was deeply honoured...
But the best thing, all revenues will go to the memorial funds of two great guys of the APEX Development Team: Carl Backstrom and Scott Spadafore. (For the complete story, see John's blog). So I immedtiately said: Yes, sure!
And now the book is almost there - should be released somewhere in May. So if you're slightly interested in APEX or an APEX geek: This is THE book to order (click here). All authors wrote one chapter on an area that they are expert on, and the goal was to write stuff that has never been written before. So the book contains not a lot easy stuff, but more the complex / think-out-of-the-box kind of stuff (with lots of code of course!).
So there are chapters on: Security, Configuration Management, Charts, Team Development, Tabular Forms, Globalization and Plug-ins (and this is not even a complete list). If you're familiar with the authors you can probably map the chapters on the authors...
So get your hands on it a.s.a.p. You will not only learn tons of new stuff, but also support a good cause when buying it...