Do you have a data model of your production schema?

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

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

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

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

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!)