Search

OakieTags

Who's online

There are currently 0 users and 21 guests online.

Recent comments

Affiliations

performance

Oracle Database Time Model Viewer in Excel 3

March 3, 2011 (Updated March 5, 2011) (Back to the Previous Post in the Series) (Forward to the Next Post in the Series) In the previous articles of this series we started building a solution in Microsoft Excel that duplicates some of the functionality in one of my programs – the Oracle Database Time Model viewer.  So far, [...]

Expert Oracle Exadata book – Alpha chapters available for purchase!

Hi,

Apress has made the draft versions of our Expert Oracle Exadata book available for purchase.

How this works is:

  1. You purchase the “alpha” version of the Expert Oracle Exadata book
  2. You get the access to draft/alpha PDF versions of some chapters now!
  3. As more chapters will be added and existing ones updated, you’ll receive an email and you can download these too
  4. You will get a PDF copy of the final book once it’s out!

This is an awesome deal if you can’t wait until the final launch and want to get ahead of the curve with your Exadata skills ;-)

Buy the alpha version of our Expert Oracle Exadata book from Apress here!

If you haven’t heard about this book earlier – I’m one of the 3 authors, writing it together with Kerry Osborne and Randy Johnson from Enkitec and our official tech reviewer is no other than THE Kevin Closson and we are also getting some (unofficial) feedback from Oracle database junkie Arup Nanda.

So this book will absolutely rock and if you want a piece of it now, order the alpha book above!

P.S. This hopefully also explains why I’ve been so quiet with my blogging lately – can’t write a book and do many other things at the same time… (at least if you want to do it well…)

Share

Oracle Database Time Model Viewer in Excel 2

March 1, 2011 (Updated March 2, 2011) (Back to the Previous Post in the Series) (Forward to the Next Post in the Series) In the previous blog article in this series, we examined the output generated by one of my programs.  Most of that output was centered on the analysis of the time model statistics in Oracle Database [...]

Advanced Oracle Troubleshooting v2.0 Online Deep Dives in April and May 2011

Due to a lot interest I’m going to do another run of my Advanced Oracle Troubleshooting v2.0 Online Deep Dive seminars in April and May (initially I had planned to do it no earlier than Sep/Oct…)

Check the dates & additional info out here:

P.S. People who already attended the AOT2 seminars last year – I will schedule the follow-up Q&A sessions in mid-March!

Share

Oracle Database Time Model Viewer in Excel 1

February 28, 2011 (Forward to the Next Post in the Series) Previously, I had written a couple of blog articles that showed how to build a reasonably usable Oracle Database Time Model Viewer using nothing more than a text file (containing a VBS script) and a dynamically generated web page that is displayed on a Windows [...]

Distinctly Odd

I recently got involved with a performance investigation for an Oracle 9.2 database. The process of investigation threw up some interesting information for me regarding the accuracy of statistics collection in Oracle. It also highlights how different defaults in different versions of Oracle can lead to remarkably different statistics and hence execution plans.  Finally, it [...]

Enabling constraint in parallel

Recently I did some tuning of data generation scripts, which purpose is to build large amount of representative data for application testing. Direct-path inserts are in use and as a prerequisite all constraints and indexes on target tables are disabled before the load and are enabled after it. Since I wanted to utilize available resources on the machine for that task, almost each step uses parallel execution. Well, kind of almost, because enabling constraints didn’t run in parallel, although I’ve politely asked Oracle to do so. I’ll explain here why it didn’t work.

SQL Performance Problem, AWR Reports Query is #1 in Elapsed Time, #2 in CPU Time – How Would You Help?

February 6, 2011 (Updated February 25, 2011) I occasionally read various forums on the Internet including a couple of Usenet groups (comp.databases.oracle.server, comp.databases.oracle.misc), OTN forums (Database – General, SQL and PL/SQL), Oracle-L, AskTom, and a handful of other forums.  I don’t participate on all of the forums, but I try to help when possible on [...]

Getting up and running with Universal Connection Pool – Take 2

