Database Systems

Salesforce to Oracle Real Time Integration

2011-12-16T15:44:00.001Z

In this blog post I will show you how to make Web Service like calls from Salesforce to Oracle, using Informatica Cloud Endpoint URL in real time.

In Winter 12 release of Informatica Cloud we will have the ability to make Salesforce outbound message calls to an Informatica Cloud task and enable real-time integration.

The idea is simple

Use Salesforce Workflow Rule (a database trigger) and make an outbound message call whenever a new Account Record is edited or created.

Step by step instructions

1) You will create a vanilla Data Synchronisation task, I called mine 'test ws', where you read the Account fields from Salesforce(left) and you UPSERT them to an Oracle(right), in a table called FROMSFDC like this:

2) Then at the 6.Schedule step of the Informatica Cloud task you will see the a new option called:

Run this task in real-time upon receiving an outbound message from Salesforce

When you tick this option, as seen above, an Endpoint URL is generated for the 'test ws' Informatica Cloud task. You can call this task whenever you save a record in Salesforce and force it to execute the UPSERT and update the Oracle table FROMSFDC. Next is to put the above Endpoint URL in a Salesforce Workflow Action (Basically an AFTER INSERT/UPDATE TRIGGER).

3) Next thing to do is to open up Salesforce and go to Workflow & Approvals > Outbound Messages and paste the above Endpoint URL like this:

4) Last thing to do on this is to create a Workflow Rule to fire the Outbound Message you have just created to call the Informatica Cloud task. Here I created something like this:

That's it! Now, don't forget to Activate the rule.

Whenever a new Salesforce Account is added to Oracle As well. Whenever a Salesforce Account is edited it is UPSERTED to Oracle as well.

Below you see the test records inserted in Salesforce and then Upserted to Oracle (Oracle Apex in this case).

Here is the record in Salesforce being created, look at timestamp.

That is the record created in Salesforce. Check the timestamp.

And here you can see the same record pushed in real-time to my Oracle Apex account via the Informatica Cloud URL Endpoint option in my 'test ws' task.

Conclusion:

It is easy to connect in real-time Salesforce and Oracle, or any other database. The benefits are tremendeous, for something similar with Web Services you would have to write code to send SOAP messages to the Database, and you would have to do lots of configuration work in the database to receive those messages. The beauty with Informatica Cloud Endpoint URL task schedule is that you can call Informatica Cloud tasks in real-time from Salesforce with zero coding and just config.

Note: The Data Synchronisation Endpoint URL functionality of Informatica Cloud will be available in January 2012. I have a beta test org which I have used for this blog post. You can find out more about Informatica Cloud Winter 2012 release here.

Update data in the same Salesforce Object with a 'Self-join' Informatica Cloud Task

2011-09-01T19:51:00.001+01:00

When you use Informatica Cloud you usually use it to do Migration, Integration and Synchronisation. That's about what it is used for, right?

It is all about moving data from point A to point B or syncing data between A and B. That is what Informatica Cloud is built for, to move/sync data from A to B and while moving it, maybe transform it as well. A is the 'source' and B is the 'target'. Correct! A can be an Oracle or ERP database and B can be a Salesforce Org. Classical use case of Informatica Cloud.

Well, not exactly. How about if you think a little bit differently.

How about if A is the source and A is the target, as well!

Yes, that's it. Read from A and Write to A via an Informatica Cloud synchronisation task. A kind of Informatica Cloud Data Syncronisation Task which will 'self-join' an Object or Table. To be used to 'self cleanse' or 'self update' the Salesforce custom object data or Oracle database table. This is possible, yes we can use Informatica Cloud as 'self-join' SQL command as well, to edit our data 'in-place' in one single object/table.

I will try to explain this concept with an example. Suppose you have data in your Salesforce standard object called 'Account' which you want to change. A standard approach would be to 'export' data in a CSV file via a report, change the data manually in Excel or something and then use Apex Data Loader to put the changed data back to the Accounts object in your Salesforce Org right?

The screen shots below show you how to do this type of operations without actually exporting the data, but doing it in place with an Informatica Cloud synchronisation task.

What this synchronisation task does is, it uses the same Salesforce Org connection and Account object as 'Source' and as 'Target'. That is it, the Account Standard object for this task is the Source connection and the Target connection at the same time.

This way the Informatica Cloud task, reads the data from the Account object and writes it back, after it transforms it, into the same Account object.

Create a Source on the Account object

Create a Target for the same Account object in the same org. Just choose as target the same object.

Field Mapping a kind of 'Self-Join', see Id=id

Use DECODE function in the same Field Mapping to manipulate data

The Informatica Cloud string function 'DECODE' does the trick. Observe how it changes the occurrences of the string 'Direct Employer' to 'Employer' in the Type field of the standard Salesforce Account object, which is actually the same object it reads it from.

decode(Type,'Direct Employer','Employer')

When you save and run this task it will read from the same Account object and write back to the same Account object the changed data. All within an Informatica Cloud Data Synchronisation 'Self-join' task! No file downloads, Excel use or whatever. Just one command. Given the plethora of Informatica Cloud String, Arithmetic, Date etc... functions available, just imagine what you can do to your data!

This is a wonderful and simple example of how Informatica Cloud can be used to cleanse data, I think.

Copy your Salesforce org into Oracle with CopyForce

2011-08-18T20:06:00.001+01:00

We found this really cool piece of code called CopyForce in Google code the other day which lets you:

Copy your Salesforce org objects (tables, indexes) into Oracle or any other RDBMS, yes you can!

The code I believe is freely available. But not to lose time I used the GUI which is available with trial a license of 30 days from CAPSTORM. In this post I want to quickly show you how you can copy your whole Salesforce org dev or prd with its data into Oracle, then do whatever you like with it.

Installing CopyForce with CAPSTORM GUI.

1. Download the CopyForceOrace.zip file from the above site.

2. Extract the zip file to a directory of your choice

/home/crazyman/copystorm

3. Change directory permissions chmod +x on shell script CopyStorm.sh

4. Start the GUI by running

./CopyStorm.sh

5. Start the CAPSTORM GUI, accept EULA.

6. Remember this is a 30 day trial only.

Once the GUI is up you just have to provide your Salesforce and Oracle connection credentials. Before that I logged to Oracle and created a dummy schema called ORA_SFDC and in this schema the CAPSTORM programme will copy the whole Salesforce developer org objects.

Once you have configured and tested both connections, then you can start the copy process.

Once the process finishes lets login to Oracle and see how many objects were created in this blank ORA_SFDC schema. So I can see by running an Oracle dictionary view SQL that I get in my Schema only tables, views and LOBs.

SQL&amp;gt;

select object_type, count(*)
from user_objects
group by object_type
order by 2 desc

OBJECT_TYPE         COUNT(*)               
------------------- ---------------------- 
TABLE               161                   
INDEX               161                   
LOB                 89

That's it, that's what you get in Oracle from a vanilla Salesforce developer Org and a few custom objects that I had in this particular developer org. 161 tables per org only, not bad huh!

Now just imagine what you can do with this!

The other good thing you get with CopyForce is since now you have copied your Salesforceorg  schema and your Salesforce data into Oracle, you can now use SQL to your heart's content and massage this data as you wish! That is, gone are the days where you had to dump Salesforce data to Excel and change it manually before you put  it back into Salesforce. Now you can change your Salesforce data in an Oracle database with all the sophistication ofSQL and put it back to Salesforce.

I think CopyForce has lots of future, and really would be a good choice for one-off data integration, data beautification projects.

I wonder what is in those LOBs ? I bet is the Apex code....

Salesforce Dev 401 Exam study material

2011-07-15T11:30:00.004+01:00

Just a quick post to share with you my past Salesforce Dev401 Exam study materials. I have assembled this table of 87 questions when I was studying for the exam back in January 2011.

These are not actual Exam questions, don't be fooled!

This is study stuff that I have compiled reading the study guides and the available training materials I found online. It did help me pass the exam. The quiz like questions I compiled helped me a lot to understand concepts and limits.

I have also tried to categorise the questions & quizzes by the Salesforce Dev401 study guide syllabus.

Here is the link to the questions: http://apex-outsource.com/pls/apex/f?p=309:Dev401

Hope it helps!

Salesforce API call from Oracle APEX

2011-04-02T23:40:00.001+01:00

This post is about integrating Salesforce force.com with Oracle using the Salesforce API.

I will try to show you how you can write a PL/SQL procedure to quickly send an SQL INSERT you do in an Oracle table to a Salesforce custom object as well. That is, how you can INSERT to a Salesforce object simultaneously when you insert to an Oracle table. I will use Oracle APEX, an Oracle RAD (Rapid Application Development) tool, as the Oracle database.

You can achieve this by using the Oracle APEX package APEX_WEB_SERVICE.MAKE_REQUEST and send SOAP envelopes to the Salesforce WSDL, without the HTTPS option, as I couldn't get that part working with my FREE Salesforce Developer account and my hosted Oracle Apex instance.

The idea is simple, you will create the PL/SQL procedure in Oracle, run it and insert data into an Oracle table and a Salesforce custom object simultaneously via SOAP envelope. The SOAP elements will be sent to Salesforce from the PL/SQL block using the APEX_WEB_SERVICE.MAKE_REQUEST package. The data flow direction is from Oracle Apex to Salesforce. To do this, you will create two SOAP envelopes.

1. One to get authenticated and login to Salesforce, once you login you can get the Salesforce sesionID.

2. And two to send the INSERT (Create in Salesforce parleur) data DML.

Here is the code you run in Oracle, you don't have to do anything in Salesforce, all you need in Salesforce is to have a custom object to insert the data into, your credentials and obviously your XML envelopes should be written accordingly based on the Salesforce WSDL which you will generate. I have the following code as a PL/SQL anonymous block in an Oracle Apex page process and does the job for me. Just to save you time, you need an Oracle Apex instance with enabled external network calls, unfortunately apex.oracle.com will not let you do that, that was when I checked it last time, you can always get a low cost fully external network enabled Oracle Apex instance from hosting companies, the one I use is Enciva.com

Enough said, here is the code make sure you pass in your Salesforce credentials with the security token.

DECLARE
l_envelope CLOB;
l_envelope_create CLOB;
l_xml XMLTYPE;
l_xml_create XMLTYPE;
v_session_id varchar2(1000);
BEGIN
wwv_flow_api.set_security_group_id;
--
--
-- ENVELOPE LOGIN
l_envelope :='<?xml version="1.0" encoding="utf-8"?>
   <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://
www.w3.org/2001/XMLSchema">
      <soap:Body>
         <login xmlns="urn:enterprise.soap.sforce.com">
            <username>YOUR@SALESFORCEUSERNAME</username>
            <password>YOURPASSWORDANDSECURITYTOKEN</password>
         </login>
      </soap:Body>
   </soap:Envelope>';

-- make request
l_xml := apex_web_service.make_request(
p_url => 'http://na7.salesforce.com/services/Soap/c/20',
p_action => 'http://na7.salesforce.com/services/Soap/c/20/login',
p_envelope => l_envelope
);
--
-- ENVELOPE CREATE
-- get session id
-- You will need the salesforce session id , I know this is an ugly way of getting it, write a better one then, I just wanted to get it working

v_session_id := replace(replace(
substr(substr(l_xml.getClobVal(),0,instr(l_xml.getClobVal(),'</sessionId>')),instr(substr(l_xml.getClobVal(),0,instr(l_xml.getClobVal(),'</sessionId>')),'<sessionId>'))
,'<sessionId>',''),'<','');
-- debug
dbms_output.put_line('session_id is: '||v_session_id);

