A handy way to tune one's SQL

Recently, a french project manager working in a 5 year long European commission project in Brussels, contacted me and complained that their fully working code (in testing  environment), using Oracle Text 10g, failed to meet the customer expectations. In  their RAC production environment the application was unbearably slow. The project deadline is early June this year. Not an unknown situation, isn't it?

One of the most usual issues when moving from the development, or testing  environment to  the production, is setting up properly or updating the database schema and tuning it. In this new post, a fast and handy way for web developers to create, or even drop, indexes, partition tables or materialized views will be demonstrated. No SQL plans or other hard core DBA stuff will be mentioned here, the SQL of the notorious TUHRA ADF model project will be used as an example. So, let's start!
 Consider for instance the following trivial ANSI query for the employees search page based on the email, first and last name criteria entered by the user:

---

SELECT /* + INDEX_COMBINE( Employees Departments )*/

Employees.EMPLOYEE_ID,

Employees.EMAIL,

Employees.FIRST_NAME,

Employees.LAST_NAME,

Employees.PHONE_NUMBER,

Departments.DEPARTMENT_NAME,

Departments.DEPARTMENT_ID,

Jobs.JOB_TITLE,

Jobs.JOB_ID

FROM EMPLOYEES Employees join DEPARTMENTS Departments on (Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID)

join JOBS Jobs on (Employees.JOB_ID = Jobs.JOB_ID)

ORDER BY LAST_NAME,FIRST_NAME

---

When one opens the relevant view object and observes the SQL  query to explain, one sees the image presented below:

The highlighted code below, the  where clause, is actually missing. You need  to enable ADFLogger logging, run the app and search for the actual full query in the output log which is rather time consuming. Besides, when you open sqlplus and enter:

---

Explain plan for

SELECT * FROM (SELECT /* + INDEX_COMBINE( Employees Departments )*/
Employees.EMPLOYEE_ID,
Employees.EMAIL,
Employees.FIRST_NAME,
Employees.LAST_NAME,
Employees.PHONE_NUMBER,
Departments.DEPARTMENT_NAME,
Departments.DEPARTMENT_ID,
Jobs.JOB_TITLE,
Jobs.JOB_ID
FROM EMPLOYEES Employees join DEPARTMENTS Departments on (Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID)
join JOBS Jobs on (Employees.JOB_ID = Jobs.JOB_ID)) QRSLT

WHERE ( ( (UPPER(EMAIL) LIKE UPPER('%' || :QueryEmail || '%') ) 

AND (UPPER(FIRST_NAME) LIKE UPPER('%' || :QueryFirstName || '%') )

AND (UPPER(LAST_NAME) LIKE UPPER('%' || :QueryLastName || '%') ) ) )

ORDER BY LAST_NAME,FIRST_NAME

select *
from table(dbms_xplan.display)
/

---

you get a new plan, which is naturally different. Many people would rightfully argue that creating a function based index, concatenating all the three columns in that order, would solve the tuning problem. But would that really solve it?

To index or not to index? That is the question!

The easiest way to fly is via the automatic pilot. You test fully your app, ideally in isolation and now you  can ask the SQL advisor, thanks to the enterprise manager, following the images below:

 Click on Advisor central to proceed. Next click on sql access advisor in order to create a db job and schedule it to run immediately. Next click on current and recent sql activity, proceed  to select indexes or materialized views  or partition tables, reach the review page where show sql, reveals what is happening under the cover:

---

DECLARE