In yesterday’s post (which is actually didn’t want to post that day) I wrote about the Universal Connection Pool feature. You should be able to get started with the information I gave you, but it didn’t include any hints on how to have a look under the covers of UCP. This can be changed …Oracle includes very fine-grained logging information with UCP, but experiment show that you have to either use log level FINE or FINEST to get to the real information of what’s going on.

LOGGING

Tomcat uses the log4j framework to define its own logging, as shown in the catalina.{bat,sh} file:

set LOGGING_CONFIG=-Djava.util.logging.config.file=”%CATALINA_BASE%\conf\logging.properties”

One thing you possibly don’t want to do is to include your own log4j configuration there-any change to your application’s file requires a restart of tomcat. Think of a production environment and then it becomes clear why such an approach is neither desirable nor practical.

Instead, you can put a file called logging.properties into the src directory within your application. In it you define logging to your heart’s delight. I have taken the following from the documentation and Pas Apicella’s blog. It really opens the floodgates for very detailed logging, so don’t use this in any other than a development environment…

handlers = org.apache.juli.FileHandler, java.util.logging.ConsoleHandler

org.apache.juli.FileHandler.level = ALL
org.apache.juli.FileHandler.directory = ${catalina.base}/logs
org.apache.juli.FileHandler.prefix = ucp.
org.apache.juli.FileHandler.formatter = oracle.ucp.util.logging.UCPFormatter

java.util.logging.FileHandler.level = WARNING

.level = FINEST

# FCF classes
oracle.ucp.common.FailoverEventHandlerThreadBase.level = ALL

oracle.ucp.jdbc.oracle.ONSDatabaseFailoverEvent.level = ALL
oracle.ucp.jdbc.oracle.ONSRuntimeLBEventHandlerThread.level = ALL
oracle.ucp.jdbc.oracle.ONSOracleRuntimeLBEventSubscriber.level = ALL
oracle.ucp.jdbc.oracle.ONSRuntimeLoadBalancingEvent.level = ALL
oracle.ucp.jdbc.oracle.ONSOracleFailoverEventSubscriber.level = ALL
oracle.ucp.jdbc.oracle.ONSDatabaseEventHandlerThread.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverEvent.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverEventSubscriber.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverEventNotification.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverHandler.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverablePooledConnection.level = ALL

oracle.ucp.jdbc.oracle.OraclePooledConnectionConnectionPool.level = ALL
oracle.ucp.jdbc.oracle.OracleConnectionConnectionPool.level = ALL
oracle.ucp.jdbc.oracle.OracleXAConnectionConnectionPool.level = ALL
oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.level = ALL

oracle.ucp.jdbc.oracle.OracleDatabaseInstanceInfo.level = ALL
oracle.ucp.jdbc.oracle.OracleDatabaseInstanceInfoList.level = ALL

# RCLB classes
oracle.ucp.jdbc.oracle.ONSRuntimeLBEventHandlerThread.level = ALL
oracle.ucp.jdbc.oracle.ONSOracleRuntimeLBEventSubscriber.level = ALL
oracle.ucp.jdbc.oracle.OracleRuntimeLoadBalancingHandler.level = ALL
oracle.ucp.jdbc.oracle.ONSRuntimeLoadBalancingEvent.level = ALL

Now when you redeploy your application, a new file ucp.timestamp will be created in %CATALINA_HOME%\logs and list all the beauty of what’s going on. The log file is now very verbose though.

FAST CONNECTION FAILOVER

The previous source code didn’t take full potential of the fact that the fastConnectionFailover functionality was enabled. For the following to work you might want to disable the validateConnectionOnBorrow setting in META-INF/context.xml. In fact, we’d like to validate the connection when we get it from the pool ourselves. The code of the Hello.java servlet has changed as follows:

