Category: Oracle

October 1st, 2010 by Mathias

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".

Posted in OOW, Oracle, Performance

September 29th, 2010 by Mathias

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.


Posted in APEX, OOW, Oracle, Performance, SQL

September 23rd, 2010 by Mathias

Wednesday starts off with a presentation about a new feature in APEX 4.0 that I have not had a chance to look into. It is how you use and develop plugins. The presentation was held by Patrick Wolf who is also the developer of the plugin feature.

Scott began by showing how a plugin is used (that is using a plugin compared to developing it).

You install a plugin by selecting plugins in the shared components section, and then it is just a standard APEX import.

Using a plugin has the benefit that it is declarative while using javascript directly requires codechanges and explicit inclusion of necessary code on every page it is used on.

Once installed, the use of it is identical to using other supplied item types in APEX. It is like the plugin was part of the product.

The productivity is much better than it is with just using javascript as once you have the plug in it is just a matter of selecting it to be used and not having to change code to match what you need.

There are already many plugins available and more are added all the time.

Patrick recommends starting with finding a JQueryplugin that performs the action you want and then truing it out on a static html page. When that works, you're ready to create an APEX plugin for it.

When developing an APEX plugin, you need to follow certain API guidelines to make sure your functions has the signature they are expected to accept.

Patrick them moved on to showing how a plugin is developed.

You create your plugin in shared components and then it is a matter of providing calls to the plsql code you write to generate the html and other things that are needed.

Patrick finished up with some recommendations for developing plugins and they are similar to what you would expect in any weboriented technology.

Always escape all data, both from the database and from the browser. Not doing that opens a risk of criss site scripting attacks. There is a function available for this, I believe it is sys.htf_escape_cs (possible typo as I wrote it from memory after he moved on to his next slide).

Reference apex.jquery in the code to make sure the apex supplied version of jquery is used.

Do not trust client side checks on data as the can easily be overridden. The same checks has to be performed on the server side also.

Look at Oracles Best Practice plugins to get a feeling for what to do and how to do it. They have lots of comments to guide you.

Use the packages apex_plugin_util, apex_javascript, apex_css.

Test your pliugins with more than one item using it on a page.

Patrick recommends avoiding inline css and javascript when it becomes a lot to make sure the page loads faster.

Patrick finished up with reminding us to share plugins when it is possible to do so.

The only other presentation for the morning was a handson allowing the participants to repeat the steps that Patrick had just done. As with many handson exercises it was too much of following a set of steps with instruction to click here or there and to type this or that into different places. Though useful it is not creative enough to allow me to fully understand the process. That can however be done some other time when I have a need to develop a real plugin.

Posted in APEX, OOW, Oracle

September 22nd, 2010 by Mathias

The afternoon was kicked off with a presentation on using APEX in large projects by Dimitri Gielis. These re my notes for what Dimitri told us.

Many people feels that APEX cannot be used for anything larger than a singe developers app. 

Sumneva uses EC2 for both demo and development work with their clients.

They have readymade scripts to create new projects that sets up filesystem directories, APEX application, and subversion.

They're finding that the agile project methodology works very well for APEX-projects.

To automate testing, they're using Selenium. I find the number of times this product mentioned at OOW interesting. It is a product we have on the list of technologies to know/learn at work, but most people I've talked with have not been aware of it. I guess it differs between markets and it may also be a tool that more advanced web-developers are more familiar with.

To check consistency in a large projects with many developers, they recommend to use the APEX advisor to get a report on things to look into.

Sumneva has created their own PM tool to track tickets with. It is aware of APEX specific things such as the url to the app and the alias of the app.

Using the feedback features to collect data from the users works very well for them in their projects. They use the provided features when possible, and a custom version when they cannot access production environments that allos the users to log data into an application on their servers.

Tools they find useful includes:

  • Selenium
  • Firebug
  • SQL Developer
  • Subversion
  • Jira or other ticketing system
  • Reusable APEX components
  • Publish defaults to apps via a master app
  • Use interface defaults

While the presentation didn't really tell me what it takes on the technical side to run a large project, it was clear that it is very possible. But, as with all large projects it requires planning for both functional and technical aspects of the project.

The next presentation was moving from mod_plsql in Oracle E-Business suite to APEX. The reason it is needed is that mod_plsql is not supported in the latest version of ebiz.

