All entries for Wednesday 22 November 2006

ORM tools

Writing about web page /java/entry/java_connection_pool/

Writing about an entry you don't have permission to view

Object Relational Mapping (ORM) Tools provide a slick way of persisting objects (data) into database. It has a great impact on Java applications. It reduces the need for developers to write native SQL. The developers can easily let the tools generated complicated SQL. To achieve there program efficiency, developer tend to join several objects, without realizing it will generated a huge SQL require multiple table joins. For DBAs, it means they need to improve performance tuning skills. Oracle optimizer might choose inferior execution plan for a big query.

As a DBA, aware of the following point:

• Using native SQL in corner cases 90/10. ORM tools is good for manipulate data. For batch loading, updating, deleting, it is far better to use native SQL. For instance, to display all information of several parents and their children, a Java application is likely to loop over every parent to get his children. This way will generate a lot of hit to database. With native SQL, you can easily get this information in one single SQL.
  Some tools provide semi-native portable SQL, for instance, hibernate has HQL, EJB has EJB-QL, you might encourage developer to use them as well.

• Use lazy loading to avoid generated tons of SQL. Suppose a parent has many children, every child has its own children. If you just want to get information fom a child, with eager loading, this child will automatically load its children. This can leads to generate several queries.

• break down one complicated big SQL into two or more SQLs.

Hongfeng Sun : 22 Nov 2006 22:08 |  Tags: Java Oracle |  Comments (0) |  Close comments |  Report a problem

SQL*Net more data from client

A while ago, OEM report some alert messages to us:

Wait class "Network" was consuming significant database time.  
Wait event "SQL*Net more data from client" in wait class "Network" 
     was consuming significant database time. 

We also see a 30ms slower to render our web pages.

According to Oracle doc,

SQL*Net more data from client
The server is performing another send to the client. The previous operation was also a send to the client.

It is not of much help.

The OEM gives a more clear indication:

Investigate the cause for high “SQL*Net more data from client” waits in Module “JDBC Thin Client”.

It is telling us that something is wrong with the JDBC drivers.

Chris May find a good article here

The cause of this event was a sequence of parse calls that passed excessively long SQL text strings through SQL*Net from the client to the server (instead of using stored procedure calls to accomplish the same thing). The long SQL text strings wouldn’t fit into a single SQL*Net packet, so the Oracle kernel spent a considerable amount of time awaiting second and subsequent SQL*Net packets during parse calls.

Hibernate, the ORM tools used by our application, generates long SQLs. Instead of select * from table, it use select col_1, col_2 from table.

After upgrading to latest JDBC driver, the problem fixed.

Hongfeng Sun : 22 Nov 2006 11:39 |  Tags: Java Oracle Oracle-Tune |  Comments (0) |  Close comments |  Report a problem