Web services

I have been going around websites/wikis to find a definition for, what is a webservice? Especially I was interested to find out the kind of web services, which of course, have to do with databases, Oracle in particular. I spend the whole day looking at certain websites and here is my DIY one-liner definition:

Web services is all about give and take data over HTTP. When you give data you are a 'provider' of a web service and when you take data you are a 'consumer' of a web service.

Oracle 11g is doing both.

You can turn your Oracle database into web service provider, a source of data for the internet where people can come to a certain URL (WSDL) and be able to query data that you keep in your database, just using HTTP. The visitors to this URL will run your database PL/SQL procedures, functions and packages using this URL and then they will be able to read the results. This setup is called being a provider of a web service.

On the other hand, being a consumer of a web service in Oracle, is being able to write SQL or PL
/SQL which makes external calls from your Oracle database to other web service providers' services on the internet. I have used this type of webservices in my blog. An example of such a web service is when you make an HTTP_REQUEST to the Google Maps API or other sources such as Yahoo and PayPal which themselves are providers of web services. See this demo app where I use Google Maps and Forecast.io API

Link: http://www.beachmap.info

When your database makes a call to an external web services then you become the consumer of  a web service.

A platform to try both, with no hassle, of course is Oracle Apex. The installation of Apex in Oracle 11g will enable you to use web services in both ways described above.

The best place to start is Chapter 33 of Oracle XML Developers Guide. Here you will find how to setup and get Oracle 11g to use Native Oracle XML DB web services. Once you set up, all you have to do really is create a PL/SQL procedure or function and the WSDL is automatically generated and saved in Oracle for you. All you have to do then is just run the procedure from the URL via a little program which will call this web service, yes you can use 'wget' or any other command line script which will make a call to web service.

The next link is a good blog entry which describes step by step how to setup Native Oracle XML DB Web Services  and how to Access PL/SQL stored Procedures Using a Web Service in Oracle. The post is called the  First Tests of 11g native web services by Paul Gallagher I really liked Paul's post, is concise and straight to the point. It even has a perl script which tests to see if your web service provider database setup works.

Other related posts on the topic of Oracle web services are:

Create a Native Database Web Service by Marco Gralike

CONNECT_BY_ROOT with XMLAGG

This is a post about Oracle hierarchical queries. The other day I was after a solution which would display the hierarchy in the execution of some PL/SQL procedures. That is, there was  a main procedure which would call several dozens of other procedures in a predefined order. This order was pre-recorded in a table. What I was after was the ancestral relationship in the order of executions. To demonstrate simply, say there is table called steps, which looks like this:




MAIN_ID                DEPENDANT_ID           
---------------------- ---------------------- 
1                      2
1                      3   
2                      4
2                      3

Process MAIN_ID 1 depends on the execution of 2 and 3 first and then 2 depends on the execution of 4 and 3. Can we execute 1 after we execute 2 and 3?

Well the table says YES!. But how about ancestry? We can clearly see that for process 2 to execute process 4 must execute first! Wouldn't it be nicer, if we could show this ancestry relationship via an SQL query straight out of this table? An SQL query which would show us all dependants of 1 and 2, including parents, granddads etc...

So the results we are after could look like this:

MAIN_ID                DEPENDS_ONTHIS
---------------------- --------------
1                      2,4,3  
2                      3,4 

Here is the query to obtain such hierarchical relationships out of this table by using the CONNECT_BY_ROOT, Oracle 10g operator and some XMLAGG function magic for hierarchical queries:

select main_id, rtrim(xmlagg(xmlelement(d, dependant_id||',')).extract ('//text()'),',') depends_onthis 
from 
(
SELECT  distinct connect_by_root main_id main_id,
    dependant_id
  FROM steps
    CONNECT BY prior dependant_id=main_id
  ORDER BY 1,2
)
group by main_id
order by main_id

MAIN_ID                DEPENDS_ONTHIS
---------------------- --------------
1                      2,4,3  
2                      3,4 

I found this new Operator in Oracle 10g amusing and cool. Here is the Oracle documentation:

And here is the test table scripts if you want to run the above test case for yourself:


drop table steps;

create table steps
(
main_id number,
dependant_id number
);

insert into steps values (1,2);
insert into steps values (1,3);
insert into steps values (2,4);
insert into steps values (2,3);



Materialized View Refresh and the ATOMIC_REFRESH parameter

When refreshing big materialized views in large data warehouses it is always good to check the parameter options available in the DBMS_MVIEW.REFRESH procedure.

