Oracle Multimedia Blog

OMMUD Website

2011-10-02T03:49:00.002+11:00

The Oracle Unstructured Data with Multimedia Website new home is running at:

OMMUD Web Site

To make the acronym pronounceable, I had to rearrange the letters,where O is Oracle, MM is Multimedia and UD is unstructured data. Thisis going to be the home of the site until its permanent home on ODTUG issetup.

Introduction Papers

2011-09-23T09:41:00.000+10:00

For those wanting an introduction the following two papers will help:

1. Multimedia Concepts (How to Multimedia-Enable your Applications with Oracle Multimedia)

2. Building Multimedia PL/SQL Applications - Introduction for DBAs and Developers

3. Why you should be storing unstructured data in the Oracle database

Inaugural Unstructured Data with Multimedia SIG

2011-08-14T14:10:00.000+10:00

At OpenWorld in October 2011, will be Inaugral International SIG for Unstructured Data with Multimedia

Session ID: 32440
Moscone West
Sunday 9am - 10:30am

Everyone is welcome.

This is the inaugural SIG meeting for Unstructured Data with Multimedia in Oracle Database and MySQL databases. The meeting covers the basic concepts of why the SIG is needed, what it will achieve, technology access, and links to papers and programs. The role of the SIG is to gather experts in the field to share ideas and to help those who are learning about multimedia and any unstructured data be able to find resources and share programs, papers, and ideas on anything in this field.

"All relational databases now look the same. As predicted ten year ago, a database is just viewed by most as a commodity item. Add Multimedia and Unstructured Data to the mix and the rules change. This is a real differentiator. Not all databases are the same anymore."

"The future of IT database management involved a heavy focus on multimedia"

"Over 80% of data is unstructured and not managed - Isn't it about time you decided to manage it?"

"Business intelligence is moving into video and email, what are you doing to deal with this?"

"The use of multimedia is widespread. All devices now have a heavy focus on it. From iPhones, iPads, Android devices and a whole range of other devices, organisations are embracing its usage and using it to grow their business. Are you denying the existence of all this data in your organisation or tackling it head on?"

"Its officially acknolwedged that over 80% of all data is unstructured including multimedia like video and photo's. Why is there so much resistance from all database vendors to embrace the need to properly manage this data when there is such an obvious demand for it?"

Webinars on ODTUG

2011-08-14T13:45:00.000+10:00