// time to get UCP to start
writer.println("

UCP

"); Connection conn = null; oracle.ucp.jdbc.PoolDataSource ds = null; try { Context ctx = new InitialContext(); Context envContext = (Context) ctx.lookup("java:/comp/env"); ds = (oracle.ucp.jdbc.PoolDataSource) envContext.lookup ("jdbc/UCPPool"); writer.println("Got the datasource - "); writer.println("FCF enabled? " + ds.getFastConnectionFailoverEnabled()); conn = ds.getConnection(); } catch (Exception e) { try { // here's where the FCF comes in if (conn == null || !((ValidConnection) conn).isValid()) { writer.println("

Have to retry connection (" + e.getMessage() + ")

"); OracleJDBCConnectionPoolStatistics stats = (OracleJDBCConnectionPoolStatistics) ds.getStatistics(); writer.println("Pool stats: " + stats.getFCFProcessingInfo()); conn.close(); } else { writer.println("Unknown exception: " + e); } } catch (SQLException sqle) { e.printStackTrace(); return; } } try { writer.println("

Connected to Oracle intance

"); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select 'Hello World from '||sys_context('userenv','instance_name') from dual"); while (rs.next()) { writer.println("

" + rs.getString(1) + "

");
}

rs.close();
stmt.close();
conn.close();

} catch (Exception e) {
writer.println("

" + e + "

");
}

writer.println("");
writer.println("");

}

I will probably attach the code to the post as the layout is pretty broken.

IS THE POOL INTELLIGENT?

To see if the runtime load balancing feature works at all I shut down the second of my two database instances before restarting the application. I expect all sessions to be on the first instance (to be expected as there is not a second one). Is that so?

SQL> ed
Wrote file afiedt.buf

 1  select count(inst_id),inst_id,
 2  status from gv$session where username='SCOTT'
 3* group by inst_id, status
SQL> /
COUNT(INST_ID)    INST_ID STATUS
-------------- ---------- --------
58          1 INACTIVE

Now what happens when I start the second instance?

SQL> !srvctl start instance -d lnrut1d -i LNRUT1D_2
/

SQL>
COUNT(INST_ID)    INST_ID STATUS
-------------- ---------- --------
 69          1 INACTIVE

SQL> /

COUNT(INST_ID)    INST_ID STATUS
-------------- ---------- --------
 57          1 INACTIVE
 19          2 INACTIVE

SQL> /

COUNT(INST_ID)    INST_ID STATUS
-------------- ---------- --------
 25          1 INACTIVE
 45          2 INACTIVE

That’s neat-the second instance catches up. I eventually ended up near-equilibrium. The inverse is also true. Consider two instances up as shown here:

SQL> select count(inst_id),inst_id,
 2  status from gv$session where username='SCOTT'
 3  group by inst_id, status
 4  /

COUNT(INST_ID)    INST_ID STATUS
-------------- ---------- --------
 21          1 INACTIVE
 26          2 INACTIVE
 1          2 ACTIVE
 2          1 ACTIVE

SQL> !srvctl stop instance -d lnrut1d -i LNRUT1D_2 -o abort

SQL> /

COUNT(INST_ID)    INST_ID STATUS
-------------- ---------- --------
 36          1 INACTIVE

Now when I shut the second instance down, the number of sessions on the first node goes up:


SQL> /

COUNT(INST_ID)    INST_ID STATUS
-------------- ---------- --------
 65          1 INACTIVE

I like this!

Getting up and running with Universal Connection Pool

Oracle’s next generation connection pooling solution, Universal Connection Pool, can be a bit tricky to set up. This is especially true when a JNDI data source is to be used-most example don’t assume such a scenario. A lot of information is out there on the net, but no one seems to have given the full picture. During the research for chapter 11 of “Pro Oracle Database 11g RAC on Linux” I learned this the hard way. Since the book has been published, a few minor changes changes have been made to the software I used at the time, and those merit an update. Please note that this article’s emphasis is to get  this example running-it is by no means meant to be secure enough for a production release! You need to harden  the setup considerably for production, but it serves well for demonstration purposes (only).

THE SETUP

I have used a four node 11.2.0.2 RAC system as the source for my data. A 2 node cluster database with service “TAFTEST” runs on nodes 1 and 2. It’s administrator-managed and the service has both nodes set aside as “preferred” nodes. The database nodes run Oracle Enterprise Linux 5.564bit with RAC 11.2.0.2. For the sake of simplicity, I used my Windows laptop to host the Tomcat instance, which is now updated to version 6.0.30. I am using apache Ant to build the application. The current stable ant build is 1.8.2. My JDK is also upgraded to the latest and greatest, version 1.6.0_23. I am using the 32bit 11.2.0.2 client package to supply me with ons.jar, ojdbc6.jar and ucp.jar.ORACLE CLIENT

