XPath slow

I’m working on a SOA project that handles a lot of XML messages.
Every message that goes through the system will be logged.
To make it uniform all messages are transformed to SOAP messages,
and when receiving SOAP messages we need to get the important fields
from a message in order to log these.

I used the JAMon tool to monitor the performance of handling these messages, and it turned out that the parsing of XML with XPath and Java 5 took (relatively) much time. Parsing a message took 19ms on average. And we already cached the XPathExpression.
It’s a very simple XML message with 1 root element and 10 child elements.
I needed to extract the value of 8 of these elements.

I tried to rebuild the XPath expression with different methods for extracting fields from an XML document with the following results:
Traversing with DOM: 21 times as fast
Using JDOM: 17 times as fast
Using indexOf string manipulation: 881 times as fast!

So using indexOf to extract fields in XML documents can be much faster!

Java StringTokenizer

Today I discovered that StringTokenizer is considered a legacy class by Sun. It’s retained for compatibility reasons although its use is discouraged in new code. Sun recommends the use of the split method in the String class or using the java.util.regex package instead.

By using the split method you lose the option to include the seperator-character in the split set. For parsing EDI I wanted to split an EDI segment on seperator characters + or : but I want to make sure that the seperator characters are included in the split set, so I can check if the right seperator-character was used in the original file. With StringTokenizer this can easily be done by calling java.util.StringTokenizer tokenizer=new java.util.StringTokenizer (segment,”+:”,true);

Return a file with a Servlet

Here is some sample java code.
This code returns a file from a Servlet:

package com.javaoracleblog.servlets;
import java.io.*; import javax.servlet.*; import javax.servlet.http.*;
/**  * Servlet to download text to a file.  *  * @author Edwin Korsten  * @version 1.0  */ public class Download extends HttpServlet {
 /**   * Return the contents of the requested file.   */  public void doGet(HttpServletRequest request, HttpServletResponse response)    throws IOException, ServletException {   PrintWriter out = null;   try {
   String fileName = "test.txt";    response.setContentType("application/x-msdownload");    response.addHeader("Content-disposition", "attachment; filename=\""      + fileName + "\"");    out = response.getWriter();    out.write("This is the contents of the file");   } catch (Exception e) {    if (out == null) {     response.setContentType("text/html");     out = response.getWriter();    }    out.write("”      + “Error trying to get file : “);    out.write(e.toString());    out.write(”");   }   out.close();  }
 public void doPost(HttpServletRequest request, HttpServletResponse response)    throws IOException, ServletException {   doGet(request, response);  }
}

Creating CBO statistics in Oracle

Since I am using the Cost Based Optimizer in Oracle (Oracle 7.3), I created the statistics needed for the optimizer with the command ‘ANALYZE TABLE’.I recently found out that since Oracle 7.4 this is not the optimal statement for creating statistics.

In Oracle 7.3.4 and Oracle 8.0 you should use the DBMS_UTILITY.ANALYZE_SCHEMA command.

From 8i you should use the DBMS_STATS.GATHER_SCHEMA_STATS command.

Analyze table

The analyze table can be used to create statistics for 1 table, index or cluster.

Syntax:

ANALYZE table tableName {compute|estimate|delete) statistics  options

ANALYZE table indexName {compute|estimate|delete) statistics  options

ANALYZE cluster clusterName {compute|estimate|delete) statistics options

Code examples   ANALYZE table scott compute statistics;  ANALYZE table scott estimate statistics sample 25 percent;  ANALYZE table scott estimate statistics sample 1000 rows;  analyze index sc_idx compute statistics;  analyze index sc_idx validate structure;

DBMS_UTILITY.ANALYZE_SCHEMA

With DBMS_UTILITY.ANALYZE_SCHEMA you can gather all the statistics for
all the tables,
clusters and indexes of a schema.

Code examples  exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');  exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows => 1000);  exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent => 25);  exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');

Note: It’s also possible to analyze the whole database with the
DBMS_UTILITY.ANALYZE_DATABASE(’COMPUTE’); command.

 

DBMS_STATS.GATHER_SCHEMA_STATS

From Oracle 8i the DBMS_STATS package is the preferred method
Oracle list a number of benefits to using it including parallel
execution, long term storage of statistics
and transfer of statistics between servers. Once again, it follows a
similar format to the other methods:

Syntax:

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname,estimate_percent, block_sample
, method_opt,degree,granularity,cascade,stattab, statid,options,statown
,no_invalidate, gather_temp,gather_fixed);

Code examples:   exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);  exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);  EXEC DBMS_STATS.gather_schema_stats(ownname => 'SCOTT', estimate_percent => 25);  EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');  EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');   exec DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');

Note: It’s also possible to gather statistics for the whole database
with the
DBMS_STATS.gather_database_stats; command.

Transfering statistics between database.

It can be very handy to use production statistics on your development
database,
so that you can forecast the optimizer behavior.

You can do this the following way:

1. Create the statistics table.

exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>’SCHEMA_NAME’
,stat_tab => ‘STATS_TABLE’
, tblspace => ‘STATS_TABLESPACE’);
Example:

exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>’SYSTEM’,stat_tab =>
‘STATS_TABLE’);

2. Export statistics to statistics table

EXEC
DBMS_STATS.EXPORT_SCHEMA_STATS(’ORIGINAL_SCHEMA’
,’STATS_TABLE’,NULL,’SYSTEM’);

3. Import statistics into the data dictionary.

exec
DBMS_STATS.IMPORT_SCHEMA_STATS(’NEW_SCHEMA’,’STATS_TABLE’,NULL,’SYSTEM’);

4. Drop the statistics table.

exec DBMS_STATS.DROP_STAT_TABLE(’SYSTEM’,’STATS_TABLE’);

Create excel from JDBC resultset

Here is some simple code to transform a JDBC resulset to an excel-sheet.
You can also use a tool like Apache POI, but this one is easy and simple.

package com.javaoracleblog.servlets;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager;
import java.sql.ResultSet; import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.math.BigDecimal;
public class ExcelWriter {
 protected String excelXmlHeader() {   return ("/n”>http://www.w3.org/TR/REC-html40”>n”     + “    n”     + ” n”     + “n”     + ” n”     + ”
“);
  for (int j = 0; j < columnNames.length; j++) {    result.append("
” + columnNames[j] + “

“);

 }
 public static void main(String[] args) {   try {    String driverName = "oracle.jdbc.driver.OracleDriver";    Class.forName(driverName);
   Connection conn;    String url = "jdbc:oracle:thin:@orakt02:1521:aridev01";    conn = DriverManager.getConnection(url, "ari", "ari");    Statement stmt = conn.createStatement();    ResultSet rs = stmt.executeQuery("SELECT * FROM ari_config");
   PrintWriter out;    try {     out = new PrintWriter(new OutputStreamWriter(       new FileOutputStream("c:/test.xls")));     ExcelWriter ew = new ExcelWriter();     ew.writeResultset(out, rs);     out.close();
   } catch (FileNotFoundException e) {     // TODO Auto-generated catch block     e.printStackTrace();    }
  } catch (SQLException e) {    // TODO Auto-generated catch block    e.printStackTrace();   } catch (ClassNotFoundException e) {    // TODO Auto-generated catch block    e.printStackTrace();   }
 }
}