l_envelope_create :='<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <soapenv:Header>
         <ns1:SessionHeader soapenv:mustUnderstand="0" xmlns:ns1="urn:enterprise.soap.sforce.com">
            <ns2:sessionId xmlns:ns2="urn:enterprise.soap.sforce.com">'||v_session_id||'</ns2:sessionId>
         </ns1:SessionHeader>
      </soapenv:Header>
      <soapenv:Body>
         <create xmlns="urn:enterprise.soap.sforce.com">
            <sObjects xsi:type="ns3:linkstream__c" xmlns:ns3="urn:sobject.enterprise.soap.sforce.com">
               <ns3:wbid__c>'||:P3_WBID ||'</ns3:wbid__c>
               <ns3:Link__c>'||:P3_LINK ||'</ns3:Link__c>
               <ns3:Description__c>'||:P3_DESCRIPTION ||'</ns3:Description__c>
               <ns3:Display_link__c>'||UPPER(:P3_DISPLAY_LINK) ||'</ns3:Display_link__c>
            </sObjects>
         </create>
      </soapenv:Body>
   </soapenv:Envelope> ';

dbms_output.put_line(l_envelope_create);

l_xml_create := apex_web_service.make_request(
p_url => 'http://na7.salesforce.com/services/Soap/c/20',
p_action => 'http://na7.salesforce.com/services/Soap/c/20/create',
p_envelope => l_envelope_create
);
dbms_output.put_line(substr(l_xml_create.getClobVal(),1,25500));

END;

As you can see the code executes in one PL/SQL block. You can call the procedure via an Insert Trigger on the Oracle table, or as a 'Page processing' process in Oracle Apex. It ads a link to my force.com sites custom object from my Oracle Apex app. I display this mock force.com sites on the left column in this blog, under 'My force.com apps' section.

Needless to say, there are maybe dozens of easier ways of doing the same thing between Oracle and Salesforce with tools such as CastIron, Talend, Informatica or Apatar. Some are even free. But hey, this is using no tool but just HTTP!

Salesforce workflow

2011-01-29T17:50:00.000Z

What is Salesforce Workflow?

Workflow in force.com, is business logic and in this post I will try to explain it.

In force.com, you can create custom objects and then you can create relationships on your custom objects according to the application data model you have designed. Very good, but if you don't have workflow, your force.com app will be nothing more than a lousy database. A mere database to enter and search records. What is salesforce anyway? Isn't it a data-centric, based around a database application building platform? Another database front end?

Not, just that! Force.com workflow will bring this database into life. Instead of being a stagnant repository of passive and past data, it will become a forward thinking and forward acting alive database. Maybe the thinking bit was a bit of an exaggeration but nevertheless, deductive databases is what we want, isn't it? Maybe one day...

What the force.com workflow engine does is things like: automatically send email alerts, assign tasks, or update field values based on rules that we define. That is it's job, and that is exactly what it does. The force.com workflow engine, based on rules we define, can:

Assign a 'Task' to a force.com user
Send an email alert to 'anyone' (a valid email will suffice).
Update the field of a particular record on an object
Send an outbound message to an external system, things like webservice call, SOAP etc.

When does the workflow action fire?

So a workflow can do all these things. That is great! But when? Based on what? It is nice to have the ability to send emails and update fields, but what determines or induces these actions? Well the answer is, The Workflow Rule.

The workflow rule is the main container where all these events are included and are programmed to execute. They execute only when certain workflow 'evaluation criteria' and workflow 'rule criteria' are met.

That is, the workflow rule is basically watching a custom object (it can only watch over one custom object at a time) and when certain evaluation criteria on the watched custom object and rule criteria (a condition) are met, bang!... Then it activates (fires) the workflow actions and does all the things the workflow can do, like send emails, field updates etc. It sounds so much like a database trigger, hmm... doesn't it?

The 'evaluation criteria' tells the workflow rule when to watch the custom object.

The 'rule criteria' tells the workflow rule when to fire the events and based on what condition.

The 'evaluation criteria' and 'rule criteria' together must be met successfully for the workflow rule to fire.

Now, for the workflow rule 'evaluation criteria' we have predetermined choices and these are:

1. When a record is created, or when a record is edited and did not previously meet the rule criteria (this again and again repeatedly triggers the rule every
time a record is saved only until the rule's criteria are met, then it stops, sort of once only if you can say, subsequent edits are ignored)
2. Only when a record is created (this ignores updates to existing records)
3. Every time a record is created or edited (which repeatedly triggers the rule every time the record is saved, it does this only when the rule criteria is met as well).

These are the only times that we can choose for a workflow to be evaluated. Remember evaluation is not enough, the 'rule criteria' has to be met as well for the workflow actions to fire!

For setting the 'rule criteria' we have 2 options. We can use a filter like setup, a condition on the watched/associated custom object, i.e when the Status field of the custom object equals to Open(Status=Open) then fire the workflow or when a Formula Expression evaluates to true.

So to summarise, we can send emails, update fields and do outbound web service calls from force.com using workflow. This is easy to setup, is based on an object and the evaluation and rule criteria must be satisfied for the workflow to fire. Well quite handy piece of work from salesforce, so you don't need to write your own workflows any more! The mantra 'No Software' used in Salesforce's adverts suits this well.

What are you waiting for? Use your Free developer.force.com account login and go to Setup > Create > Workflow & Approvals > Workflow Rules and start practising.

Salesforce Security

2010-12-22T00:30:00.004Z

Salesforce security? Cloud security? Multi tenancy? I can't believe myself I set off writing a blog entry maybe on the hottest discussion topic in the industry nowadays. No, no, not at all, not yet, not yet.

I will rather try to quickly outline for the benefit of the interested reader how security is applied within a force.com Org. That is, security which enables or prevents certain users in the Org seeing the other user's records/data and how users are prevented from seeing certain fields and objects in the Org. So in-house Salesforce 'data' security I should say, not inter-cloud security.

Were you ever interested? We know Oracle and other databases use words like 'permissions' to describe data access. We know applications use words like 'authorisation' or 'authentication' to describe security. Were you ever interested what and how security is accomplished in Salesforce and force.com? If you are, keep on reading, else give up now.

Data Security in Salesforce is tricky, different and not something which is similar to anything else. There aren't words like 'authorisation' and 'privileges' in the force.com world. Once a Salesforce or force.com user logs in, 'data access' security kicks in. Somebody can only login if he/she is a salesforce user. I use Force.com and Salesforce interchangeably here, it is the same platform at the end of the day. And the security in this platform described in descending order from big data to small data access is something like this:

1. Object Level Security: This type of security prevents a user form deleting, updating, seeing or creating any instance of the object (record in the table or if you like more old fashioned, tuple in the relation).

2. Field Level Security: In this type of security a user is prevented from seeing, editing and/ or deleting the value for a particular field of an object.


I know you already started visualising a database table. Object, what object?

3. Record Level Security: In this type of security we control data with a little bit more of a 'finesse'. We allow particular users to see an object, but we restrict the individual object records a user can see. A bit like virtual private database (VPD). In Oracle achieved with DBMS_RLS. I wonder how is this done in Salesforce? Salesforce is implemented on an Oracle database anyway. This is hot stuff. Everyone is interested in this nowadays, this means you can slice and dice the 'object' (table) horizontally! How is this done in Salesforce? Well the methods are four.

3.1 Organisational Wide Defaults (OWD): This is the baseline level of access to objects (tables ;-) ) a user has. OWD is defined for each standard or custom (your make) object in Salesforce. OWD in other words is the "baseline level of access that the most restricted user should have". Another name for OWD is "Sharing Model". That is, use OWD to lock-down objects to the most restrictive level.

3.2 Role Hierarchies: Role hierarchy, is the first way we can share access to records per se. OWD is a very 'lock-down' and 'restrictive' thing at the object level, whereas roles is the beginning of opening access to certain records via a hierarchy. The role hierarchy ensures that a manager will always have access to the same data as his or her employees. A polite way of saying your boss owns all your data. That is if you own 10 records in Salesforce, you own them, but your boss owns them as well. Typical, hierarchical record level access control coming down vertically, top to bottom and the 'Top' owns all! You can even assign access through hierarchies on an object-by-object basis as well.

3.3 Sharing Rules: Is when you start making exceptions, and confusing people. Not really! Sharing rules let us make automatic exceptions to Organisational Wide Defaults (OWD) for particular groups of users. As you realised in Role hierarchies section 3.2 above, the Role Hierarchies enable certain group of people to own lower level people's data. Low, down that is. Well Sharing Rules, don't do down, they do left and right. Did you get it? Basically saying that if there are 2 managers in the same hierarchy at the same level, how would they be able to see each others data? Enter Sharing Rules. Sharing Rules enable the access to records across levels in the hierarchy, by establishing certain groups which contain this same level managers, so that they can share their data. Something like Marketing wants to share its records with the IT department, with Role hierarchy this is not possible unless the departments are one 'under' the other and not across at the same level and next to each other in the hierarchy.

3.4 Manual Sharing: What manual sharing in few words is to 'give access to your record, to the guy you like'. No, not exactly but similar. With manual sharing you can grant read or read/write access on records that you own to any other user, role, or public group in Salesforce manually whenever you fancy and like. Although it isn't automatically like in all 3 cases above, manual sharing gives you the flexibility to share particular records. So manual sharing is when you can't do it in any of the 3 ways above. That is when you can't grant access to the record via OWD, Role hierarchies and Sharing Rules, you use Manual Sharing.

This was my attempt to give you a taste of what is data security in force.com. We started by letting you see the object, then playing with which fields of the object you can see, and lastly what records of the object you can see.

If you are interested to read more on this topic check 'Force.com Fundamentals' by Chris McGuire and Caroline Roth. A very Cloud book.

Oracle Apex 4.0 UKOUG SIG in London

2010-11-04T21:33:00.001Z

I attended the UKOUG SIG on Apex 4.0 in London yesterday and learned lots of new things. Thanks to presenters like John Scott from SumNeva and Hillary Farrell from Oracle. Looking forward to receive the seminar notes.

Briefly the things to take home from this SIG meeting for me were:

Oracle Apex plugins are great. Great presentation from John Scott on this.
In Oracle Apex release 4.0.2, there will be new themes which will render nicely, almost like native apps, on iPhones and iPads.
Lots of tips and tricks on how to scale and tune Oracle Apex using tools like, Jmeter, Firebug, Yslow

The Q&A was interesting, as it seems like I am not the only person on earth who thinks that having such an excellent tool such as Oracle Apex without "Publishing" capabilities, printing in PDF, Word etc, is a bad thing. Why doesn't Apex enable you to print in PDF by default? Why do you have to buy more software from Oracle such as BI Publisher to do this? I hope I am not sounding stupid. I know there are Free alternatives which enable you to print in PDF such as Apache FOP, but hey, why can't I just be able to print in PDF out of the box?

Specifically, Oracle Apex has a feature which enables you to migrate your Oracle Forms & Reports into Apex. That is, you can say bye bye to your Oracle Forms & Reports fat clients and get your Forms & Reports on the web with Apex, a brilliant free alternative, instead of the expensive Oracle Fusion Middleware stack, the new version of Forms & Reports. Who wouldn't want that? Especially in the age of Cloud computing! What a cool idea!

But there is a snag. You can't just convert your stylish Reports for free. If you want to carry an Oracle PDF report you created in Oracle Reports, say an invoice with colors and boxed sections, pixel-by-pixel as it is to Oracle Apex, you will have to first convert it in BI Publisher to a "Report Layout" file and then import it in your Oracle Apex workspace. There is no other way to extract the XSLT from your Oracle Report and upload it to Oracle Apex, you will have to go through the BI Publisher route. Prove me wrong, please has anyone found another free and legitimate way?

You might say, with a bit of configuration work you can get Apache FOP to print you PDF output from your Apex reports and regions and you can do that free. You can even create new layouts and print them in PDF using Apache FOP, that is fine. But what if you want to migrate 100 reports, all invoice and billing templates which took developers ages to create? You will realize that to move an existing Oracle Reports report style (XSLT) into Oracle Apex as-it-is, pixel-by-pixel is possible only through BI Publisher. I tried and researched deep and wide on this topic. Is there anybody who managed to extract XSLT from their existing Oracle Reports and create an Apex Report layout?

I like Apex, I like the idea. But to get the business and the managers to like it, will have to do a little bit more.