The following webinars are available for ODTUG members (http://www.odtug.com) at their site:

1. Audio and Video Management using PL/SQL
2. Integrating Oracle Spatial and Google Maps
3. DBA for Developers
4. Using the Oracle Database with PL/SQL to manage the delivery of Digital Images and Photos

Is Middleware Dangerous?

2010-09-23T01:19:00.000+10:00

During OpenWorld 2010 I jokingly said that with the rise in popularity of middleware and with this view point being especially pushed by Java developers, why don't we move the database into the middle layer?
I didn't even consider it to be taken seriously, but it is, though not in the way you might think about it.

My personal view is to move the middleware layer into the database. Within five years we will have off the shelf servers running 64 cores and over 128Gb of memory.
Even well spec'd single servers will have a terabyte of memory and over 128 cores. On top of this, off the shelf server will have a huge level of built in redundancy in them.

Memory or core failure will be automatically managed by the server giving a much higher level of availability than we can see from today's servers. With cheaper disks servers will have mirroring, or even higher built into them as a default setup. Solid State Disks with their high reliability will be cheap and large enough for most sites data requirements.

The argument that middleware is needed to offload capacity and provide a higher level of redundancy will not make sense anymore for the bulk of customers (yes, those running incredibly large sites will still need it).
With a rising push to use multimedia, in particular video, audio and digital images it will become obvious that by keeping these large volumes of data will result in scalability problems between the middleware and the server (just imagine processing a 50Gb HD video in the middleware and storing this in the database, then add a high volume streaming server on top of that. A gigabit network will quickly become saturated.

So the solution will be to store all multimedia in the middleware layer. This will make it easier for Java FX to process it and it will make perfect sense. The next step is to then use DBFS to create an Oracle file system in the middleware layer in a database. And that will make perfect sense. And now we have the database in the middleware and a database at the back end.

Problem is. If you insist on maintaining the view that multimedia is just a file, your view point is dangerously wrong. With over ten years working in the market with multimedia and understanding what customers are moving to, its crucial to store any unstructured or binary data with relational data. The two should be joined at the hip, not separated. The message is keep keep all the data together. Keeping it separate is bad for a large number of reasons, including scalability, security, backup and recovery, and I know this will please the foreign key lovers - data integrity.

Remember that the Oracle database can manage all data not only relational. It has had this capability since Oracle8. If you believe that only relational data should be stored in Oracle then you are what I term, a relationist.

Unfortunately I know this will fall on deaf ears as the experience in the marketplace to understand this obvious concept to me, just isn't there. We are going to see an increase in middleware. All unstructured data will not be correctly managed and this is an unfortunate direction. And I say that with my DBA hat on. The DBA's will need expertise trying to load balance and juggle two or more databases that struggle to talk to each other.

I am reminded of the joke in Black Adder Season 2 with Tom Baker the sea captain. Even though his view was very funny at the time and we scoffed at the lunacy of what Tom Baker said, if you think about it, it is the direction we are moving to with improvements in technology. Sometimes you need to think creatively.
(Only serious Black Adder followers will understand that so I will not quote it).

Multimedia Workshop

2009-10-15T09:13:00.000+11:00

As promised, I am making available the workshop for configuring the embedded gateway, loading in images and displaying them in a browser. In addition there is a section on encrypting the PK of a URL.

The original workshop was done on a database running windows with vmware, so you might have to change some values to match your environment (e.g. database SID, location of scripts)
I am making available the PDF standalone, as well as the Original Document in Word, PDF and the multimedia used in a zip file. As I own the images there is no copyright issues with you using them. If you want to just download the PDF you can replace the images used with your own and save on bandwidth.

For the next week I will try and support any questions raised on the blog about issues encountered without the workshop (it does work, we have run it a number of times). So the standard answer of "have you rebooted" is always the first step. The other one is drop and recreate the DAD. Another one is you have likely got the accounts mixed up, and created your schema in SYS and not Multimedia. And finally another one is for Unix people - "Windows syntax doesn't work, you need to convert it to Unix"

It was designed for Windows, but I have included Unix references where possible. The workshop will not work on Oracle XE because multimedia is not bundled with it. Otherwise any supported Oracle release will work including 11gR2. Am happy to receive any feedback on typo's, wording not clear or improvements you think should be added (maybe suggest your own tips if you have experience in that area).

http://www.eternal-donut.com/downloadp/workshop_mm.pdf (328k)
http://www.eternal-donut.com/downloadp/workshop_mm.zip (11.5Mb)

(alternate download site)

http://www.xor.com.au/data/workshop/workshop_mm.pdf
http://www.xor.com.au/data/workshop/workshop_mm.zip

A very dangerous direction: Reviewing SOA with Web Services.

2009-10-14T11:15:00.000+11:00

(Note: Tomorrow I will be releasing a workshop just done on using the embedded gateway to build web applications)

This is a such a potentially serious issue regarding performance that it deserves a mention in its own right. The move to SOA is now progressing down a path where it is considered a trend and the new direction.

SOA is a double edged sword. Its theoretical side is a good direction to be in. By using Web Services it becomes possible to integrate systems in different companies and in different places of the world. It allows a new direction where data can be shared between companies (whether that is good is another topic) and I believe it is a healthy and good way to go.

The potential for catastrophic failure is there if the SOA architecture is misused, and misused it will be. This is guaranteed to happen because the lessons from history say it will happen. Within five years we will be reading papers and going to conferences where the talk is how to tune and get working SOA because it runs so slow and doesn't scale. Consultants from major companies will be brought into sites to explain how to develop a scalable SOA. It will be a new industry.

The reason why I highlight the potential for catastrophic failure, and not just your run of the mill failure, is that in SOA, the DBAs are really out of the loop on the design side. They can only be brought in when it is shown not to scale, and all they will be able to say is, “I can't fix it". The reason is because with SOA compared to other architectures, is that scalability and performance with SOA hinge on the network bandwidth.

The bleak picture that I am going to paint is that the Web Services used by SOA are going to be automatically generated by tools that have no notion of performance and assume infinite bandwidth. When you factor in that the bandwidth on the Internet is not only not infinite but rather slow, the methodology required to implement SOA requires a complete change in thinking. Tools that automatically generate Web Services should be banned from an organization. Do not use them. Avoid them. Unfortunately, managers and developer will fall for the allure of tools claiming they can generate these services quicker than it takes to ask the DBA if they are any good. This will happen because it has happened in the past. How many times have we heard companies claim that the tool they have allows rapid development? I recall this claim was made with Forms 2.0, then 2.3 and a complete set of tools after that, some which don't exist any more. When you see product managers showing how quick it is to create a one to many relationship on a screen using point and click and then claim its the new best thing, you realize there is no hope, it will just happen. No amount of warnings, no amount of shouting and going don't, don't, will help. The big companies will push it and they will not care about performance (I must acknowledge a glimmer of hope that exists with the Google philosophy where they acknowledge the performance issue and have made performance a core part of their user usage strategy. This is seen by Chrome and the new Google O/S).

The potential for achieving abysmal performance is now assured as we move down this path. The ray of sunshine is that the companies providing early Web Services now have realized this and are working to build intelligent Web Services that scale. They have to, their business depends on it.

Now here is the important point. Scaling with Web Services doesn't involve getting a faster box, putting more memory in it, or even moving to a cluster. As mentioned the bottleneck, the slow point is the network. Even though performance on the Internet is improving, the rise in Spam and multimedia downloads is going to grow to saturate this growth. Achieving scalability with Web Services involves a multi-pronged attack and it involves building smart , compact Web Services. Its a very simple solution but requires hard work to achieve it.

The current thinking strategy is that if we have a schema with tables, all I need to do is just put Web Services over them, and that's that. The first round of automatic Web Service generating tools will do just this. The end result will be a set of Web Services that are hard to use and understand, and which will require a lot of calls to achieve simple requests. The next round after that of automatic generation tools might move up a layer and allow PL/SQL or Java program call to be Web Serviced. This is still going to fail in achieving scalability. Its also going to fail in usability and writing efficient Web Service calls. At this point we will start to see automatic generation tools use WSDL to talk automatically with each other. With the automatic generation of WSDL we can be assured that the problem of performance is now going to be hidden behind the scenes and near impossible to fix. The solution will not be to get a better, faster network, the result is a core component failing abysmally because it cannot be accessed due to insufficient network bandwidth. With Internet applications, you can't demand that the customer gets a faster network to use your interface. Its naive thinking and will result in lost business.

The realization why its a problem can be explained by looking at this scenario.
Lets say a Web Service is built and because its automatically generated, there is a lot of junk surrounding it as well as comments embedded in it as well as obtuse words. A snippet of the call might look like:

<mylargenamespace:averylongtagname>T
<mylargenamespace:moredatarepeated>value</mylargenamespace:moredatarepeated>
<mylargenamespace:moredatarepreate>value</mylargenamespace:moredatarepeated>
</mylargenamespace:averylongtagname>

What we will see happening is programming invoking Web Service calls that return a large amount of information when only a single value was needed. In the above example, lets say all I was interested in was the first value.

If a Web Service call is 100K in size, and one person requests it, then the amount of data to return is 100K + overhead. Time to retrieve grows to handle TCP/IP packet management. Additional overhead will likely be required as packets go through firewalls and are inspected.

Lets say 100 people do this call. That is then 100K x 100. But what is more realistic is that the program will not be doing one call, but might be doing 10 to 100 in the one page display, with each request doing a different call. It might get more complex if there is a one to many relationship, and a call is requested for every result in the many relationship. In Forms when this happens, the DBA might have a chance of detecting and possible tuning this situation, but in Web Service calls its going to be really difficult. So we now have 100k x 100 x 100, and our network has reached capacity with only 100 users. What happens when we get to 1000 or 10,000 or more? The architecture fails and fails abysmally.

So to achieve efficient and scalable Web Services there are some core concepts we have to adopt and remember:

1. Do not use automatic Web Service generation tools. Never use them. Ever.

2. Parsing XML does not scale linearly. A 10K Web Service could take 1ms to compile. A 100K Web Service will not take 10 x 1ms, but will take longer and consume more memory. It might take 15ms, and a 1Mb Web Service will take longer again. The growth is a possible logarithmic scale. This is when all the XML is parsed up front. If you point out that the DOM parser is different to the SAX parser, keep in mind what is the total time to parse. One parser use the CPU cycles up front, the other as you navigate, but to parse the whole structure still takes time. The solution is to keep the Web Services as small as possible with data volume.

In the above example, if the Web Service was written as <m a="value" b="value">T</m> The total size is dramatically reduced. Add an intelligent layer to it, and it could be possible to shorten it to just <m v="T"/> only if the application requested a compact version.

Enable calling programs to request compact or detailed data views.

3. If there are two Web Service calls done in the one global page call, then combine both Web Services. If a program has to make a call to retrieve data, then do a call to retrieve another set of data, it is more efficient to get both sets of data in the one call.

This is where automatic Web Service generation tools fail. They cannot factor this in. Its just too complex to deal with this and requires the skill of an intelligent programmer.

4. If one value (or a small subset) is required to be returned, then that is all that should be returned. Do not send back junk data unless the calling program requests it.

5. If the server can do smarts efficiently, then let it do it. Is it best to return data back to the program unsorted because sorting is seen as an extravagant waste of CPU cycle or is it better to send it back sorted? Its better for it to be returned sorted, and this is because scalability occurs at both the client and server end. If the program has to do a call and then do its own sort program to deal with it, its just not efficient. This moves into the area where the Web Services generated must take into consideration the performance and usability aspect of the client calling program.

The mentality of SOA drives home the point that we are providing a service and you either live with it or else.

6. Prevent large volumes of data from being returned unless requested. This is equivalent to the optimizer ALL_ROWS and FIRST_ROWS. If the calling program only wants an initially set of rows then return a small set. Don't employ the strategy of here's all the data, you deal with it. This mentality lends itself to massive volumes of data being returned and saturating the network.

Sometimes its efficient to return a large set of rows and avoid repeated calls. In this case compact the data.

Review these rules every 12 months.

These concepts might be new to some, but are obvious to anyone programming against Web Services and are a blessing when sites support these concepts.

Adopt these concepts and you will achieve scalability of your application on the Internet by using SOA. And remember SOA Web Service usage is a double edged sword. You can either get great performance results by doing the hard work, or suffer catastrophic failure by adopting the easy option and using automatic generation tools.

Methods that can be used to create an environment that encourages efficient Web Service creation (ones that can be used to establish a healthy culture that focuses on network efficiency) :

A. Setup the environment so all developers are forced to work over a modem speed for all Web Service calls. Follow the rule that ten concurrent users must be able to run the application over a shared modem line and achieve good response times. This will ensure that if inefficient calls are built, the developer will immediately know about it and feel the pain.

B. Setup a virtual charge system for the developers. Allocate a virtual salary to the developers. For every web service call deduct $100 from their salary. For every 1K of data beyond a 10K call, charge $10. Treat multimedia data in a separate category. At the end of the project compensate the developers using the virtual salary as a guide. Use a reward system. If they build a proven, functional Web Service call that is under 1K in size then they achieve a bonus.

These two simple methods will change the culture of development and will ensure that scalable Web Services are built. It will motivate the developers to think that the network is not an infinite resource but a limiting one that must be respected.

From Bind Variables to Middleware

2009-10-13T09:58:00.000+11:00

After sitting in a presentation run by a well known presenter, I came out of it very disappointed. Not with the presenter, not with how it was presented, nor the technical nature of the topic. Its accuracy wasn't to be questioned. What was disappointing was that after 15 years we haven't changed.

The topic was on why its crucial to use bind variables for performance and as a safe guard against SQL code injection. I distinctly recall when Mod PL/SQL first came out in the 1995 time frame, the notion of URL modification and doing SQL Code injection was raised. In fact at the time it was an obvious issue because the support tools that Oracle provided all had this issue. The tools provided were designed to be user friendly and fast to develop. It took some convincing but eventually the security holes in the packages were cleaned up and the holes patched. At the time, the notion of using bind variables was sensible, it was obvious, you just did it and PL/SQL encouraged their use.

So after 15 years, to be present in a conference where this issue is seen as so serious that it deserves a topic in its own right makes me wonder, how did this happen, why are we still having to drill home such obvious constructs? Is it because there is a huge turn around of programmers? Is it because developers are not being trained properly, or most programmers just don't care?

Lets now look at why from some angles. During the "which is the best database wars" a very dangerous notion was formed. The notion centered around the idea that databases are commodity items and all perform the same. Programmers were taught that if you knew SQL you could program against any database. It didn't matter how you programmed, how you wrote SQL, the database optimizer would sort it out. What we now know is that this notion is dangerous and when adopted leads to poorly performing applications and ones with gaping security holes in them. The idea that if the database isn't smart enough, then we should get a faster box or a different database, has led to all sorts of problems. Its basically held back efficient application development.

When developers started to program without understanding tuning and scalability, it was then that the PC Mentality was ingrained in the common culture. This is when a developer thinks that if my program runs OK on a computer it will run fine with ten users or one hundred users. The PC mentality thinks that the database should be hidden and treated as just a data store. The PC mentality has resulted in simple applications like a word processor still not being able to scale to handle more than 60 pages, and takes 60 seconds to start up. The PC mentality results in it taking 60 seconds to login to an internal network over a high speed line and doing over five minutes to do a directory listing with over ten thousand files in it. The PC mentality is a serious issue that has been reinforced by a poor culture.

Lets now try and look at this without focusing on the developers and ask could it be the tools that they use? Do the programming languages used enforce efficient use of the database, encourage good performance and security practices? Its well established and proven that PL/SQL has this built into it. It can take more effort to use dynamic SQL than it is to use a cursor with bind variables (try retrieving a large set of rows using the execute immediate statement).

But what about other tools like Java, PHP, Perl, C and VB? Do they have built in constructs that make it easy to use bind variables? Do they encourage good access to the database? I don't believe so. I have programmed in all these environments, and each has great strengths and capabilities but all lack any smarts for dealing with a database. Most are database agnostic and all encourage inefficient database access. If you are not convinced on this point, there is a large amount of information on all the features built into PL/SQL which tightly integrate it with the database. Once you see these features you realize what features the others programming languages are really missing.

Is this article just a propaganda tool pushing PL/SQL? It might look that way, but I would rather focus on the core issue raised as to why we still focus on bind variables usage. Why aren't we focusing on the culture and changing it to ensure these tools when used are used efficiently? They can be. It takes more effort, but it is possible.

Are bind variables and SQL code injection the only issue? Interestingly no. 15 yrs ago the issue of SQL code injection was seen as a trivial issue to address. A good DBA would spot the issue when doing a code review and get the developer to fix it. In today's environment, this level of code review rarely happens. Most DBAs wouldn't even know where to look. Once again, a development culture that is not established correctly is leading to security and performance issues.

So are there other issues? Yes, and there are more serious ones. In particular how do you stop someone who is correctly authorized from modifying the URL (or changing a POST) and accessing data they are not allowed to? This isn't SQL code injection but standard URL modification.

How do you stop someone from invoking a procedure call from the URL line they are not meant to be able to call? How do we secure Web Service calls and ensure that all data and requests passed to the database are valid? How do we prevent denial of service attacks or stop data flooding (when a large number of legitimate requests are done which fill the database with useless data e.g. someone adds a million items to their shopping basket).

There are more important and more serious issues that need to be addressed, and being forced to keep reiterating the basics is very disappointing. It shows that the development community is locked into a world view that prevents them for seeing out. It needs to change.

As for Middleware, I will finish with the observation that most sites do not require Middleware. Scalability can be achieved by efficient database programming and ensuring the database logic and objects reside next to each other. Middleware only offers solutions for sites that require serious scalability or have legacy systems. Generally sites using a Middleware layer will produce a culture that encourages inefficient programming habits because they insist on hiding the database from the programmer (who might not even know what the database is) and adopt the mentality that the bandwidth between the middle tier and the database is infinite. This will not lead to a scalable application but rather the opposite. Only when used correctly and intelligently will a 3 tier architecture scale correctly. This topic is worth further discussion which I am sure will happen.

Oh, and yes, there are really good solutions to the issues raised above, but that is for another time.

3 Papers

2009-10-12T05:45:00.000+11:00

I have decided to pull the 3 papers in the appendix of the foreign key paper into this Blog for separate discussion and comment. I will release one paper every day during OpenWorld. Though the papers are core to the discussion about the merits of the Relational Model they do stand up in their own right.

Why donuts are the solution to the tuning problem.

2009-10-12T05:43:00.000+11:00

If this hasn't got you thinking, then nothing will. I'll answer the what the? question at the end of this discussion, but first lets see how one can come to this conclusion.

Moving to a new world view. Step back and look at tuning. Keep stepping back and lets see if we can get to the root cause of what causes tuning issues. DBAs when they step back might end up saying - write better SQL. Managers would say get a faster box. Developers would say - improve the design. There are many dimensions to resolving tuning, but when you really look at it, when you take the big step back and say, what fundamentally is the cause of all tuning issues, then in my experience, and this is shown and proven to me time and time again, is that database performance issues are caused by management. The best way to tune is to educate management.

This is a fundamental principle of proactive tuning (a concept I have been advocating for over 15 years, and have written papers on). Yet it is such a foreign concept for most DBAs/Developers it is inconceivable. The thought processes do not even allow them to contemplate this, so ingrained are they in their world view. And yet they all know it, they all talk about it, and elude to it as being the core problem in papers and discussions.

Management are responsible for the culture of an organization. That is their primary goal. Forget about management being there to sign forms, be a leader and buy things. Establishing the culture is really the only true reason why management exist. Management are not productive. They don't build anything, write code, do design work, answer problems, work with users, debug, tune. They make decisions. Decisions to hire, fire, buy and manipulate. In short they control the culture of an organization through the decisions they make. In turn the culture dictates how people work, how they interact.

Let's try and highlight this by looking at some cases. All these cases are based on real world scenario's, ones you have likely encountered. The goal is to highlight how bad practices have led to a culture promoting inefficiency. This is similar to Foreign Key usage.

Case 1: Separate Buildings
DBAs and Developers just do not get on. A good DBA will act as an auditor and review what the developers are doing with the aim to expose tuning, design and security flaws. A good DBA can do this in a way that doesn't make the developer feel inadequate. Problem is, this rarely happens and conflict ensues. Given time something nasty will happen. The management mentality is to see that its best to keep the two groups apart. This just makes it harder to communicate and slower. Developers will avoid dealing with the DBAs and some will hope they can sneak through code without it being reviewed. Performance/Security issues occur, the DBAs are forced to reactively tune and the two groups are now in a conflict scenario. Management at this point will then move the DBAs to separate floors, maybe different buildings. In some cases different cities. The smart thinking manager might even outsource one or both groups. Management set the culture. They don't see what is happening from a technology viewpoint they see it from a point where two groups are fighting and we need to keep them separate. Change management is then put in place to allow them to communicate. The culture is now set in place. Its ingrained in the psyche of everyone in the organization. DBAs and Developers are never to speak or meet unless there is legal representation. So many sites have gone down this path and yet this cultural setup is a leading cause of performance problems. Why?

When DBAs and Developers can work with each other, then they can solve performance and security problems before they occur. DBAs can review code quickly and identify security holes immediately and ensure they are fixed before they become part of the core code. The developers, if they know a DBA is reviewing their code and reviewing it well will more than likely attempt to write efficient SQL and use efficient practices. If you know you are being audited but audited in a good way you are likely to do good work. Such a concept of having the DBAs and Developers working together leads to an environment of proactive performance and tuning.

So why doesn't this happen? As mentioned, the culture imposed by management has insisted this cannot happen. If you are in a site where the DBAs belong to one department and the developers to another, then its nearly impossible to arrange for them to work together. Also, DBAs and Developers are not trained to understand how by working together its in their best interest. They are not trained in dealing with personalities and handling clashes when they occur. They are not training in the basics of psychology and personality management. They are not trained in conflict management and basic communication skills. Management tend to view all DBAs and Developers as the same. Usually just coders, some with bigger ego's than others. See Case 5.

How many environments outsource solutions to other places because its less hassle than dealing with their own internal structure - most. And why, because of the culture.

Case 2: Nice Testers
Testers who are nice are dangerous. Testers who are afraid of hurting the developers feelings because they can see they have put so much time and effort into a program are not doing their job. Management who hire nice testers and who do not setup a culture where the testers can make honest comments without fear of retribution or fear of hurt are ensuring that code is not properly tested. Testers should be able to comment about poor screen design, potential inefficient application navigation issues and should be able to test outside the scope of what they are told to look at.

Also at some point in the testing, DBAs and Developers should be able to review and see how the testers use the application. How they do searches, how they move around the application, and check for potential bottlenecks or tuning holes caused by a bad interface.

This culture of testers, DBAs and Developers all working together and yet being able to critique inefficient areas of the application is a concept so foreign to most sites that the notion cannot even be raised for fear of being laughed at (which also highlights another management cultural issue).

Case 3: The cheap solution (or the expensive solution)
When management buy a hardware or software solution because its either cheap and can just do the job, or its very expensive and must be good then, they put in place the ground rules for how the Developers and DBAs have to work. If the DBAs and Developers, both cannot be involved in the decision from the beginning (and not the situation where management say please pick Option A or B) then they are putting the DBAs in an environment where they now have to reactively tune. A good DBA, a good Developer will know where in the hardware the slow points are. These are likely based on the architecture and model they are working in. They should know what works and what will not work. They should be advising management on the hardware that should be bought and giving them the choice, not the other way around.

Case 4: The next trend
How many times has this happened in your environment. Management are sold on a new technology because its seen as the next trend or saviour to all their problems. SOA is he way to go, all screens must be carousel, ADF will solve all our issues, Apex will solve all our issues. Java will solve all our issues. You might hear the justification from management (or developers/DBAs influencing management) by using the adage "everyone is using it, so should be". We should use PHP, we should use Middleware, we should use Ruby. Or maybe the negative situation happens. Lets not use PL/SQL because we don't want to be locked into Oracle. Or maybe they justify a trend based on an impractical concept. Lets build our application so it works against any database because we don't want to be locked in.

Has it also ever happened that management have adopted a new technology, usually on the basis that its cheaper, requires less developers, is simpler, purely so they can be seen as smart in their own managers eyes and thus hope to get promoted and move up the corporate chain? I have seen this happen on a number of occasions and the results have always been disastrous.

Managers who establish a culture where they determine the direction, are creating an environment where the DBAs and Developer are forced to react. The development tool chosen might not work efficiently with the database, the methodology chosen might encourage inefficient programming habits. In the end the DBA will likely be put in a situation where they have to spend a lot of time doing reactive tuning or having to jump through hoops to get the application to scale (if that is possible), all because of poor management decisions, decisions resulting because of the culture of the environment prevented the correct decision from being made.

Case 5: Personality driven environments
Have you been in an work environment where the
Developers are in control? In this case, it is an environment where a dominant developer has been given control. In worst case scenario's they will take the developer they like the least and make them the DBA. This is referred to as a puppet DBA. The developer then assigns the DBA the role of doing backups and taking their table parameters and adding storage parameters to them.

In this case the DBA is not a real DBA. The developer then has free reign to impose whatever trend, design methodology they want. In this case what will happen is that the developers will push through their own architectures, designs and impose their own methodologies without looking at the tuning or security ramifications. They will also likely use their puppet DBA to sign of on all designs so when performance and scalability is not reached they can blame the DBAs because they have a proven paper trail.

The reverse can also happen, where an obnoxious DBA, one with a persecution complex, one who cannot handle being criticized grabs the reigns of power and then ruthlessly imposes a strict regime of terror over the developers.

This DBA normally holds contempt for developers and views them as coders who have no idea how to write efficient code. The DBA then imposes stringent practices and change management procedures ensuring that requests come in at a trickle and any perceived ignorance of the developers is responded to with anger or heavy handed tones.

The developers are then hamstrung and cannot work efficiently. The DBA makes them tune statements which either do not require it or are run so infrequently its not worth the effort to focus tuning resources on them.

In both these cases the issue can be seen that the Developer or DBA are at fault and what has this to do with management? Management are responsible for the culture and they are responsible for the personalities and how the teams work with each other. If they have just sat back and watched these scenarios unfold, then they are guilty of incompetence through neglect. In these situations management have been intimated by the dominant players and rather than addressing the conflict have more than likely supported it by ensuring the dominant personalities get their way. No where have they tried to train, teach or advise them to change their behavior. Nowhere have they tried to create a culture where the DBAs and Developers work together and not at each other.

Case 6: User database creep
Have you been in an environment where users sneak in their own databases because its too hard to work within their own organization? You now get a situation where data is stored in a variety of open source and and free databases which are not backed up, secure or allow the organization to create a global view of all data. All because the users who need to store the database cannot deal with the players in their own internal structure. All because the culture imposed by management prevents this from happening. Change the culture, change the habits and turn things around so the users want to put the data in a controlled and central data store.

DBAs and Developers keep looking at a granular focus to find the solutions to performance issues. If I improve locks, adjust the latches the tuning issue will be solved. Its the mentality that fundamental tuning improvements can be made by going more granular.

All that will happen is you get inversely geometric improvements. It can take 10hrs of effort to tune it from 80-90% optimal, but 100hrs to get it from 90-95%. No one is encouraged to think the other way, to look at the bigger picture - and why not? The thinking is that its management's job - but management have no idea. Simply they are clueless (Scott Adams really has this right). We are so used to reactive tuning its become part of the core culture of most organizations, and as mentioned management are responsible for the culture. So DBAs and developers should start to go the other way and tune management. More so to encourage them to establish the correct culture so that the correct habits are put in place. The sites that I have seen that have efficient and well tuned systems actually do this. In most cases they might not even realize they have done it, but a good, competent manager will establish the culture and then watch as good habits, practices and efficient tuning architecture naturally form.

Lets now look at one of the most insidious methodologies created, one that if seen highlights that the culture is suffering, and that is change management. Which brings things back to foreign keys. If you see them being used is just like seeing change management in an environment. Its symptomatic of a bigger issue.

Its very rare to see a site using change management correctly. A number of times I have heard the comment from people that they feel they invented change management, because it solved a core communication issue in their environment. For them change management was the solution.
Why pick on change management here? All it achieves is slowing down the whole process to the slowest point. It converts everyone into a protocol, and an inefficient one at that. Seriously, how many people think their change management architecture can run faster? Change management has a place when used intelligently. In most cases it highlights that the culture of an organization is so bad, that DBAs and Developers can only communicate with each other via official communication protocols.

Now lets jump to foreign keys in this analogy. They represent a stage in an otherwise inefficient process. They represent a failure in our tuning architecture.
Lets go back to change management. It might be seen as slow and cumbersome, so what is the solution that most people will come up with? They will look at each step and see where improvements can be made. Maybe introduce an automated change management system, maybe better use Service Level Agreements to get faster throughput, maybe even let the DBAs meet the developers in an officially controlled meeting. All you are doing is spending more time to make more efficient steps in a process that is flawed and created as a result of poor management who did not setup a culture to encourage better communication.

In my environment, I threaten people and say if you/we don't change our behavior I'll be forced to bring in change management - ooh the bogeyman cometh.

Management are usually so ignorant about culture in their environment that when one gets it right, its an achievement worth celebrating. But so often all that is made are excuses. Yes, we could do it that way, but my manager won't let me, or they have their own agenda.

So in the end, because of bad culture (bad decisions made), the DBAs and Developers are put on the back foot and asked to solve issues that don't lend them well to tuning and performing well. So the environment becomes reactive.

So what is a solution to this? This where the donuts come in. Donuts are the universal communication device. Everyone knows about them. Most know they are bad. Most love to eat them, most feel guilty for eating them, and nearly everyone will cut a donut in half or quarters and then eat each piece thinking that doesn't count for calorie intake. They are equally loved and loathed and they are a great way for breaking down barriers. Walk into a room with donuts and everyone can be your friend. DBAs and Developers should be using this device as a way of re-establishing communication and contact and solving their issues in a more friendly environment, rather than through cumbersome change management. Managers should remove all barriers and get the groups working with each other, only then will an effective and efficient environment be seen.

So when tuning, if you do not factor in the culture of an organization, the psychology and relationship between the DBAs, Developers, Managers, Users and other groups. If you do not factor in the usage of tools, the methodologies used, and insist on only focusing on the obvious performance issues and look at only the database, then you are not tuning, you are just applying a band aid.

Open World 2009

2009-10-12T04:53:00.000+11:00

For those attending OpenWorld I am involved in a Multimedia Workshop and Unconf Paper:
Tue 2pm (13th Oct): S312309:
Build Fast, Secure Web Applications with the PL/SQL Gateway and Oracle Multimedia

Wed 1pm (14th Oct): UnConference: Overlook II:
Mod PL/SQL Development Tips - including Google Maps, Spatial Integration and sending HTML formatted emails with graphics
http://wiki.oracle.com/page/Oracle+OpenWorld+Unconference

Foreign Key Discussion Paper

2009-08-26T09:41:00.001+10:00

When it comes to Multimedia and Objects (non Relational), the level of ignorance, mistrust and general lack of basic knowledge of these two major features in the Oracle community is frightening. I liken it to being in the dark ages.

This is highlighted by the adherence to the notion that foreign keys are the best thing ever. To discuss this and more on multimedia and tuning, I have written a lengthy paper covering this topic which I have given its own dedicated blog for discussion.

http://foreignkeys.blogspot.com/

with the paper available at : foreign_key_discussion_kratochvil_v11.pdf
also at : http://www.xor.com.au/data/foreign_key_discussion_kratochvil_v11.pdf

Topics to Annoy #4 - Standards in the World

2009-04-04T10:59:00.009+11:00

Its interesting that we view competition as healthy but in the IT world competition slows down progress. The point I am trying to start out with is that standards allow IT to progress. Competition slows it down. The trick is to work out when competition is good and healthy and when its dangerous.

Imagine if there were competing standards for HTML. Imagine also if these standards used different network protocols and photo display. You could have one standard supporting HTML, TCP/IP and JPG, but the competing standard involves a markup language running only on named pipes and Bitmaps (which for those that are interested Microsoft has tried to do many times in the 90's). The internet would not be around now if there were competing standards.

The use of standards involves a group of experts getting together and agreeing on a common format and usage for a particular IT area. Its agreed the standard is not perfect and will evolve over time based on feedback and usage in the market place. There are arguments and temper tantrums thrown in the standards forums. There is politics and the attendees putting together the standards will have various agenda's, but you have to be honest, in the most part the various standards committees have done a good job in getting the standards out there.

We have standards agreed to for HTML, SQL, XML and Networking. There are standards for software development, design and implementation. The concept of standards isn't just limited to the IT world, it does exist in other fields like the building industry and in shipping (containers). In the IT world they reign supreme.

And yet we still have competition. There was beta and vhs, DVD-R, DVD+R and recently with HD and BlueRay. Even when the different players in the market agreed that they needed one standard and only one could survive, they couldn't decide on which one. It was left to the market to decide. In the case of Blue Ray, it was more political maneuvering than market forces that drove the decision in the end. The consumers didn't care, they just wanted one - unless they bought the wrong one.

I guess its easier to define standards for software than for hardware. When you look at Mp3 players, its hard to find two different brands that:
- support the same charging device
- support the same plug-in to load music
- support the same controls
And yet, the force that drives the companies to standards is cost. How much cheaper would it be if all mobile phones could use the same battery? Better still use the same charger? The world would change if this happened. Its in the consumers best interest for this to happen and its in the manufacturers best interest also, so lets hope the initiative to do this happens quickly.

Still there are places in software that we need standards. A good example is for internet messaging and communication. Imagine being able to use Skype to call Yahoo. We have standards for email, why not messaging (or maybe we do, but the players do not want to use them just yet). I would really like an agreed standard on Video and Audio. MP3 seems to be winning for Audio, but Apple has introduced additional standards, and Microsoft would like to put their own in. For video there are so many standards its a minefield dealing with them all.

When it comes to spoken language there is debate whether a universal language is better than a mixed set. Esperanto was proposed as the international language for communication and was designed with standards in mind. It looked good on paper, but never made it. We are now looking at English as becoming the de facto language for communication because of the internet (which I know the French do not like). But English as the universal language is not guaranteed and even Josh Whedon in his TV show Firefly thought the end result might be a mix of English and Chinese. I know linguistics abhor the concept of universal speaking language and are doing their best to keep alive dying languages of the world. I would say that a universal speaking language is worthy of more debate, I personally would like to hear more about why keeping alive all the languages is good.

Wouldn't it be great if there was an agreed standard for Remote Controllers?
What about Solar Power Cells. If they could make them to plug-and-play between A3, A4 and A5 size, then consumers could buy the cells they need and mix and match as required. And it would be great if the solar cells had a USB adaptor. Now here is a standard that is simplifying computing, the humble USB. But it competes with firewire, which is losing out. Now wouldn't it be great if more appliances in the house that were low powered, used USB interfaces? Why do we need AC for every low powered device when DC is sufficient?

Some other areas that standards are needed include:
- Power. Different countries of the world use different voltages and device shapes to plug into the walls. Europe is different to America which is different to Australia. The Australian one is the least used (because of population size), but the safest. If there was an agreed standard it would be easy for manufacturers to make devices that work universally.
- Driving on the road. Left or right? Road rules. Different countries, different parts of the country, all different rules. In the global world people travel and drive in different countries. Switching to drive on the other side of the road requires expertise and is fraught with danger. Unfortunately I doubt we will ever get a standard in this area.

Now onto my topic to annoy. The one major standard in the world that everyone has agreed to work with and use is Metric. Its easy to use, understand and work with. So which countries in the world do not use it? Even though the USA has agreed to use Metric and was one of the first countries in the world to attempt to adopt its usage, its still not being used. Only three countries in the world do not use it : See Wiki and Metric

Why is it annoying? I travel a lot to the US. I am inundated by TV shows from America, and in all cases I have to translate units from imperial to metric to understand it, and likely get it wrong doing so. Even shows like Mythbusters try to say units in Metric but are always caught out. Try watching a documentary and they express weight in bounds, size in feet and speed in miles per hour. Annoying and frustrating, makes me want to get out my Tandy Model 3 and Betamax and use it to access MSN. (my understanding is that the only time feet is OK to use the expression feet, is when on an aeroplace and you are expressing the height you are travelling at).

Metric is a powerful standard. All countries have had to do the switch to it, most recently the UK. And it takes time to do the switch, but its worth it. My guess is that as the US sells more goods to the rest of the world, and as more overseas goods come in to the US, the packaging, weighting and sizing will have to conform to the agreed metric system. Slowly the US will use the metric system, but they will not start using it because a lone Australian is having a temper tantrum on the other side of the world.

Before I close this blog, my personal wish is for the banks of the world to come up with a standard for communicating merchant payments, because in the last 3 years I have had to build 12 different interfaces for 12 different banks, to handle merchant (credit card) - ecommerce payments. Each interface was different and each time I built it, I thought they couldn't come up with a new different way, but they always did. Its not efficient, its a pain to support and it costs the IT industry time and money to integrate. Well it costs me patience and sanity, but you get the point.

Experiences #8 - on National Languages and Indexes and Diacritics

2009-04-04T10:59:00.008+11:00

Ok, here is the issue. A column in a table needs to be searched on. It needs to be case insensitive and it needs to be able to deal with Diacritic characters (not to be confused with Diuretic which always get me). These are latin based characters that have inflections or accents on them (see Wiki). The aim is to allow searching that ignores these characters.
e.g. If the column contained: de Niró, then searching on DE NIro, dë Nirõ, DE níro will all return the same results. Also wild card searching will also work.

How to achieve it? There are two methods, the first involves using features for NLS provided by Oracle. The second is to build it yourself.

To start, you should ensure your database has a character set that is UTF-8, Unicode or some equivalent of better character set. By now, if you are on Oracle 10 or greater you should be using Unicode, because of XML (if you aren't then look to migrating to it).

The hard part involves getting these characters in. Don't try using SQL*Plus and cutting and pasting. Odds are, the SQL*Plus session will corrupt them in the buffer before sending them to the database. Using a HTML page seems to work, and I have had good success migrating from non-Oracle databases into Oracle using database links.

Once in, if you want to use the Oracle features to support this type of query, it involves modifying your session. With the tests I did, I used these statements:

ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=generic_m_ai;

Then I did my search as per normal (no SQL change).
I recreated my indexes like this:
create index i_myindex_n1 on mytable(mypk,NLSSORT(word_upper, 'NLS_SORT=generic_m_ai'));

Check the manual for what happens behind the scenes. Really what happens is the equivalent setup of materialised views and indexing. On Oracle 10 I could search correctly, but wild card searches were slow because they couldn't use the index. This has been addressed in Oracle 11.

The downside was Mod PL/SQL. By changing the session the Mod PL/SQL output was corrupted. The workaround was to do an execute immediate and alter the session in between my search calls and set it back after. Slightly messy but it worked. Didn't solve the 10g wild card issue - making it a good reason to migrate to 11g.

The manual solution involves setting up a trigger (or equivalent) and on insert/update just taking a copy of the data and running this function put it into the replicated column:

function nls_conv_val(vtxt varchar2)
return varchar2
as
rtxt varchar2(1000);
begin
rtxt := upper(vtxt);
rtxt := translate(rtxt,'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝØ','AAAAAAACEEEEIIIIDNOOOOOUUUUYO');
rtxt := replace(rtxt,'ß','SS');
return( rtxt );
exception
when others then return( rtxt );
end nls_conv_val;

I can then index this column. Its simple and the only downside is the management of this replicated column. I then have to make sure all searches use the column containing the converted data and not the original one. But it does work with wildcards in Oracle10, I don't need to create extra indexes, and I can control which characters are converted.

So these are two methods available for dealing with NLS. One is transparent, one is manual but controllable. Each is as good as the other and its up to your business to decide which one is right for you. I expect there are additional methods available and am keen to hear from anyone who might have them.

Tip #6 - Oracle XE Version

2009-04-04T10:57:00.005+11:00

The free version of Oracle is called Oracle XE. It has limitations including:
- Database size is 4Gb
- No Java
- Platforms and # CPUs

When using Oracle Multimedia, the size limit of 4Gb is an issue and is very restrictive (which encourages you to buy a licence). But with 4Gb of storage (really assume 3Gb once you factor in loss due to database use, block sizing, indexes, inefficient use of relational storage structures), then how much can be stored?

Lets say we wanted to store images in the database. If a thumbnail is around 2k, a web quality around 50K, and an original 400k (OK, so most cameras today are 5mega pixel or greater meaning the images are 2Mb - 10Mb in size. But 6 yrs ago, this wasn't the case and most original images were lucky to make it to 140K). If your original images are for viewing on a computer screen, then 400K is better than what most screens can handle.

Ok, back to the sizing. Lets keep it simple and round up, and assume Thumbnail + Web + Original = 500K. That means 2 images per Mb or 2000 images per Gigabyte, allowing 6000 images to be stored. That's a pretty healthy figure. Now if you only store the web quality + tnail (lets assume 50k for both), then we get 20 images per Mb, or 20,000 images per Gb, or 60,000 images that can be stored in XE. Now that is a good figure.

Back to the other limitation. Oracle XE doesn't support Java, and Oracle Multimedia uses Java. Bit of a show stopper that. There is nothing stopping you from running the scripts to define the Oracle Multimedia types in XE. The methods will not be available, but if you are willing to write your own, you can still have a database that has type support with Oracle Multimedia (so when you do migrate to Standard/Enterprise its an easy migrate).

So without Java, how do you shell out? For those not in the know, if your Oracle database is running Java, its fairly easy to shell out to operating system and run a script. This script can then call a tool like Image Magick to modify the images and create thumbnails/web quality. These can then be loaded back into the database using standard dbms_lob packages.

But if XE doesn't support Java how do you shell out? Oracle XE supports the use of Scheduler, and with Scheduler you can shell out. Problem is with Unix, there is a nasty bug in 10.2.0.1 relating to permissions and shelling out (fixed in 10.2.0.2). There is a manual work around to fix this (check metalink).

Following are examples of how to use scheduler for Windows and Unix:

Windows:

SHELL := myjob;
jobidx := 1;
jaction := 'c:\winnt\system32\cmd.exe';
dbms_scheduler.create_job
(
job_name => SHELL || jobidx,
job_type => 'EXECUTABLE',
job_action => jaction,
enabled => false,
number_of_arguments => 3,
comments => 'Run shell-script'
);
dbms_scheduler.set_job_argument_value(SHELL || jobidx,1,'/q');
dbms_scheduler.set_job_argument_value(SHELL || jobidx,2,'/c');
dbms_scheduler.set_job_argument_value(SHELL || jobidx,3,cmd);
dbms_scheduler.enable(SHELL || jobidx);

Unix:

jaction := '/bin/sh';
dbms_scheduler.create_job
(
job_name => SHELL || jobidx,
job_type => 'EXECUTABLE',
job_action => jaction,
enabled => false,
number_of_arguments => 2,
comments => 'Run shell-script'
);
dbms_scheduler.set_job_argument_value(SHELL || jobidx,1,'-c');
dbms_scheduler.set_job_argument_value(SHELL || jobidx,2,cmd);
dbms_scheduler.enable(SHELL || jobidx);

Where cmd is a call to a .bat script or .sh script. Don't try and get it to run a command, it will likely fail. Big issue on Unix you will find is the script run has no parent knowledge. So you need to define the PATH and all other Environment variables. Assume it knows nothing and you will be OK.

Big Limitation on Windows for XE. I have noticed without finding a workaround, that if the script shelling out does not finish and terminate, it will hang and consume resources, and the max number of scheduled jobs that can run is around 6. So if the shell script has issues you will get these scheduler jobs hanging around, that "cannot be killed" when using scheduler commands (I have tried) and require a database bounce to fix it. My guess is its a bug in 10.2.0.1.

Also, if you want to try and trap the output then grind your teeth, its easier. Redirecting job output to a file, STDOUT, STDERR doesn't work consistently, if at all. If you can embed in your script the ability to dump information to a file, and then from PL/SQL read that file, that is a better method.

So don't think you can't use XE for managing multimedia. You can, and you can build some fancy applications in it. With Piction I have managed to port our application to it with only minor limitations and functionality loss. I have even ported it to Ubuntu. On Windows I managed to get it running from an 8Gb flash drive plugged into the computer (that's the Windows o/s, Oracle software, database all in 8Gb total). For Linux, couldn't do it in 8Gb, but achieved it in 16Gb. With the right will it can be done (and at times I wish I had a sledgehammer).

Experiences #5 - integrating with other Image Managment tools

2009-04-04T10:56:00.005+11:00

There are hundreds of image formats, video codecs and audio codecs out there and Oracle Multimedia only supports the most common ones.
So what do you do if you want to store a non supported one in the Oracle database?

This is where I have spent the last 8 years trying out software from different vendors and looked at a variety of methods for manipulating and controlling these images.

The challenge is that it is hitting a moving target, especially with video. Each 6 months new codecs appear and based on user popularity (versus what is best) determines what is required to be supported. A long time ago AVI was popular, but AVI isn't AVI, there are 30+ codecs supported within it, including Divx, so just because the video has an avi extension, doesn't mean you can deal with it. And it gets worse when you can mix video and audio codecs together. The result is you might be able to decode the video but not the audio. Quicktime was popular, and so was MPEG, but the current popular video format is Flash Video (flv) because of YouTube. There is a huge amount of legacy video out there and trying to convert or manage them all is very challenging.

From my perspective, I look for tools that satisfy the following conditions:
- can run on Windows/Linux/Solaris
- can be called from a command line
- can be enhanced
- optional - good licencing rates

For Images, the preferred image management tool is Image Magick. It's open source and has an active community supporting it. It is also fast and has a lot of powerful options in it for watermarking and converting images. It can also deal with some video types and can handle postscript and pdf. It doesn't have memory issues and the largest file processed with it has been over 1Gb.

By being able to call the tool from the command line means an image can be managed from a shell script (or Windows Bat). Meaning, you can shell out from Java inside the database to manipulate the image and once manipulated, load it into the database.

For Video, the preferred tool is ffmpeg. This is supported across all platforms and supports a large number of codecs. It works well converting video and for extracting video snippets in other formats.

Both Image Magick and ffmpeg are open source. I will cover in later blogs techniques and lessons learnt when using these tools.

Now its just not enough to convert an image or video without thinking about scalability, both in terms of the huge amount of CPU required to convert, but also the large size of multimedia objects. But this is a topic for another conversation.

On Blog Entries

2009-04-04T10:49:00.002+11:00

If anyone has wondered why I haven't posted anything recently on Multimedia its because for the last 3 months I have been head down programming and doing a major rewrite of our e-commerce engine. I will be doing papers, podcasts and blogs on everything to do with e-commerce and multimedia soon (especially on selling audio). Its been rewritten in PL/SQL and uses database objects extensively (lots of lessons learnt). All done whilst providing a complete webservice front end to all interfaces allowing dual support for usage.
I also find it easier to blog in bulk rather than a bit at a time, and provided I don't get distracted I should have a whole new set of blogs on multimedia out today. So whilst it rains outside for the first time in 2 months, I feel the motivation to put out some new blogs.

Oracle Multimedia Basics: Directories

2008-09-25T07:32:00.003+10:00

A goal when dealing with multimedia is to load images into the multimedia types, and those images exist on a filesystem.

To load them in, an Oracle directory needs to be defined. This directory allows you to assign a name to a physical location. Access can then be given to users using the granted command. Directories are used by a number of tools including datapump.

Following are examples of creating a directory:

create or replace directory win_mydir as 'c:\temp';
create or replace directory unix_mydir as '/u01/imageloc';

You can then grant read access to a user like:

grant read on directory win_mydir to websys;

Note 1: The directory does not have to exist. Existance is only checked at runtime usage.
Note 2: Access is recursive. You can access subdirectories that exist in this top level directory.
e.g.
If there is a file called c:\temp\subdir\abc.jpg, then you can access it using directory win_mydir and file name called subdir\abc.jpg

The following highlights this concept:


create table myaudio(pk integer, myaud ordsys.ordaudio);
insert into myudio values(1,ordsys.ordaudio.init());
commit;

declare
 cursor c1 is select * from myaudio for update;
 c1rec c1%rowtype;
 ctx raw(4000);
begin
 open c1;
 fetch c1 into c1rec;
 close c1;
 c1rec.myaud.importfrom(ctx,'FILE','MYDIR','subdir\bwf_short.mp3');
 c1rec.myaud.setproperties(ctx);
 update myaudio set myaud = c1rec.myaud;
 commit;
end;
/

SQL> select m.myaud.audioduration from myaudio m;

MYAUD.AUDIODURATION
-------------------
                12

Oracle Multimedia Basics: Using DBMS_LOB

2008-09-24T05:56:00.005+10:00

At the heart of the Oracle Multimedia data types (ORDSYS.ORDIMAGE etc) are blobs. These are binary large objects and can store huge amounts of data in them. At the time of writing the largest blob I have managed to load in was over 10G and I will soon be attempting to load in ones over 100G in size.

You can use the methods that come with multimedia to manipulate and manage the types, but you can also use the dbms_lob package to manipulate the lobs in other ways.

The doco for dbms_lob can be found at:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_lob.htm#BABDDFDH

Not much changed between 10g and 11g, so the 11g documentation is great as a reference.

You can use dbms_lob to copy, trim, load and manipulate the lob. You can use dbms_lob to load an image into Multimedia, then use the multimedia setproperties method to recognise it (or you can just use multimedia methods to load and recognise in the one method). Using dbms_lob just gives you other options.

I use dbms_lob for managing temporary lobs. I will take a lob loaded from apache, store it in temporary (cached in memory), then create web quality and thumbnail images from the temporary. It runs faster this way. Downside is that my temp storage area is very large.

When manipulating lobs using dbms_lob, you need to lock the row containing the blob to ensure no one else can modify it. That means doing a select .. for update statement. One has to be careful now. Once you lock the row, certain Oracle statements do implicit commits which will release the lock. You will then get an error if you try to modify the lob (you will need to reacquire the lock.


declare
cursor c1(vpk integer) is select myphoto from my_image where pk= vpk for update;
c1rec  c1%rowtype;
blb    blob;
begin
open c1(123);
fetch c1 into c1rec;
close c1;
-- create the temporary blob
dbms_lob.createtemporary(blb,TRUE);
-- copy the blob in the table to the temporary blob
dbms_lob.copy(blb,
                           c1rec.myphoto.source.localdata,
                           dbms_lob.getlength(c1rec.myphoto.source.localdata));
-- manipulate the temporary lob
... put in your own statements here

-- copy it back
dbms_lob.copy(c1rec.myphoto.source.localdata,
                          blb,
                          dbms_lob.getlength(blb));

-- set the properties on it
c1rec.myphoto.setproperties;

-- update the database to reflect the change
update my_image set myphoto = c1rec.myphoto where pk = 123;
commit;

dbms_lob.freetemporary(blb);
end;
/

Its best to trap any errors around the setproperties command (e.g. begin .. exception when others then ... end;) in case the manipulate blob isn't valid.

I can also create a temporary multimedia type as follows:


declare
mytemp ordsys.ordimage;
begin
mytemp := ORDSYS.ORDIMAGE.init;
dbms_lob.createtemporary(mytemp.source.localdata,TRUE);
.. add your manipulation commands here
dbms_lob.freetemporary(mytemp.source.localdata);
end;
/

Download OpenWorld Paper on Oracle11G

2008-09-24T05:31:00.002+10:00

Upgrading to Oracle 11G - Tips. techniques (209Kb)

Oracle Multimedia Basics: Questions and Answers #1

2008-09-23T08:09:00.003+10:00

Q: When dealing with multimedia how big should my redo and undo be?
A: Because storage is cheap, don't get cheap on storage. When dealing with lob's the more storage you give the database the better it will perform. If I knew that the largest lob in my database was going to be 1G, I would try and ensure that my redo logs were as big as this or bigger
e.g. I would create my redo logs 10 x 1G in size (10 redo log each 1G in size). Oracle can quite happily deal with redo logs smaller than this. I have loaded into an Oracle database over 30G in one blob with redo logs 100mb in size. It just will not run as efficiently as if you made them bigger. This is crucial in an environment where multiple users are loading blobs at the same time (or if you are loading them in, in parallel).
I typically make my undo tablespaces 1G in size. Databases that are over 1Tb in size can have undo tablespaces over 50Gb in size.

Also, when dealing with Images I set my temp tablespace to be over 10G in size. Its because I do a lot of temporary work with temp lobs. Some of the databases I work with have temp over 30G in size. 30G might sound like a lot, but 1Tb costs less than $200. So don't get cheap with storage. Also remember that temp tablespace can sit on a cheap disk because if its lost, you don't need to recover it. So no need to worry about backing it up.

Q: Any special init.ora parameters needed?
A: No.
(what, you were expecting an answer here. There are some parameters but you don't need them. In Oracle 11G the defaults are great. You tune multimedia by getting your storage parameters right to begin with).

Q: What about Oracle Multimedia and Oracle XE (Express Edition)?
A: Oracle Multimedia uses Java for its methods. Java does not come with the Express Edition. The types for Oracle Multimedia need to be manually configured in the database, but having the types is only half the story, the Multimedia methods can be very useful to have.
So no, Oracle XE doesn't work with Oracle Multimedia. There are ways around it that require programming (see www.piction.com)

Q: What limitations are there with ORDSYS.ORDIMAGE, ORDVIDEO and ORDAUDIO?
A: ORDPHOTO can only process a small range of image types. This include JPG, PNG, TIF and GIF. These are the most commonly used ones. Once your JPG grows beyong 4mb in size, you might have issues processing it in 10G (you will likely run out of java memory). In Oracle 11G, Oracle tiles the image and processes it in chunks. It means that as the image grows in size it will take longer to process it. TIF images can be processed at any size in Oracle 10G and 11G as the compression algorithm used differs to JPEG.

With ORDVIDEO and ORDAUDIO, Oracle will recognise basic characteristics of the image (e.g. duration). There are no methods available for processing it (like extracting a frame, cropping or converting between formats). Dealing with audio and video codecs is very complex. Horribly complex and expensive. Converting a 1minute video of TV quality can take over 30 seconds on a well configured box. There are open source tools in the market place to do it, but this is where I have spent a lot of time doing it, so either go to www.piction.com for more info or email me with questions you have (marcel@piction.com)

Oracle Multimedia Basics: Table Storage

2008-09-23T07:54:00.005+10:00

When creating a table there are a number of options available for deciding how the blob should be stored and accessed. You can determine the tablespace it will reside in, and whether it should be cached for performance.

Blobs should be stored in a tablespace with a block size 16K or greater, but if your database is created with 8K block size, don't feel obliged to create a separate tablespace with block size 16K, unless you (or your DBAs) are skilled in SGA memory management. If you are using RAC and your block size is 4K, tuning will be different, and its best not to cache the blobs.

The dimensions used to decide on the storage options are as follows:

How large in bytes will the blob be?
How often will the blob be accessed?
How critical is memory usage on the instance?
How critical is performance accessing the blob?

To highlight this, think of an application where you have an original image and its thumbnail. The thumbnail will be small (typically under 4k) and accessed frequently. The original might not be accessed that often. As such, the thumbnail should have a different storage option compared to the original:


create table my_image
(
my_key                    number(16),
image_thumbnail           ORDSYS.ORDIMAGE,
digital_image             ORDSYS.ORDIMAGE
)
tablespace tablespace_128K pctfree 0 storage( pctincrease 0 maxextents unlimited)
LOB (digital_image.source.localdata) STORE AS l_image
   (TABLESPACE tablespace_10m
    disable storage in row
    STORAGE (MAXEXTENTS UNLIMITED PCTINCREASE 0)
    CHUNK 16384
    NOCACHE LOGGING)
LOB (image_thumbnail.source.localdata) STORE AS l_image_thumbnail
   (TABLESPACE  tablespace_10m
    enable storage in row
    STORAGE (MAXEXTENTS UNLIMITED PCTINCREASE 0)
    CHUNK 16384
    CACHE PCTVERSION 100);

In this example the original image is stored in the column called digital_image. Its configured not to be cached as its not accessed frequently. Accessing it will not consume SGA and will require i/o. The column image_thumbnail is typically under 4000 bytes, so for performance we store it in the row. We cache it in memory for faster access.

Tablespace will have to be created as locally managed. In the example above two tablespaces are used. One is created with extent size 128K and is designed for storing relational data. The other has a much larger extent size (10Mb) and is designed for storing large number of blobs. For those not familiar with tablespace creation I have included some example creation scripts below.

If running on Oracle 11G, we can enhance this example to use securefiles which are faster and better to use just by changing the STORE AS clause
from:
LOB (digital_image.source.localdata) STORE AS l_umo_photo
to:
LOB (digital_image.source.localdata) STORE AS SECUREFILE l_umo_photo

Following are example tablespace creation scripts:


CREATE TABLESPACE tablespace_128k EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K segment space management auto datafile size 400M;

Will create a tablespace of size 400M. OMF is assumed to be configured (OMF is Oracle Managed Files, and is the default location for datafiles when created. Its configured using a database parameter. To see if its configured - and if you have DBA access - type this command in:


SQL> show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
db_create_file_dest                  string      c:\oradata
db_create_online_log_dest_1          string

CREATE TABLESPACE tablespace_10m EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M segment space management auto datafile 'r:\oradata\VNLZ\piction_img_1.dbf' size 1G;

Will create a tablespace of size 1G on windows on the r: drive.


CREATE TABLESPACE tablespace_ml EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16K datafile '/usr/lib/oracle/xe/oradata/XE/piction_sml_1.dbf' size 100M segment space management manual;

Will create a tablespace used for storing small tables. Segment space must be manual as the database block size (16k) x 5 is greater than the uniform size. This example shows how to create a tablespace on Oracle XE and OMF is not used.

Oracle Multimedia Basics: Querying the attributes

2008-09-23T07:49:00.003+10:00

How do I access the attributes (column values) in a multimedia type?

Just remember that SQL*Plus can't deal with blobs, and as all multimedia types reference a blob, its not easy dumping this information out on the screen. Just keep in mind these tips:

Always use an alias on the table name
Use dot notation to access columns and attributes
In PL/SQL use a column alias to ensure the name is unique (and under 30 chars in length)
Don't use an alias that is an Oracle keyword
Avoid using alias names that might clash with PL/SQL variable names in your code

The following are examples (assume the column is myphoto of type ordsys.ordimage)

select u.myphoto.width width from my_image u;
select u.myphoto.source.srcname from my_image u where u.myphoto.width < 100;
select dbms_lob.getlength(u.myphoto.source.localdata) len from my_image u;

For those that are confused, just think of these types/attributes as very long column names. If it still confusing you can create a view thats sits over the table and hides the column names.

The advantage of using types is they are great for enforcing consistency in using common data sets. A good example is the person type. Rather than each developer creating their own table with its own attributes and column sizes for person (and their address and details). The DBA can create a central type and then everyone references it. It ensures consistency in data usage.

Oracle Multimedia Basics: What is in the type?

2008-09-23T07:45:00.003+10:00

The aim of the multimedia types is to group together related information. All the types reference another type called ORDSYS.ORDSOURCE which contains the binary (blob) for the type. The column that contains this type is called source. How do I know this? Its simple, from SQL*Plus you can reference the type's just by doing a describe on them. Downside is the describe will also list all the methods available which means there is a lot of information to see:

SQL> desc ordsys.ordimage
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 SOURCE                                             ORDSYS.ORDSOURCE
 HEIGHT                                             NUMBER(38)
 WIDTH                                              NUMBER(38)
 CONTENTLENGTH                                      NUMBER(38)
 FILEFORMAT                                         VARCHAR2(4000)
 CONTENTFORMAT                                      VARCHAR2(4000)
 COMPRESSIONFORMAT                                  VARCHAR2(4000)
 MIMETYPE                                           VARCHAR2(4000)

METHOD
------
 STATIC FUNCTION INIT RETURNS ORDIMAGE

A lot of information is displayed on the screen, most of it are the methods that are available to use. Think of a method as a PL/SQL function or procedure that is locked into the row. You can run a method that will take the current row (image) and perform an operation against it. With ORDSYS.ORDIMAGE you can invoke methods that rotate or crop the image.

The first bit of information displayed contains the attributes (sub types), or columns of the type. Internally in Oracle, when you define a type, Oracle breaks it down into the raw columns and stores them, which is why it is great that an Oracle table can store 1000 columns in it, because even if a table has a couple of types, if these types refer to other types and other columns then a table might contain hundreds of columns and you might no realise it.

Types can be nested. As seen in the example above, the column source is also a type of ORDSYS.ORDSOURCE. When we describe it, it looks like:


SQL> desc ordsys.ordsource
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 LOCALDATA                                          BLOB
 SRCTYPE                                            VARCHAR2(4000)
 SRCLOCATION                                        VARCHAR2(4000)
 SRCNAME                                            VARCHAR2(4000)
 UPDATETIME                                         DATE
 LOCAL                                              NUMBER
 MEMBER PROCEDURE SETLOCAL
 MEMBER PROCEDURE CLEARLOCAL

METHOD
------
 MEMBER FUNCTION ISLOCAL RETURNS BOOLEAN

and now we see the location of the blob. So to access the blob its column.source.localdata. Subsequent blogs will cover the syntax for accessing the columns/attributes in these types.

Oracle Multimedia Basics: Table creation with Multimedia

2008-09-23T07:37:00.004+10:00

I am putting together a series of blog's covering the basics of using Oracle multimedia. I am doing it to preempt questions that are usually asked when trying to configure it and hopefully fast track the usage of it.

To create an Oracle table that uses a multimedia type is very simple. There are a number of types you can reference depending on the data that is to be stored. Primary keys are not required on the table definitions.

The types are:

ORDSYS.ORDIMAGE - for digital photos
ORDSYS.ORDVIDEO - for video
ORDSYS.ORDAUDIO - for audio
ORDSYS.ORDDOC -for documents

Why such long names?

ORDSYS is actually an Oracle schema. This schema owns these types. So to access these types you need to be able to see this schema.

The following example shows how to create a table using the ORDSYS.ORDPHOTO type. Subsequent blogs will cover the storage and manipulation of these types.

create table my_image
(
mycolumn varchar2(100),
myphoto ordsys.ordimage
);

Its that easy.

OpenWorld 2008

2008-09-01T18:53:00.002+10:00

Presentations and events I am attending at OpenWorld 2008 in San Francisco.
Includes 11G Customer Experiences, PL/SQL Conditional Compilation and Oracle Ace in the OTN Lounge .

Following website contains all the details:

OpenWorld 2008 Agenda

Topics to Annoy #3 - Terms

2008-07-11T09:20:00.003+10:00

DBA-Donut: What happens when a DBA who was initially/strongly against a concept turns completely around on that viewpoint and supports it, whilst maintaining that they always did.

Bloat-XML: What happens when experienced or most likely inexperienced wanna-be XML developers take a perfectly good standard and bloat it out with garbage. As a result, to mark-up 1K of raw data will take over 100K of XML tags, name-spaces, comments and superfluous terms. Without any consideration to the concept of scalability, both on parsing data, but also the sheer amount of bandwidth required to store and move that XML data around.

Deviloper: When a Developer thinks they can do the job of the DBA, but do not have the depth of experience to understand what DBA's really do. End result is an application that doesn't scale, is insecure, and is built using some obscure concept that make it nearly impossible to tune or maintain.

Experiences #6 - Loading a database into itself

2008-07-09T12:07:00.003+10:00

The Ouroboros, is an ancient symbol depicting a serpent or dragon swallowing its own tail and forming a circle (reference: http://en.wikipedia.org/wiki/Ouroboros).
So being a bit mischievous, I asked the question "can you store a database within itself". I bet any DBA has thought about this at sometime when looking at lobs.

With securefiles and high compression, you could store in theory any database within a lob.
You can certainly store a virtualisation within a lob, but what about the exact same database? Would you be able to point to it, and store it in itself?
The exercise to do this is just one of those fun scenario's to play with.

The program I used for loading is one I have been using to test load virtualisations into the database. I have been saying for over a year now, that as the number of virtualisations grow, they will need to be managed.
I have to deal with over 30 at the moment, and then back them up and work out which ones go where and how they are to be used. Some are small and as little as 2Gb, and some are over 100Gb in size.

Having a database manage them seems sensible, even though they are quite large in size. Disk prices in the last 12 months have dropped to the point where 1Tb costs under $300.
One can't run the virtualisation from within the database, maybe that will happen later, but one can certainly back them up and store copies in the database.

So what happens when you try to load a database into a lob in the same database? As my database is split over 2 drives, I broke it up into two steps. The first step loaded the core database files, like system and sysaux.
They loaded in ok.

Source code found at : http://www.xor.com.au/presentations/prog.sql
(note: the routines os_command.directory_listing and gl.img_size, are my own routines used for getting directory listings and formatting output, so you need to replace them with your own equivalents).

Here is what happened:

WEBSYS vnlz> @w:\vmware_in_database\prog Start at:09-JUL-08 10.10.32.937000000 AM +10:00 . Deleted at:+000000000 00:00:00.031000000 . Loading:O1_MF_SYSAUX_46Y67DGN_.DBF[834224128]F . . Size:814.7 Mb . . Inserted at:+000000000 00:00:29.391000000 . Loading:O1_MF_SYSTEM_46Y66Z0H_.DBF[1073758208]F . . Size:1,048.6 Mb . . Inserted at:+000000000 00:00:56.234000000 . Loading:O1_MF_TEMP_46Y67SFO_.TMP[1073758208]F . . Size:1,048.6 Mb . . Inserted at:+000000000 00:00:17.219000000 . Loading:O1_MF_UNDOTBS1_46Y67GWS_.DBF[1073758208]F . . Size:1,048.6 Mb . . Inserted at:+000000000 00:00:42.313000000 . Loading:O1_MF_USERS_46Y684X9_.DBF[5259264]F . . Size:5.1 Mb . . Inserted at:+000000000 00:00:00.156000000 Finish:+000000000 00:02:25.344000000

The second part involved loading in 131Gb of allocated datafiles into itself. These datafile were pretty much empty.

Off course I was expecting it to fail, as even if it compressed it, there would be some point of infinite regression where it would have to fail.
This is the table definition:

create table vmware ( id number(16), core_file ordsys.ordsource ) tablespace piction_med_1 LOB (core_file.localdata) STORE AS SECUREFILE l_core_file (TABLESPACE PICTION_IMG_2 disable storage in row RETENTION AUTO COMPRESS HIGH KEEP_DUPLICATES STORAGE (MAXEXTENTS UNLIMITED PCTINCREASE 0) NOCACHE LOGGING);

Nothing fancy, just a PK and the tablespace it loads into.
This is the tablespace creation commands:

CREATE TABLESPACE PICTION_IMG_2 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1G segment space management auto datafile 'r:\oradata\VNLZ\piction_img_2a.dbf' size 60G; alter tablespace PICTION_IMG_2 add datafile 'r:\oradata\VNLZ\piction_img_2b.dbf' size 60G;

I had to create 2 datafiles because there is a max file size.

This was done on Windows XP, 64bit running 11.1.0.6. I just happen to have more free storage on this platform than on Linux. And the database resides on off the shelf SATA drives.
There is 8Gb of memory on the box.

Curious yet as to what happened?

WEBSYS vnlz> @w:\vmware_in_database\prog Start at:09-JUL-08 10.16.53.578000000 AM +10:00 . Loading:PICTION_ARC_1.DBF[104873984]F . . Size:102.4 Mb . . Inserted at:+000000000 00:00:02.860000000 . Loading:PICTION_IMG_1.DBF[-2147467264]F . . Size:10.49 Gb . . Inserted at:+000000000 00:04:47.047000000 . Loading:PICTION_IMG_2A.DBF[16384]F . . Size:62.91 Gb . . Inserted at:+000000000 00:39:24.703000000 . Loading:PICTION_IMG_2B.DBF[16384]F . . Size:62.91 Gb . . Inserted at:+000000000 00:50:12.687000000 . Loading:PICTION_IND_1.DBF[419446784]F . . Size:409.6 Mb . . Inserted at:+000000000 00:00:13.328000000 . Loading:PICTION_LRG_1.DBF[524304384]F . . Size:512.0 Mb . . Inserted at:+000000000 00:00:14.360000000 . Loading:PICTION_MED_1.DBF[524304384]F . . Size:512.0 Mb . . Inserted at:+000000000 00:00:15.078000000 . Loading:PICTION_SML_1.DBF[419446784]F . . Size:409.6 Mb . . Inserted at:+000000000 00:00:11.094000000 . Loading:PICTION_TSP_1.DBF[104873984]F . . Size:102.4 Mb . . Inserted at:+000000000 00:00:02.765000000 Finish:+000000000 01:35:23.937000000

Yep. It loaded it ok.

(note: for datafiles >2Gb, there seems to be a java bug where it incorrectly reports the file size. It didn't impact the load, just the reporting of the size).

WEBSYS vnlz> select gl.img_size(bytes,'A') from user_segments where segment_name = 'L_CORE_FILE';

GL.IMG_SIZE(BYTES,'A')
--------------------------------------------------------------------------------------------------
35.65 Gb

and it compressed the 130+Gb down to 35.65Gb.

Very surprised by that result. My next assumption is that what is in these lobs is corrupted as I didn't enable database backup online before doing the load. So the next question is, if I extract it from the database will the database work (assume I did enable backup online)?

I'll leave that for another time. I have other work to do.....

If someone can see fault in what I did, please let me know. Am happy to retry the test under different conditions.

Tip #5 - Using UTL_HTTP and SSL

2008-07-05T12:28:00.012+10:00

To access an SSL website using the utl_http package and PL/SQL is easy once you come to grips with the concept of a Wallet. The wallet stores all the encryption keys that the database needs to access the SSL site. But before looking at wallet config, lets review the PL/SQL code:

1. Declare some variables:
req utl_http.req;
resp utl_http.resp;
rw varchar2(32767);

2. Define the location of the wallet:
UTL_HTTP.SET_WALLET ( 'file:' || 'p:\wallet', 'mypassword' );

Where 'file:' is they keyword to state that the wallet is a file. 'p:\wallet' is the windows directory of the wallet. For unix, this might be '/u01/wallet'. 'mypassword' is the password required to open the wallet.

The disadvantage is that the password to the wallet is stored in clear text. Its recommended then that you create a wallet that only contains public keys from SSL sites, information which is publically available and will not pose a security risk. You can store multiple wallets in different locations if you are paranoid about security.

The disadvantage of wallets is that you need to know in advance each SSL site you will be accessing and extract its public key certificate. This isn't as flexible as a web browser, which does this for you.

3. Now access the SSL site. There are lots of ways of doing this, the following is just one:

req := utl_http.begin_request( 'https://www.amazon.com');
utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
resp := utl_http.get_response(req);

4. And then you can process the data that comes back using a simple loop:

loop
begin
rw := null;
utl_http.read_line(resp, rw, TRUE);
-- process rw
exception when others then exit;
end;
end loop;
utl_http.end_response(resp);

Wallet Configuration

5. To configure the wallet go into IE and retrieve the certificate of the SSL site (haven't been able to do this in firefox yet, which shows you that IE still has some uses).

You can either click on the certificate icon if it appears, or go to file-properties and retrieve it as follows.

Click on certificates to save it:

Choose the copy to file option:

And then choose the Base-64 encoded option to save it. Save it to a local directory.

Keep in mind the SSL certificates can be hierarchical, in that the certification of a site might be dependent on its parent (authorising) site also approving it.

So it might require extracting a couple of certificates to correctly authorise a site.

Once saved, create an Oracle wallet. There are similar options to the ones shown below in Linux and even if you only have apache installed. The java program is called: owm.cl

Go into the wallet and create a new wallet and specify its location as p:\wallet (or whatever directory is suitable for your database). Remember that the database has to be able to access the wallet, not your computer. So its best located on the same server as the database.

Then import the certificate you have just created.

You can cut and paste the certificate in, but I found it useful to keep the certificates in the same directory as the wallet, so I have a physical trail of all the certificates.

Don't forget to save the wallet. You will find that the program creates an ewallet.p12 file in the directory. Leave it as is, this is an important file.

The certificate is now installed. Your PL/SQL program can now access the SSL site.

Topics to Annoy #1 - Introduction

2008-05-15T08:21:00.003+10:00

As a DBA and Developer I get to experience the best and worst of both these worlds. I am constantly reminded by colleagues that I can't be both. I do like to always get back to my core roots as a DBA and do things that annoy developers and other people in general. My newest endeavor is this blog thread in which I will raise points that will make some people become very annoyed.

And as a DBA, it can be taken for granted that I am an arrogant, donut munching wannabe techo. And being arrogant, I can happily say (well say to me), that I am not interested in other people's viewpoints on these annoying topics. But by all means, please respond and try to annoy me.

My first issue raised to annoy, is the strong view that a DBA is useless as a DBA unless they can program. In particular a DBA that can't write PL/SQL and/or Java is just a novice, a wannabe DBA. Why? Lets raise some points of contention:

Security. Important point if you are a DBA (but how many DBAs really understand security, especially security in web apps?) How many DBAs review the code to ensure there are no backdoors in, loop-holes allowing code injection or just front doors that haven't been closed. I doubt many can, because they can't program.
Tuning. How many DBA's can go through a PL/SQL program and determine if its well written, well integrated with the database and performs optimally? I doubt many, because they can't program.
Being the expert. In my early days as a DBA, the DBA's were promoted from the ranks of developers. They were the experts, the best of the best. They were the ones that the developers could go to, to get the best advice on programming and development. Ah, the good ol days. Not like that now. How many DBAs are just there now to manage backups and nothing else?

So that's it for now. There are more points worthwhile raising, but lets not give the whole game away. I now hope you are suitably annoyed and irate.

Topics to Annoy #2 Which operating system is better - Unix or Windows?

2008-05-15T08:20:00.002+10:00

Neither. Its VAX/VMS, so deal with it. But its dead and gone, and its not coming back.

But for those who have never experienced a real operating system, take it to heart that neither Windows or Unix are better than each other. What is best doesn't win. And I see Windows and Unix both in this camp.

The Mac had a superior interface to Windows for a long time, then they lost it. The Unix shell interface and DOS are still required to be used even now, but are woefully primitive and not user friendly. Even TCP/IP, the network which made the internet, is backwards. I mean, we have to use tools like FTP to transfer files. For example, why can't I just do a simple copy (not cp, how unfriendly is that) from one machine to another? Why do I need ten different forms of commands to do the one simple thing (read xcopy, rcp, cp and all other variations). If you have used Vax/VMS before you know what I mean here, for the others scratching your heads at this, get a dust covered book from a library and look at VMS commands (its not likely to be referenced on the net).

So when it comes to the command line interface, Unix is far superior to Windows and DOS. Unix has a command line mentality, whereas Windows is driven by the GUI Interface.

As for GUI interfaces, Unix people - deal with the fact that so far no GUI interface available is even close to what Windows can do. What about Ubuntu? yep it has gimmicky features, but when push came to shove, there is no way I could give Ubuntu to a novice user, like I can with windows. It just isn't mature. Try do anything advanced and you have to go to the command line. The GUI tools aren't there yet. To make a user friendly and simple interfaces takes a lot of work, a huge amount, and this hasn't happened yet in Unix. Microsoft with all their resources are struggling to get it right and most of the time don't.

As for the other GUI's like KDE, Gnome, Solaris Java and the others I deal with, they are equally primitive and require extensive Unix knowledge to use them properly. But now we have Vista and we see how trying to provide GUI tools for novice users can end up making life miserable for advanced users.

As for Mac OSX, yes it lives on Unix. But I am an old Mac user and just see it as a GUI with a Unix shell interface. So I try not to put the Mac in the same camp as Linux and other Unix derivations. The Mac Interface is great, but like Windows can improve.

My view? We would be more advanced technology wise if we didn't have Unix or Windows and could go with what worked best. But, what worked best wasn't free, and the internet would never have emerged if it wasn't for Unix and its mutant offsider TCP/IP.

So I tolerate Windows and Unix. I don't get involved in which is best, because I know which is best, and its not these two. And I swear at them, get angry out how backwards they made the world with computing.

The next step would be to put Windows on Unix. Which is funny, but stranger things have happened.

I would say to the Unix world, if you want to win the GUI interface war, get all the students at Uni's around the world and get them into competitions to build better interfaces for the existing tools, and to GUI-fy all existing command line functions. With thousands of Uni students working on this, then we might start to see something that is truly usable by novice users. Then, and only then would emerge a true universal operating system that everyone can use.

Tip #4: On Upgrading a Web App from Oracle8i to 11gR1

2008-04-28T12:16:00.005+10:00

Though not officially supported, it is possible to migrate data from Oracle8i to 11gR1. Using the standard export/import the migration is normally quite painless, espcially when done one schema at a time.

For web based apps, the following are some issues to contend with:

1. Security

A new privilege has come in 11g that might bite you if you use utl_tcp (utl_smtp, utl_mail, utl_http and other tcp/ip based tools). The new dbms_network_acl_admin package allows fine grained control over access to the network. If you just want to migrate and get things working, the following will give a schema full network access from within the database:

BEGIN
-- required for Oracle11 to access network
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'myapp.xml',
description => 'Network permissions for *',
principal => 'SCOTT',
is_grant => TRUE,
privilege => 'connect');
END;
/
commit;

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'myapp.xml',
host => '*');
END;
/
commit;

exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('piction.xml','SCOTT', TRUE, 'resolve');
commit;

SELECT DECODE( DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE( 'myapp.xml', 'SCOTT', 'resolve'), 1, 'GRANTED', 0, 'DENIED', NULL) PRIVILEGE FROM DUAL;

2. SQL Aliases when using objects

Any select statement that references an object needs an alias to reference it, and an alias name to make it unique.

So if your code was:

select myimage.source.localdata from myimage;

should be changed to

select u.myimage.source.localdata blb from myimage u;

3. Ordering

In 8i to 10G when Oracle retrieved data from an index it kept it sorted. In fact, it was a useful tip to remove ordering and retrieve data just from the index.
From 11G this cannot be guaranteed. The optimizer now has new algorithms for doing queries and it might not mean the data comes back sorted.

The solution is to put an order by on the statements.
Workaround? Put an order by on SQL Statements. Yep, its painful.

At this point of time I am not aware of any init.ora parameter or lingusitic setting that forces a sort.
The SQL Standard does say that to ensure ordering using an Order by. This doesn't help if you have 3rd party apps or if you can track down the source for your app.
I don't think Oracle realise that this one cosmetic issue will cause most sites to not upgrade, as its this cosmetic issue that causes users the most heartache.

This issue has impacted upgrades I have done and made me rethink coding strategies when trying to build an app that works across multiple Oracle releases.

Experiences #4 - on File System Scalability

2008-03-17T09:13:00.005+11:00

If anyone says lets keep our digital assets (read images and documents) stored in a file system, I will ask them to do a simple scalability test. Create a folder (directory), put into this folder 10 other folders, then into each folder put 30,000 images or documents of any size. Then in a couple of these sub folders or sub directories create a couple more sub directories and put in some more images. Have about 500,000 images in total.
Now this might seem like a lot, but that's just PC mentality (which is, if it works fine with 10 images it will work fine with 100,000).
OK. Now reopen file explorer and navigate to the top folder. Open it. And wait. And wait. And keep waiting. It gets worse if the directory is on a network drive. Eventually after one, maybe two minutes you get to see the contents. Not scalable, not even close. (on a laptop you only need 30,000 assets in a couple of folders to experience this issue). Then try getting more than one person to access the directory whilst its busy looking at it (at this point in time I go to DOS which is quicker). Scalability fails on number of assets, number of concurrent users and total number that can be stored.

Now lets review this concept if the digital assets are stored in a database. So how long is it to run a query to get a view of everything? Less than a hundreth of second, and it performs the same if a hundred users do this query at the same time.

and now the coup-de-grace. Try putting more than 65,000 images into just one directory. Windows doesn't really like that. I believe the same issue is on Linux and the Mac.

These operating systems were not built to scale to deal with that large a number. Its not their fault. For a small numbers of digital assets they are very user friendly and work well.

So the lesson is this, when it comes to storing your digital assets, store them in a database that can deal with them. Don't leave them in a file system that cannot scale to manage, work with and deal with them.

Tip #3: Multipart Mime Headers

2008-03-14T10:30:00.005+11:00

This tip will show a number of methods for quick and simple header setup. This can be useful when you want fine grained control over how a file is to be downloaded via the browser.

i. In all cases the constat NL_CHAR should be defined as :

NL_CHAR constant char(1) := chr(10);

ii. In all cases, make sure the htp.prn is the very first statement that sends data back to the browser. If not, the header file will become corrupted with standard output.

a. Create a CSV File

htp.prn('Content-type: ' || 'text/csv' || NL_CHAR);
htp.prn('Content-Disposition: attachment; filename=' || 'myfile.csv' || NL_CHAR); htp.prn('Content-Transfer-Encoding: binary' || NL_CHAR); htp.prn(NL_CHAR); htp.p('Header1,Header2,Header3,Header4');
htp.p('Row1,Row2,Row3,Row4');

will produce a csv file for download called myfile.csv with two rows in it.

b. For forcing a download of a jpeg image, rather than displaying it in the browser:

htp.prn('Content-type: ' || 'APPLICATION/octet-stream;' || NL_CHAR);
htp.prn('Content-Disposition: attachment; filename=' || 'myfile.jpg' || NL_CHAR);
htp.prn('Content-Transfer-Encoding: binary' || NL_CHAR);
htp.prn(NL_CHAR);

wpg_docoload.download_file( dimage.source.localdata );
-- where dimage is of type ordsys.ordimage, and this points to the jpeg blob previously retrieved

c. For downloading very simple xml stream:

htp.prn('Content-type: ' || 'text/xml' || NL_CHAR);
htp.prn('Content-Disposition: attachment; filename=' || 'myfile.xml' || NL_CHAR); htp.prn('Content-Transfer-Encoding: binary' || NL_CHAR);
htp.prn(NL_CHAR);

htp.p( '<?xml version = "1.0"?>' );
htp.p('<xmlresponse>');
htp.p('<value>' || 'ValA' || '</value>');
htp.p('<error>' || 'ValB' || '');
htp.p('</xmlresponse>');

This method is also useful when creating ajax calls.

This shows how easy it is to quickly create your own mime type header and download files. The real challenge will be trying to work out the mime headers for obscure file types.

Tip #2: Piclens, RSS integration

2008-03-14T10:29:00.009+11:00

Piclens is a plug-in for most browsers that allows a virtual wall to appear where the images can be viewed.

Piclens uses an RSS feed and some additional HTML on each image to determine how the virtual wall is to be configured.

First Piclens has to be installed: see http://www.piclens.com. Once installed, and once the program is running correctly, when you move your mouse over an image a "Play" button will appear.

When you click on the "Play" button, the plug-in is activated and a virtual wall will be created in the browser allowing a unique way of viewing the thumbnail images and web quality:

To configure Piclens requires setting up an rss feed for the images to be displayed and then mapping the image in each feed to the thumbnail. This is not as hard as it sounds.

As Piclens uses industry standard RSS, other plug-ins that follow the RSS standard will be able to use it as well.

To integrate piclens:

Step 1: If your program to display your thumbnails is called mpack.proga, it might look like:

for crec in (loop through retrieving all thumbnails) loop
htp.img( 'mpack.progb?tnail_id=' || crec.id, cattributes=>'border="0" title="' ||
crec.tnail_name || " alt="' || crec.tnail_name || '"');
end loop;

The program mpack.progb will retrieve the thumbnail blob using something as simple as

open c(tnail_id); -- where cursor c retrieve the Oracle Multimedia Image. fetch c into dimage;
close c;
owa_util.mime_header(dimage.mimetype); wpg_docload.download_file(dimage.source.localdata);

Step 2: Put in the header, a call to retrieve the rss:

htp.htmlopen;
htp.p( '<HEAD>');
htp.p( '<link id="gallery" rel="alternate" href="ump.piclens?rnd=' || replace(to_char(systimestamp),' ','+') || '" type="application/rss+xml" title="">' );

htp.p( '</HEAD>' ); ...

The rnd parameter is a simple method used to ensure that subsequent calls are not cached. The program mpack.progc will look like:

htp.prn('Content-type: ' || 'text/xml' || NL_CHAR);
htp.prn('Content-Transfer-Encoding: utf-8' || NL_CHAR);
htp.prn(NL_CHAR);

htp.p('<?xml version="1.0" encoding="utf-8" standalone="yes"?)
<rss version="2.0" xmlns:media="http://search.yahoo.com/mrss" xmlns:atom="http://www.w3.org/2005/Atom"> <channel>' );

for c1rec in c1 loop
htp.p('<item>
<title>' || c1rec.tnail_name || '</title>
<link)' || 'mpack.progb?tnail_id=' || c1rec.id || '</link>
<media:thumbnail url="' ||'mpack.progb?tnail_id=' || c1rec.id || '"/>
<media:content url="' || 'mpack.progb?web_id=' || c1rec.id|| '"/>
<guid isPermaLink="false">' || c1rec.id || '</guid>
</item>' >;
end loop;
htp.p( '</channel>
</rss>');

The progb?web_id= c1rec.id call, will return the web quality version of the image (assume progb is overloaded). Also:

NL_CHAR constant char(1) := chr(10);

Step 3: Modify the original thumbnail call to add link the guid in the RSS feed to the thumbnail:

htp.img( 'mpack.progb?tnail_id=' || crec.id, cattributes=>'border="0" title="' || crec.tnail_name || " alt="' || crec.tnail_name || '" #gallery ' || crec.id || '' );

Step 4: And to make sure that the class call: mbf-item, is correctly handled, modify the header to include this class definition. Also add a call to retrieve the piclens javascript to allow the play button to be marked up.

htp.p( '<script type="text/javascript" src="http://lite.piclens.com/current/piclens.js"></script> ' );
htp.p( '(<style type="text/css"><!-- ' || '.mbf-item { display: none; } ' || '--) </style>' );

And that should be it. Piclens works well in Firefox. As of this blob, it works well in IE, but if the screen is built using Frames, Piclens will not show (but in firefox it works well).

You can also embed a slideshow (refer to the piclens site for the javascript call).

Experiences #3 - on VMWare

2008-03-14T10:04:00.006+11:00

I am addicted to using VMWares. I call them that because virtualizations gets me tongue tied. It started mid last year with one. It worked. It worked well, and I saw the possibilities. Then I created more of them. Some were as small as 2Gb, some as big as 100Gb. Then I expanded to different operating systems. Not just windows, but Linux and its many variants (SUSE, Ubuntu, AsianUX), then Solaris. And with each I learnt about the many quirks of each operating system.

I recently learnt that Solaris doesn't really like it when an IDE card fails and sends bad data to the vmware. It gets corrupted easily. I learnt that the internet is the best resource for finding solutions and any vendor supplied documentation is useless. I have learnt that snapshots are great for testing but one shouldn't rely on them for recovery. Use them with database backups.

I am grateful disk prices are dropping. 1Tb is less than $300 and getting cheaper every month. This means I can feed my habit by creating more. I need them for testing, for playing, for prototyping and for quick customer setup. It has saved me a lot of time and made life easier. But now I do more things in that time saved, rather than sitting back, reading the paper and munching on donuts. A complete install and setup, is as quick as copying a vmware and making some rapid network changes on restart. A complete working environment is now done in hours, when just a year ago it would have been days.

Now I am having trouble managing them. I have 12 running on two machines, and need more. A number are paused waiting to run, but because of memory limitations are just waiting. I never thought I would have to learn about DNS, I mean how hard can it be? But I am now trying to build a DNS server to make it easier to manage them all. I have over 40 of them, and each is unique in its setup. We copy them and send them between states in Australia. Its still quicker to put a 40Gb Vmware on a cheap USB disk an mail it, than try and copy it over the network. In 5 years this will likely change. But by then, my VMWares will be 10 times larger.

I have thought that it would be an efficient idea to store my VMWare backups in a database. And shortly I will be testing this out. Once I get that disk space. A new Oracle Multimedia object for VMWares will be created and I'll see how well loading a 40Gb file into the database is. Give me 6 months to do the tests and learn from them, and the results will be posted.

Experiences #2 - on Spam

2008-03-14T09:38:00.004+11:00

You got to give some credit to spammers. Their arrogant attitude of, "if its free I have a right to use it" is inspiring. I receive now over 200 spams a day, all because my email address is posted on some sites. Using Thunderbird, it gets most of them, but some get through. They motivate me into action, and in trying to build programs to check and understand how they get through I have learnt more about the english language and now imaging, as they send me spam pictures.

I do sympathize with countries where they get billed for traffic received and sent (New Zealand is one). They must appreciate the spams received and realise its not free, and they are paying for it. Imagine receiving your usual 500 junk mail pamphlets and a bill from the post office comes with it for having to deal with it.

Rather than feeling disheartened by their callous business attitude, I now use it to my advantage and will one day have a set of programs that will hopefully reduce in volume the amount and limit its impact. With new problems comes new ideas for solutions and new strategies for dealing with it. I think Bayesian filtering is too easy to circumvent and totally different ideas are needed to deal with it. If we treat spam as a bacteria, then using just one antibiotic initially stops it, but breeds resistance. The best way is to use a variety of different methods simultaneously. And if I come up with a good way, I'll let everyone know, its just finding the time to work on this hobby horse.

Experiences #1 - on being a CTO

2008-03-14T09:16:00.005+11:00

The fun part about being the CTO of a startup company is that you get to experience and work with a wide variety of technology. In addition to doing DBA work on small databases and very large ones, I am also rebuilding servers, configuring firewalls, setting up large storage devices and managing day to day activities of databases around the world. Of course I have help, but I am very hands on. I also have to do a lot of development work and there is a lot of self teaching.

Just yesterday morning, I completely rebuilt an Oracle11G database which had been corrupted (still not sure how, but when the data dictionary goes, it goes), put together a technical presentation, fixed a number of programs, put together server specifications for a new computer and worked with four work colleagues on different issues. Later that afternoon I did some research on Wiki and worked out how to design a social tagging system within Piction along with a simple method for doing data clouds (it helps to know PHP as most of the examples are written in this language). The challenge with social tagging (also known as folksonomies) is the security and what's called data noise. Am grateful the existing architecture I have supports this, so with just a few tweaks I should be able to hook something in.

Its tempting to think that new ideas and knowledge comes from specialising in a particular field. I don't subscribe to this thinking. I believe that new ideas come from looking at other fields. So a lot of ideas for database tuning haven't come from doing a lot of DBA work, rather it comes from learning about XML, firewalls and web languages. By understanding them, new ideas and tactics for tuning came from them. As such I keep up to date on security, XML, webservices, multimedia and most features in the database. Usually its just enough in depth knowledge to know how they work (which usually includes building something in them). Tuning is just one example. Its also important to note that technology is changing and changing quickly, so ideas and solutions need to change (or might need to change). By specialising its very easy to get trapped or locked into ideas and it becomes hard to see new ways of doing things. It can be hard to abandon knowledge gained, especially if it is now out of date. For the web world, its crucial to diversify and adapt.

Tip #1 - Blob Load Methods

2008-03-07T15:21:00.001+11:00

To load a blob into Oracle there are a number of methods available. Some work well, some don't and some are good to use in certain scenarios.

1. dbms_lob

Method is loadfromfile.

Pro's: It scales well, both in repeated usage and number of images in the one routine.

Con's: Requires directory access setup and configuration. If all your images are in the one and only directory, this isn't a problem. But if they are randomply scattered it can become difficult.
Also requires opening and closing and some config

e.g.

tmpvar varchar2(4000);
directory_name varchar2(4000) := 'mydir';
bf bfile;
begin
tmpvar := 'myfile.jpg';
execute immediate 'create or replace directory mydir as ''c:\temp'''; -- there are variety of ways to achieve this, all dependent on security and location of images.

bf := bfilename( directory_name, tmpvar );
dbms_lob.fileopen( bf, dbms_lob.file_readonly );
len := dbms_lob.getlength(bf);
dbms_lob.loadfromfile( dst, bf, len );
dbms_lob.fileclose(bf);

The DIRECTORY option in Oracle inherits permissions in subdirectories from the parent, so if you are allowed to read from the parent you can read from the sub directories. This is different to utl_file where each directory has to be defined.

2. utl_file

utl_file.get_raw can read the binary from a file, and using dbms_lob.writeappend can then build a blob.

Pro's: If the utl_file_dir parameter is set to *, then you can easily read in any file from the file system.
Con's: I have noticed on some windows versions, that the raw data coming in gets corrupted. This is fixed in 11G. If utl_file_dir is not set to *, then every possible directory has to be defined, and each change, requires a database restart.

3. ordsys: importFrom

Similar to dbms_lob.loadfromfile. Requires directory configuration, but available as a method for the image.

Pro's: When using Oracle Multimedia, its simpler to use and proven scale. Loading in large number of images of any size does not incur memory leaks. On load, can identify and process image, saving time and making it easier to program.
Con's: Need to know the exact image name and can't use wildcards. Need access to the directory for loading, and directory configuration can be difficult if images are located in a number of locations. Only works with Oracle Multimedia. To work with a blob, requires the creation of a temporary image type.

4. Java

A small java program can be written to read a file and create a blob. This requires the configuration of java to access external files (see dbms_java.grant_permission).

Pro's: Fine grained control over how it reads. Ability to implement a sleep in the program, if large numbers of concurrent processes are loading, to allow for manual load balancing. Can access and file on any directory (when permission given). No requirement for directories. Its fast and simple.
Con's: In 11G, when loading ten of thousands of images, memory leaks occur closing the files.

5. SQL-Loader

Use SQL*Loader to bulk load images into the database.

Pro's: Fine grain control over the load and ability to monitor it. Useful for one-of loads, or loads where a DBA can control and monitor it.

Con's: Its very hard to invoke SQL*Loader from PL/SQL, especially if that PL/SQL program has to write the control file. It's a lot simpler to use one of the above method.