Part of this excercise is to demonstrate FCF and FAN events, which means we need an Oracle client for the remote ONS configuration (please refer to chapter 11 of the RAC book for a detailed description of local vs remote ONS configurations). I downloaded the 11.2.0.2 32bit client from support.oracle.com for Windows and installed it to c:\oracle\product\11.2.0\client_1, chosing the administrator option in Oracle Universal Installer.

TOMCAT

Start by downloading Tomcat for your platform-I have successfully tested Tomcat on Linux and Windows. I deployed apache-tomcat-6.0.30 to c:\ for this test. Once it’s unzipped, copy the necessary JAR files from the Oracle client installation into %TOMCAT_HOME%\lib. These are ojdbc6.jar, ons.jar and ucp.jar. Next, you should set a few environment variables. To keep things simple, I edited  %tomcat_home%\bin\startup.sh and added these:

  • set JAVA_HOME=c:\program files\Java\jdk1.6.0_23
  • set JAVA_OPTS=-Doracle.ons.oraclehome=c:\oracle\product\11.2.0\client_1

I’m also interested in the final content of %JAVA_OPTS%, so I modified catalina.bat as well and added this line into the section below line 164:

echo Using JAVA_OPTS: “%JAVA_OPTS%”

Finally we need to add a user with access to the tomcat manager application. Edit %tomcat_home%\conf\tomcat-users.xml and create and entry similar to this one:


This is one of the major differences-starting with tomcat 6.0.30, the previous “manager” role has been split into the ones shown above to protect from xref attacks. It took me a while to discover the reason for all the http 403 errors I got when I tried to deploy my application… You’d obviously use a strong password here!

This concludes the TOMCAT setup.

ANT

Ant is a build tool used for deployment and compiling the sample application I am going to adapt. Simply download the zip file (version 1.8.2 was current when I wrote this) and deploy it somewhere conveniently. Again, a few environment variables are helpful which I usually put into a file called sp.bat:

@echo off
set ant_home=c:\apache-ant-1.8.2
set path=%ant_home%\bin;%path%
set java_home=c:\program files\Java\jdk1.6.0_23
set path=%java_home%\bin;%path%

This makes building the application a lot easier. Just change into the application directory and enter sp to get set up.

BUILDING AN APPLICATION

My earliest contact with Tomcat was a long time ago with version 3 and since then I remember the well written documentation. The “docs” application, usually accessible as http://localhost:8080/docs has a section about the first application. It’s highly recommended to read it: http://localhost:8080/docs/appdev/index.html.

To get started, I copied the “sample” directory from %tomcat_home%\webapps\docs\appdev to c:\temp and started adapting it. First of all my sp.bat script is copied in there. With a command prompt I changed into sample and edited the build.xml file. The first major section to go over is starting in line 129. I changed it to read like this:

 
 
 
 
 
 
 
 
 
 
 
 

The properties to change/add are app.name, app.version, catalina.home, manager.username and manager.password. The manager.* properties will come in very handy later as they allow us to automatically deploy the compiled/changed application.

With all this done, try to compile the application as it is:

C:\TEMP\sample>ant compile
Buildfile: C:\TEMP\sample\build.xml
Trying to override old definition of datatype resources

prepare:

compile:
 [javac] C:\TEMP\sample\build.xml:301: warning: 'includeantruntime' was not set, defaulting to build.sysclasspath=last; set to false for repeatable builds

BUILD SUCCESSFUL
Total time: 0 seconds

C:\TEMP\sample>

That’s it-the application has been created. You can now start tomcat and try to deploy the app. Open a cmd window, change to %tomcat_home%\bin and execute startup.bat. This will start tomcat-the new window shows any problems it might have when deploying application. This window is very useful for example when it comes to troubleshoot incorrect XML configuration files.

