Oracle DB Development

OOW 2010 Develop Keynote

Yes, this post is a little out of order as it clearly didn't take place after the Thursday afternoon sessions. I missed it during the conference so I had to catch up on it later on the on demand site. I thought it was interesting enough to write up a few notes.

It was held by Tom Kyte and the subject was "What's new in database development". It'd be more correct to say Oracle development than database development as he covered just about any area that Oracle focuses on.

He started with APEX and spent a lot of time on it. The coverage APEX gets in general sessions and the number of really good APEX sessions speaks volumes about how strong APEX is becoming within the Oracle development technologies.

Tom shared a little background of how APEX was built by Mike Hitchwa when Mike set next to Tom and wrote the initial code for APEX. Anyone that has been impressed with the way bind variables are handled in APEX got their explanation for why. Tom has relentlessly been preaching about how to to it and the issues caused when not done well. So it all makes sense when you hear that it was Tom who wrote the initial code for bind variable handling in APEX.

Tom went on to say that they are seeing a lot of momentum behind APEX right now. The catch phrase he used to explain why it grows fast in popularity was "Easy architecture, easy to deploy, and easy to run".

Tom then spent some time talking about performance of APEX as a lot of people still thinks it is not able to handle enterprise level system demands.

The site apex.oracle.com that is used to run all the applications people create on Oracles hosted apex "test and demo" site has a surprisingly small computer running it. The specifications for it is:

• Poweredge 1950
• 2 Dual core Xeon 2.33 Ghz
• 32 GB Ram
• Cost : $4,300 (in 2007)
• Runs 11G database

Not a very big computer to serve a site that provides support to anone who wants to try APEX.

Tom shared som of the numbers for what apex.oracle.com is used for.

• They had 3.5 million views (pages loaded and processed) last week.
• Distinct users accessing during the week: 3028
• Total workspaces :9062
• Applications: 32776

I'd think most "enterprise" systems would have less demand than that.

Some of the biggest things on apex.oracle.com are:

• SQL Developer updates 800K hits /week
• Pro MED (Prodution system) 770K hits / week
• Asktom 250K-1000K hits / week
• APEX Appbuilder 238K hits / week
• JDeveloper updates 175K hits / week

Impressive to serve all of that from a small(ish) computer.

Tom then shared some key features from the 4.0 release.

Websheets

Designed for the enduser and designed for webbased content sharing. A solution that allows endusers to stop mailing exceldocuments around would be a good thing...

Dynamic Actions

Declarative client-side behavior. It is integrated in the framework. Uses JQuery.

This allows using common javascript function for handling advanced user interface improvements. From validations to drag and drop and more.

Team Development

Allows you to manage the application development process within APEX. This makes APEX able to be used by large development projects. It has built in end-user feedback. When a user has a problem they can tell you directly in the application and it feeds into the team development features.

Oracle APEX Listener

Built in Java.

It is a good alternative to mod_plsql and it is certified against WLS and Glassfish.

After that long piece on APEX, Tom went through som improvements for other technologies.

SQL Developer

The datamodeller is now free. It will now come with the product and will be supported with the database (just like SQL Developer itself).

It has real-time SQL monitoring. It will allow you to see what part of the execution plan Oracle is currently spending time in for a SQL. This exists in Enterprise Manager, but this gives the insight to many more as EM often is just used by the DBAs.

It can now find the SQL trace file and display the contents graphically in SQL Developer. This ought to make trace files much more accessible to developers.

It has support for AWR and ASH reports and has added developer specefic things that are not available in the other AWR/ASH aware tools.

A hierarchical profiler is now also included to allow you to see what functions calls which in a run of PL*SQL code.

SQL Developer now used PL/SCOPE data to show information about where variables are defined and used, This is something that has been added to the database and can alays be accessed with SQL, but it is more convenient to have it hooked into the development tool.