Unfortunately the presentation focused so much on the solution they had and the specifics of what they needed that it was hard to extract any APEX specific knowledge from the presentation. Most of the presentation was centered on the fact that they wanted to have SSO between ebiz and the APEX application and how they needed links to go from ebiz to the apex application so it felt like one application for the end-users. They succeeded with that, but the presentation did not provide enough details for what they did or even what they main hurdles they encountered was.

Posted in APEX, OOW, Oracle

September 21st, 2010 by Mathias

This will be a APEX only day. The day starts with two APEX 4.0 presentations.

First up was a presentations about dynamic actions in APEX held by Anthony Raynor.

Dynamic acttions is a declarative way (in 4.0) to use javascript to enhance clientside behavior of an APEX application. It is used for things such as disabling some fields on the page in some situations.

Using it is done by defining four things:

  • When
  • Condition
  • Action
  • Affected elements

For example it coould be used on pageload to disable fields it a checkbox is nog checked and then it could be applied to any number on items.

The presentations wrapped up with showing some nice demos on how the dynamic actions can be used from display to setting items to values in sosme conditions. One neat way to use it was to use it to delete rows in a alist without having to do a pagerefresh for everey delete.

Anthony has a nice sample application that I think are available on OTN that shows how to use dynamic actions.

The next session was on tabular forms in apex.

Tabular forms are used to make updates to multiple rows shown in a list/grid. The presentations was a fairly basic review of what can be done with tabular forms.

Some new features were shown. One of the nicer is that errors on multiple rows are now highlighted and an error per row is dispslayed on top of the page.

Other improvements are that radio and checkboxes are better supported. FMAP arrays have been introduced to make it easay to map a columnname in a form with the name eof the array containing the values in that columnname. It is alsos possible to make a call in pl_sql to find the array holding the data for a columnname (such as EMPNO).

That was the end of the mornings presentations, more apex fun to look forward to after lunch.

Posted in APEX, OOW, Oracle

June 10th, 2010 by Mathias

I like when a blog I follow links to a blogpost I have already seen and that I thought was a gem. I used to happen quite frequently when Cathy Sierras blog was active, but it still happens. The reason it is nice is that I like it is that if the first post was really good, you can almost guarantee that I will like the next writer I follow that discusses the same thing.

This week it was Seth Godin who wrote about deadlines and why they work even if you set them for yourself. They have to be set publicly, but they do work. I have recently had a couple of discussions at work around it and this blogpost summarizes it and adds a few more points. I enjoyed the fact that someone else has found the same effect on having to set deadlines for themselves.

The post that referred to it was Tom Kyte's where he talks about finishing up his new book by starting to give Seth's blog praise and talk about finding that he is not the only one deadlines work for. One post that both discusses Seth's blog and gives us the news that Toms new book will indeed find it's way to the bookshelves one day is a blog post to celebrate.

Deadlines do work and no you are not special, they work the same way for everyone. If you do not set one and tell someone else about it, you are likely to not be done until after the original deadline you set.

Go set yourself a deadline today for something you really want to achieve/complete.

Posted in Books, Oracle

June 6th, 2010 by Mathias

In case you haven't noticed already, David Peake's blog just announced that APEX 4.0 moves one step closer to become available for download. It is now the release that powers

This is good news as it shows that the release is getting close to be completed. Upgrading is usually one of the last public steps before a release becomes available to download.

Posted in APEX, Oracle

January 10th, 2010 by Mathias

I've just uploaded my latest article. This time it is a look at the SQL keyword CASE. It's power is often not fully appreciated. It is much more flexible and powerful than you may expect from just taking a quick glance in the official documentation.

The article is located here.

Please post comments and questions here.

Posted in Oracle, SQL

January 9th, 2010 by Mathias

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.

Posted in ITL, Oracle, Performance

January 9th, 2010 by Mathias

I have just uploaded an article I wrote almost five years ago to the day. It was my first attempt and I wrote about Oracle AQ which is Oracle version of a messaging product.

AQ is included with the database and has a lot of nice features including full support for XML payloads. Another important benefit is that your messages are synchronized with your DML as both are committed in the same transaction.

You can find the article here. Please post comments and questions here.

I will post a couple more articles that I've written but somehow never got posted to this site. I will write a short blog entry about each of them to get a permanent place for comments about each article.

Posted in AQ, Oracle, XML