Now the next step is to use the ant target “install” to test the installation of the web archive. I changed the install target slightly in the way that I depend on the “dist” target to be completed before deployment to the tomcat server. My modified working install target is defined as this in build.xml:

 

 
 

Try invoking it as shown in the example below:

C:\TEMP\sample>ant install
Buildfile: C:\TEMP\sample\build.xml
Trying to override old definition of datatype resources

prepare:

compile:
 [javac] C:\TEMP\sample\build.xml:301: warning: 'includeantruntime' was not set, defaulting to build.sysclasspath=las
t; set to false for repeatable builds

javadoc:
 [javadoc] Generating Javadoc
 [javadoc] Javadoc execution
 [javadoc] Loading source files for package mypackage...
 [javadoc] Constructing Javadoc information...
 [javadoc] Standard Doclet version 1.6.0_23
 [javadoc] Building tree for all the packages and classes...
 [javadoc] Building index for all the packages and classes...
 [javadoc] Building index for all classes...

dist:

install:
 [deploy] OK - Deployed application at context path /ucp

BUILD SUCCESSFUL
Total time: 1 second

C:\TEMP\sample>

This operation succeeded. You should also see a line in your tomcat window:

INFO: Deploying web application archive ucp.war

When pointing your browser to http://localhost:8080/ucp/ you should be greeted by the familiar tomcat sample application.

ADDING THE DATASOURCE

So far this hasn’t been groundbreaking at all. It’s only now that it’s getting more interesting: the JNDI data source needs to be defined and used in our code. Instead of messing around with resources and res-ref configuration in the global %tomcat_home%\conf directory it is advisable to add the context to the application.

Back in directory c:\temp\sample create a new directory web\META-INF. Inside META-INF you create a file “context.xml” which takes the JNDI data source definition:


 

It really is that simple to implement-if it only were equally simple to find how to do this… The next step is to modify the Hello.java Servlet to reference the JNDI data source. The code for the servlet is shown below-it’s basically the existing servlet code amended with the JNDI and JDBC relevant code. It actually does very little: after looking the JDNI name up it grabs a session from the pool and checks which instance it is currently connected to. It then releases all resources and exits.


/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package mypackage;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;
import javax.naming.*;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.Enumeration;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public final class Hello extends HttpServlet {

public void doGet(HttpServletRequest request,
 HttpServletResponse response)
 throws IOException, ServletException {

 response.setContentType("text/html");
 PrintWriter writer = response.getWriter();

 writer.println("");
 writer.println("");
 writer.println("Sample Application Servlet Page");
 writer.println("");
 writer.println("");

 writer.println("");
 writer.println("");
 writer.println("");
 writer.println("");
 writer.println("");
 writer.println("
"); writer.println(""); writer.println(""); writer.println("

Sample Application Servlet

"); writer.println("This is the output of a servlet that is part of"); writer.println("the Hello, World application."); writer.println("
"); // this is the UCP specific part! writer.println("

UCP

"); try { Context ctx = new InitialContext(); Context envContext = (Context) ctx.lookup("java:/comp/env"); javax.sql.DataSource ds = (javax.sql.DataSource) envContext.lookup ("jdbc/UCPPool"); writer.println("Got the datasource"); Connection conn = ds.getConnection(); writer.println("

Connected to an Oracle intance

"); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select 'Hello World from '||sys_context('userenv','instance_name') from dual"); while (rs.next()) { writer.println(rs.getString(1)); } rs.close(); stmt.close(); conn.close(); conn = null; } catch (Exception e) { writer.println("
" + e + "

");
}

writer.println("

");
writer.println("");
}
}

Done! Now let’s compile the code and deploy it to Tomcat before testing. The most common problem I get with the code is a JNDI error, stating the “jdbc/UCPPool” is not defined. This can happen in 2 cases:

  • You have a typo in the resource definition, in which case the context really doesn’t exist (it’s case sensitive)
  • You have non-compatible line breaks in the context.xml file. In this case I’d try having all the contents of the file in 1 line (use “J” in vi to join lines together)

VALIDATION

You should now see a number of sessions as user “scott” against the database-query gv$session for username “SCOTT” and you should see the result of your hard work.