Oracle changes the default parameters of its DBMS packages from release to release. I remember back at the times of Oracle 9i a complete refresh would truncate the materialized view, thus the only work that the database was actually doing in a complete refresh, was just an INSERT after the TRUNCATE.

Now in Oracle 10g and Oracle 11g parameters have changed. When there is a COMPLETE materialized view refresh, for the purposes of data preservation, a DELETE is done instead of a TRUNCATE!

The reason for this is because Oracle "changed" the default parameter value of ATOMIC_REFRESH in the DBMS_MVIEW.REFRESH package. In earlier releases the parameter was set to FALSE by default but now it is set to TRUE, which forces a DELETE of the materialized view instead of TRUNCATE, making the materialized view more "available" at refresh time.

But this DELETE is an expensive operation in terms of refresh time it takes. A DELETE is always expensive and sometimes even impossible when we are talking about the complete refresh of materialized views with millions of rows. At COMPLETE refresh a DELETE command takes long time, as it has to make the materialized view available to the users and comply with the ACID theory and its transactional reasons, but who cares? What if we really want to get the refresh done quickly and we don't care about the availability of the materialized view, say in a data warehouse maintenance window?

I have tested how the refresh time improves when you set the ATOMIC_REFRESH => FALSE in the procedure on a 1 CPU home computer with Oracle 11g. If you have more CPUs it will even be quicker because of the PARALLEL parameter.

I just wanted to show the improvement you get in refresh times when ATOMIC_REFRESH is FALSE and Oracle does a TRUNCATE instead of a DELETE at complete refresh. A real time saver with big materialized views in data warehouses. Here is the test:

-- Enable parallel DML

ALTER SESSION FORCE PARALLEL DML


-- Cleanup

-- DROP TABLE BASE_TABLE;




-- Create the Test table

CREATE TABLE BASE_TABLE

(

id NUMBER(10) PRIMARY KEY NOT NULL,

X VARCHAR2(255),

Y VARCHAR2(255)

)

PARTITION BY RANGE

(

ID

)

INTERVAL

(

100000

)

(

PARTITION P1 VALUES LESS THAN (100000),

PARTITION P2 VALUES LESS THAN (200000)

);




-- Make the test table paralllel

ALTER TABLE BASE_TABLE PARALLEL 5

Load the test table with data from a dummy cartesian join so that you get millions of rows. Do you like my loop? Loops only once. Well I had it there to add more millions if I wanted to :-) >

BEGIN

FOR I IN 1 .. 1

LOOP

INSERT /*+ PARALLEL(BASE_TABLE, 5) */ INTO BASE_TABLE SELECT ROWNUM, a.object_name, a.status FROM all_objects a, emp b, dept c;

END LOOP;

COMMIT;

END;




3010392 rows inserted

Create a materialized view log for fast refresh

DROP MATERIALIZED VIEW LOG ON BASE_TABLE;

CREATE MATERIALIZED VIEW LOG ON BASE_TABLE;

Create the fast refresh materialized view

DROP MATERIALIZED VIEW MV_BASE_TABLE;

CREATE materialized VIEW mv_base_table parallel 5 refresh fast

AS

SELECT * FROM BASE_TABLE;

Update the Test table to simulate changing data

UPDATE BASE_TABLE SET Y='INVALID';

COMMIT;

3010392 rows updated

Now you are ready to do the materialized view refresh with the ATOMIC_REFRESH values set to TRUE and then to FALSE. Observe the refresh times. Refresh the materialized view with the two different values in the

ATOMIC_REFRESH parameter.




TRUE case with DELETE

EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_BASE_TABLE', METHOD => 'C', ATOMIC_REFRESH => TRUE);




Elapsed 558.8 seconds

Now is time to do the test with the ATOMIC_REFRESH parameter set to FALSE.




FALSE case with TRUNCATE

Must update again to simulate change in the logs

UPDATE BASE_TABLE SET Y='VALID';

COMMIT;

EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_BASE_TABLE', METHOD => 'C', ATOMIC_REFRESH => FALSE);




Elapsed 215.3 seconds

Half the time! Well if you have real computers it will be much faster. So again we see DELETE is really not good when you work with millions of rows. Nice to have options!

Migrate your Oracle 10g database to ASM

This blog entry is about migrating your Oracle 10g database to ASM using OEM Database Control on ubuntu 9.10 Karmic Koala. To be able to do this you must have:

• CSS service running on your ubuntu box
• ASM instance running
• OEM Database control running

To resolve your problems of starting the Oracle CSS daemon and ASM instance running on Ubuntu Karmic Koala, follow the resources I give below.