Oracle 11g Streams Synchronous Capture

2010-10-22T14:36:00.020+01:00

Have you ever wanted just to maintain a table in two different databases? In two different schemas? Something like replicating, keeping a copy of a table in sync in another database, not in real-time, but almost real time? There is an easy way, use Oracle Streams Synchronous Capture!

Many times in development I get this request, where a developer in a team will come with a request like "Can we get a copy of that table in our schema in some sort of sync please?". Alternative ways of providing the developer with this table would be backup/restore, data-pump, both not so much "in sync" and overkill solutions. There is a better way, Oracle Streams Synchronous Capture.

Oracle Streams is a tool which propagates information between databases using Advance Queuing in almost real-time. It has 3 main components Capture, Propagate and Apply. Capture is the process with which you capture the data in the source database. Propagate is the process which transfers the data from the source database to the target database. The Apply process copies (inserts) the data into the target database, once the data arrives to the target database. Oracle Streams is easy to setup and use, most important of it all, it is FREE. Yes, Oracle Streams would be the perfect candidate to have data available in two or more database systems in sync for whatever reason, be it development tests, production replication between sites or whatever legitimate reason you want a copy of your data to exist somewhere else as well.

You might argue, there is something fundamentally wrong with asking to have a copy of the data. That is, the data twice, three times and even more, in different places. The action of having the copy of data in different places not in real-time, with delay, might dilute the truth and might even version the truth. When all we want is just "one" truth, one real and correct answer. Oracle streams usually will have two ends, the source database and the target database. There is nothing stopping us from changing the data of the target database after it is streamed down from the source. I will probably agree with you if you are thinking like this. Nevertheless, Oracle Streams still proves useful.

Reading the Oracle documentation on Oracle Streams you can see that although there are multiple ways to configure Oracle Streams, there seems to be only 2 kinds of Oracle Streams. The Asynchronous Redo Log Capture based Streams and the Synchronous Capture "internal-mechanism/trigger" based Streams. Both have their uses. The former is the heavy weight type, where all changes on your source database objects are captured from the Redo Logs of the source database and then they are propagated in the Redo Logs of the target database where the Apply process picks them up and applies them. A system suitable if you want to stream everything, including DML and DDL, between the source and target databases, or more just than few tables. On the other hand, if you just want to stream only the DML of few tables between databases then you can use Oracle Streams Synchronous Capture, where triggers are used to capture the changes as they happen in the source database and then propagate the changes to the target database,no redo log mining is used for this. This is exactly what this post is all about, using Oracle Streams Synchronous Capture to replicate few tables between databases, a New Feature of Oracle 11g, which doesn't rely on archivelogs or redo logs to propagate DML changes on tables between databases.

Below is a step-by-step implementation of Oracle Synchronous Capture in Oracle 11g. I use two database instances SOURDB for the source database and TARGDB for the target database. I create two Oracle Streams administrator schemas on these databases first. Then I create the Oracle Streams users, the table and the capture, propagate and apply processes. The PL/SQL package DBMS_APPLY_ADM is used, although if you read the documentation you will see that there are other packages which you can use.

Step by step implementation

1. Create U_SOURCE users to be used for the test on both databases. Yes on both systems the users is called U_SOURCE, as it has to be identical for the following example to work. You can have different usernames, but then you have to create a transformation process, look in the documentation you will understand. I didn't want this blog entry to take forever, I took a shortcut by naming both schemas in both databases SOURDB and TARGDB as U_SOURCE.


-- on SOURDB create user
connect / as sysdba

create user u_source
identified by u_source
default tablespace users
temporary tablespace temp;

grant connect,resource to u_source;

-- db link to connect to target
CREATE DATABASE LINK TARGDB CONNECT TO strmadmin
IDENTIFIED BY strmadmin
USING 'TARGDB';

-- on TARGDB create user

connect / as sysdba

create user u_source
identified by u_source
default tablespace users
temporary tablespace temp;
grant connect,resource to u_source;

-- db link to connect to source
CREATE DATABASE LINK SOURDB CONNECT TO strmadmin
IDENTIFIED BY strmadmin
USING 'SOURDB';

2. Now create the Streams Administrator users on both SOURDB and TARGDB databases, with their own STREAMS tablespace. Run the script below on SOURDB as well as TARGDB as SYSDBA.


CREATE TABLESPACE streams_tbs
DATAFILE SIZE 25M
AUTOEXTEND ON MAXSIZE 256M;

CREATE USER strmadmin IDENTIFIED BY strmadmin
DEFAULT TABLESPACE streams_tbs
QUOTA UNLIMITED ON streams_tbs;

grant dba to strmadmin;

3. Now is time to create the Capture process in the SOURDB. We use Oracle AQ as the structure to capture DML changes. That is why first we need to create the queue and then add the table which we want to capture the changes from. Also, in synchronous capture you don't have to start the capture process, it starts automatically once you create the table rule.


--in SOURCEDB as user STRMADMIN set up the queue
--if you want to remove queue use 
--exec dbms_streams_adm.remove_queue('strmadmin.SOURDB_queue',true,true);
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.SOURDB_queue_table',
queue_name  => 'strmadmin.SOURDB_queue',
queue_user  => 'strmadmin');
END;
/

4. Now still as the STRMADMIN user in the SOURDB which owns the above queue, you can add the table from which you want to capture the changes like this:


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name   => 'U_SOURCE.T1',
streams_type => 'SYNC_CAPTURE',
streams_name => 'SYNC_CAPTURE',
queue_name   => 'strmadmin.SOURDB_queue',
include_dml  => true,
include_ddl  => false,
include_tagged_lcr => false,
inclusion_rule => true,
source_database => 'SOURDB');
END;

5. Still logged in as the STRMADMIN user in SOURDB, create the propagation process between SOURDB and TARGDB.


--To drop existing propagation
--BEGIN
--DBMS_PROPAGATION_ADM.DROP_PROPAGATION('strmadmin.SOURDB_propagation');
--END;

--To create a propagation between SOURDB to TARGDB
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name              => 'u_source.t1',
streams_name            => 'SOURDB_propagation',
source_queue_name       => 'strmadmin.SOURDB_queue',
destination_queue_name  => 'strmadmin.TARGDB_queue@TARGDB',
include_dml             => TRUE,
include_ddl             => FALSE,
include_tagged_lcr      => FALSE,
source_database         => 'SOURDB',
inclusion_rule          => TRUE,
queue_to_queue          => TRUE);
END;

6. On the target database TARGDB create the Apply process, again first you will have to create the queue and then the Apply process.


--exec dbms_streams_adm.remove_queue('strmadmin.TARGDB_queue',true,true);
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.TARGDB_queue_table',
queue_name  => 'strmadmin.TARGDB_queue',
queue_user  => 'strmadmin');
END;
/

--now create the apply process 
begin
dbms_apply_adm.create_apply(
queue_name      => 'strmadmin.TARGDB_queue',
apply_name      => 'sync_apply',
apply_captured  => false); &lt;-- very important to be set to FALSE!       end;       /  --set Apply process rule, which table etc..  begin dbms_streams_adm.add_table_rules( table_name         => 'U_SOURCE.T1',
streams_type       => 'APPLY',
streams_name       => 'SYNC_APPLY',
queue_name         => 'strmadmin.TARGDB_queue',
include_dml        => true,
include_ddl        => false,
include_tagged_lcr => false,
source_database    => 'SOURDB');
end;
/

7. Once you have created the Apply process on the target database, you will have to instantiate the target database 'replica' table with the SCN number. This creates a point in time on the target table which shows the point the target table started accepting changes from the source database table. So, in the target database TARGDB as the user U_SOURCE do the following:

 
select dbms_flashback.get_system_change_number apply_scn from dual;

insert into u_source.t1
(select *
from u_source.t1@SOURDB as of scn 13983123089);

commit;

--Instantiate, point of no turning back! 
begin
dbms_apply_adm.set_table_instantiation_scn(
source_object_name   => 'u_source.t1',
source_database_name => 'SOURDB',
instantiation_scn    => 13983123089);
end;
/

--Confirm the instantiation is complete 
select source_database,
source_object_owner||'.'||source_object_name object,
instantiation_scn
from dba_apply_instantiated_objects;

--Start the Apply process, from this point onwards all changes in source table T1 are 
--propagated to the target table T1.
begin
dbms_apply_adm.start_apply('SYNC_APPLY');
end;

8. Now, you can test the setup by going to the SOURDB U_SOURCE schema and inserting some rows to the table T1 and coming back to the TARGDB U_SOURCE schema and seeing the changes being replicated. If everything went alright, your DML will be propagated across from source to target, as a logical chanage record (LCR) as the Oracle Streams parlor denotes it.

For more reading and monitoring of the Oracle Streams operation go to Monitoring a Capture Process You can also use Oracle Enterprise Manager to monitor Oracle Streams. Unfortunately although you CAN setup the first type of Oracle Streams using Redo Logs with Enterprise Manager, you CAN NOT setup Oracle Streams Synchronous Capture.

Web services

2010-09-11T21:58:00.005+01:00

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

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

2010-06-21T00:15:00.004+01:00

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

2010-04-01T20:52:00.009+01:00

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

2010-02-01T19:30:00.031Z

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

2010-01-19T14:38:00.008Z

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.

SQL Tuning Advisor and SQL Access Advisor in Oracle 10g

2010-01-04T09:43:00.003Z

Oracle 10g with its AWR (Automatic Workload Repository) for automatic collection of performance statistics and with ADDM (Automatic Database Diagnostic Monitor) for identifying root causes of problems, is a self-healing and self-tuning getting wiser every day and very soon will start to think on its own database.

There are features in Oracle 10g which help you to quickly find out what is wrong with the database. Features like ASH (Active Session History) to peak into what your active sessions are waiting for and Server Generated Alerts with Metrics and Thresholds are all tools which tell you what is really going on in your database. The OEM (Oracle Enterprise Manager) Database control is a brilliant interface you can use to learn and find out more about all these smart enhancements. All in the name of pro-active database management.

What is database well-being all about anyway? Isn't it high throughput(OLTP) and response time(DSS)? Aren't all databases working to achieve these?

Oracle 10g has also an Advisory Framework which based on workload, will also try to advise you on configuration issues like Buffer Cache, Library Cache, PGA, Undo and Segments used. There are also Tuning-Related Advisors which deal with SQL Tuning and these are:

I - SQL Tuning Advisor
II - SQL Access Advisor

In this post I will try to explain the usage of these two advisors in the job of tuning bad and nasty SQL statements.

All automation is good, self healing databases are very good. But what happens in the development arena when a developer turns up to your desk pointing to an email he/she just sent to you and says '...what's wrong with this SQL statement? it takes ages? Why is Oracle so slow?...' I am particularly fond of the last comment.

How can we quickly look at what is wrong with the SQL statement using the SQL Tuning Advisor? Here is how.

I - SQL Tuning Advisor

Using the DBMS_SQLTUNE package to tune SQL

Assuming the suspect query is something like "SELECT * FROM SESSIONS, SOURCE_EVENTS", two very large tables, and your schema has the ADVISOR privilege granted, you would put this query into the Oracle SQL Tuning Advisor engine using the PL/SQL package DBMS_SQLTUNE like this:

1. Create the tuning task

In SQL*Plus and in the schema where objects live, use the procedure dbms_sqltune.create_tuning_task to create a tuning task.


SQL>
declare tname varchar2(30);
begin
tname := dbms_sqltune.create_tuning_task(sql_text=>'select * from sessions, source_events');
end;
/

PL/SQL procedure successfully completed.

Then you can check to make sure that your task is created and get the TASK_NAME value as you will need this in the next step. I could name the task, just being lazy...


SQL> select task_name from user_advisor_log;

TASK_NAME
------------------------------
TASK_51415

2. Execute the tuning tasks

Execute the tuning task you created in step 1 with the procedure dbms_sqltune.execute_tuning_task


SQL> exec dbms_sqltune.execute_tuning_task('TASK_51415');

3. Get the tuning report