They have also added a DBA Navigator that allows DBAs to access most things they deal with in the database. Most objects (such as extents and tablespaces) can be seen and modified.

An interface for DBMS_SCHEDULER has been added. It is supposedly a much nicer way to configure jobs than to do it by have, which can be both tedious and complicated.

Another neat feature is that auto tracing has been improved to not only allow tracing of a statement, you can now trace two statements and see a comparison report of the tracing so you can compare them. Since this usually has been done with Toms "Runstats" package, I'd assume this feature has Toms fingers all over it.

ODP.Net

It is free and provides native access to the database. It has statement caching (on the client side) and will support TimesTen in 11.2.0.2.

I'm not a .Net person at all , but it sounds as if this provides a lot of benefits both in use and in performance. If you work with .Net, then you will want to take a look at all the things Oracle provides in this area.

Java

There is a Universal Connection Pool for all technologies using connection pools to use. This is supposed to reduce the overhead caused by having several connection pools.

For Secure Files there has apparently been a performance issue dealing with very large files, my understanding from Toms presentation is that it resulted in double buffering. Now there is Zero Copy which sounded as if it would remove all buffering and thereby improve the speed significantly.

Database Improvements

Tom focused on the optimizer and on developer related topics as this was a Develop keynote.

Tom showed how the optimizer could detect a three-way join that was not needed. It was a fairly complicated situation, but the optimizer can apparently completely skip accessing tables now if they are not needed for the end result.

PL*SQL

Edition based redefinition is available in a editions of 11G. Tom thinks this is the killer feature of 11G, and says that it may be the first time the killer feature is available in all editions and with no additional licensing requirements.

It allows live changing of code (PL and views). Live changing of data can already be done with online redefinition and online actions on indexes.

The process now is:

• Create new edition
• Test new edition of the code while users still use the old.
• Two options for rolling out
  • Let new sessions use the new one and allow old to keep using the old edition.
  • Switch everyone over to the new when the system is "idle". Takes just seconds.

That was the end of Toms presentation. He covered a lot of technologies, even some that are not mentioned above (like Ruby). WHat I think is interesting is what wasn't mentioned at all.

The fist one that comes to mind is JDeveloper. It has not become very popular with Java purists and it is not mentioned at all. There are some rumors of it being de-emphasized by Oracle as how you work with it seems to be very far from how most Java projects want to organize their builds and deployments.

Next interesting omission is the technology mostly integrated with JDeveloper. No mention at all on the Oracle Develop for their key Java development framework. ADF did not get any love at all and that is not true just for this presentations, there were very few mentions of it in any of the presentations I attended. True, I did not seek out ADF sessions, but virtually every single presentation on non APEX technologies found a way to talk about APEX, the oposite was true for ADF. Even when you felt that someone was building up to a plug for ADF, they never went there.

The lack of love for ADF and JDeveloper may mean that they are not considered critical to Oracles success anymore. I assume ADF was used to build the fusion applications which would mean that the tool and product is probably not going away, but could it mean that they are primarily being focused for large product development and not something that is good for custom development projects?

Personally it feels as if ADF is now essentially becoming a tool used when integrating with Oracle (fusion) Apps or Oracle SOA Suite. I'm not sure a custom development project would end up using ADF. Of course there will be exceptions to that, but they will probably be fewer and fewer.

It could also be that JDeveloper and ADF starts becoming less and less of something for the customers and more and more of an internal tool at Oracle. I'm sure they have very specialized versions and techniques used in development to help the development of the fusion applications.

On the other hand, it could just be that this wasn't the time for releasing improvements to JDeveloper or ADF. I have trouble convincing myself of that though. The reason is that Oracle now owns Java and if there ever would have nee a reason to show JDeveloper and ADF in its full glory, now would have been the time. By not releasing improvements to what is considered problems with JDeveloper and ADF, they have probably given more momentum to the competing tools. They of course ha another tool in this space now, maybe NetBeans will be the future migration path of JDeveloper and when ADF finds a new home.