taskname varchar2(30) := 'SQLACCESSTuhra3';
task_desc varchar2(256) := 'SQL Access Advisor';
task_or_template varchar2(30) := 'SQLACCESS_EMTASK';
task_id number := 0;
wkld_name varchar2(30) := 'SQLACCESSTuhra3_wkld';
saved_rows number := 0;
failed_rows number := 0;
num_found number;
BEGIN
/* Create Task */
dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR,task_id,taskname,task_desc,task_or_template);/* Reset Task */
dbms_advisor.reset_task(taskname);/* Delete Previous Workload Task Link */
select count(*) into num_found from user_advisor_sqla_wk_map where task_name = taskname and workload_name = wkld_name;IF num_found > 0 THEN
dbms_advisor.delete_sqlwkld_ref(taskname,wkld_name);END IF;/* Delete Previous Workload */
select count(*) into num_found from user_advisor_sqlw_sum where workload_name = wkld_name;IF num_found > 0 THEN
dbms_advisor.delete_sqlwkld(wkld_name);END IF;/* Create Workload */
dbms_advisor.create_sqlwkld(wkld_name,null);/* Link Workload to Task */
dbms_advisor.add_sqlwkld_ref(taskname,wkld_name);/* Set Workload Parameters */
dbms_advisor.set_sqlwkld_parameter(wkld_name,'VALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'VALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'SQL_LIMIT',DBMS_ADVISOR.ADVISOR_UNLIMITED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'VALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'ORDER_LIST','PRIORITY,OPTIMIZER_COST');
dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'VALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_SQLSTRING_LIST','"@!"');
dbms_advisor.set_sqlwkld_parameter(wkld_name,'JOURNALING','4');
dbms_advisor.set_sqlwkld_parameter(wkld_name,'DAYS_TO_EXPIRE','30');
dbms_advisor.import_sqlwkld_sqlcache(wkld_name,'REPLACE',2,saved_rows,failed_rows);/* Set Task Parameters */
dbms_advisor.set_task_parameter(taskname,'ANALYSIS_SCOPE','INDEX,TABLE,PARTITION');
dbms_advisor.set_task_parameter(taskname,'RANKING_MEASURE','PRIORITY,OPTIMIZER_COST');
dbms_advisor.set_task_parameter(taskname,'DEF_PARTITION_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'TIME_LIMIT',10000);
dbms_advisor.set_task_parameter(taskname,'MODE','LIMITED');
dbms_advisor.set_task_parameter(taskname,'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
dbms_advisor.set_task_parameter(taskname,'DML_VOLATILITY','TRUE');
dbms_advisor.set_task_parameter(taskname,'WORKLOAD_SCOPE','PARTIAL');
dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVLOG_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'CREATION_COST','TRUE');
dbms_advisor.set_task_parameter(taskname,'JOURNALING','4');
dbms_advisor.set_task_parameter(taskname,'DAYS_TO_EXPIRE','30');/* Execute Task */
dbms_advisor.execute_task(taskname);END;

---

You end up as shown:

 where one can see the whole set of the sql statements issued. If you click on schedule implementation the recommended indexes are created, while clicking show sql gives you the script to run when you wish, on your own:

---

Rem SQL Access Advisor: Version 11.2.0.1.0 - Production
Rem
Rem Username: SYSTEM
Rem Task: SQLACCESSTuhra3
Rem Execution date:
Rem
/* RETAIN INDEX "HR"."IMAGE_USAGES_PK" */
/* RETAIN INDEX "HR"."DEPT_ID_PK" */
/* RETAIN INDEX "HR"."JOB_ID_PK" */
/* RETAIN INDEX "HR"."IMAGE_USAGES_ASSOCIATED_IDX" */
/* RETAIN INDEX "HR"."JOBS_TITLE_IDX" */
/* RETAIN INDEX "HR"."EMP_DEPARTMENT_IX" */
/* RETAIN INDEX "HR"."EMP_EMAIL_UK" */
/* RETAIN INDEX "HR"."IMAGES_PK" */
/* RETAIN INDEX "APEX_030200"."WWV_FLOW_PLATFORM_PREFS_FKIDX" */
/* RETAIN INDEX "HR"."EMP_EMP_ID_PK" */
CREATE INDEX "HR"."EMPLOYEES_NAME_IDX"ON "HR"."EMPLOYEES"
("LAST_NAME","FIRST_NAME")COMPUTE STATISTICS;

---

Thus, the aforementioned concatenated index is not being used, and is just decelerating inserts and taking up  valuable disk space.We can safely drop it for now. This does not mean that we are done! Since the production db will have millions of rows, other statistics,etc, the db job results, i.e.  about indexing, may frequently change. The morale of this story is that the earliest one starts to test against the full production data,  the higher the possibility of project success. Performance tuning is not an afterthought, but a gradually ongoing process, which must ideally begin with the first application release. Nonetheless, not many developers follow this rule. Cynicals say that experience is just a set of past, bitter failures. Germans say: Durch Schaden wird mann klug. The man on the street tends to forget more easily, whatever has been unpleasant in the past.  It seems that IT staff does not have that privilege!

Vacation in eastern Crete: visit the place of 'The Island' by Victoria Hislop!

In case you are fed up with programming and reading several books or manuals, this could be something of a break. This is a brief description of travel experiences in eastern Crete, or Kreta for German speaking people, that is Malia, Ag.Nikolaos, Neapoli, and finally Chersonisos. You can see the map on your left hand side, the position of the island of Crete in the Greek territory. If you have read, watched on television, or even heard of a book called 'The island' authored by Victoria Hislop: http://www.amazon.co.uk/Island-Victoria-Hislop/dp/0755309510 printed in many editions, this  is the place mentioned in the text!
You can reach Crete by air, which is more convenient and a shorter trip, and by ship which  can last for 8 hours. Once you arrive, you certainly need a vehicle, because the island is big and there many places worth visiting, starting from...

Ag.Nikolaos
It is a crowded city, full of shops, traditional pubs, dancing clubs, restaurants etc. There is a fantastic salty  lake and a bridge linking the two parts of the city, which I very much enjoyed walking on. There is plenty of real estate property available here for sale. Pensioners or investors may make bargains. The place is most suitable for families and people who prefer a quiet vacation. In case you 'd rather a more hectic life, what follows might interest you more.

Malia 

It is a small notorious village in Crete, resembling rather more an English one. That is, it is full mostly of Britons having fun by the beach during daytime, or dancing, getting drunk and raving during the night. Please bear in mind that drunkards often cause riots there and the police arrest many people during the night. Apart from the nightlife, one can visit the archeological site of Malia and see the remains of the ancient palace, near the beach. You can see another picture of its beach below. As you can see part of it is sandy, while another part is rocky.

Chersonisos

This the place most loved by northerners. It is full of Scandinavian and German people wandering in its streets, visiting the jewelery, leather, pastry shops   and of course the beaches. The beaches there are mostly sandy and rather crowded. During the night the pubs are full of young people dancing and having fun. The nightlife is here more quiet and peaceful compared to Malia mentioned above.

Neapolis

Neapolis is a provincial town with old, traditional buildings, made mostly by stones. Its streets are narrow, there are only less than a handful   of cars crossing them, so this is  a chance  for you to exercise, by walking or jogging. There are naturally many old fashioned shops and modern department stores as well. The night life in Neapolis is quite peaceful and quiet. On your left hand side, is the view you see every morning outside the window, when you wake up. In addition to all the mentioned places, one can also visit the more high society, aristocratic Elounda and at last what so many people have been longing for: local authorities have announced that the island of Spinaloga will be now open to the public! 

Finally, as far as prices are concerned, some goods such as food and fuel are slightly more expensive than in Athens. On the other  hand, fruit, vegetables and olive oil are fresh, of high quality and at reasonable prices. Besides, the healthy Cretan diet is famous around the world. The people are hospitable, always obliged to the British commonwealth foreigner troops who helped them, all those having fought against the N.A.Z.I. invaders back in May 1941, organizing yearly memorials for the living, now over 90 years old soldiers to remember the ones who have fallen and express their gratitude. Furthermore,  there are plenty of ancient sites to see, lovely sea for sailing and swimming, fisheries and warm climate... Thus, visiting Crete might be an alternative suggestion one has to consider, before booking the  next  summer holidays! 

Setting up Oracle 11g Complex Event Processing (CEP) server

 The power of change in human life has long fascinated people. Babies are born, other people pass away,  employees are made redundant, others get promoted. Businessmen, or whole states, go bankrupt, while others prosper.  Even at war, a sudden change in weather can reverse the outcome of an initially easy to win, battle. The highly educated executives soon realized that staying informed about  business, environmental, or other changes is of vital importance. That's why they keep asking for suitable dynamic IT tools, such as the business activity monitor (BAM), Business Intelligence (BI) and CEP.
In order to briefly compare the characteristics of each technology, I shall quote a few lines of text of the  new SOA Handbook authored by Lucas Jellema: "BAM may seem very similar to Business Intelligence (BI) as you know it, and of course there is a lot of similarity if not overlap. The key differentiator is the real-time aspect of BAM, along with the active alerting responsibility it has. The scope in time of BAM dashboards is usually fairly short—up to hours or days—and would hardly ever cross the quarter or year boundary, whereas traditional BI tends to take a look at data from a more historic, longer-term perspective in a more passive way."
Let us now focus on CEP. The scope of CEP is less than a hour, in other words the data are as dynamic, as your processor power and network speed permit. As for detailed setup instructions of the CEP server one can visit:
http://theo.vanarem.nl/2010/05/27/getting-started-with-oracle-11g-complex-event-processing-cep/

Once you are done with the setup you start the Jetty server and receive the following messages in the Eclipse Galileo 3.5.2 log:

---

<8 Φεβ 2011 2:36:28 μμ EET> <Notice> <LibExt> <BEA-000000> <The extension library bundle "oracle.jdbc.driver" was started successfully>
<8 Φεβ 2011 2:36:30 μμ EET> <Warning> <Store> <BEA-280101> <The persistent file store "tlog_WlevServer" is forced to use buffered I/O and so may have significantly degraded performance. Either the OS/hardware environment does not support the chosen write policy or the native wlfileio library is missing. See store open log messages for the requested and final write policies. See the documentation on store synchronous write policy configuration for advice.>
<8 Φεβ 2011 2:36:30 μμ EET> <Notice> <LoggingService> <BEA-320400> <The log file C:\Oracle\Middleware\singleServer\user_projects\domains\ocep_domain\defaultserver\server.log will be rotated. Reopen the log file if tailing has stopped. This can happen on some platforms like Windows.>
<8 Φεβ 2011 2:36:30 μμ EET> <Notice> <LoggingService> <BEA-320401> <The log file has been rotated to C:\Oracle\Middleware\singleServer\user_projects\domains\ocep_domain\defaultserver\server.log00016. Log messages will continue to be logged in C:\Oracle\Middleware\singleServer\user_projects\domains\ocep_domain\defaultserver\server.log.>
<8 Φεβ 2011 2:36:35 μμ EET> <Notice> <evs4j> <BEA-2049007> <The cluster protocol is disabled>
<8 Φεβ 2011 2:36:35 μμ EET> <Notice> <BDBProvider> <BEA-2052004> <Created and registered BDBProvider default-provider>
<8 Φεβ 2011 2:36:38 μμ EET> <Notice> <Spring> <BEA-2047000> <The application context for "com.oracle.cep.cartridge.jdbc" was started successfully>
...
Exception in thread "RMI Reaper" java.lang.OutOfMemoryError: PermGen space

---

For the solution to the problem one needs to consult a useful post published back in 2009:
http://oraclebpelindepth.blogspot.com/2009/04/javalangoutofmemoryerror-permgen-space.html
 in order to edit the file: startwlevs.cmd, found in C:\Oracle\Middleware\singleServer\user_projects\domains\ocep_domain\defaultserver as shown:

---

@ECHO OFF

@rem Copyright 2006 BEA Systems, Inc.

call ".\setDomainEnv.cmd" %*

set DGC_ARGS=-Xgcprio:deterministic -XpauseTarget=10ms
set DEBUG_ARGS=-Xdebug -Xrunjdwp:transport=dt_socket,address=8453,server=y,suspend=n
set DGC=

set JVM_ARGS=-XX:MaxPermSize=1024m

set DEBUG=
set ARGS=

:loop

if "%1" == "" goto endloop

if "%1" == "-dgc" (
 set DGC=%DGC_ARGS%
 shift
) else if "%1" == "-debug" (
 set DEBUG=%DEBUG_ARGS%
 shift
) else if "%1" == "-debugPort" (
 set DEBUG=-Xdebug -Xrunjdwp:transport=dt_socket,address="%2",server=y,suspend=n
 shift
 shift
) else (
 set ARGS=%ARGS% "%1"
 shift
)

goto loop
:endloop



"%JAVA_HOME%\bin\java" %JVM_ARGS% %DGC% %DEBUG% -Dwlevs.home="%USER_INSTALL_DIR%" -Dbea.home="%BEA_HOME%" -jar "%USER_INSTALL_DIR%\bin\wlevs.jar" %ARGS%
:finish

---

I quote from the aforementioned blog:

"Is this the final solution?


Not really, if you keep loading loads of new processes or huge number of classes this might delay your Out Of Memory Error but will not fix it.


The only solution?


The only solution is to bounce the server once you see this error.


Where else?


This is a general issue faced by all sun java based servers. When huge number if class are loaded as part of an application, this error is expected."

In short, it seems that not all some things change, some are everlasting and prosperous, such as software defects, which persist against any change in time, wear, version or any attempt to fix! Are java bugs really immortal?

Setting up Oracle service registry 11.1.1

db options screen shot

Last time  I attended the Oracle day in Athens, Greece, I was impressed by a presales consultant stating that the real difficulties of SOA projects arise not in the first project, but in the second. That is when there is already a prebuilt foundation of services, on which one needs to base the  new applications of the second project. This is the time to prove whether the initial design is worth of the effort, time and money spent.You have guessed  it right, it is all about agility, adaptability to business, or IT changes, and service reuse!
In this new post the installation traits of  Oracle service registry 11.1.1 will be mentioned. No need to clarify the purpose of existence of a service registry within the scope of SOA in this post. You might consult the current SOA governance literature, take for instance chapter 9 of: http://nickaiva.blogspot.com/2010/12/comments-on-ws-bpel-20-for-soa.html as an introduction. I quote some text: "In addition to reuse, a service registry can also be helpful when we need to migrate services from one server to the other. This can happen because of various reasons,
but one of the most common reasons is the migration between the development, test, and production environments. A service registry is also helpful when we need to version services and manage changes. With a service registry, we can also develop more loosely coupled composite applications, because we do not need to hard-code the service URLs. Rather, the application will resolve URLs at run time."
Let's now proceed with the setup. The first  installation attempts failed with rather cryptic error messages. Well, another personality trait which is very important to programmers and pretty scarce to find, is not being too lazy to type a helpful error message, when exceptions occur. So, the technical data follow:

OS is windows 7 64bit, not officially tested by Oracle at present

DB version is 11.1.0.6.0 64bit

Java version 1.6.0_20 64bit

JDBC driver at C:\app\Nick\product\11.1.0\db_1\jdbc\lib\ojdbc6.jar

---

The log output follows:

Expanding C:\Users\Nick\Downloads\ofm_osr_generic_11.1.1.2.0_disk1_1of1\oracle-service-registry-11.1.1.jar to C:\Oracle\Middleware\registry111 ...
Building scripts ...
Platform is Windows
Preparing 'admin' account ...
Preparing account_list ...
Preparing permission_list ...
Preparing approval management ...
Creating standalone configuration ...

Java returned: 1
Installation failed. If accessible, see "C:\Oracle\Middleware\registry111\log\install.log".
To correct installation parameters and resume installation click Recovery.

---

Next come the contents of C:\Oracle\Middleware\registry111\log\install.log:
#
#Tue Dec 28 15:42:49 EET 2010
db.system.name.condition=oracle
install.server.operator.name=Oracle
oracle.database.admin.password=***
oracle.database.datafile=uddinode.dbf
oracle.server.host=hera
oracle.database.admin.user=system
install.server.smtp.port=25
oracle.database.password.confirmation=***
install.http.connector=8081
alldb.create.datasource.name=jdbc/registryDS
install.server.smtp.password.confirmation=***
install.server.admin.mail=nickaiva@
install.os.is.win.andcondition=true
create.desktop.icons=no
porting.standalone.http.port=8080
oracle.database.tablespace=uddinode
install.server.smtp.default.sender.name=
install.server.smtp.password=***
porting.https.use=yes
alldb.install.registry.name=Oracle Service Registry
account.backend.type.condition=database
install.server.smtp.title=
install.server.admin.name=admin
alldb.create.datasource.weblogic=no
security.ssl.password=***
alldb.install.demo.data=no
install.server.admin.password.confirmation=***
security.ssl.username=uddiadmin
dist.version=11.1.1
install.server.smtp.default.sender.email=
porting.standalone.https.port=8443
alldb.create.datasource=yes
alldb.jdbc.custom.uribox=no
install.type.condition=standalone
install.server.smtp.host=[ SMTP server hostname ]
alldb.install.demo.data.settings=
create.menu.items=yes
alldb.create.drop.condition=createComplete
oracle.database.name=orcl
install.directory=C\:\\Oracle\\Middleware
registry111
install.server.admin.password=***
oracle.server.port=1521
db.showall.condition=false
alldb.jdbc.custom.urifield=
alldb.jdbc.drivers.paths=C\:\\app\\Nick\\product\\11.1.0\\db_1\\jdbc\\lib
ojdbc6.jar
porting.hostname=hera
security.ssl.password.confirmation=***
porting.type.condition=jetty
install.server.smtp.account.name=
oracle.database.user=uddiuser
install.windows.menu=Oracle Service Registry 11.1.1
oracle.database.password=***
[echo] Expanding C:\Users\Nick\Downloads\ofm_osr_generic_11.1.1.2.0_disk1_1of1\oracle-service-registry-11.1.1.jar to C:\Oracle\Middleware\registry111 ...
[echo] Building scripts ...
[echo] Platform is Windows
[echo] Preparing 'admin' account ...
[echo] Preparing account_list ...
[echo] Preparing permission_list ...
[echo] Preparing approval management ...
[echo] Creating standalone configuration ...

[java] BUILD FAILED
[java] C:\Oracle\Middleware\registry111\etc\setup\database.xml:737: The following error occurred while executing this line:
[java] C:\Oracle\Middleware\registry111\etc\setup\database.xml:339: The following error occurred while executing this line:
[java] C:\Oracle\Middleware\registry111\etc\setup\database.xml:422: The following error occurred while executing this line:
[java] C:\Oracle\Middleware\registry111\etc\setup\database.xml:207: The following error occurred while executing this line:
[java] C:\Oracle\Middleware\registry111\etc\db\oracle\installOracleDB.xml:99: The following error occurred while executing this line:
[java] C:\Oracle\Middleware\registry111\etc\db\oracle\installOracleDB.xml:166: Java returned: 1

[java] Total time: 4 seconds
Java returned: 1
Installation failed. If accessible, see "C:\Oracle\Middleware\registry111\log\install.log".
To correct installation parameters and resume installation click Recovery.

---

However many times one tries, with the options selected as shown in the db options screen shot, the installation fails due to some build error of the db scripts. Although the installation appears to have failed, the db uddiuser schema has been actually created. On the contrary, some people report that one is better off to create the schema on his own, as you can see in the references. Thus, when one runs installation the next time, having selected an existing db schema "Connect to schema" option, all appears to go well. Well, not everything! If one uses jetty instead of weblogic server, the create data source option does not apply. Therefore, one needs to uncheck the selection, in order to avoid receiving a "Cannot obtain new connection" exception.
The morale of the story is quite obvious, if related to the introduction of the current post: the first time is just the basis, the second time is what matters most. Happy new year to you all!


Further references:
http://www.javamonamour.org/2010/05/trouble-installing-oracle-service.html
OSR - Oracle Service Registry won't create database datasource

Comments on WS-BPEL 2.0 for SOA Composite Application with Oracle SOA Suite 11g, by Matjaz B. Juric, Marcel Krizevnik

On the bright side, the text is written in American English, formal, well organized and neat, having the last sentence of each paragraph linking to the following one. Although the title sounds pretty familiar, the content of the book is actually about the newer version of BPEL 2.0. That's what makes this particular book unique, as the time of this writing. I quote: "Oracle SOA Suite 11g PS2 supports BPEL 2.0. However, BPEL 2.0 is only supported at runtime and not in JDeveloper. BPEL 2.0 support in Oracle SOA Suite 11g PS2 is not yet production ready, so by default, BPEL version 1.1 is used. However, we can write BPEL 2.0 code in text mode (graphical mode is currently not supported)." This book will help readers utilize BPM suite for integrating BPEL with BPMN. Readers will be able to explore BPEL 2.0 activities, loops, decisions, flow control, variables, scopes and other constructs that will enable them to develop BPEL processes. The authors dig into advanced BPEL topics, such as fault handlers, event handlers, compensation, concurrent activities, links, correlations, message properties, dynamic partner links, process lifecycle, and more. The text is accompanied by two online appendices about the syntax of the BPEL versions 1.1 and the newer 2.0, the  full source code, in addition to a sample chapter and its table of contents, which can be found here. Furthermore, the authors use UML sequence, activity diagrams to describe the composite applications, which is useful to the analysts and designers and scarce to find in competitive books.

On the dark side, there are a few spelling errors, the first 3 chapters are rather tedious to read, since there is no hands on practice, nor questions and answers for the reader to exercise.  The action starts in chapter 4 and forward on. However, there  is only purely descriptive text, with no full, step by step detailed instructions how to build from scratch the application, or how to setup the SOA suite. In addition, parts of the application source code is given as is, i.e. java classes, without any explanation how it was created, or how to deploy it correctly; For example, EmployeeTravelStatus,  in chapter 4, should perhaps be deployed as a shared library, or a stand alone application? 

All in all, the book is a complete and detailed treatise on BPEL 2.0, presenting its origins, other BPEL servers available in the market such as tibco, exotic themes such as Oracle service registry, Business process architect, and so on. Its project is an application about employees travel requests and booking the cheapest available ticket offered by the airline companies,  etc. The book is no introduction to BPEL, but of fairly advanced level, since drag and drop operations are rarely mentioned, the authors prefer to edit the source BPEL and XML code directly, which can be error prone, especially for beginners. Its source code is full of advanced java classes, so developing web services using java, XML basics, look like  prerequisites.
Further references:
You can visit the book page at the publisher's site

SOA: Decoupling the database adapter service from the BPEL process

BPEL process

In this article a common source code reuse problem will be brought to the SOA level, using a small demonstration example based on the SOA 11g Handbook chapter 7 source code. Please bear in mind that in order to fully comprehend the content of this post, one needs as a prerequisite, knowledge of xsd and web services wsdl files. The JDeveloper 11g IDE will be used as in the book, but NetBeans could well be another alternative for your SOA development,  which also supports the 64 bit java JDK, or Eclipse Helios,which is preferred by many big organizations, such as the current European Commission IT projects. So try them all to decide on your own whichever suits you best!


Consider for instance a standard JEE web application which requires access to a database. Using the hard coded jdbc connection string to provide the connection details, requires editing the source code, compiling , testing and redeploying, when a new database is provided or the application needs to become distributed. On the other hand, using a jdbc data source, permits the reuse of the code only with modifying the relevant deployment descriptors by the web administrator.  This analogy exists also in the example SOA application, which requires database access , and will be presented next.

composite.xml

The sample medical examination booking application is about querying a database table based on the   the patient identifier (ID), or the patient names in order to query another table and retrieve its hospital record; as shown in the picture of the BPEL process. The initial BPEL process with direct calls to the Database Adapter Services RetrievePatientIdentifier and RetrievePatientRecord is not presented. The XSD and WSDL created for the Database Adapter Services were tied directly into the BPEL process, which means tight coupling. The text of the SOA 11g handbook only guides to the first step of decoupling one DB adapter. Now, as you can see in the composite.xml image, only one   mediator (purple) exists to decouple the DB from the bpel process, the other DB adapter is rather hastily, directly connected to the   bpel process.
This semi decoupled situation is actually presented as an anti-paradigm or anti-pattern. That is, an example case, that you should avoid by all means, since you must abandon the simple drag and drop operations, you now need to manually edit the xml files to solve the problem of fully decoupling. The  steps necessary to decouple the second db adapter follow in brief. The final bpel process we are to implement  and the final composite.xml is shown:

final form of BPEL process

final form of composite

If you 'd rather not delve into the manually editing details, you can consider the following steps as optional, skip them and jump to the next horizontal line to read the two last paragraphs. This is a very long post after all!

---

Well, if you insist on going beyond drag and drop operations, here is a way of doing it:
First, start  by creating the XSD definitions for the PatientDataService.xsd:

 <schema targetNamespace="http://stmatthews.hospital.com/patient/PatientDataService"
        xmlns:hospital="http://stmatthews.hospital.com/patient/PatientDataService"
        xmlns="http://www.w3.org/2001/XMLSchema">
  <element name="PatientDataServiceProcessRequest"
           type="hospital:patientIdType" />
  <element name="PatientDataServiceProcessResponse"
           type="hospital:patientType"/>

  <!--Add on the 2 following elements  for decoupling-->
 <element name="PatientRecord"
           type="hospital:patientType"/>
  <element name="PatientIdentifier"
           type="hospital:patientIdType"/>
  ...

Based on those, it is easy to create the Mediator and its WSDL as shown.

Next, you need to edit the PatientRecordProvider.wsdl file, so that you don't get this screen shot when you wire the mediator to the db adapter, to the bpel process and later insert the new assign and   the invoke activities:

old type is now wrong

<?xml version= '1.0' encoding= 'UTF-8' ?>
<wsdl:definitions
     name="PatientRecordProvider"
     targetNamespace="http://stmatthews.hospital.com/patient/PatientDataService"
     xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
     xmlns:inp1="http://stmatthews.hospital.com/patient/PatientDataService"
     xmlns:tns="http://stmatthews.hospital.com/patient/PatientDataService"
     xmlns:out1="http://stmatthews.hospital.com/patient/PatientDataService"
    >
    <wsdl:types>
      <schema xmlns="http://www.w3.org/2001/XMLSchema" >
        <import namespace="http://stmatthews.hospital.com/patient/PatientDataService" schemaLocation="xsd/PatientDataService.xsd" />
      </schema>
      <schema xmlns="http://www.w3.org/2001/XMLSchema" >
        <import namespace="http://stmatthews.hospital.com/patient/PatientDataService" schemaLocation="xsd/PatientDataService.xsd" />
      </schema>
    </wsdl:types>
<!--replace RequestMessage with patientRecordRequestMessage
    and refresh the variables in the assign and invoke windows
    to get the patientIdentifier appear instead of the older patientQualifier -->
    <wsdl:message name="patientRecordRequestMessage">
        <wsdl:part name="request" element="inp1:PatientIdentifier"/>
    </wsdl:message>
    <wsdl:message name="replyMessage">
        <wsdl:part name="reply" element="inp1:PatientRecord"/>
    </wsdl:message>
    <wsdl:portType name="patientDataServices_ptt">
        <wsdl:operation name="getPatientRecord">
            <wsdl:input message="inp1:patientRecordRequestMessage"/>
            <wsdl:output message="inp1:replyMessage"/>
        </wsdl:operation>
    </wsdl:portType>
</wsdl:definitions>

Next you declare the new patientRecordRequestMessage type to be used, so:

Thus, you now get to this screen shot instead of the old, wrong one, which shows the patientIdentifier (ID):

The process goes on, you delete the old wire, the old invoke and assign activities, insert new as shown in the final form of the bpel picture, and edit their properties to define the parameters correctly, to avoid such failing build messages:
...
     [scac] FATAL_ERROR: in PatientDataService.bpel(168): wrong messageType
     [scac] messageType "{http://stmatthews.hospital.com/patient/PatientDataService}requestMessage" of variable "Invoke_PatientRecordProvider_getPatientRecord_InputVariable" does not match the expected     messageType "{http://stmatthews.hospital.com/patient/PatientDataService}patientRecordRequestMessage" in <invoke>
     [scac] Make sure the correct variable is used in invoke

BUILD FAILED

that is, if you follow the screen shots:

new invoke properties

Create the mediator xsl files:

Delete the old transform, insert a new one and edit as shown:

Create the xsl mappings:

You end with deployment and testing  the composite, as shown:

---

The question surely arises: is it really worth bothering to edit  all those xml files, is it not error prone? I quote: "The Mediator has several functions that all help promote decoupling and agility. It maps from the canonical data structure to the potentially very specific schema for the adapter service, possibly including any value conversions that may be required. When the Database Adapter Service is replaced by a different service implementation, hence with new WSDL and XSD definitions, the BPEL process is unaffected, because the Mediator shields it from these changes. When the data is for some reason distributed over multiple databases, and based on some patient property we have to determine which of those databases to access, the Mediator will take care of this content-based routing. In addition, the Mediator can handle problems with availability of the database service, retrying calls or taking alternative steps. Moreover, the Mediator may present what is a synchronous service through an asynchronous interface, which allows a client such as a BPEL process to continue with other, parallel activities or to be dehydrated altogether to free up resources after the call is made, instead of blocking the thread waiting for the reply, which could take fairly long."
Finally, the gist of this post is:  BPEL processes should never call to adapter services directly. There should always be a Mediator in between. Sooner or later the need to decouple will arise, if you design correctly straight from the beginning, you won't waste your time and effort. So, next time you are about to wire a database adapter, think twice!

Further references

http://oracle-fusion-blogs.com/oracle-fusion-osb-mediator/

Comments on Oracle SOA Suite 11g Handbook, by Lucas Jellema

On the bright side, the book offers a plethora of web resources, apart from the textbook itself. The online supplements are actually a way of previewing it by studying the material mentioned, before buying the book. The text is well organized with a few spelling errors.  It delves deeply into XML code details, such as creating the xsd files and editing them. The historical review serves well as an introduction, presenting both the evolution of technologies and relevant acronyms, such as SLA, SCA and so on. Moreover, the step by step guidance offered, is most of the times, easy to understand and follow. Furthermore, some parts  of the source code is available online, including the intermediate steps. The chapter about complex event processing is remarkable, however one needs Eclipse Galileo, not Helios, to follow the hands on guidance.
On the dark side, some sections are too long to read. Once you are done with the printed text, the online supplements must follow. Besides, the huge volume of information is often hard to manage, therefore rather messy. Some parts of text are duplicated,  some others missing. For instance, on page 74 it reads: "Details for the configuration of JMS, JDBC, and UMS can be found online in Appendix C." which has been actually until recently missing. In chapter 7  some errors are reported, due to missing steps, or xsl files and a misleading image, but not yet confirmed. In addition, parts of the source code are missing, like the XML file SimplePatient.xsd mentioned on page 109. Moreover, some spelling errors are critical, omitting an 'a' from 'asynchronous', surely changes the meaning! Some crucial xpath expressions have typographical errors as well. Web services presented in the text such as ConsultWithHealthInsurers in chapter 11, are not implemented. Furthermore, in chapter 13, page 448: "The wiki provides configuration details for setting up a local e-mail server with these domains and accounts based on the JavaEmail project" but  no such details actually existed until recently. Such errors, or missing information prevent the reader from completing the practice, build the necessary self confidence and continue. Unfortunately, the source code for chapters 10 and above has not been  available until recently,  due to broken links.The code for chapter 15 is still missing. Although most times  the known bugs of soa suite are mentioned, this is not true for the ADF service data objects examples of chapter 20.
All in all, the text is of average quality, the author has striven to equally balance the theoretical and practical  views of the subject. It offers an introduction to XML, XPath too, some of hands on practice. Hence, the book is definitely among the top choices for the reader who would like an introductory book, but clearly inferior to  the Getting started with SOA 11g, as far as quality and precision of hands on practice is concerned. As far as quantity, the difference in number of pages speaks for itself!

JDeveloper 11g: Making use of Http Analyzer for testing and debugging

Anyone who has  developed a JEE or SOA application, sooner or later faces some kind of unexpected, or strange behaviour  of the system under development. However helpful the audit process screens of the weblogic server, once you enter and submit the sometimes numerous input values, one is not allowed to tamper with the request values anymore; you just wait  until you get hopefully a response, or an error. An interesting , but often overlooked, alternative is  Http Analyzer. It lets you save some time, by copying the request so that you don't have to enter all values from scratch, you can edit the value of interest and resend your modified request. Let's see some specific examples.

Setting up the browser

In order to quickly set it up, you go to "Tools" menu and click on "Preferences" in JDeveloper 11g as shown in the image. You need to copy the "Listen on port" value, which is 8099. Next,  you need to setup the browser of your choice. The images below show the screen shots for Mozilla Firefox, I suppose finding your way in IE or other browser won't be such an onerous task!

Again click on "Options", then on "Advanced", "Network" and lastly on "Settings" to reach the connection settings window as shown . You need to select "Manual proxy configuration", enter your computer name, and paste the port number you copied before, i.e 8099. The "No proxy for" section must not contain any entries, such as localhost, nor 127.0.0.1, because in that case Http Analyzer will only analyze your internet requests! When you are done with debugging, you might need to  reenter here your old values. That means you need to save them, i.e in a new back up text file! For an alternative more elegant configuration using profiles, you can consult the JDeveloper documentation, by searching for"Configuring External Web Browsers"

Testing a trivial ADF form

Next, you start the  application, using the "Run project" button, and click the "Start HTTP analyzer" button. The small  form of the ADF ejb bidding application window appears. You should now see the output in the log window. If you enter the form values and submit, the HTTP analyzer captures all submitted values. You can now copy the request,  edit only the bidder's name for instance, and resend.

Testing a plain SOA component

For the second example, you need to reenter your old settings to your browser(i.e. No proxy). The request values will be submitted by JDeveloper itself, by right clicking on the service, as shown.
Here a US stock exchange price in dollars, i.e. for OTE or GE, is converted into a different currency, i.e. EUR or GBP for British pounds.  You can see the request and response values as well. As mentioned before, you need to copy the request, modify only where necessary and resubmit.
Although the  examples given are deliberately simple, a small relevant quotation will be given, concerning the importance of retaining the input parameters, and another alternative as well. I quote from the Oracle SOA Suite 11g Developer's guide: "If you have a very complicated interface, you may not want to have to enter the parameter values every time you test the composite. In 10g, there was a facility to set a default input to a BPEL process. Unfortunately, there is no such facility for 11g composites. In order to avoid retyping complex inputs, the input can be saved to a file and then pasted into the test dialog every time...". Finally, if your needs are bigger, you might consider another alternative testing tool like SOAPUI which is  available online.

JDeveloper 11g: Using assert() for testing and debugging

Assertions are a very useful feature for testing and debugging, but are rather neglected. Instead of using loggers to provide continuously diagnostic messages in critical programme  flow points, or even worse if- else - System.out.println() constructs, assert statements can only be enabled only when necessary. Thus, you avoid wasting valuable resources. In addition, you do not need to add any jar libraries to your IDE  project, or application server to use it. In order to use assert, you need to  pass the -ea argument, call a non void java method and check a Boolean  condition. For instance: assert(Month < 13). Nonetheless, you can not  use an assert statement to check the values passed to public methods. Consider for example the following code snippet, from an business components, employees entity object:
              
protected void doDML(int operation, TransactionEvent e) {
                if (operation == DML_UPDATE) {
    ...
               histStartDate = row.getEndDate();
              /*Check whether end date equals current date*/
              /*if (histStartDate.equals(new Date(Date.getCurrentDate()))){
                  System.out.println("End date equals current date!");
                  }*/
               assert(histStartDate.equals(new Date(Date.getCurrentDate() ) )) :
                          "End date equals current date! ";

Another trivial example from an ejb client follows:

public class PlaceBidClient {
    public static void main(String [] args) {
...
                     Bid bid = placeBid.addBid("Lila",  Long.valueOf(100),  2001.50);
                     System.out.println("Bid Successful, BidId Received is:" + bid.getBidId());
                     assert(bid.getBidId() == 502):"BidId Received is "+bid.getBidId();
  }
}
Finally, when run, the log output reads:

-javaagent:C:\Oracle\Middleware\jdev_11gR1\jdeveloper\..\modules\org.eclipse.persistence_1.0.0.0_2-0.jar -Duser.language=en -Duser.country=US -ea actionbazaar.buslogic.client.PlaceBidClient

Exception in thread "main" java.lang.AssertionError: BidId Received is 393
    at actionbazaar.buslogic.client.PlaceBidClient.main(PlaceBidClient.java:24)Bid Successful, BidId Received is:393

A free site for sports funs: http://www.woop.gr/

This is a new free online Greek sports channel covering both national and international soccer, basketball matches, motorcycle racing events and so on. Since Eurosport channel now asks for subscription and payments, this could be an interesting alternative, in case your spouse insists on watching that boring soap opera. Most text is in Greek, but you can find your way around. Try clicking on "Live" links, in order to find something interesting to watch. So, this is it: http://www.woop.gr/
Finally, if you would like to try other online tv channels there is http://2onlinetv.com.