Get the tuning report/recommendation with the following SQL.


SQL> set long 50000
SQL> select dbms_sqltune.report_tuning_task('TASK_51415') from dual;


DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_51415')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

(CLOB) GENERAL INFORMATION SECTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
-------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
Tuning Task Name                  : TASK_51415                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
Tuning Task Owner                 : ACHILLES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
Scope                             : COMPREHENSIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Time Limit(seconds)               : 1800                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
Completion Status                 : COMPLETED                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
Started at                        : 12/20/2009 18:44:05                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
Completed at                      : 12/20/2009 18:44:14                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
Number of SQL Restructure Findings: 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

-------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
Schema Name: ACHILLES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
SQL ID     : 8qsdf0a6qv4g7                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
SQL Text   : select * from sessions, source_events                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

-------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
FINDINGS SECTION (1 finding)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
-------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

1- Restructure SQL finding (see plan 1 in explain plans section)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
----------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
An expensive cartesian product operation was found at line ID 3 of the                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
execution plan.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

Recommendation                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
--------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
- Consider removing the disconnected table or view from this statement or                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
add a join condition which refers to it.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

Rationale                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
---------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
A cartesian product should be avoided whenever possible because it is an                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
expensive operation and might produce a large amount of data.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

-------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
EXPLAIN PLANS SECTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
-------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

1- Original                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
-----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
Plan hash value: 3883039950                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      

---------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
| Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
---------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   0 | SELECT STATEMENT         |               |    33T|  4158T|  4367M  (2)|999:59:59 |       |       |        |      |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   1 |  PX COORDINATOR          |               |       |       |            |          |       |       |        |      |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   2 |   PX SEND QC (RANDOM)    | :TQ10001      |    33T|  4158T|  4367M  (2)|999:59:59 |       |       |  Q1,01 | P->S | QC (RAND)  |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   3 |    MERGE JOIN CARTESIAN  |               |    33T|  4158T|  4367M  (2)|999:59:59 |       |       |  Q1,01 | PCWP |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   4 |     SORT JOIN            |               |       |       |            |          |       |       |  Q1,01 | PCWP |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   5 |      PX RECEIVE          |               |  8439K|   829M|  3175   (2)| 00:00:39 |       |       |  Q1,01 | PCWP |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   6 |       PX SEND BROADCAST  | :TQ10000      |  8439K|   829M|  3175   (2)| 00:00:39 |       |       |  Q1,00 | P->P | BROADCAST  |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   7 |        PX BLOCK ITERATOR |               |  8439K|   829M|  3175   (2)| 00:00:39 |     1 |    16 |  Q1,00 | PCWC |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   8 |         TABLE ACCESS FULL| SESSIONS      |  8439K|   829M|  3175   (2)| 00:00:39 |     1 |    16 |  Q1,00 | PCWP |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   9 |     BUFFER SORT          |               |  4013K|   122M|  4367M  (2)|999:59:59 |       |       |  Q1,01 | PCWP |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  10 |      PX BLOCK ITERATOR   |               |  4013K|   122M|   532   (4)| 00:00:07 |     1 |    16 |  Q1,01 | PCWC |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  11 |       TABLE ACCESS FULL  | SOURCE_EVENTS |  4013K|   122M|   532   (4)| 00:00:07 |     1 |    16 |  Q1,01 | PCWP |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
---------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

1 rows selected

Well done SQL Tuning Advisor, a good answer. It immediately spotted the Cartesian product and it recommends with a rationale that it would be expensive!

II - SQL Access Advisor

SQL Access advisor is another Advisory Framework tool which provides primarily advice on the creation of indexes, materialized views, materialized view logs to improve query performance and response time.

Both SQL Tuning Advisor and SQL Access Advisor tools are quite powerful as they can source the SQL they will tune automatcially from multiple different sources, including SQL cache, AWR, SQL tuning Sets and user defined workloads. Look at the Oracle documentation to find out how all this is possible under OEM Database Control.

What I focus on here is how to quickly pass through these tools ad-hoc SQL that the developer will bring to you and will demand to be tuned there and then.

So using the QUICK_TUNE procedure from the DBMS_ADVISOR package here is how you would tune a nasty single SQL statement for better data access paths with SQL Access Advisor.

1. Create tuning task

In the schema where the table is run the following


VARIABLE tname    VARCHAR2(30);
VARIABLE sql_stmt VARCHAR2(30);
EXECUTE :sql_stmt := 'select count(*) from sessions where short_code=''abracadambra'' ';
EXECUTE :tname    := 'myquicktunetask';
EXECUTE DBMS_ADVISOR.quick_tune(dbms_advisor.sqlaccess_advisor, :tname, :sql_stmt);

2. Check to see the task is there

Again to see the task myquicktunetask which you just created run in the schema


SQL> select task_name from user_advisor_log
TASK_NAME                      
------------------------------ 
TASK_51415                     
myquicktunetask                

2 rows selected

3. See tuning advice

Now, to see the advice you get you will have to look in the DBA dictionary view
DBA_ADVISOR_ACTIONS. So login as DBA and filter for the task_name='myquicktunetask' in the DBA_ADVISOR_ACTIONS dictionary view. Well, you can view the advice in the USER_ADVISOR_ACTIONS dictionary view as well.


SELECT command,
attr2        ,
attr3        ,
attr4        ,
attr5
FROM dba_advisor_actions
WHERE TASK_NAME='myquicktunetask'

TASK_NAME||'|'||COMMAND||'|'||ATTR2||'|'||ATTR3||'|'||ATTR4||'|'||ATTR5
(CLOB) myquicktunetask|CREATE MATERIALIZED VIEW LOG||ROWID, SEQUENCE| INCLUDING NEW VALUES|("SHORT_CODE")
(CLOB) myquicktunetask|CREATE MATERIALIZED VIEW||REFRESH FAST WITH ROWID|ENABLE QUERY REWRITE|SELECT ACHILLES.SESSIONS.SHORT_CODE C1, COUNT(*) M1 FROM ACHILLES.SESSIONS WHERE (ACHILLES.SESSIONS.SHORT_CODE = 'tl') GROUP BY ACHILLES.SESSIONS.SHORT_CODE
(CLOB) myquicktunetask|GATHER TABLE STATISTICS||-1||

Conclusion

Well I find both tools extremely useful. Both are good starting points in resolving SQL tuning issues and discussions. SQL Tuning Advisor seems to be doing logical optimization mainly by checking your SQL structure and statistics and the SQL Access Advisor does suggest good data access paths, that is mainly work which can be done better on disk.

Do you have a data model of your production schema?

2009-11-10T19:09:00.002Z

For almost a year I have been running a poll on my blog with the above question. Finally the polling time has come to an end and I have some results I can share with you.

In total I had 36 votes for the poll with the question Do you have a data model of your production schema? and the results are as follows.

* 33% answered 'YES'.
* 58% answered 'NO'
* 8% answered 'I don't know'

What does this tell us?

I know the sample of 36 is too small almost irrelevant, but what is the right size anyway?

By looking at these results can we say that people in majority 58% do not know the data model of their databases?

I suppose as long as the database is performing well and there is enough space to accommodate whatever is thrown into the database, all is jolly and fine and nobody complains. Especially with technologies such as Hibernate where the data model is derived and implemented automatically from the object model persistence layer!? Who cares about Primary Keys and Foreign Keys, is this the end of databases? Is this how I should interpret this? I am not sure.

Next, what can we say for the second group of voters who are the 33% and who said, Yes! Are these voters in a stronger position than the voters of the 58%? Does this mean that this group does know their data model and do understand the relationships between their entities, and therefore command the SQL and either as a DBA or Developer are able to write better SQL scripts and much more high performance queries and code. Once again I am not sure.

Last the 8% who said 'I don't know', were they just casual visitors who just wanted to show their indifference in data modeling but at the same time, by voting chose to show that they were still involved with database systems? Again I don't know.

What I know is that all of you who voted gave me a small glimpse of what is out there and may I send you a BIG THANK YOU for that!

Library Cache Pin Waits during PL/SQL compilation

2009-07-23T16:27:00.045+01:00

Did you ever tried to compile a PL/SQL procedure in your production database and it took long time?

Did you face slowdown or other production issues because of this?

Did you hit an error similar to the following when you or a developer tried to compile PL/SQL procedure which is referenced by another constantly executing high load PL/SQL procedure?


ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object AZIMUTH.UPDATEME

If yes, then carry on reading.

This post is about Conncurrency Wait issues you might get when trying to compile highly used constantly executing PL/SQL code in your production database.

I have come accross this situation on an Oracle 10g R2 production platform. What hapenned was someone tried to quickly compile PL/SQL procedures which were executing 2 times in a second by many sessions. More this procedure was called by a dozen of other PL/SQL procedures that is there were lots of dependencies between packages, views. The PL/SQL procedure in question was basically the busiest piece of code in the database. Logging sessions and their events.

You have to be carefull when you want to compile such highly used constantly executing piece of PL/SQL code in your system, at run-time. The rule is, that objects cannot be changed when they are used. As the session you want to compile the code in tends to keep on waiting to obtain a Library Cache Pin latch from the shared pool. If you do go ahead and compile such code, without scheduling downtime, you will probably halt the system. Try it with re-compiling packages which have dependencies with multiple other packages which are constantly in use, if you dare on production, and see what happens. You will face concurrency waits. Oracle says in metalink that such behaviour is expected and that you have to schedule downtime and maintenance window for re-compiling heavily used PL/SQL. The solution seem to be not to write complex dependencies in PL/SQL or to arrange maintenance window for deployments of PL/SQL. That is what the support told me, they also asked me to use some undocumented "_???" parameters to help with the latches. But the maintenance window seems to be the easiest or maybe standby database configuration. Feel free to comment on this post, workarounds and better solutions you can suggest.

See some Metalink notes with good reading on the subject:

Doc ID: 115656.1 WAIT SCENARIOS REGARDING LIBRARY CACHE PIN AND LIBRARY CACHE LOAD LOCK

Doc ID: 1054939.6 COMPILATION OF PACKAGE IS HANGING ON LIBRARY CACHE LOCK AND LIBRARY CACHE

Doc ID:264476.1 ORA-4021 'LIBRARY CACHE PIN' and PL/SQL blocks

Doc ID:169139.1 How to analyze ORA-04021 or ORA-4020 errors?

I am trying to simulate the above Concurrency waits with the following procedures where I run lock_test by many sessions and then try to re-compile updateme. I do see the Concurrency Waits in V$SESSION_WAITS.


CREATE OR REPLACE
PROCEDURE updateme
AS
BEGIN
  FOR i IN 1 .. 100000000
  LOOP
     UPDATE test SET names = 'x' WHERE names = 'b';
  END LOOP;
  ROLLBACK;
END;



CREATE OR REPLACE
PROCEDURE lock_test
AS
BEGIN
  updateme;
END;

Oracle Apex Hosting

2009-06-27T09:10:00.107+01:00

Long time now, I have been looking for a web hosting company, where I could host my Oracle Apex pages in full and economically, and found one for £3.50 a month!

Oracle's free http://apex.oracle.com hosting platform is not enough as it doesn't allow you to make external network calls, i.e. calls to Google services like Google Maps.

If you want to create an Oracle Apex application which uses Google Maps or even use internet able PL/SQL packages such as UTL_HTTP on the internet, you have to look elsewhere as the free Oracle Apex hosting platform doesn't let you do that.

But don't worry because the Application Outsource UK LTD platform for £3.50 a month gives you 10MB storage and full Oracle Apex hosting with the ability to do external network calls!

I can't say how pleased I am with their service. Now I am able to do everything I couldn't do with Oracle's Free Apex hosting platform. They have a great support website too.

Have a look at my Oracle Apex and Google Maps demo application I have crated using their platform here on My Oracle Apex and Google Maps samples.

Yahoo, Google API and Oracle Apex

Google maps API and Oracle Apex

More Reading

A good starting document on Integrating Google Maps and Oracle Apex. This document gave me the inspiration to try Google Maps with Oracle Apex.