OOW 2010 Thursday Afternoon

For the afternoon I had two sessions I attended. The first was "Quantifying Oracle Performance" and the second was "The X-files - Managing exadata and highly available databases". I anticipated both to be great and possible be among my favorites for the week.

Unfortunately neither met my expectation, so this is going to be a fairly short post.

The first one was "Quantifying Oracle Performance" with Craig Shallahamer, I have seen many presentations with Craig and he has always been good and his material very interesting. Even if you do not use queueing simulations at work, having seen his presentations on how it affects performance helps when trying to understand system performance. This presentation was intended to help you build a model where you could show where your solution is today on a response time curve, i.e. where in the famous elbow are you now and where would different changes put you.

Craig showed some parts of how to model your current performance and to use AWR to figure out some numbers to use. However, there was virtually no data on how to model a planned change. I guess you have to implement and test that change with production like load to get the same data and then be able to plot it on the same graph as you created for your current solution.

I think the idea of showing non technical users the impact and how close to instability you currently are in a graphical manner is very good, as is the idea of comparing possible alternative solutions with it. However, I think the participants get only this idea and they have to do a lot of work on their own to make this a model they can use to work with their clients.

So, (sorry Craig) this did not feel like a presentation that is complete at this point. I do believe it could be a great one if it reaches its goal but I don't think it is there yet.

The next one was "The X-files - Managing exa databasemachine and highly available databases" and this was not one that fell short of the goal, it did not even make an attempt at meeting it. Pure bait and switch.

To explain this better here is the abstract

The Oracle Exadata is a complete package of software, servers, storage, and networking designed for all data processing and data management challenges. In this session, experts will walk you through Oracle maximum availability architecture best practices for managing this critical piece of your operating infrastructure through Oracle Enterprise Manager. They will also provide additional guidance on troubleshooting the Oracle Exadata and Oracle high-availability database solutions.

I read that as a session that will talk about how I should set things up snd how to achieve a HA-solution that is stable and can be proactively managed. What I got was a long session about how I should fork over money to ACS (Advanced Customer Support). Yes, ACS is probably great and big and important installations should probably have them as their dedicated Oracle support. However, knowing how to take care of your own solution is what I expected from the abstract and there was non of it. This was instead a one hour long plug for ACS.

I took away two things. The first is that a lot of work has been done to make EM monitor all aspects of an exadata box. It looks like they have a lot of interesting information. Unfortunately access to a lot of it seems to require using ACS and the appliance they put in place to aggregate information. The other thing is that setting up dataguard via EM now looks really easy. Maybe it is time to start using EM for setting up DG.

There was a brief mention of a tool that can estimate the benefit of using exadata based on a SQL tuning set. It was called something like "SPA exa database machine simulator".

OOW 2010 Thursday Morning

The day starts with a presentation b6 Tom Kyte about "What else can you do with system and session data".

Tom starts with reviewing the history of tuning an Oracle database.

The prehistoric era (v5) required writeing debug code as that was the only way to get any information about what the code did.

The dark ages followed (v6) and now Oracle introduces:

• Counters/ratios
• bstat/estat
• SQL Trace
• The first few (7?) v$-views are introduced

In the rainessance era (v7) introduced a few more things:

• The wait event instrumentation was introduced
• Move from counters to timers
• Statspack

The modern era (v10) introduced the tools we prefer today

• DB-Time tuning
• Multiple scoping levels (AWR)
• Always on, non-intrusive
• Bult into the infrastructure - instrumentation, ASH, AWR, ADDM, EM

DBA_HIST_* views is where the historic data is located.

Tom contunued with discussing the metrics and the timemodel and how to join that to get data about them. However, all his presentations are available for everyone and you're better off reading them on his site than getting a summary of it here.

As usual, Tom places his presentations and other things under files on asktom.

