Thank you for visiting! If you liked the site, please add a bookmark, else add a critical comment! Would you like to visit Greece? Traditional Greek flag This real estate site is only available in Greek! The holy mountain

Sunday 27 March 2011

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!












Monday 14 March 2011

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! 

Tuesday 8 February 2011

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?