1. Integrating_Application_Express_with_Google_Maps.pdf.

2. Google API

Update one table from another using ORACLE MERGE INTO

2009-05-18T08:47:00.002+01:00

Sometimes, during ETL or when is necessary to do data cleansing, I need to update a table with data from another table. I have been looking for scripts to do this easily and I have come across MERGE INTO. A very easy to use and understand SQL statement.

Assume we have 2 tables as:

create table t1 
(
id number primary key not null,
name varchar2(50)
);


create table t2
(
id number primary key not null,
job varchar2(50)
);

insert into t1 values (1, 'Kubilay');
insert into t1 values (2, 'Robin');
insert into t1 values (3, 'Kenny');

select * from t1;


ID                     NAME                                               
---------------------- -------------------------------------------------- 
1                      Kubilay                                            
2                      Robin                                              
3                      Kenny                                              

3 rows selected




insert into t2 values (1, 'DBA');
insert into t2 values (2, 'SA');
insert into t2 values (3, 'Developer');

select * from t2;


ID                     JOB                                                
---------------------- -------------------------------------------------- 
1                      DBA                                                
2                      SA                                                 
3                      Developer                                          

3 rows selected

And now we want to create a new column in t1 and move the data which is matching (primary key to primary key) from table t2 to table t1.

alter table t1 add job varchar2(60);

Use MERGE INTO to update table t1 with data from table t2.

merge into t1
using t2
on (t1.id = t2.id)
when matched
then update set 
t1.job = t2.job;


select * from t1;

ID                     NAME                                               JOB                                                          
---------------------- -------------------------------------------------- ------------------------------------------------------------ 
1                      Kubilay                                            DBA                                                          
2                      Robin                                              SA                                                           
3                      Kenny                                              Developer                                                    

3 rows selected

As you can see in the link you can use the MERGE statement to insert new rows like UPSERT when data in table t2 doesn't match with t1. That is, an Oracle UPSERT statement!

Upgrading to 11g, a technical workshop seminar by Mike Dietrich

2009-04-09T08:01:00.016+01:00

On 7th of April I have attended the Oracle 11g upgrade technical workshop seminar given by Mike Dietrich in the London City offices of Oracle.

This was one of the best Oracle Events I have attended.

Mike run us through his 400+ slides of tips & tricks and best practices when upgrading to Oracle 11g. He also presented 3 real life cases in which he helped 3 big Oracle customers during their upgrade to Oracle 11g. The contents of the slides are very good and the presentation skills of Mike were excellent. In this post there is a link to the slides. The slides are quite big in size so you have to download them in 3 parts (about 8MB in total). Here are the slides: Oracle 11g Upgrade Workshop presentation

Workshop presentation highlights

Better support to the optimizer during upgrade.
Definitely apply the timezone patch, you can't upgrade without it.
Recalculate statistics before upgrade.
If you stay on the host use dbua.
If you move host use command line upgrade.
Provision for performance degradation, do tests, tests and tests.

It seems when it comes to upgrades in Oracle, people will always wait for R2. Mike said that Oracle is well aware of this and that with 11g they tried to break this taboo by explaining to us how Oracle actually are not introducing anything special as bug fixes in R2. People will see this themselves when they see how many little bugs will be fixed in R2.

From the presentation I also got the impression that you can upgrade to 11g, it is easy takes little time. But the performance implications the upgrade might introduce are still uncontrollable if you don't have diagnostic & tuning pack license purchased (£1777 each per CPU) . A Diagnostic packs license designed to show and sort out automatically all SQL statements which will regress! If you don't have diagnostic pack purchased, your DBA might end up dealing for days with hundreds of ''Regressed'' SQL statements because of the upgrade. Whereas the diagnostics package deals with it in hours. The DBA poor chap! (Hopefully Not!)

"Informatica on Demand" an ETL tool running in the Cloud

2009-04-02T16:23:00.027+01:00

Recently I was after ways which I could use to integrate (ETL) data from separate sources into the Cloud. Move data between MS SQL Server and Salesforce or MS SQL Server to Oracle for example. But mostly I was interested in an easy peasy way to migrate data from back-end database systems such as MS SQL Server into the Salesforce Cloud.

How is that possible? First thing which comes to mind is API, Java, Import & Export and custom code, code and code.

Not really, all you need is Informatica on Demand! A simple and intro level absolutely free ETL tool which is running in the Cloud!

Informatica is a very reputable company in the Data Integration field. Informatica on Demand is part of their free Software as a Service (SaaS) offering. An ETL tool which is itself in the Cloud. You don't have to install any software anywhere in your infrastructure, apart from a little Informatica agent which identifies your network to the Informatica Cloud. All you do is just create a login on their website and start working! How cool is that! You login to the Informatica on Demand website and configure and even schedule your data migration and ETL tasks between your databases and the Cloud as easy as checking your email!

This free tool does schema-to-schema migrations only. It doesn't allow you to migrate by writing custom SQL on the source window and loading its result set to the target. It is limited to table-to-table migrations between separate sources and targets. You can however, save your SQL as a view in your source database and the tool will see this as a table object.

In the same Informatica on Demand website and within the same Cloud infrastructure, they also have a suite of other more advanced tools such as Data Synchronization, Data Replication and Data Assessment. This stack of tools is not free. But a 30 day trial is still possible.

Overall, I found the tool quite easy to use and with few clicks, I could transfer thousands of rows from say Oracle to Salesforce and from Microsoft SQL Server to Oracle. No need to install and configure gateways, ODBC and no need tweak any hardware locally. It also has a scheduler which works and sends emails when tasks are complete and all this is still hosted and running in The Could. So you don't have to backup anything or look after things.

Here is a snapshot of the Field Mapping screen:

ER modeling Tools now on the Web!

2009-02-17T07:48:00.016Z

I was looking at Google Apps Gallery and came across this ER modeling tool which is very cool and I decided to blog about it.

The tool is called GAE SQL Designer and is done by Jason W. Miller. It is an ER modeling tool on the web! All you need is a browser and you start modeling databases, with no installs and no extra software. You can access it directly here http://gaesql.appspot.com/

Excellent, I liked the idea very much! One thing that is missing is the ability to generate SQL from your data models. There is another less intuitive google app for that here: http://code.google.com/p/wwwsqldesigner.

Apart from that, I think Jason's GAE SQL Designer will be useful in quick prototyping and learning. Well done Jason!

Oracle SQL Developer for Ubuntu

2009-02-04T12:17:00.004Z

Here is a nice post about a packaging for Oracle SQL Developer on Ubuntu.

http://blog.kizoom.com/2009/02/oracle-sqldeveloper-for-ubuntu-version.html

Blocking sessions-locks in Oracle

2009-01-13T10:44:00.007Z

Did you ever wanted to quickly find which sessions are blocking each other in Oracle?

In this post I will introduce an SQL script, which would quickly tell you if there is blocking between the sessions and which would also show you what SQL these sessions are using.

To see the script working, first create a dummy table and insert some test data.


SQL> create table t (a char(1));

Table created.

SQL> insert into t values ('z');

1 row created.

SQL> commit;

Then select some rows from the dummy table for update.



SQL> select * from t where a='z' for update;

A
-
z

In second session try to update the rows which you have selected above. Due to locks this will block! ACID and serialization kicks in.


SQL> update t set a='x' where a='z';

It will just hung!

To see what is blocking, run this query in a third session as SYSDBA.


SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
  || '  User '
  ||s1.username
  || '@'
  || s1.machine
  || ' ( SID= '
  || s1.sid
  || ' ) with the statement: '
  || sqlt2.sql_text
  ||' is blocking the SQL statement on   '
  || s2.username
  || '@'
  || s2.machine
  || ' ( SID='
  || s2.sid
  || ' ) blocked SQL ->  '
  ||sqlt1.sql_text AS blocking_status
   FROM v$lock l1,
  v$session s1   ,
  v$lock l2      ,
  v$session s2   ,
  v$sql sqlt1    ,
  v$sql sqlt2
  WHERE s1.sid  =l1.sid
AND s2.sid      =l2.sid
AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id
AND l1.BLOCK    =1
AND l2.request  > 0
AND l1.id1      = l2.id1
AND l2.id2      = l2.id2
/


BLOCKING_STATUS
--------------------------------------------------------------------------------
15-JAN-2009 07:41:27  User hero@world.com ( SID= 144 ) with the statemen
t: select * from t where a='z' for update is blocking the SQL statement on   hero@world.com ( SID=147 ) blocked SQL -> update t set a='x' where a='z'

Google Chart APIs

2009-01-12T22:48:00.004Z

Now this is cool!

How about if you could just pass 2-3 numbers to a URL and get back a very nice looking SVG chart on a web page for FREE and on your website?

I found out that Google now give an API where you pass few numbers in a URL and it just Charts them for your.

Try for yourself here:

http://code.google.com/apis/chart/

I taught I blog about this, as I can see uses of it in real time monitoring. They claim that you can call their API up to 250K times a day for free.

Here is a nice idea. A day has 86400 seconds, hmmm how about monitoring the load of your server with it? :-)

Create a database using OSDM (Oracle SQL Developer Modeling)

2009-01-12T22:23:00.001Z

In this post I show you how easy it is to create a database using Oracle SQL Developer Data Modeling.

Suppose your database requirements are as simple as follows:

You want an event management database system. Not a calendar, but something where you can write events into.
You want to be able to invite friends to attend to these events.
You also want your friends/users to choose the types of events they like most.

Data model it with OSDM

Using OSDM you can quickly draw the data model with foreign keys and primary keys for your database design requirements above. Is not difficult to use just filling in dialog boxes about the properties of the tables.

(Click to enlarge)

OSDM will not only draw you the model, but will also give you the ability to extract the DDL and then run it on something like Oracle Apex's SQL command prompt. How great is that! I just hope that Oracle WILL give this tool free with Oracle SQL developer in later releases.

Extract the DDL from the Model

In OSDM use File -> Export -> DDL File to extract the SQL statements from the data model.

Using the simple OSDM you can extract the DDL from the above model and you get a nice SQL file with the CREATE TABLE and ALTER TABLE ... CONSTRAINT ... commands which will look like this:


-- Generated by Oracle SQL Developer Data Modeling Version: 1.5.1 Build: 518
--   at:        2009-01-12 21:56:49
--   for:       Oracle Database 10g
--              Oracle Database 10g



CREATE TABLE EVENTS 
    ( 
     EVENT_ID NUMBER  NOT NULL , 
     EVENT_DESCRIPTION CLOB , 
     EVENT_DATE DATE , 
     EVENT_TYPE_ID NUMBER  NOT NULL 
    ) LOGGING 
;



ALTER TABLE EVENTS 
    ADD CONSTRAINT EVENTS_PK PRIMARY KEY ( EVENT_ID ) ;


CREATE TABLE USERS 
    ( 
     USER_ID NUMBER  NOT NULL , 
     USER_EMAIL VARCHAR2 , 
     USER_NAME VARCHAR2 
    ) LOGGING 
;



ALTER TABLE USERS 
    ADD CONSTRAINT USERS_PK PRIMARY KEY ( USER_ID ) ;


CREATE TABLE ATTEND 
    ( 
     USER_ID NUMBER  NOT NULL , 
     EVENT_ID NUMBER  NOT NULL , 
     ATTEND_DATE DATE 
    ) LOGGING 
;




CREATE TABLE EVENT_TYPES 
    ( 
     EVENT_TYPE_ID NUMBER  NOT NULL , 
     EVENT_TYPE_DESCRIPTION VARCHAR2  NOT NULL 
    ) LOGGING 
;



ALTER TABLE EVENT_TYPES 
    ADD CONSTRAINT EVENT_TYPES_PK PRIMARY KEY ( EVENT_TYPE_ID ) ;


CREATE TABLE USER_EVENT_PREFERENCES 
    ( 
     USER_ID NUMBER  NOT NULL , 
     EVENT_TYPE_ID NUMBER  NOT NULL 
    ) LOGGING 
