Comments on Oracle Performance Survival Guide: A Systematic Approach to Database Optimization, by Guy Harrison

On the bright side, this is one of the easiest review for me to write, since there are so many readers of the several editions of the book, that I shall mainly copy and paste  some lines of reviews I agree to. Thus, I quote: " This book is an encyclopedic overview of all aspects of Oracle performance. Mr. Harrison takes a layered approach, starting at the top with application and data model design where the focus is minimizing the demand for database resources. He then moves down into database code internals, where the focus is maximizing concurrency through reduction of lock, latch, and mutex contention. The next step is to optimize memory usage to minimize the need for physical IO. Finally, he moves to the bottom layer, where the focus is on optimizing physical IO at the disk layer.

Each of these layers is worthy of its own book, so to combine all these topics in a single book is an ambitious goal. Indeed, experienced readers will often want a bit more detail, or wonder why their favorite optimization was not mentioned. However, Mr. Harrison strikes a very good balance between depth and coverage. He also provides a very useful bibliography, including the Oracle documentation, books, and Internet sites and blogs.

For each chapter, Mr. Harrison provides extraordinarily clear, concise, and helpful introductions and summaries. He also uses boxed borders to highlights particularly important points within the text. One can learn a great deal by simply reading these summaries and boxed items, and these can also be used to help the reader find relevant sections, which is especially valuable in a book of this length.

This book is not the last word on SQL tuning, optimizer internals, Oracle troubleshooting, the SGA, nor latch contention. However, its coherent approach, useful summaries and highlights, and efficient organization, make it a valuable and essential guide to anyone wishing to expand their Oracle performance skill set.

The target audience includes both application developers and DBAs. It covers 10g, 11g, 11gR2, with context from 8i and 9i ." The latter is true for the newest edition of course! Furthermore, the full source code is available, as well as an errata page on the author's site.

On the dark side, there are extensive sections of the text promoting the products Mr. Harrison and his company create, which although humorously introduced ("Full disclosure..."), are a waste of time to read, for open source funs or anyone who is not interested in buying the software.

All in all, the book is highly recommended and I am looking forward to reading each new edition. Congratulations to the author. Well done, indeed!

Comments on Oracle PL/SQL by Example (4th Edition) by Benjamin Rosenzweig, Elena Silvestrova Rakhimov

On the bright side, the text is easy to follow and understand with only a few spelling errors. It follows a structured approach with worked out examples to teach the basics to the beginner, offering many clarifications and new methods also useful to the intermediate developer in the same time. For example, the method for timing the execution of pl sql code block for performance comparison is original, not mentioned in the oracle university classes notes. The db schema is small and simple, about instructors and students  attending the classes taught. In addition, there are solutions at the back of the book for the unsolved lab exercises and projects.
On the dark side, there is little for 11g db version, less than a handful of pages. Furthermore, some scripts about using the RESTRICT_REFERENCES pragma instruction in your pl sql code did not run in the current 11g db version. Obtaining the errors page and the source code scripts was something of an adventure,  since there was nothing on the publisher's, but  everything on the informIT site. The companion Web site is located at: http://www.informit.com/title/9780137144228
The last chapters of the book about web development via pl sql is mostly targeted to forms developers, discussing about the currently obsolete Oracle Application Server.
All in all, the book is suitable for those who would like to learn pl sql, or old developers who need brushing up their memory, but find that the fees for the oracle university classes are exorbitant, i.e. more than 1080 € for less than 40 hours; in a country where a mean hourly rate for a programmer is 10 € gross!

Migrating an EJB 3 application to the new JDeveloper version

This new post discusses several exceptions which have occurred because of migration to the current version of JDev. As far as I can remember the application was working well with the previous version. It seems that code rots!
The first has to do with populating a primary key field with a sequence in an entity EJB:

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "BID_SEQUENCE" )
    @SequenceGenerator(name = "BID_SEQUENCE", sequenceName = "BID_SEQUENCE",
    allocationSize = 10)
       
If you get an:

 Exception Description: The sequence named [YOUR_SEQUENCE_NAME] is setup incorrectly. Its increment does not match its pre-allocation size.

 A way to avoid the exception is to  use 100 as a start value in sql create sequence statement. That will resolve the problem: by default the start value is 1, when Eclipselink attempts to use the first allocated sequence value it's negative 1 - 100 + 1, that causes the exception.

The second is more subtle to resolve, as the error message does not appear at once. If you get a mysterious error about a missing table which does not belong to the application db schema:

      Eclipse Persistence Services - 2.1.3.v20110304-r9073):
      org.eclipse.persistence.exceptions.DatabaseException Internal Exception:
      java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
      Error Code: 942 Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
      bind => [50, SEQ_GEN] Query: DataModifyQuery
      (sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")

a way to get rid of the exception is editing the persistense.xml as follows:


<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
             version="1.0">
  <persistence-unit name="Model">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>jdbc/ActionBazaarDS</jta-data-source>
    <class>actionbazaar.persistence.Category</class>
    <class>actionbazaar.persistence.BillingInfo</class>
    <class>actionbazaar.persistence.Order</class>
    <class>actionbazaar.persistence.User</class>
    <class>actionbazaar.persistence.Bid</class>
    <class>actionbazaar.persistence.Bidder</class>
    <class>actionbazaar.persistence.Seller</class>
    <class>actionbazaar.persistence.Item</class>
    <class>actionbazaar.persistence.ContactInfo</class>
    <class>actionbazaar.persistence.ShippingInfo</class>
    <properties>
      <property name="eclipselink.target-server" value="WebLogic_10"/>
      <property name="javax.persistence.jtaDataSource"
                value="jdbc/ActionBazaarDS"/>
      <property name="eclipselink.target-database" value="Oracle11"/>
      <!--Addon in case of
      Eclipse Persistence Services - 2.1.3.v20110304-r9073):
      org.eclipse.persistence.exceptions.DatabaseException Internal Exception:
      java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
      Error Code: 942 Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
      bind => [50, SEQ_GEN] Query: DataModifyQuery
      (sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")>
      -->
      <property name="eclipselink.ddl-generation" value="create-tables"/>
    </properties>
  </persistence-unit>
</persistence>

Thus, the new SEQUENCE table will be automatically created and the application will at last commit the insert successfully.
The morale of the story is that frequent migrations due to upgrades can cause trouble. Perhaps, applying gradual patches instead, i.e. via the jdev online extensions way and waiting for a new full release would be safer. Otherwise you might spend hours searching about solutions on the internet, experimenting with each one and certainly overheating the computer. Always make sure you are in a well ventilated room, with full air conditioning when trying to migrate, or else high temperatures will cause your code to decay so much faster! Certain laptops such as Hewlett Packard Pavilion, overheat and power off immediately without prompting for save, very frequently at warm climates. After all, you don't want your code to go rot, do you?