The next presentation was Apex debugging and tracing with Doug Gault from Sumneva.

Debug mode is truned off on the app level by default. It neds to be enabled there before it can be used.

Debug data is now written to a table, and not dumped on the webpage as before. It is viewed by clicking view debug in the developer bar in the APEX interface.

Use conditional display to output things only in debugmode (using v('DEBUG') ).

Things that happens on a page after it has been rendered will not be captured. This is things like javascript running on this client.

APEX_DEBUG_MESSAGE is currently undocumented, but the code shows how to use it.

Debugging can be turned on/off on the page programmatically.

Logging from you own code allows you to see what parts of a package call it is that takes the time.

Doug then shows a few different ways to collect and report on the debugdata for a report.

I recommend looking at the presentation for ideas on how to use it and what can be done. Unfortunately this presentation does not seem to be available on the sumneva website at this point. If you are interested in it, send an email to them asking if they are willing to share it on their presenation page. You'd of course want to check their site first to make sure it isn't uploaded between the time I write this and the time you read it.

 

OOW 2010 Starts

I arrived to San Francisco yesterday and OOW kicked off today. The efficiency at the registration alone was impressive. The lines we're very long, but it probably took less than 15 minutes to get through it.

This post will essentially be a dump of notes from the sessions I attended.

The first session was the start of the APEX symposium. It was supposed to feature Scott Spendolini and an overview of APEx 4.0. He had been delayed so the presentation was held by Tim st Hilaire and Doug Gault. It was a nice review of focusing on userinterfaces. Technology is nice, but in the end it is all about how productive the end-user is.

They looked closely at the interface netflix has for managing the movies you want to see and used their own version of it for a troubleticket system. There was quite a bit of discussion about the problem with getting the business involved in prioritizing and how they built the solution to make it easy to get the busines side involved in making the right things prioritized.

The technical solution uses JQuery which is now part of APEX out of the box. They have placed their work in a plugin that can be downloaded from apexplugin.com.

The next session was SQL Developer Best practices. It went through the presenters favorite things with SQl Developer.

It covered a lot of things, the following are the ones I feel I need to use more.

Creating code templates and adding a hot key for activating it would eliminate rewtiting similar code over and over.

There is a way do generate documentation for a schema in HTM and other formats.

It includes a function for monitoring SQL and sessions that will reduce work with querying v$session and related tables to see what is going on in a system.

Unit testing is included in version 2.1. You create a repository and define tests that can be executed both from SQL Developer and from the commandline (ututil).

The third session of the day was "Messed up Applications" with Carly Millsap. It was an interesting session that wasn't very technical for a Millspa presnetation. He reviewed three systems that had been designed to perform poorly. Of course neither was designed that way on purpose, but they were all bult in a way where tuning would not help.

I missed the first whoch was about something he had built himself. The next was a ticket reservation system that spent over a minue to bring back all possible trips a user could go on, when the user almost always chooses the first one. The interface presented him with 2136 trips to choose from. This can of course not be fixed without adjusting how the system interface works.

The last was a ramp anti-pattern for a company that had a system that ended up printing every invoice since day one. So day one it printed all invoices for day one, day two it printed all invoices for day ane and two and so forth. This of course wasted lots of paper and lots of resources very soon.

The last presentation was about trending performance and capacity through using AWR data. The presenter had reverse engineered the SQL in AWS and then used that to bring out data for different times for the same execution or the same SQL over time. It takes some work to do, but it increases the value of AWS significantly.

After that was the keynote, but that will be in its own post.

Interested Transaction List

Interested Transaction List is the method through which Oracle keeps track of which transaction has updated which row in a block. I wrote an article about it a while back where I show how the ITL works and how it impacts performance in certain situations. The basis of the article is a page locking scenario I encountered. Sure, page locking is not possible in Oracle, but an ITL wait is very similar to a page lock. 

You find the article here.

Please post comments and questions here.