;





ALTER TABLE EVENTS 
    ADD CONSTRAINT EVENTS_EVENT_TYPES_FK FOREIGN KEY 
    ( 
     EVENT_TYPE_ID
    ) 
    REFERENCES EVENT_TYPES 
    ( 
     EVENT_TYPE_ID
    ) 
    NOT DEFERRABLE 
;


ALTER TABLE ATTEND 
    ADD CONSTRAINT ATTEND_USERS_FK FOREIGN KEY 
    ( 
     USER_ID
    ) 
    REFERENCES USERS 
    ( 
     USER_ID
    ) 
    NOT DEFERRABLE 
;


ALTER TABLE ATTEND 
    ADD CONSTRAINT ATTEND_EVENTS_FK FOREIGN KEY 
    ( 
     EVENT_ID
    ) 
    REFERENCES EVENTS 
    ( 
     EVENT_ID
    ) 
    NOT DEFERRABLE 
;


ALTER TABLE USER_EVENT_PREFERENCES 
    ADD CONSTRAINT USER_EVENT_PREFERENCES_USERS_FK FOREIGN KEY 
    ( 
     USER_ID
    ) 
    REFERENCES USERS 
    ( 
     USER_ID
    ) 
    NOT DEFERRABLE 
;


ALTER TABLE USER_EVENT_PREFERENCES 
    ADD CONSTRAINT USER_EVENT_PREFERENCES_EVENT_TYPES_FK FOREIGN KEY 
    ( 
     EVENT_TYPE_ID
    ) 
    REFERENCES EVENT_TYPES 
    ( 
     EVENT_TYPE_ID
    ) 
    NOT DEFERRABLE 
;

          

-- Oracle SQL Developer Modeling Summary Report: 
-- 
-- CREATE TABLE                             5
-- CREATE INDEX                             0
-- ALTER TABLE                              5
-- CREATE VIEW                              0
-- CREATE PROCEDURE                         0
-- CREATE TRIGGER                           0
-- CREATE STRUCTURED TYPE                   0
-- CREATE COLLECTION TYPE                   0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          0
-- CREATE SNAPSHOT                          0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
-- 
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
-- 
-- ERRORS                                   0
-- WARNINGS                                 0

To get Oracle SQL Developer Data Modeling go to Oracle's Website here

Oracle Apex and Ajax

2009-01-08T20:39:00.030Z

In this post I will try to explain how you can create an Ajax Search Page, in just few steps and very little code on your Apex pages.

I have already created my Ajax Search Page, see here http://apex.oracle.com/pls/otn/f?p=38209:fishbase, by reading instructions from an example in a book called Pro Oracle Application Express book by John Edward Scott and Scott Spendolini . A very good all-round concepts book on Oracle Apex Development.

You will need 2 pages to create the Ajax Search Page.

1. Create a new blank page in your application with a search query

You need to create a new blank page in your application and you need to use the Printer Friendly page template as it has to be minimal looking with no headers, footers or tabs. This page will also have a Report region where you will write the SQL query which the Ajax Search Page will use to search your chosen table on your chosen column. In my case this page was page7.

The SQL I used for searching the "fish" table in my application is like below
select f_name_gb "English", f_name_gr "Greek", f_latin "Latin", f_name_tr "Turkish" from fish where ( instr(upper(f_name_gb), upper(nvl(:P7_SEARCH, f_name_gb))) > 0 or instr(upper(f_name_gr), upper(nvl(:P7_SEARCH, f_name_gr))) > 0 or instr(upper(f_name_tr), upper(nvl(:P7_SEARCH, f_name_tr))) > 0 or instr(upper(f_latin), upper(nvl(:P7_SEARCH, f_latin))) > 0 )

2. Modify the Printer Friendly Page Template you used in step 1 above

In page7 above, you will also need to change the Body section of the Printer Friendly template to use the code below, that is the template code around the tag #BOX_BODY# , should be modified like below.

<ajax:BOX_B0DY> <div id="BOX_BODY"> #BOX_BODY##REGION_POSITION_02##REGION_POSITION_04#</div> </ajax:BOX_B0DY></td>

Now you can run page7 and it will display all records from your table without applying the search.

Now the most important bit is the fact that we will call and display page7 with Ajax into a region in another page. That is why the use of the Printer Friendly template on page7. Because page7 will display via a <div> tag in page1. One more thing, is that page1 which calls the AjaxReport page7, must have a Search Box Text item for the search field where users will type. You must create this text field in page1. This text field is your Search Box, it is where users will type and search.

3. Do an Ajax call on the page you have just created above from another page.

When users enter text in the Search filed in page1, a call is made to the Ajax page page7 from page1 via the JavaScript function on page1, see below. The search is performed and the SQL is executed. The Javascript you will add to the Header of the calling page1 performs the Ajax call and retrieves the report from page7 and places it in the <div> tag which is in a region of page1. You will see what the region with the <div> contains in later steps.

Just include your page number in lines 5 and 6 below. As you see because my Ajax page number is 7, it has 7 and P7_SEARCH included in these lines. Modify this to reflect your page number.
<script type="text/javascript">  </script>

4. Create a new HTML region on page1 call it AjaxReport

Next, I created a new HTML region on page1 and called it AjaxReport. This is where the results from page7 will be displayed. The source of this region is the <div> tag :

<div id="AjaxReport"><br /></div>

5. Last Step, call the Javascript function created in step 3

Everytime something is entered into the search field P1_SEARCH on page1 the JavaScript function of step 3 will be called. To do that enter the code below to the HTML Form Element Attributes section for P1_SEARCH (search box Text field item) page item on page1.

onkeyup=" AjaxReportRefresh (this)"

You are ready to test the page, type some letters and see how the page responds by filtering records from your table as you type the letter. Enjoy!

Installing Oracle Apex on Oracle 10g R2 with EPG is not supported

2009-01-05T08:03:00.000Z

Apex with Oracle EPG (Embedded PL/SQL Gateway) is not supported for any version below version Oracle 11g. See here, http://download.oracle.com/docs/cd/E10513_01/doc/install.310/e10496/otn_install.htm#BABFICIA

The Note in section "3.3.5 Configure the Embedded PL/SQL Gateway" says it all.

If you are going to use Oracle Apex on 10g, you will have to use an HTTP Server, like Apache or Oracle's version of Apache.

Do not try to configure EPG in an earlier release than Oracle 11g, you will be frustrated as this is not supported. Oracle Apex with a seperate HTTP server, that is NO EPG, is possible and supported from Oracle 9.2.0.3 or later releases.

Oracle Apex is a rapid application development environment using Oracle XMLDB. Oracle Apex comes free and you get it in Oracle 11g by default. Apex is, I think, Oracle's answer to hosted development and Cloud Computing.

I don't know for what reason one would do install Apex with EPG in Oracle 10g. The purpose of this post was to tell you that this is NOT possible.

My Oracle 10g R2 dev installation didn't have extra database applications and it was not ready for any kind Apex configuration not even for the supported configuration which uses an HTTP server.

I had to manually configure the Oracle 10gR2 installation on my dev box to be able to use Apex. I had to install Oracle Text, JVM and the Oracle XML database and do other pre-installation tasks such as change SGA component sizes, see below for details of what you have to do.

Oracle Apex and Oracle 10g database requirements

http://download.oracle.com/docs/cd/E10513_01/doc/install.310/e10496/pre_require.htm#BABFCFBC

Steps for manually installing the extra database components needed for Apex to an existing database installation are below.

1. install Oracle Text

You will have to create the DRSYS tablespace first and then run the following script as SYSDBA

SQL> @$ORACLE_HOME/ctx/admin/catctx.sql ctxsys DRSYS TEMP NOLOCK

2. Install Oracle JVM (for releases before 10g R1 (10.1) )

SQL>@$ORACLE_HOME/javavm/install/initjvm.sql

3. Install Oracle XML Database

As XMLDB is not installed, (this is not a DB for XML LOBs) you will have to manually install it after creating the XDB tablespace.

SQL>@$ORACLE_HOME/ctx/admin/catqm.sql change_on_install XDB TEMP

and then

SQL>@$ORACLE_HOME/ctx/admin/catxdbj.sql

4. Install Apex

SQL>@apexins password APEX_TS APEX_TS TEMP /i/

If you succeed you will get:



Thank you for installing Oracle Application Express.

Oracle Application Express is installed in the FLOWS_030000 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex

sp_spaceused and other MS SQL Server Dictionary views

2008-12-20T09:19:00.014Z

Many times I am asked to look at Microsoft SQL Servers to investigate things, and I find the following T-SQL procedures and Microsoft SQL Server dictionary views very handy.

To find out how much disk space you Microsoft SQL Server database consumes and other things, you can run the following T-SQL procedures inside the Query Analyzer.


sp_spaceused @updateusage = 'TRUE'

To see quickly how many stored procedure, views, tables and other objects your database has you can use this query here

See table disk space usage


sp_spaceused 'Tablename'

See all tables


select *
from INFORMATION_SCHEMA.tables

See all foreign key constraints


select *
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

See foreign keys, primary keys of a particular table


select 
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

See the foreign key of a particular table.


select table_name, column_name "foreign key",  constraint_name
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where TABLE_NAME='put_table_name_here'
and constraint_name not like 'PK%'

Find which MS SQL Server Stored procedure is using a particular table, or in other words dependencies between stored procedures and tables.


SELECT DISTINCT so.name
    FROM syscomments sc
            INNER JOIN sysobjects so ON sc.id=so.id
    WHERE sc.TEXT LIKE '%put_table_name_here%' -- enter table name here.

For more info go to: http://msdn.microsoft.com/en-us/library/aa933209(SQL.80).aspx

Service Level Agreement (SLA) figures

2008-12-01T16:39:00.021Z

How does downtime affect your Service Level Agreement (SLA) with your customer?

What does 99.99999 % uptime per year mean?

If you want answers to these questions you have too look at the availability levels below. They show how many minutes, seconds downtime/system outage you are allowed to have in a year, to meet certain SLA figures and your contractual obligations.


Level of availability         Total downtime per year
=====================         ======================
99%                           3.6 days
99.9%                         8.76 hours 
99.99%                        52 minutes 
99.999%                       5 minutes 
99.9999%                      30 seconds 
99.99999%                     3 seconds

Data Integration and the Cloud

2008-11-22T08:07:00.052Z

Recently I read a Special Report from the magazine Economist about the computing "Cloud". The 1 year old, yet still buzzy and exciting new phenomena in the IT world.

The article is quite analytical and it is definitely worth reading. Here is the full link to the article called "Let it rise!":

http://www.economist.com/specialreports/displaystory.cfm?story_id=12411882

I have to warn you the article is quite lengthy but is worth every page of it.

After you read the article you get a feeling like returning back to the past in the supercomputers era of mainframes. That is what The Cloud is. A "thing" which lives on the internet, and where all the IT infrastructure lives and it is remote and you connect to it via the internet.

No more hardware and server rooms in the office, just smart PCs with browsers and the browser actually becoming the operating system, full of internet based applications for you to work with.

The Cloud is getting rid of all the dusty servers in the server room. All your applications are sucked by the Cloud. If you need word-processing to be done, you will just login to the internet and you use Google Notes or something. If you need spreadsheets, you can use Google Docs. Do you need to design and run a database use Oracle Apex. These are already existing and mature Cloud technologies. Do you need a ready-made CRM application without the complexity of installing it and maintaining it yourself on your PC or your servers, use Salesforce or SugarCRM.

All these and many more are available for you on the internet, in the form of SaaS which stands for Software as a Service. All you need to have is an internet browser and little money to pay your subscription to these SaaS services as you would pay for your mobile phone bill. Software is becoming a Pay-As-You-Go thing! There is no more licenses, product installation keys and certificates to worry about. You don't have to download anything and you don't have to install anything. Even better you don't need to upgrade to the new version each time, as this is done for you automatically. Sounds nice doesn't it?

