FULL SCANs must be bad, SQL Developer Says so.

I've downloaded the evaluation edition of SQL Developer 1.1.
It's great that I don't have to set my NLS_DATE_FORMAT everytime I connect.
BUT, one of the changes to the Explain Plan is that some operations are being coloured red, including Cartesian Joins and Full Table Scans.










1.0 was fine. Nice, simple, black and white.











Using Explain Plan, Oracle tells us the optimal plan that the Optimizer came up with for the query we gave it. It doesn't tell us the second best plan, and keep the best hidden away. It doesn't tell us the plans it rejected. It doesn't tell us "But you'd do better with a TO_CHAR on this to make us of an index" or "The stats indicate you don't have any nulls in this column, but it does allow nulls so I'm having to avoid this index". If you asked Oracle to join a table to itself on the primary key, the Explain Plan still wouldn't say "Are you just plain stupid ?" In short, the Explain Plan does NOT tell us what's wrong with an SQL.

10G has introduced features to make suggestions about how to improve SQLs, for example suggesting new or changed indexes. Those features are not part of the explain plan. The Explain Plan describes what the optimizer decided was the best access path, with the cardinality and costs giving a (vague) suggestion as to why it might have decided that. The 10053 event can be used to obtain very detailed trace information on why a particular plan was chosen ahead of other plans.

A developer (or DBA) can look at an Explain Plan, and (if desperate) the 10053 trace, and use their knowledge of the business, the application, the data and the requirements to see if Oracle has made any incorrect assumptions based on insufficient data.

Quite simply, you cannot look at an Explain Plan and, without any additional information, say "This plan is BAD". Worse is the concept of saying "This operation in this plan is bad". The plan is the sum of the operations. You can't just excise one and pretend that there's a magic way of doing that process for free. Even worse than that is saying "This operation is pretty much always a bad idea."

Sticking a highlighter over the Explain Plan is trying to turning the very complex process of query optimization into a childs game of follow-the-leader and it won't work.

"FULL SCANs must be bad, SQL Developer Says so."
I say NO, and I'll keep a SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) in my snippets folder.

Melbourne AUSOUG Day 2

I finished Thursday with Connor's presentation, and started Friday with another, 'Being a successful Oracle developer'. As I mentioned, I've got Connor's Mastering PL/SQL book, as well as Tom Kyte's Expert One-on-one, Effective Oracle Design and Expert Oracle Database Architecture. I also follow AskTom as well as I can. So I knew 90% of what he presented, but it was well structured and ANYONE taking on an Oracle project would benefit by making everyone on the team attend this presentation. As soon as the conference papers become available, this one is getting printed out and attacked with a highlighter for use a reference.

Marcel Kratochvil's talk veered wildly between AJAX and the PL/SQL preprocessor (or conditional compilation, if you prefer the term) but worked well. I like the concept of AJAX (and anything else that might mean I don't have to learn Java) and referring to Application Server as a legacy technology sounds interesting.

After lunch, I tried Anjo Kolk's talk on cursors and the shared pool, but it was a bit over my head, probably because I'm a developer not a DBA. I think I grasped the issue (that cursor caching isn't always appropriate) but not what I'm supposed to do about it.

Then Tony Jambu woke up everyone who attended his 'Database Hacking - Is your database safe ?" talk. I keep an eye on Pete Finnegan's blog and forum, so wasn't as shocked but probably just as scared. Hey, these databases have OUR credit card numbers !

Finally another Connor talk on 'Odds and Ends'. I was tempted by more on 11g Availability by Mark Townsend, but with 11g the best part of a year away, I figured I'll find out more details closer to the time.

There were other sessions I wish I could have attended. However I'll have to make do with the conference papers (or winning the lottery and flying to Perth for the West Coast version at the end of this week).

Melbourne AUSOUG Day 1

I've been quiet for the past few months, mostly due to a lack of time. However to try to kick things back off again, I'll do a report on my attendance at the AUSOUG conference in Melbourne last week.
This isn't on the same scale as OpenWorld, with attendees measured in the hundreds rather than tens of thousands, but it is the closest I'll come on my budget. Being a contractor, no-one else will pick up the tab for events like these. However it is an opportunity to find out what's happening in and around Oracle, and the conference does manage a few Oracle 'names'.

Mark Townsend presented on Oracle database's future, and I was interested in heaing about 11g. Like OpenWorld, there was the disclaimer about 'what you see and here isn't guaranteed to actually ever turn up', which is fair enough. However the big ticket item does seem to be the 'editioning feature' for upgrading applications without downtime. It will be interesting to see how that pans out, but also how we might need to cater for it from a configuration/change/version management point of view.

The other nice feature is the SQL and workload 'replay' that will allow for some decent performance testing/impact assessment.

For the next session, I considered 'Understanding Oracle RAC' in the DBA thread, but the session prerequisites included an....'understanding of RAC', so rather than get caught in a recursive loop I opted for the 'Rough Guide to Data Warehousing', so I may still be a bit lost on what an interconnect is, but I now know that a slowly moving dimension isn't something requiring Captain Kirk and a pointy-eared science officer.

After lunch, I went for Penny Cookson's Apex hands-on workshop. A an ex-Forms developer trying to keep away from Java, I like the sound of Apex, and this was a good introduction. I've still got half the workshop guide to finish off, and then its just a matter of thinking of the snazzy little application I can build to make me into the next Youtube.

I finished off the day with a Connor McDonald talk. While I've got his book, I'd guessed from his 'co.uk' domain name that he was based in the UK. Apparently he works out of Perth in Western Australia. His presentation 'Once around the block' was a informative talk on the contents of the Oracle database block. From a developer perspective, I may not USE anything I learned there, but I LIKE knowing, for example, that the data block points directly to the 'undo' block that reverses the last change (which will in turn point at the one that reverses the previous change) and how the transactions are actually listed on the block.