You will have to do some hocus-pocus with some parameters to adjust memory, improvise disks with dd (cause I only had one) and CSS script startup hick-ups. Insist with the resources excession.org.uk and pythian.com and oraclebase kindly provide and you will be running ASM on single instance on ubuntu 9.10 Karmic Koala in no time.

You should have the above three ready before you attempt to migrate your databases to ASM. You must go through the trouble and create an ASM instance with DBCA as per the resource below. No ASM instance no migration!

Once you are ready there only 4 steps to follow on the OEM and they are easy!

About ASM

For years DBAs and SAs have been looking over spindles and trying to decide where to put what. Which files shall I put on which disk? RAID this RAID that and so on...

What is the best way to tune I/O for Oracle?

The answer is, easily enough, Let Oracle tune I/O for you with their SAME (Stripe and Mirror Everything) approach. More, the mirroring and striping is done at the extent level and file level and not disk level.

That is exactly what ASM does, it balances I/O activity. It is an Oracle instance which runs alongside your database instances, in memory, and balances the I/O of database data files. It balances and tunes I/O whenever you add disks or you drop them, even online with no intrusion!

All your Oracle datafiles now are controlled by ASM and the way it does it is that ASM makes sure that a file is evenly spread across all disks when the file is allocated, so rebalancing is not required. 


All this sounds very good, if we don't care about I/O tuning what will happen to SQL Tuning? Are we going to do structural and logical tuning only?


Further, with emergence of "Oracle TimesTen In memory" database technology where the whole database can be loaded easily in solid state memory (Flash Disks), I wonder how long will ASM last?


Resources: 


http://www.excession.org.uk/blog/installing-oracle-on-ubuntu-karmic-64-bit.html

http://www.pythian.com/news/810/howto-set-up-oracle-asm-on-ubuntu-gutsy-gibbon/


http://www.oracle-base.com/articles/10g/AutomaticStorageManagement10g.php

Salesforce and force.com

I like salesforce.com and force.com. Both I think are real pioneers in the terra-incognita, or maybe I should call it the nimbus-incognita, which is called the "Cloud-platform". Both are very easy to use SaaS (Software as a Service) platforms. The applications are based on solid data modelling principles and the back-end relational database used is a massive parallel implementation of Oracle databases around the world. You can check their systems availability and the number of transactions done per day here trust.salesforce.com.

Although salesforce insists that their applications are stored in objects which are not "just database tables", their first step in creating an application involves capturing the data model. This is really a very "database" platform.

The platform offers a solid 2x2=4 environment to build and host applications and all work is done with a browser. That is, their IDE is the browser. They even provide you with a sandbox for your unit tests and and Eclipse plug-in for code access to the platform. The IDE is shared between their CRM platform salesforce.com and their developer platform force.com. In the salesforce.com platform you are able to extend a complete and very well designed CRM data model to include anything you wish. In the force.com platform you can create applications from scratch about anything! Among the two, force.com is what impressed me the most and made me to write this blog post. There is not much to be done in the salesforce.com platform, as is a complete working CRM application in itself which would suit any size company which needs a good CRM system.

Force.com on the other hand is a blank canvas. As long as you now what you want, you can start building it straight away. You start with the data model, then you determine your objects, and their relationships and once you have layered your data model in your system, the platform straight away provides you with the means to update and insert records in the back-end database. It automatically builds forms and reports. It also provides you with work-flow and automatic callendaring and emailing functions. Plus, it has a coding language called Apex, which is Java like with classes and triggers enabling you to enhance your application logic. It also has a very HTML like markup language called Visualforce. If you want to migrate your legacy data into the platform you should look at cloud ready ETL and data integration tools like Informatica (£££) and Talend Open Studio  (free).

A project I recently have done in Kizoom using this platform which took me 82 hours that is 10 days approximately to complete. The project was migrating all data and application logic from a legacy database system to force.com. During my work, data integration tools like Informatica and Talend helped me to move the data quickly into the force.com data model, and the already built in features of force.com provided me data entry forms and advanced reports. Because all this is in the cloud I was able to easily utilise geolocation with Google Maps API. The legacy system was a Microsoft SQL Server online database of fault records of computers in publicly available street kiosks around UK. Force.com made the application available in the cloud which meant the UK-wide field engineers of the company were able to easily login and access it on the road without having to use the company network (VPN) but just the cloud.

Below is a snapshot of the app.

Conclusion

The platform is very capable, easy to use and very intuitive. It has tremendous potential and achieves good economics in the development efforts. One shouldn't be shy to use it.