The Cloud seems a heaven of applications, but how about data? What if I want to store gigabytes-terrabytes of data in the Cloud? How about backups?

Yep it is possible. Check Amazon AWS and S3 you pay pennies for gigabytes of storage per month. But how practical is this? Will all companies suddenly decide to part from their valuable and competitive data and leave it to the misty-airy hands of The Cloud? Will it happen quickly? I think it won't happen as quickly as it happened for the applications. You can guess reasons why. Usually we don't sell data but we sell applications, applications come and go but data stays, we don't like to pass the control of our data to someone else or somewhere else, are just a few reasons.

But what is the alternative? The alternative is to connect the cloud to your database or legacy data. Keep the data, but get The Cloud connect to it! Do we have to care where the data is when we want to analyse it? Do we have to know if it is on Oracle, MS SQL or MySQL or in an MS Excel spreadsheet? Do we have to be looking for the right tool to connect to the right database? In tools issue, I like what Oracle did with Oracle SQL Developer, you can connect it to any database.

To carry on, we should just be able to get to the data with no gimmicks. Also would be nice to have the data presented in an application in the Cloud. Right? If these are sort of your requirements then probably you are looking at one of those Data Integration solutions.

An integration solution which is not just buying Crystal Reports or Business Objects or Oracle OBIEE and installing it on one of your new servers and firing up more and more instances and doing lots of more configurations etc. But a solution where you can have all this in the Cloud, done by others at a Pay-As-You-Go cost, and all you get is a Login to start developing your reports and web applications.

Who wouldn't prefer not to own the complexity of maintaining a reporting server infrastructure but would like to own the data?

At the end all you need is a web page which connects to your database and you develop it as easy as writing an email. A web page which is an application, developed on the internet and stays on the internet but connects to your database in your data centre or your PC. Wouldn't that be cool?

Apatar a Data integration tool

Yes, there are independent tools out there for the integration of database systems with the Cloud. There are ways to get the cloud to connect to your whichever database system. I have been looking at one of them recently called Apatar You can find out more on www.apatar.com.

Apatar is an Open Source data integration and ETL tool. It is FREE and it lives in http://sourceforge.net. Available both for Linux and Windows. It works a treat. It is just 22MB of software. You can downoad Apatar from:

http://apatar.com/download.html

Once you installed it you just drag and drop objects to its screens. These objects could be databases and maybe applications you have in the Cloud. You configure the objects by double-clicking on them and providing database login and Cloud login info. At no time, you start pumping and synchronizing data between your database and your Cloud application (i.e. Salesforce). Look at some screen-shots from the Apatar website to understand how it works. You can even schedule this operations to happen at times of your choice with the tool.

It is great to see Cloud enabled great software like Apatar for FREE and this doesn't stop here. I found even more tools like apatar for free here:

Talend

Xaware

Entity Relationship diagrams using Oracle SQL Developer Data Modeling

2008-11-12T13:17:00.022Z

Finally, the early adopter release of Oracle SQL Developer Data Modelling tool is out. It is easy to use and I quickly managed to reverse engineer a database schema from an Oracle 10g database.

All you have to do is to connect to a database, specify which schema and which tables you want to include in your ER Model, and then just press the button. It even connects to Microsoft SQL Server 2000 database and extracts the ER Models. Pretty impressive!

I use ubuntu 8.04 and the install was easy, just unzip the downloaded file osdm-1.5.1-518-no-jre.zip to a directory and run the osdm.sh script. See instructions below.

It was about time for Oracle to come with a solution like this and for FREE, well almost. Still not clear! But I downloaded the early adopter version to try it.

You can download Oracle SQL Developer Data Modeling from: http://www.oracle.com/technology/products/database/sql_developer/files/Modeling.html

Here are the instructions to reverse engineer a schema using the tool.

1. Go to directory where you unziped the download (in my case the download osdm-1.5.1-518-no-jre.zip) like this:


cd /opt/osdm1/osdm/bin

2. Run the osdm.sh file like this


sh osdm.sh

3. When the application starts from the menu, go to:


File > Import > DB Catalog

4. The "DB Metadata Extraction Wizard" starts.

Just follow the instructions and provide your login credentials like you would do with any other tool connecting to a database and choose the tables you want to model.

Enjoy modeling in your universe of discourse!

You can also read my other post on this topic which shows you how to create a database with OSDM here

Microsoft SQL Server 2000 Data Dictionary view to view all objects in the database

2008-11-11T08:10:00.006Z

See all objects in your Microsoft SQL database and their types.

This is one of my favorite data dictionary queries in MS SQL Server. It shows me quickly what makes up a database, how many tables, triggers, views etc.

I have been using it on MS SQL Server 2000 and it works!


select owner,  cnt, object_type =
CASE xtype
when  'C' then 'CHECK constraint'
when  'D' then 'Default or DEFAULT constraint'
when  'F' then 'FOREIGN KEY constraint'
when  'L' then 'Log'
when  'FN' then 'Scalar function'
when  'IF' then 'Inlined table-function'
when  'P' then 'Stored procedure'
when  'PK' then 'PRIMARY KEY constraint (type is K)'
when  'RF' then 'Replication filter stored procedure'
when  'S' then 'System table'
when  'TF' then 'Table function'
when  'TR' then 'Trigger'
when  'U' then 'User table'
when  'Q' then 'UNIQUE constraint (type is K)'
when  'V' then 'View'
else 'unknown'
end
from 
(
select su.name owner, so.uid uid, so.xtype xtype,count(*) cnt from sysobjects so, sysusers su 
where so.uid = su.uid
group by su.name, so.xtype, so.uid
) sysobjects_info


Owner           Count   Object Type
----------      ------- ----------------------------------
dbo             12      CHECK constraint
dbo             363     Default or DEFAULT constraint
dbo             127     FOREIGN KEY constraint
xanthi          34      Stored procedure
dbo             241     Stored procedure
dbo             234     PRIMARY KEY constraint (type is K)
dbo             19      System table
dbo             10      Trigger
dbo             399     User table
dbo             6       unknown
dbo             11      View

ORA-01000: maximum open cursors exceeded

2008-11-10T11:17:00.018Z

When you get the ORA-01000: maximum open cursors exceeded did you ever want to see which SQL is causing this error? That is, try to find where the leak is?

Then try running the following sql statements on the dynamic dictionary views V$SESSION and V_$OPEN_CURSOR. This statement shows which SQL statement, which username and which machine are using up your cursors.


SQL> select s.machine, oc.user_name, count(1) as count, oc.sql_text
from sys.v_$open_cursor oc, v$session s
where s.sid = oc.sid
group by s.machine, user_name, sql_text
order by count desc;


MACHINE       USER_NAME COUNT   SQL_TEXT
----------------------- --------------- ------- ------------------------------------------------------------
Thessaloniki.com   Athena    127  SELECT NAME, EAR_NAME, MASTER_NODE_ONLY, ENABLED, DEFINITION
Rome.com      Athena    120  SELECT NAME, EAR_NAME, MASTER_NODE_ONLY, ENABLED, DEFINITION
Kavala.com      Athena    43   SELECT NAME, EAR_NAME, MASTER_NODE_ONLY, ENABLED, DEFINITION
Kavala.com      Athena    39   select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
Thessaloniki.com   Athena    35   INSERT /*+ IDX(0) */ INTO "Athena_OWNER"."MLOG$_USERS" (dmlt

You must run this as SYSDBA. The output is from an Oracle 10g instance.

Cloud computing

2008-10-28T07:48:00.029Z

These two buzz words "cloud computing" have been in my attention lately and I really wanted to find out what they stand for. I got a classic and short definition from Wikipedia:

"Cloud computing is Internet-based ("cloud") development and use of computer technology ("computing"). "
see full definition....

It seems like it is time for the internet to prove what it stands for, the ultimate computer!?

Yes, from the look of things, all of the applications and databases of humankind are going to be on the internet. At the office we will not have "server rooms" any more, but just PCs with Internet browsers. Developers, will be the only kind of IT people that will stay back in the office, they will sit very near to the boss together with Business Analysts and IT Managers. Modern day developers of the future will be using the browser, and rapidly building applications, maybe with "declarative programming". Is it already hapenning? If you look at the business model of companies like Salesforce where they market concepts like "No Software!" and Software as Service (SaaS) and Oracle's hosted rapid application development environment called Oracle Apex one can easily start seeing The Clouds gathering in the sky. How about the Greenplum the Mega-Giga Titanic Datawarehouse for everyone on the Internet. A datawarehouse internet appliance where everyone can put their datawarehouse and access it from anywhere on the earth on a browser and do analysis. No Servers, No hardware, No Software just a login to a workspace on the internet and that's it. Everything you need, tools, spreadsheets will be there on the internet wating for you.

I wonder what will happen to all those other IT guys, SysAdmins, DBAs. Will they lock them up in huge data centres somewhere in the countryside? Will they be needed at all?

Tom Kyte an Oracle Expert was asked a smilar question on his website and here is Thomas Kyte's comment on Cloud Computing.

Or is it going to be as Tom Kyte says that with 'Cloud Computing', databases will just get larger and larger.

Monitor the time an RMAN backup takes

2008-10-27T14:08:00.016Z

To see what RMAN is doing now, and see what SID is doing what sort of work, and how much it has got left to do, use the following SQL. This script is good when you are trying to see how much work an RMAN Channels have got left to do. It is good to watch with the RMAN backup script log (tail -f) as the backup is hapenning. For both scripts you have to lonig as SYSDBA on the instance where the BACKUP
is or RESTORE hapenning.



select
sid,
start_time,
totalwork,
sofar,
(sofar/totalwork) * 100 pct_done
from
v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%'
/

SID        START_TIM  TOTALWORK      SOFAR   PCT_DONE
---------- --------- ---------- ---------- ----------
       100 27-OCT-08    1554952    1364978 87.7826454

To watch the success or failure of an RMAN job in the past, or even when it is hapenning, you can use the dynamic v$ view v$rman_status. The following query will show you a history of your BACKUP and RESTORE operations. By changing the where start_time > sysdate -1 clause you control how much in the past you want to look at. I am using this on Oracle 10g, I don't know if it is available on Oracle 9i and before.



select to_char(start_time, 'dd-mon-yyyy@hh24:mi:ss') "Date", 
status, 
operation,
mbytes_processed
from v$rman_status vs
where start_time >  sysdate -1
order by start_time
/

Date                 STATUS                  OPERATION                         MBYTES_PROCESSED
-------------------- ----------------------- --------------------------------- ----------------
27-oct-2008@11:40:11 FAILED                  RMAN                                             0
27-oct-2008@11:40:29 COMPLETED               BACKUP                                       11812
27-oct-2008@12:06:30 COMPLETED               BACKUP                                       23112
27-oct-2008@12:41:45 COMPLETED               BACKUP                                         160
27-oct-2008@12:42:28 FAILED                  CONTROL FILE AUTOBACKUP                          0
27-oct-2008@17:24:28 RUNNING                 RMAN                                             0
27-oct-2008@17:24:43 COMPLETED               DELETE                                           0
27-oct-2008@17:24:51 COMPLETED               CATALOG                                          0
27-oct-2008@17:25:16 RUNNING                 RESTORE                                  22082.875

Partitioning using CREATE TABLE AS (CTAS) and Column Default Values

2008-09-25T12:55:00.012+01:00

This is about the CTAS (Create Table As...) operations during the creation of partitioned tables from normal tables in an Oracle database.

The CTAS operation although copies column constraints such as NULL, NOT NULL from the normal table to the partitioned table during the partitioned table creation, it does not copy DEFAULT VALUEs of the columns. This might lead to a nasty surprise if you are doing RANGE PARTITIONING and the partition key DATE column has a DEFAULT VALUE of SYSDATE in the normal table. This DEFAULT VALUE setting is NOT copied to the partitioned table!

What will happen is, if you do not specify a date explicitly in your INSERT statements you will get an 'ORA-14400: inserted partition key does not map to any partition' error. As the partition key value passed in will be NULL and the partitioned table will NOT know about the DEFAULT value.

Things can be worse if you are using a combination of CTAS to create partitioned tables from normal tables, and then you use an ALTER TABLE ... RENAME TO .. to replace your normal production tables with the new partitioned tables. Let me tell you how. If your application's INSERT statements into these new partitioned tables, do not explicitly specify the date value of the partition key, you will pass in NULLs and hit ORA-14400. Watch out you will suffer production outage!

To fix this problem, you will have to either explicitly change your code to pass in a date value for the partition key column, or alter the partitioned table after CTAS and modify the column to have a default value.

Here is a demonstration on Oracle 10g R2 of how easily this thing can happen.

Create the normal table


conn scott/tiger
Connected.

drop table big_table;

create table big_table
(id number primary key,
 subject varchar2(500),
 created_date date default sysdate
)
/

insert into big_table (id, subject) values (4,'tset3')
/

1 row created.

commit;

Commit complete.

Create the partitioned table with CTAS from the normal table above, consider using NOLOGGING table creation option to avoid trashing the logs if you think this data is recoverable from elsewhere. This will also create the table faster.





drop table par_big_table
/
-- change dates below appropriately to include the SYSDATE 
-- at the time you run this example.

create table par_big_table
partition by range (created_date)
(
partition p200809 values less than (to_date('01-10-2008', 'DD-MM-YYYY')),
partition p200810 values less than (to_date('01-11-2008', 'DD-MM-YYYY'))
)
as
select * from big_table
/

Now try to insert into the new partitioned table without passing the CREATED_DATE value, as you will wrongly assume the new partitioned table will have a DEFAULT VALUE. See how you get the error.


insert into par_big_table (id, subject) values (5,'test4')
/

insert into par_big_table (id, subject) values (5,'test4')
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

This happens because the value the INSERT statement is passing for the CREATED_DATE column is NULL and the partitioned table doesn't have a DEFAULT VALUE for this column.

To fix this error and stop the production outage you might have caused :-) you have two choices:

* ALTER the new partitioned table and make the column to have a DEFAULT VALUE
* Change the application code to always include a value for the CREATED_DATE

Let's ALTER the table.


alter table par_big_table modify ( created_date date default sysdate);

insert into par_big_table (id, subject) values (5,'test4');

1 row created.

commit;

Commit complete.

Again watch for those DEFAULT VALUES in columns when you are creating partitioned tables with CTAS.

Capture bind variables in SQL with Oracle FGA

2008-08-15T21:34:00.013+01:00

In Oracle 10g with fine-grained auditing (FGA) it is possible to track the bind variables of your application's SQL statements. I know, Oracle 11g is out but how amazing, I am still discovering new things in Oracle 10g!

Oracle FGA is good for bind variables, if that is what you want after all. It is lightweight and more easy to use than its alternatives, for example the full SQL TRACE with the option '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'.

Another benefit of FGA is that you can choose which object you want to audit and what kind of statements, SELECT, INSERT...etc you want to audit. Whereas with the SQL TRACE you just have to accept the trace dump and the performance implications.

More good news is that in Oracle 10g you don't have to bounce the database anymore to set the initialization parameter AUDIT_TRAIL and enable auditing for your system. FGA does not require a database shutdown/restart.

Object or user auditing can be done ad-hoc and on the spot, thanks to FGA. You can audit INSERT, DELETE or just SELECT statements. Moreover, you can be object specific or user specific. For example if you wanted to audit the DML happening on a table all you have to do is to create an Audit Policy for that table. You can use the DBMS_FGA.ADD_POLICY procedure to create the policy. The modifications on the table, together with the bind variables, are captured and logged in a SYS owned log table called FGA_LOG$. How amazing!

Here is an example:

We set up an object auditing policy, which will monitor all INSERT, UPDATE and DELETE operations on the table EMP on Scott's schema.


SQL> begin
     dbms_fga.add_policy (
      object_schema   => 'SCOTT',
      object_name     => 'EMP',
      policy_name     => 'EMP_DETECTIVES',
      audit_column    => 'ENAME',
      statement_types => 'INSERT, UPDATE, DELETE',
      audit_trail => DBMS_FGA.DB_EXTENDED
);
end;

PL/SQL procedure successfully completed.

Then connect as SCOTT the owner of the object you are auditing and do some changes on the object.


SQL> conn scott/tiger

Connected.

SQL> variable myname varchar2(50);
SQL> exec :myname :='ROBIN';


PL/SQL procedure successfully completed.

SQL> insert into emp values (9999, :myname, null, null, null, null, null,null);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> delete emp where ename = :myname;

1 row deleted.

SQL> commit;

Now look at the FGA_LOG$ to see the audit entries.


SQL> conn sys/***** as sysdba

Connected.

SQL> column ntimestamp# format a30
SQL> column lsqltext format a15
SQL> column dbuid format a15
SQL> column obj$name format a10
SQL> column lsqlbind format a15
SQL> column lsqltext format a20

SQL> select ntimestamp#, dbuid, obj$name, lsqlbind, lsqltext  from sys.fga_log$;


NTIMESTAMP#                    DBUID           OBJ$NAME   LSQLBIND        LSQLTEXT
------------------------------ --------------- ---------- --------------- --------------------
14-AUG-08 19.39.48.427696 PM   SCOTT           EMP         #1(5):ROBIN    insert into emp valu
                                                                          es (9999, :myname, n
                                                                          ull, null, null, nul
                                                                          l, null,null)

14-AUG-08 19.39.56.577619 PM   SCOTT           EMP         #1(5):ROBIN    delete emp where ena
                                                                          me = :myname

WARNING : Don't forget to drop the policy once you are done, cause the auditing will go for infinite time on the object. The FGA_LOG$ table will fill up and you will waste space. You can drop the policy once you have done with it like this:


SQL>  conn sys/***** as sysdba
SQL> 
begin
    dbms_fga.drop_policy ( 'SCOTT', 'EMP','EMP_DETECTIVES');
end;

Oracle OBIEE Install on Unbreakable Linux

2008-06-16T13:49:00.043+01:00

After visiting a seminar given by Oracle on Data Analysis and Oracle BI, I have decided to explore the Oracle OBIEE product and see it first hand. In this post I would like to share my experience of installing Oracle OBIEE on Oracle's Unbreakable Linux.

I am in the opinion that all things should be in the database and I was reluctant and skeptical about Oracle BI. Why on earth do we need another app server kind of server, a server which is half webserver and half app server and all it does is things like daily office automation tasks. After reading some marketing text about the tool I found it to claim to do things like:

Mail merge
Cheque printing
PDF reports
Flash graphs
Any format Reports
Interactive dashboards
Ad-hoc analysis
Market analysis

A true mix of Siebel, JD Edwards and Peoplesoft one can say. Especially what is the point of having a tool like OBIEE, when you can have all this (maybe not all, but a significant portion) directly from the database with Oracle APEX in Oracle 11g served to the user via a web browser and for FREE. Having Google offering MS Word like applications on the web what is the point really of OBIEE. Is OBIEE already old technology?

Installing Oracle OBIEE on Unbreakable Linux

So, I have downloaded Unbreakable Linux from the Oracle Store here. Installed the OS on a Desktop Machine and then, downloaded Oracle Business Intelligence Enterprise Edition OBIEE and particularly the Linux version file biee_linux_x86_redhat_101333_disk1.cpio from Oracle Downloads Website.

One thing I noticed when I was reading about OBIEE is that it is "Database Agnostic", that is it doesn't need a database to operate, although you can put it in XMLDB. But this is not necessary as OBIEE can live on a file system.

That is what I did and I run the command on the downloaded file like this from my Linux prompt

$ cpio -idmv < biee_linux_x86_redhat_101333_disk1.cpiowhen the file extracted gave me following directory structure

/home/oracle/RH_Linux/Server/Oracle_Business_Intelligence/

then all you need to do to start installilng the OBIEE is run the setup.sh script like this

/home/oracle/RH_Linux/Server/Oracle_Business_Intelligence/setup.sh

During the installation the trickiest part was to provide your JDK software's home. That is you have to have Java installed on the box where you will run OBIEE as it runs on OC4J (Oracle Containers 4 Java) platform. Basically a J2EE platform.

Once I have correctly indicated the directory where my Java JDK 1.5 (or greater) was installed the installation was smooth and finished quickly. I have chosen the full complete installation of OBIEE from the install options and it put software in the designated Oracle BI homes.

/home/oracle/OracleBI
/home/oracle/OracleBIData

As this is not MS Windows I didn't get lots of menu items at the end of the installation. Instead I had to go to a directory and start certain scripts in order to get the whole of my OBIEE infrastructure up and running. If you want to know which servers and services to start, you will need to read stuff on the Installation manuals and other BI blogs. But as a starter page after the installation you will get the welcome page: file:///home/oracle/OracleBI/index_bi_ee.html

This page tells it all. You get links to the 3 main components of your OBIEE installation on the right top corner of this page and these are:

Application Server Control
Oracle BI Interactive Dashboards
Oracle BI Publisher

Theoretically all 3 should work when you click them. In my case I found only the 1st link to be working. After some Google whacking and troubleshooting search I have realised that some services or some '.sh' scripts required didn't automatically run and required services weren't started properly. Tsk, tsk tsk... Remember? This is a linux platform. Probably OBIEE is best run on Windows, then!?

Anyway I found the /home/oracle/OracleBI/setup directory to be full of cool xxxx.sh scripts which you can use to start stuff in OBIEE.

After successfully starting Oracle BI Server, Oracle BI Presentation Services (SAW server) I have finally managed the get all 3 links to work and started exploring the Oracle BI Infrastructure.

In another post I hope to write about my thoughts on OBIEE.

NOTES:

OBIEE Defaut User is : Administrator
OBIEE Default User password is : Administrator

Resources I used for the OBIEE installation.

Data Analysis: A Quick & Cost Effective Approach by Oracle Cornerstone Briefings

2008-06-12T06:53:00.012+01:00

I have attended yesterday the Data Analysis: A Quick & Cost Effective Approach by Oracle Cornerstone Briefings for customers in TVP, Reading, Oracle UK Headquarters.

I must say working in UK for the last decade I have never visited this part of the country which is considered the "Silicon Valley" of UK. I was impressed by the buildings Oracle's training rooms and facilities. Woow!

The topic was about Data Analysis and the OBIEE Server. The presentation was in two parts and was Given by Aidan and Stuart of Oracle UK.

Part one focused in the current situation and challenges in Data Management today. The reference architecture was a 3 component chart described as "process data" , "manage data" and then "access data".

The highlights of the first part can be summarized as:

The data management process is still difficult, there are still a questions about the "single version of truth" in data management still people chop and spread data all over the place and analyse it in tools such as Excel and etc.
There is still complexities even the best of the tools out there can not address.
The sources, where we gather data from are still evolving, and new sources appear from which we are not able to harvest the data quickly and efficiently.

The second part of the briefing was, if you haven't guessed already, about how Oracle with its Business Intelligence Server Enterprise Edition (OBIEE), ex Siebel a company Oracle has purchased recently, is able to meet these challenges and complexities on data management.

It seems to me like OBIEE, sweet name for a product, they call it just "obiee", is an application which is "Database Agnostic" that is, it doesn't need to belong or be installed on or work with a particular database. It is a system which is installed on a file system, windows, linux whatever and then it runs all your reporting, report publishing, dashboarding and metadata in one place. So it is not a database! It is a kind of application which manages your reporting and dashboarding metadata storage and publication processes. A kind of webserver and appserver together which schedules and runs queries on any database, the extract of reports via email, pdf, Excel, XML, Flash Charts, dashboards you name it, and it is possible kind of tool.

When I was listening and seeing demonstrations about all this I was thinking how different is OBIEE from Oracle APEX. Then I asked them.

Their answer was that they were not similar at all. APEX is more like a rapid application development environment on the web and is all stored in the database in XMLDB. Whereas although OBIEE can be stored in XMLDB like Apex, Obiee is not an application development tool but a data analysis environment. They are different. Are they?