Building a web application layer using Zend Framework

fishhookFladgeInternet and Web Development

Dec 13, 2013 (3 years and 8 months ago)

190 views

Issue 02/2012 (2) February
Building a web application
layer using Zend
Framework
Build something more useful than a simple application
Combining PHP with Java in the same
application using PHP/Java bridge
2 in 1 – make it easily
Benchmarking and optimizing PHP
Optimize our code from the very first moment
Making Friends with the Oracle Database
Increase your software performance
2
No 2/2012
www.phpsolmag.com
2
Editor in Chief:
Justyna Książek
justyna.ksiazek@software.com.pl
Junior Product Manager:
Maciej Mościcki
maciej.moscicki@software.com.pl
DTP:
studio@studiopoligraficzne.com
Publisher:
Software Press Sp. z o.o. SK
02-682 Warszawa, ul. Bokserska 1
http://www.phpsolmag.com
team@phpsolmag.com
Whilst every effort has been made
to ensure the high quality of the
magazine, the editors make no
warranty, express or implied,
concerning the results of content
usage.
All trade marks presented in the
magazine were used only for
informative purposes.
All rights to trade marks presented
in the magazine are reserved by the
companies which own them.
DISCLAIMER!
The techniques described in our
articles may only be used in private,
local networks. The editors hold
no responsibility for misuse of the
presented techniques or consequent
data loss.
cooperation@software.com.pl
P
erformance, performance, performance... Car engines, machines,
workers, computers... Nowadays from all of us/ from everything
performance is required. As you can assume this also applies to web
sites.
Someone who has written software before that has been put into
production use, had performance problems, and then been rejected
back to the developer to repair. It’s this situation that best inspires a
software developer to figure out things like How am I going to create
a reproducible test for the problem?, How am I going to measure the
problem when it happens again so that I can figure out how to change
my code? Have you had to deal with this problem before? If yes or to
prevent this kind of situation we suggest you to read Making Friends
with the Oracle Database article by Cary Millsap.
When the number of visitors increases and the amount of concurrent
users becomes bigger, we start having performance problems, and,
running against the clock, we try to fix it. We all know that emergency
solutions, usually, are not the best ones, so let‘s think about another
approach, let‘s learn some tips to optimize our code from the very first
moment. In the Benchmarking and optimizing PHP article you will find
out how to do it and how much work does that requires.
The fact that day after day more and more we sell and buy on
the internet results in the need to take a look at your traffic reports.
Chances are, organic search results (unpaid results) are one of your
top 3 revenue drivers. Among our e-commerce clients, organic search
drives 60%+ of all online revenue. That’s too big a channel to ignore.
Why you should not rely on the general opinion about SEO you will
learn in Building an SEO-ready e-commerce site article.
Java and PHP, PHP and Java. What would happen if these two
languages met? Could we combine their powers constructively? In the
Combining PHP with Java in the same application using PHP/Java
bridge article you will find out that trying to answer these questions
gave birth to the PHP/Java Bridge, which creates a communication
channel between these two entities. What can you do with this device?
Read to learn.
PHP Solutions Team
3
PRACTICE
18 Developing an MVC framework
Michael Gray
It is often the case that when learning a language such
as PHP - you can mix the PHP code with the HTML co-
de. While this does occasionally happen – this tends to
lend hand to the fact that, you get some very nasty; unti-
dy code. In order to reduce development time in future
projects it might be an idea into making a smaller, default
framework in which to work with.
20 Combining PHP with Java in the same
application using PHP/Java bridge
Octavia Andrea Anghell
As the two languages got stronger, they became highly
appreciated by dedicated developers, who began to ask:
What would happen if these two languages met? Could
we combine their powers constructively?
26 Building an SEO-ready e-commerce site
Ian Lurie
Take a look at your traffic reports. Chances are, ‘organic’
search results - unpaid results - are one of your top 3 re-
venue drivers. Among our e-commerce clients, organic
search drives 60%+ of all online revenue. That’s too big
a channel to ignore.
28 Making Friends with the Oracle Database
Cary Millsap
To many application developers, a database is just a da-
ta store with an API that they call when they need to
persist an object. It’s an abstraction that makes sense
from one perspective: in a world where you’re expected
to write dozens of new features every day in Java, PHP,
or C#, who has the time or the inclination to dive into
what’s going on deep inside the Oracle Database? As a
result of this abstraction, though, developers sometimes
inflict unintended performance horrors upon their custo-
mers. The good news is that you can avoid most of these
horrors simply by better understanding a bit more about
what’s going on inside the Oracle kernel.
CONTENTS
TOOLS
4 Building a web application layer using Zend
Framework – Part 2
Marco Mocellini
Second part but not the last one of a journey that will
lead us to build our application layer using Zend Frame-
work. In fact, we’ll build something more useful than a
simple application, that is an application framework.
BASICS
14 Benchmarking and optimizing PHP
Claudio Corlatti
PHP is a very fast programming language, even so,
benchmark and optimize our scripts is a very good idea.
Most programmers learn the importance of optimizing
the hard way. When the number of visitors increases
and the amount of concurrent users becomes bigger,
we start having performance problems, and, running
against the clock, we try to fix it. We all know that emer-
gency solutions, usually, are not the best ones, so let’s
think about another approach, let’s learn some tips to
optimize our code from the very first moment. Take it
easy, you will see that is not really a lot of work and it’s
going to save us time and, more important, our mental
health, in the future.
CONTENTS
No 2/20124
Tools
In this article we will see:
• Creation of a separate directory structure (
zf _
core
) to make our code reusable by any application.
• Global class
• Database tables
Directory structure
Very soon I realized that to make this application frame-
work reusable was necessary to revise the organization
of the folders.
So, some of the files that are specific for any site/ap-
plication reside in a folder under the document root,
while all the rest of application reside in a centralized
single folder called zf_core, which is the core of our ap-
plication framework
site/application structure
In the example, the document root of my
site/
application is my_site/web.
As you can see, the folder application remains outside
the document root for security reasons.
Building a web
application layer using
Zend Framework – Part 2
Building an application layer using Zend Framework to
facilitate the development of any kind of web application.
What you will learn...
• How to configure the environment for the site and for the cen-
tral core and how to use the Globals.php class;
What you should know...
• PHP5;
• OOP;
• basic knowledge about of MVC paradigm;
• Building a web application layer using Zend Framework – Part 1;
listing 1. Standard version of local index.php file (my_site/web/admin/index.php)
<?php
/**
* My new Zend Framework Project
*
* @author
* @version
*/
set_include_path('.' . PATH_SEPARATOR . '../../../zf_core/library'
. PATH_SEPARATOR . '../../library'
. PATH_SEPARATOR . '../../../zf_core/application/default/models/'
. PATH_SEPARATOR . get_include_path());

require
'../../../zf_core/application/bootstrap.php';
?>
Building a web application layer using Zend Framework – Part 2
www.phpsolmag.com
5
Is important to note that all the paths are relative,
and you must change the number of levels to rise
only if your document root is different from
my_site/
web
.
So, for example, if your document root is only
my_site
,
your index.php file become (Listing 2).
Core
The core contains all the main files of our project, ge-
neric and do not have any way to be customized as
needed to all the site. It is also important to have a sin-
gle core to maintain and upgrade.
The most important file is bootstrap.php. As you can
see, there are some references to Globals class, we will
see in detail in the next section (Listing 3).
In fact, it contains the config.ini file. Under
my_site/
web/admin
resides others specific folders and files
as:
• images folder
• css folder
• js folder
• .htaccess file (that redirects every request to the in-
dex.php file)
• index.php file
As you can see, the file is very essential,
set
include _ path
and than require the file boostrap.php
that is centralized and carries out the activity that was
originally in index.php
listing 2. New version of local index.php file (my_site/admin/index.php)
<?php
set_include_path('.' . PATH_SEPARATOR . '../../zf_core/library'
. PATH_SEPARATOR . '../library'
. PATH_SEPARATOR . '../../zf_core/application/default/models/'
. PATH_SEPARATOR . get_include_path());

require
'../../../zf_core/application/bootstrap.php';
?>
listing 3. Standard and centralized version of zf_core/application/bootstrap.php file
<?php
/**
* My new Zend Framework project
*
* @author Marco Mocellini
* @version 3.1
*/
// From version 1.8
require_once "Zend/Loader/Autoloader.php";
$autoloader = Zend_Loader_Autoloader::getInstance();
$autoloader->setFallbackAutoloader(true);
// Prepare the front controller.
$frontController = Zend_Controller_Front::getInstance();
// Set path for config.ini
Globals::setIniFile($local_ini_path);
// Default: 'development' (Change to 'production' parameter under production environment) in config.ini
$frontController->registerPlugin(
new
Custom_Controller_Plugin_Initializer(Globals::getConfig()->env-
>ambiente,$local_ini_path));
$frontController->registerPlugin(
new
Custom_Controller_Plugin_ViewSetup(), 98);
$frontController->setBaseUrl(Globals::getConfig()->path->baseurl);
// Dispatch the request using the front controller.
$frontController->dispatch();
?>
No 2/20126
Tools
Important is also the registration of the plugins Cus-
tom_Controller_Plugin_Initializer and Custom_Control-
ler_Plugin_ViewSetup, that we can se later.
GREAT ClAss: GloBAls.PHP
In our applications there are a series of actions that are
performed multiple times and at different times (for ex-
ample get config parameters, get connection to data-
base, get/set session informations).
In larger applications is very important to organize this
actions so that they are written only once and invoked
in the same way
setting up Globals.php
To be able to fetch many common items in the applcia-
tion from a single place, without repeating ourselves,
we will put it in a common places.
We need a place where we can insert items that are
related to applcation but not a model, view, controller or
module. So we can create zf_core/library/Globals.php
file. This become the second most important file of our
applications framework, behind bootstrap.
Globals.php is never instantiated. It is a static class in
that it only has static properties and method.
Building it this way has two clear advantages:
• We never have to check it’s already been instantiat-
ed and in the session somewhere. It’s included and
it’s available.
• It allows to use it as a factory for the various help-
ers we will nedd
Let’s look at same code:
GetDbConnection()
This is the method that creates or returns the instance
of the database connection.
There is a private property fora an instance of the
db.
First we check to see if we have already created
a connection to the database. If we have, just return it, if
not, proceed to connection (using static method Zend_
Db::factory). This is the listing. (Listing 4).
We don’t write the hostname, username and pass-
word directly in the method, but we retrieve these in-
formations from my_site/application/etc/config.ini file,
because, obviously, are specific data. To read these in-
formations, we use getConfig() method
listing 4. Extract of zf_core/library/Globals.php file – connection to database
<?php
static
public function getDbConnection () {

if
(self::$_db != null) {

return
self::$_db;
}
self::$_db = Zend_Db::factory(self::getConfig()->db->driver,

array
('host' => self::getConfig()->db->host,
'username' => self::getConfig()->db->username,
'password' => self::getConfig()->db->password,
'dbname' => self::getConfig()->db->dbname));
self::$_db->setFetchMode(Zend_Db::FETCH_OBJ);
Zend_Db_Table::setDefaultAdapter(self::$_db);

return
self::$_db;
}
listing 5. Extract of my_site/application/etc/config.ini file
with [db] section
[db]
driver = pdo_mysql
host = localhost
username = moce
password = skjett
dbname= zf_ipanel
prefix = zf_
Figure 1. New folders organization
Building a web application layer using Zend Framework – Part 2
www.phpsolmag.com
7
getConfig()
Globals.php is a way to store global and local configu-
ration items. Things like directory names, database cre-
dential and every specific parameters that is not con-
venient to hard-code into application.
For these information, Globals.php provides the
getConfig()
method.
First we check to see if we have already enhanced the
$_config
protected property. If we have, just return it, if
not, proceed to use Zend component
Zend_Config_Ini
that returns all the directives stores in config.ini file.
getSession()
To store persistent informations used to navigate, search
and other need , Zend Framework provides a special
component
Zend_Session_Namespace
In the Globals.php class we implement a method
named
getSession()
.
listing 6. Extract of zf_core/library/Globals.php file – get config parameters
<?php
static
public function getConfig() {

if
(self::$_config != null) {

return
self::$_config;
}
self::$_config =
new
Zend_Config_Ini(self::$config_ini, null);

return
self::$_config;
}
listing 7. Extract of zf_core/library/Globals.php file - get session object
<?php
static
public function getSession() {

if
(self::$_mySession != null) {

return
self::$_mySession;
}

Zend_Session::start();
self::$_mySession =
new
Zend_Session_Namespace(self::getConfig()->session->namespace);

return
self::$_mySession;
}
listing 8. Extract of zf_core/library/Globals.php file – get cache object
<?php
static
public function getCache() {

if
(self::$_cache != null) {

return
self::$_cache;
}
$cacheFrontendOptions =
array
(
// 'lifetime' => self::getConfig()->cache->lifetime,
'automatic_serialization' => true
);
$cacheBackendOptions =
array
(
// 'cache_dir' => self::getConfig()->cache->cache_dir
);
self::$_cache = Zend_Cache::factory('Core', 'File', $cacheFrontendOptions, $cacheBackendOptions);

return
self::$_cache;
}
No 2/20128
Tools
In this method, first we check to see
if we have already enhanced the
$_
mySession
protected property. If we
have, just return it, if not, proceed to
use
Zend_Session_Namespace
that re-
turns the object used for the session
variables.
getCache()
Outside of security, the most important
aspect of any web based applcation
is performance. On common strategy
to improve performance is caching.
Locky for us, this i san easy problem to solve using
Zend_Cache
.
One of the biggest benifits of
Zend_Cache
, outside of
enhancing the performance of your application is that
it can use multiple back end storage devices (File, Sql-
ite, MemCached)
In this method, first we check to see if we have al-
ready enhanced the
$_cache
protected property. If
we have, just return it, if not, proceed to use Zend_
Cache::factory that returns the object used for the
cache.
DATABAsE
In this article we start to view the organizaion of da-
tabase, including table of users, roles and resources
and menu that will be used to build the layer for the
authentication and access control list.
Every installation of application will have its data-
base.
Figure 3.
Figure 2. Database model
Figure 4.
Building a web application layer using Zend Framework – Part 2
www.phpsolmag.com
9
WHAT WIll WE sEE IN THE NEXT ARTIClE
In the next issue we will see in detail the functionality of front controller plugin Initializer.php and the authentication
process.
Figure 5.
listing 9a. The complete class zf_core/library/Globals.php
<?php
class
Globals
{
private
static
$config_ini = '../../application/etc/config.ini';
public
static
$permessi =
array
();
public
static
$hook =
array
();

/**
* Database
*
* @var Zend_Db
*/
private
static
$_db = null;
/**
* Config
*
* @var Zend_Config_Ini
*/
private
static
$_config = null;
/**
* Session
*
* @var Zend_Session
*/
private
static
$_mySession= null;
/**
* Cache
No 2/201210
Tools
listing 9b. The complete class zf_core/library/Globals.php
*
* @var Zend_Cache
*/
private
static
$_cache = null;

/**
* getDbConnection
*
* @return Zend_Db
*/

static
public function setIniFile ($path='')
{

if
($path)
self::$config_ini=$path.'/application/etc/config.ini';

}
/**
* getDbConnection
*
* @return Zend_Db
*/

static
public function getDbConnection ()
{

if
(self::$_db != null) {

return
self::$_db;
}
self::$_db = Zend_Db::factory(self::getConfig()->db->driver,

array
('host' => self::getConfig()->db->host,
'username' => self::getConfig()->db->username,
'password' => self::getConfig()->db->password,
'dbname' => self::getConfig()->db->dbname));
self::$_db->setFetchMode(Zend_Db::FETCH_OBJ);
Zend_Db_Table::setDefaultAdapter(self::$_db);

return
self::$_db;
}

/**
* getConfig
*
* @return Zend_Config_Ini
*/

static
public function getConfig()
{

if
(self::$_config != null) {

return
self::$_config;
}
self::$_config =
new
Zend_Config_Ini(self::$config_ini, null);

return
self::$_config;
}

/**
* getSession
*
* @return Zend_Session_Namespace
*/

static
public function getSession()
{

if
(self::$_mySession != null) {

return
self::$_mySession;
Building a web application layer using Zend Framework – Part 2
www.phpsolmag.com
11
listing 9c. The complete class zf_core/library/Globals.php
}

Zend_Session::start();
self::$_mySession =
new
Zend_Session_Namespace(self::getConfig()->session->namespace);

return
self::$_mySession;
}

/**
* getCache
*
* @return Zend_Cache
*/

static
public function getCache()
{

if
(self::$_cache != null) {

return
self::$_cache;
}

$cacheFrontendOptions =
array
(
// 'lifetime' => self::getConfig()->cache->lifetime,
'automatic_serialization' => true
);
$cacheBackendOptions =
array
(
// 'cache_dir' => self::getConfig()->cache->cache_dir
);

self::$_cache = Zend_Cache::factory('Core', 'File', $cacheFrontendOptions, $cacheBackendOptions);

return
self::$_cache;
}
}
listing 10a. Sql dump of top tables
--
-- Database: `zf_ipanel`
--
CREATE TABLE
IF
NOT EXISTS `zf_resources` (
`id_resource`
int
(11) NOT NULL AUTO_INCREMENT,
`module` varchar(255) NOT NULL,
`controller` varchar(255) NOT NULL,
PRIMARY KEY (`id_resource`),
UNIQUE KEY `module` (`module`,`controller`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8 COMMENT='Tabella delle risorse' AUTO_INCREMENT=23 ;
CREATE TABLE
IF
NOT EXISTS `zf_roles` (
`id_role`
int
(11) NOT NULL AUTO_INCREMENT,
`role` varchar(255) NOT NULL,
`description` varchar(255) NOT NULL,
`id_parent`
int
(11) NOT NULL,
`homepage` varchar(255) NOT NULL,
PRIMARY KEY (`id_role`),
KEY `id_parent` (`id_parent`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8 COMMENT='Ruoli degli utenti' AUTO_INCREMENT=8 ;
No 2/201212
Tools
listing 10b. Sql dump of top tables
CREATE TABLE
IF
NOT EXISTS `zf_roles_acl` (
`id_acl`
int
(11) NOT NULL AUTO_INCREMENT,
`table_foreign` varchar(255) NOT NULL,
`field_foreign` varchar(255) NOT NULL,
`descr_foreign` varchar(255) NOT NULL,
`label_foreign` varchar(255) NOT NULL,
`tree`
int
(11) NOT NULL,
`multiple`
int
(11) NOT NULL,
PRIMARY KEY (`id_acl`),
UNIQUE KEY `table_foreign` (`table_foreign`,`field_foreign`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8 AUTO_INCREMENT=7 ;
CREATE TABLE
IF
NOT EXISTS `zf_roles_mm_acl` (
`id_acl`
int
(11) NOT NULL,
`id_local`
int
(11) NOT NULL,
`id_foreign`
int
(11) NOT NULL,
UNIQUE KEY `id_acl` (`id_acl`,`id_local`,`id_foreign`),
KEY `id_local` (`id_local`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8;
CREATE TABLE
IF
NOT EXISTS `zf_roles_resources_mm` (
`id_mm`
int
(11) NOT NULL AUTO_INCREMENT,
`id_role`
int
(11) NOT NULL,
`id_resource`
int
(11) NOT NULL,
`full`
int
(11) NOT NULL,
`
list
`
int
(11) NOT NULL,
`
new
`
int
(11) NOT NULL,
`edit`
int
(11) NOT NULL,
`del`
int
(11) NOT NULL,
`moreactions` varchar(255) NOT NULL,
PRIMARY KEY (`id_mm`),
UNIQUE KEY `id_role_2` (`id_role`,`id_resource`),
KEY `id_role` (`id_role`),
KEY `id_resource` (`id_resource`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8 COMMENT='Tabelle many to many resource - roles' AUTO_INCREMENT=56 ;
CREATE TABLE
IF
NOT EXISTS `zf_roles_users_mm` (
`id_role`
int
(11) NOT NULL,
`id_user`
int
(11) NOT NULL,
UNIQUE KEY `id_role_2` (`id_role`,`id_user`),
KEY `id_role` (`id_role`),
KEY `id_user` (`id_user`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8 COMMENT='ruoli degli utenti ';
CREATE TABLE
IF
NOT EXISTS `zf_users` (
`id_user`
int
(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL,
`password` varchar(32) NOT NULL,
`nome` varchar(60)
DEFAULT
NULL,
`cognome` varchar(60)
DEFAULT
NULL,
`email` varchar(80)
DEFAULT
NULL,
`profilo` tinyint(1) NOT NULL,
`active` tinyint(1) NOT NULL,
`data_scadenza`
date
NOT NULL,
`primo_accesso` tinyint(1) NOT NULL,
`scadenza_password`
date
NOT NULL,
Building a web application layer using Zend Framework – Part 2
www.phpsolmag.com
13
listing 10c. Sql dump of top tables
PRIMARY KEY (`id_user`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8 AUTO_INCREMENT=44 ;
CREATE TABLE
IF
NOT EXISTS `zf_users_acl` (
`id_acl`
int
(11) NOT NULL AUTO_INCREMENT,
`table_foreign` varchar(255) NOT NULL,
`field_foreign` varchar(255) NOT NULL,
`descr_foreign` varchar(255) NOT NULL,
`label_foreign` varchar(255) NOT NULL,
`tree`
int
(11) NOT NULL,
`multiple`
int
(11) NOT NULL,
PRIMARY KEY (`id_acl`),
UNIQUE KEY `table_foreign` (`table_foreign`,`field_foreign`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8 AUTO_INCREMENT=9 ;
CREATE TABLE
IF
NOT EXISTS `zf_users_mm_acl` (
`id_acl`
int
(11) NOT NULL,
`id_local`
int
(11) NOT NULL,
`id_foreign`
int
(11) NOT NULL,
UNIQUE KEY `id_acl` (`id_acl`,`id_local`,`id_foreign`),
KEY `id_local` (`id_local`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8;
ALTER TABLE `zf_roles_mm_acl`
ADD CONSTRAINT `zf_roles_mm_acl_ibfk_1` FOREIGN KEY (`id_acl`) REFERENCES `zf_roles_acl` (`id_acl`) ON DELETE
CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `zf_roles_mm_acl_ibfk_2` FOREIGN KEY (`id_local`) REFERENCES `zf_roles` (`id_role`) ON DELETE
CASCADE ON UPDATE CASCADE;
ALTER TABLE `zf_roles_resources_mm`
ADD CONSTRAINT `zf_roles_resources_mm_ibfk_1` FOREIGN KEY (`id_role`) REFERENCES `zf_roles` (`id_role`) ON
DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `zf_roles_resources_mm_ibfk_2` FOREIGN KEY (`id_resource`) REFERENCES `zf_resources` (`id_
resource`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `zf_roles_users_mm`
ADD CONSTRAINT `zf_roles_users_mm_ibfk_1` FOREIGN KEY (`id_role`) REFERENCES `zf_roles` (`id_role`) ON DELETE
CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `zf_roles_users_mm_ibfk_2` FOREIGN KEY (`id_user`) REFERENCES `zf_users` (`id_user`) ON DELETE
CASCADE ON UPDATE CASCADE;
ALTER TABLE `zf_users_mm_acl`
ADD CONSTRAINT `zf_users_mm_acl_ibfk_1` FOREIGN KEY (`id_local`) REFERENCES `zf_users` (`id_user`) ON DELETE
CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `zf_users_mm_acl_ibfk_2` FOREIGN KEY (`id_acl`) REFERENCES `zf_users_acl` (`id_acl`) ON DELETE
CASCADE ON UPDATE CASCADE;
MARCo MoCEllINI
Co-founder, CEO and IT Project Manager of Inode SRL, an italianweb agency specialized in open source web solution. He develop
php applications from about 10 years and since 2007 he use technologies such as ZendFramework, Jquery, Typo3. Contact the
author at: marco.mocellini@inode.it
No 2/201214
Basics
W
e all know that emergency solutions, usually,
are not the best ones, so let's think about an-
other approach, let's learn some tips to opti-
mize our code from the very first moment. Take it easy,
you will see that is not really a lot of work and it's going
to save us time and, more important, our mental health,
in the future.
Optimizing a website is such a very big topic, today I'm
going to center this article on PHP code optimizing, but
have in mind that there are many other aspects about
optimizing, such as webserver configuration, hardware
limitations, and connectivity among others. I'll write a
second part for this article soon, talking about PHP con-
figuration optimizations, a very interesting topic too.
How to benchmark our PHP script
Yes, but first of all, what is benchmark? In a nutshell,
benchmark is the act of executing a portion of code in
order to assess his relative performance in the whole
program.
There are tools out there that will help us to profile and
benchmark our PHP code (XDebug, Zend Studio inte-
grated profiler, etc), in this article we are going to keep
it simple, however is a good idea to take a look at this
tools if we have a complex or large website.
We will write a simple function to know how long takes
it to our webserver to resolve and execute our php code.
Let’s see how to use this function (Listing 1).
How it works? Actually it’s pretty simple, before ex-
ecute our problematic code, we set a variable with the
microtime function (just in case you didn’t know, this
function returns the current Unix timestamp, which ad-
ditionally appends the corresponding microseconds),
then after the code runs, we look for the difference be-
tween times obtaining the execution time of the script.
At this point, we created an awesome precise bench-
marking function that we can use to timing our php
scripts. Isn't it great? Now we are ready to try to isolate
where our problems are and start optimizing.
Optimizing PHP code
There are many ways to improve your PHP code; we
can easily increase the efficiency doing a little extra ef-
fort during development.
Looping around
A loop is considered an efficiency killer, not only in PHP
but in general, if you have many nested ones. One loop
will require running n times and if you have 1 nested
loop, this means your program will have to run n2 times
and so on.
One common mistake, writing loops, is to calculate
the length in the procedure. It's easier to explain it with
an example:
As you can see, the difference is that the first exam-
ple doesn’t run
sizeOf
operation n times while the first
one did, this is a good tip to improve loops performance:
Only write inside the loop the strictly necessary opera-
tions.
Now talking about the different ways of write a loop,
wich is the best one?
Based on different benchmarks, seems that the
foreach()
is substantially faster than both the
while()
and
for()
loop procedures.
Benchmarking
and optimizing PHP
PHP is a very fast programming language, even so,
benchmark and optimize our scripts is a very good idea.
What you will learn...
• How to benchmark a PHP script;
• Basics about code optimization;
• Basics about cache;
What you should know...
• Good understanding about how PHP works;
Benchmarking and optimizing PHP
www.phpsolmag.com
15
• Think carefully which code you are going to re-use,
remember that you can split code in methods later,
when needed
• When you are about to write a very complex PHP
classes, consider using the Singleton pattern.
• If a method can be static, declare it static; this will
improve speed by factor of 4! This is specially use-
ful when working with Util classes that groups sim-
ple functions.
Listing 1.
<?php
function benchmark($startTime=false,$precision=4){
$time = 0;

list
($usec, $sec) =
explode
(" ",
microtime
());

if
($startTime){
$time =
round
((((
float
)$usec +
(
float
)$sec) -
(
float
)$startTime),$precision);
}
else
{
$time =
round
(((
float
)$usec + (
float
)$sec),$precision);
}

return
$time;
}
?>
Rysunek 1. Image obtained at: http://phplens.com/lens/php-book/optimizing-debugging-php.php As you can see, there two cache types
involved.
Avoid doing SQL queries within a loop!
A common mistake is placing a SQL query in-
side of a loop. This results in multiple round
trips to the database, and significantly slower
scripts.
Echo, Print, notes about strings
Echo and print both are language constructs
and both have the same functionality, to out-
put content to the screen; however there is
a difference between them, the print function
returns a value. It may sound insignificant in-
deed, but it is worth noting these are the most
frequently used functions along a script, so
why not to try to optimize them?
Next I give you a list of performance com-
parisons working with strings, some are pretty
interesting.
As a general rule to have in mind, always is
better and faster to use a language construct
vs a function since it’s execution does not re-
quire function look ups.
When we need to check if a string has a certain length
usually we use the
strlen()
function but there is a trick
to do this a little bit faster using
isset()
construct.
Not everything has to be OOP
This might generate discussion: good practices vs per-
formance. The true is (at least talking about PHP) that
often it is too much overhead to use OOP on everything;
each method and object call consumes a lot of memory
and directly impact in the performance.
So let's see how to balance this to have good code
but, at the same time, good performance.
• Do not implement every data structure as a class,
POJOs are good but arrays are useful too
No 2/201216
Basics
• Have in mind that incrementing an object prop-
erty (eg.
$this->p++
) is 3 times slower than
a local variable. Do you really need that var as
a property?
• Methods in derived classes run faster than ones
defined in the base class, so try to extend your
classes when possible.
To cache or not to cache and optimizers
There is no debate here, one of the secrets of high
performance is try to cache as much as possible and
try to avoid our server to work extra hours.
Let’s see how PHP works to understand how cache
and optimizers can improve performance (Figure 1).
Opcode cache
PHP is an interpreted language, this means that each
time a PHP generated page is requested, the server
must read in the various files needed and compile
them into something the machine can understand
(opcode). PHP Accelerators/Cachers will boost the
performance by caching the compiled form of php
scripts to avoid parsing and compiling the code eve-
ry time the page is loaded. A PHP accelerator typi-
cally reduces server load and increases the speed of
your PHP code by 1-10 times.
There are many PHP accelerators/cachers prod-
ucts out there (APC, eAccelerator, XCache, Zend
Optimizer, are the most frequently used) , please
take a look at this article to know more: http://www.
ducea.com/2006/10/30/php-accelerators/
cached HTML
One of the secrets of high performance is not to write
faster PHP code, but to avoid executing PHP code
by caching generated HTML in a file or in shared
memory.
I’ll show you a pretty simple example just to see
how the idea works, this is not for production pur-
poses.
As you can see, the general idea is to save a file
with the content, and during the time that the cache
is valid, just print this file to the browser without any
extra job.
There are solutions like this to be implemented in
production enviroments, here are some of them:
• PHP smarty template http://www.smarty.net/
manual/en/caching.php
• For wordpress users - Wordpress super cache
http://wordpress.org/extend/plugins/wp-super-
cache/
• Ob content caching http://uk3.php.net/manual/
en/function.ob-get-contents.php and memcache
http://ar2.php.net/memcache
Listing 2.
<?php
$start = benchmark();
some_suspicious_function_or_code();
echo
benchmark($start); //outputs something like 0.0165
?>
Listing 3.
<?php
//this is faster
$size =
sizeof
($x);
for
($i=0; $i<$size; $i++){ }
//than this
for
($i=0; $i<sizeOf($x); $i++){ }
?>
Listing 4.
<?php
$a = 'a'; $b = 'b'; $c = 'c';
//this is faster
echo
$a;
echo
$b;
echo
$c;
//than this
echo
$a . $b . $c;
//and this is even faster
echo
$a, $b, $c;
?>
Listing 5.
<?php
$a = 'a'; $b = 'b'; $c = 'c';
//this is faster
echo
'the value of $a is: ' , $a;
//than this
echo
"the value of \$a is: $a";
?>
Benchmarking and optimizing PHP
www.phpsolmag.com
17
There is a nice cache article in the following link http://
www.mnot.net/cache_docs/ and here is another one ht-
tp://www.devshed.com/c/a/PHP/Output-Caching-with-
PHP/. I really recommend to keep reading about this
subject, it’s really interesting to know how cache works!
Final words
There is tons of information about php code optimiza-
tions and many of them vary along PHP versions, so I
hope that this article works as a kickstart to begin inves-
tigating about performance!
There are some final general recommendations that
are good to know:
• The results of the benchmarks may vary in different
environments, so you might run every test in your
own server to see how it behaves.
• A significant number of problems concerning slow
execution of PHP scripts are related to slow and
unoptimized database queries, so that might be a
good idea to take a look at this before start.
• Learn as much as possible about the software you
are using (Apache, IIS, databases, etc.), have in
mind that the more you know, the better and more
you can perform optimizations.
• Try to deepen your knowledge about the operating
system, networking and server hardware too
I sincerely hope you have found this article useful,
remember to keep investigating and learning every
day!
Listing 6.
<?php
//we use to write
if
(
strlen
($var) < 4)
echo
"var is too short";
//magic trick!
if
(!
isset
($foo{4}))
echo
"var is too short";
?>
Listing 7.
<?php
$cachefile = 'cache/' .
basename
($_SERVER['SCRIPT_URI']);
$cachetime = 120 * 60; // 2 hours (time to expire cache)
// Serve from the cache if it is younger than $cachetime
if
(
file_exists
($cachefile) && (
time
() – $cachetime < filetime($cachefile)) {

include
($cachefile);

exit
;
}
ob_start(); // starts the output buffer
// Your normal PHP script and HTML content here
$fp =
fopen
($cachefile, ‘w’); // open the cache file for writing
fwrite
($fp, ob_get_contents()); // save the contents of output buffer to the file
fclose
($fp); // close the file
ob_end_flush(); // Send the output to the browser
?>
cLaudiO cORLaTTi
Born in Argentina, the author has been in love with pro-
gramming since his early schooldays. Has been work-
ing since 2006 in one of the largest retail company of his
country as a programmer. Always passionate about new
technologies, has been working in various personal and
freelance projects too.
Now working in a new expenses tracking and saving
web application integrated with banks information at
www.al-centavo.com
No 2/201218
Practice
Firstly, what is MVc?
MVC also known commonly as Model View Controller,
is a software architecture in which one is able to sepa-
rate their concerns into separate manageable system
components.
It is often the case that when learning a language such
as PHP – you can mix the PHP code with the HTML
code. While this does occasionally happen – this tends
to lend hand to the fact that, you get some very nasty
untidy code.
In order to reduce development time in future projects
it might be an idea into making a smaller, default frame-
work in which to work with.
Suppose we have a website called www.mysite.com
and that we have several user and non-user areas,
such as: index.php, admin.php, blog.php. There are
several ways in which we can expect to see different
websites display this (by no means is there a standard
approach to this).
When it comes down to SEO we want to have URL
friendly links within the compounds of our site. Thus
in order to do so – we can create a system that han-
dles these wonderfully simplified urls. For instance
www.mysite.com/blog/blog-post.
The tutorial will cover how to achieve such a feat.
In actuality, we are messing around with a file called
.htaccess and the true form of our URL parameters look
something like this,
www.mysite.com/index.php?myurl=blog/blog-post
We will however, ensure that it looks a lot more clean-
er than that.
The purpose of the framework will be to:
• Call user made modules (classes) from the main
system object
• Simplified, SEO friendly links and URL's
• Basic DB abstraction
In this tutorial we will cover step 1.
creating the hierarchy
The first thing we must do is create a repository in which
our files will reside. Start, by creating a folder called
mymvc, within this we will have a folder called reg, cfg
and any other folders you feel necessary for future de-
velopment such as img, scripts, … etc.
Within the mymvc, create a file called index.php
and within the reg folder create a file called
mysystem.class.php
Developing an MVc
framework
What you will learn...
• PHP5,
• Object Oriented Programming,
• Knowledge about design and development patterns.
What you should know...
• How to develop your own framework,
• A further understanding of MVC architecture.
Listing 1.
class
mysystem {
public function __construct() {
}
public function __destruct() {
}
}
Developing an MVc framework
www.phpsolmag.com
19
As such the outcome would be like so:
$system->get('myobject')->myobjectMethod();
this means we access everything through
$system

and don't have to create a separate variable object
$myobject
in order to access just
myobjectMethod()
.
the singleton
In order to create the singleton we need a method in
which to initialize it. We will call it sInit.
See the class structure below for the full singleton im-
plementation.
What we are doing here is providing a simple inter-
face ( not in the literal oo sense ), in which the program-
mer can store classes as objects and retrieve them at
a later stage.
This can be very handy when you need to deal with
multiple classes that interact.
Sinit, checks to see if an instance already is set, if
it isn't – then an instance is created using the current
class name using the magic word __CLASS__.
Put()
allows us to store a new instance of a class and
assign it a name. This name assigning is great as it al-
lows us to store it as a simple name instead of a possi-
ble complex class name such as myClassWhichIsLong-
Named, you could store it as mcwiln.
Get()
allows you to retrieve the class mcwiln for ex-
ample and access it's class methods.
In the index.php file, require_once the main system
class and type the following:
$system = new mysystem();
$system->sInit();
Now create a random file called my
randomfile.class.php, below
$system->sInit() - put...
$system->put(new randomfile(),'rf');
At the moment, you will probably get an error, as the
randomfile isn't automatically included, however in the
next tutorial we will run over creating a simple auto in-
clude for the directory and a very simple URL SEO
friendly class. Which will handle our parsed url param-
eters.
Note, that we don't necessarily need .class.php and
we can use .php, however to make life easier to distin-
guish between a class and a module file (which we will
later be developing) we do this.
We now are going to create our main system class,
go about defining a class as you would do in php like
so (Listing 1).
We are going to utilize what is known as a singleton
pattern as our main entry into the system. We want to be
able to store objects in a accessible static array so that
we can access other classes within the system without
having to re-instantiate or instantiate other classes.
Listing 2.
class
mysystem {

private
static
$object =
array
();
private
static
$instance;
public function __construct() {
}
public function __destruct() {
}
public function sInit() {

if
(!
isset
(self::$instance)) {
$obj = __CLASS__;
self::$instance =
new
$obj;
}


return
self::$instance;
}
public function get($obj) {

if
(
is_object
(self::$object[$obj]) ) {

return
self::$object[$object];
}
}
public function put($obj,$name) {
require_once("sys/".$obj.".class.php");
self::$object[$name] =
new
$obj(self::
$instance);
}
}
MichaeL Grey
He is middleweight PHP LAMP developer, He has been work-
ing commercially for four years, however has accrued ap-
proximately more than 6 years. He has worked on several very
large commercial projects involving such companies as Rolls
Royce, Mandarin Continental Hotels. In his spare time he like
to code, play guitar and he is interested in games develop-
ment and visual / audio engineering.
No 2/201220
Practice
T
rying to answer these questions gave birth to the
PHP/Java Bridge, which creates a communica-
tion channel between these two entities. Using
the bridge, you can develop classes in Java and call
their methods from PHP or you can use PHP scripts in
your Java desktop/web applications.
How it works
As you probably know, PHP 4 supported an exten-
sion for combining PHP with Java, but from PHP 5
or PHP 6, to combine PHP with Java you should in-
stall PHP/Java Bridge (you can download it from http:
//php-java-bridge.sourceforge.net/pjb/download.php),
whereof you can learn more from http://php-java-
bridge.sourceforge.net/pjb/.
Using PHP/Java Bridge
The current distribution of the PHP/Java Bridge is avail-
able in .zip at http://sourceforge.net/projects/php-java-
bridge/ . The installation process depends on which
Java platform will be interacting with PHP through this
bridge.
For J2SE, installation is simple:
• Install J2SE 1.6 or above.
• Install PHP 5.1.4 or above.
• Extract the php-java-bridge_5.2.2_j2ee.zip.
• From the command prompt, navigate to this folder
and type:

…>java –classpath
JavaBridge.war TestInstallation
In the current folder, you should see an ext folder that con-
tains four .jar files. Copy JavaBridge.jar and php-script.
jar to your J2SE ext directory
{JAVA _ HOME}/jre/lib/ext
.
Here's how to install the PHP/Java Bridge for J2EE:
• Copy JavaBridge.war archive into the auto-deploy
folder of your J2EE server or servlet engine (Tom-
cat, Resin, etc.)
• Rename this .war archive whatever you're calling
the new application and restart the J2EE server.
Wait for the auto-deploying process to create the
directory associated to this .war. In this example,
the app is called appName.war.
• Test the new application from the browser like this:
http://localhost:8080/appName (click on test.php).
• If you have your J2EE server running on a different
host or listen on a different port, then modify these
parameters accordingly.
To begin with, you must know that the PHP/Java
Bridge comes with a set of functions (PHP classes)
that were especially created to integrate Java code in-
to PHP scripts. Some of these functions are:
combining PHP with Java
in the same application
using PHP/Java bridge
As the two languages got stronger, they became highly
appreciated by dedicated developers, who began to ask:
What would happen if these two languages met? Could we
combine their powers constructively?
What you will learn...
• Install and configure the PHP/Java Bridge,
• Use Java classes in PHP scripts,
• Use PHP scripts in Java classes.
What you should know...
• knowledge of Java SE (bean) and PHP5 core and how they in-
teract using the PHP / Java Bridge.
combining PHP with Java in the same application using PHP/Java bridge
www.phpsolmag.com
21
Listing 1a. tenisBean.jar
package com.tenis;
import java.io.Serializable;
public

class
TenisBean
implements
Serializable {

final
String[] allPlayers =
new
String[]{"rafael.png", "nicolas.png", "roger.png", "novak.png", "andy.png",
"juan.png"};

final
String[] names =
new
String[]{"Rafael Nadal", "Nicolas Almagro", "Roger Federer", "Novak Djokovic",
"Andy Murray", "Juan Martin Del Potro"};

final
String[] countries =
new
String[]{"Spain", "Spain", "Switzerland", "Serbia", "United Kingdom",
"Argentina"};

final
String[] birthdates =
new
String[]{"3 June 1986", "21 August 1985", "8 August 1981", "22 May 1987", "15
May 1987", "23 September 1988"};

final
String[] birthplaces =
new
String[]{"Manacor, Mallorca, Spain", "Murcia, Spain", "Basel, Switzerland",
"Belgrade, Serbia", "Dunblane, Scotland", "Tandil, Argentina"};

final
String[] residences =
new
String[]{"Manacor, Mallorca, Spain", "Murcia, Spain", "Bottmingen,
Switzerland", "Monte Carlo, Monaco", "London, England", "Tandil, Argentina"};

final
String[] heights =
new
String[]{"6 ft. 1 in. ( 1.85 metres )", "6 ft. 0 in. ( 1.83 metres )", "6 ft. 1
in. ( 1.85 metres )", "6 ft. 2 in. ( 1.88 metres )", "6 ft. 3 in. ( 1.91 metres )", "6 ft. 6
in. ( 1.98 metres )"};

final
String[] weights =
new
String[]{"188 lbs. ( 85.5 kilos )", "179 lbs. ( 81.4 kilos )", "187 lbs. ( 85
kilos )", "176 lbs. ( 80 kilos )", "185 lbs. ( 84.1 kilos )", "182 lbs. ( 82.7 kilos )"};

final
String[] plays =
new
String[]{"Left", "Right", "Right", "Right", "Right", "Right"};

final
String[] pros =
new
String[]{"2001", "2003", "1998", "2003", "2005", "2005"};

private
String[] players =
new
String[]{"", "", ""};

private
String currentplayer = "";

private
String name = "";

private
String country = "";

private
String birthdate = "";

private
String birthplace = "";

private
String residence = "";

private
String height = "";

private
String weight = "";

private
String play = "";

private
String pro = "";

public
TenisBean() {
}

public
String[] getPlayers() {

if
(players[0].trim().length() == 0) {

int
player =
new
java.util.Random().nextInt(4);

for
(
int
i = 0; i < 3; i++) {
players[i] = allPlayers[player];
player++;
}
}

return
players;
}
No 2/201222
Practice
Listing 1b. tenisBean.jar

public

void
setPlayers(String[] players) {

this
.players = players;
}

public
String getCurrentplayer() {

return
currentplayer;
}

public

void
setCurrentplayer(String currentplayer) {

this
.currentplayer = currentplayer;

if
(currentplayer.trim().length() > 0) {

for
(
int
i = 0; i < allPlayers.length; i++) {

if
(allPlayers[i].equals(currentplayer.t
rim())) {

this
.name = names[i];

this
.country = countries[i];

this
.birthdate = birthdates[i];

this
.birthplace = birthplaces[i];

this
.residence = residences[i];

this
.height = heights[i];

this
.weight = weights[i];

this
.play = plays[i];

this
.pro = pros[i];

break
;
}
}
}
}

public
String getBirthdate() {

return
birthdate;
}

public

void
setBirthdate(String birthdate) {

this
.birthdate = birthdate;
}

public
String getBirthplace() {

return
birthplace;
}

public

void
setBirthplace(String birthplace) {

this
.birthplace = birthplace;
}

public
String getCountry() {

return
country;
}

public

void
setCountry(String country) {

this
.country = country;
}

public
String getHeight() {

return
height;
}

public

void
setHeight(String height) {

this
.
height = height;
}

public
String getName() {

return
name;
}

public

void
setName(String name) {

this
.name = name;
}

public
String getPlay() {

return
play;
}

public

void
setPlay(String play) {

this
.play = play;
}

public
String getPro() {

return
pro;
}

public

void
setPro(String pro) {

this
.pro = pro;
}

public
String getResidence() {

return
residence;
}

public

void
setResidence(String residence) {

this
.residence = residence;
}

public
String getWeight() {

return
weight;
}

public

void
setWeight(String weight) {

this
.weight = weight;
}
}
combining PHP with Java in the same application using PHP/Java bridge
www.phpsolmag.com
23
• java:
This allows you to access the Java type with
the given name. For example:

java("java.lang.System")->getProperties();
• java _ autoload:
This allows you to load a set of
Java libraries in the current PHP script. For exam-
ple:
java _ autoload("my _ 1.jar;my _ 2.jar");
• java _ cast:
This allows you to convert a Java ob-
ject into a PHP value. For example:
$mystr=new java("java.lang.String","9");$phpnr=java _
cast($mystr,"integer");echo $ phpnr;
• java _ is _ null:
This allows you to check if a value
is null or not. For example:
java _ is _ null(java("java.lang.System")-
>;getProperty("my _ prop"))
• java _ session:
This allows you to return a session
handle. For example:
$session = java _ session();
• java _ values:
This allows you to evaluate the ob-
ject and fetch its content (only if this is possible).
For example:
$result = java _ values($calcinstance->addAB($term
_ 1,$term _ 2));
To make use of these functions, your PHP applica-
tions must contain the corresponding PHP classes.
The most important class is Java.inc, but the com-
plete list of classes can be seen in the
appName/java
directory.
PHP/Java application
Now, that you know the PHP/Java Bridge basics, it is
time to develop your first PHP/Java application. The
application listed next, is a PHP script that implements
a Java session bean (A session bean is the enterprise
bean that directly interact with the user and contains the
business logic of the enterprise application. A session
bean represents a single client accessing the enterprise
application deployed on the server by invoking its meth-
od.) that will be the engine of the application, because
the results will be provided by a Java class named tenis-
Bean. In other words, Java behaves as a pure business
logic component and PHP behaves as an interrogator
component.
Practically, this application randomly displays three
tennis players, from a list containing six players, as you
can see in the tenisBean class, and their correspond-
Listing 2. tenisBean.jar
<?php require_once("java/Java.inc");
$session = java_session();
if
(java_is_null($t=$session->get("bean"))) {
$session->put("bean", $t=
new
Java("com.tenis.TenisB
ean"));
}
if
(
isset
($_GET['cp'])){
$t->setCurrentplayer($_GET['cp']);
}
?>
<html>
<head>
<title>ATP Best Players Ever</title>
<style>
.atpStyle1 {
font-family: Helvetica,Arial,sans-serif;
color: #000000;
font-size: 11;
font-weight: bold;
}
.atpStyle2 {

font-family: Helvetica,Arial,sans-serif;
color: #000000;
font-size: 11;
font-weight: bold;
}
.atpStyle3 {
font-family: Helvetica,Arial,sans-serif;
color: #cc0000;
font-size: 15;
font-weight: bold;
}
.atpStyle4 {
font-family: Helvetica,Arial,sans-serif;
color: #00000;
font-size: 13;
font-weight: bold;
}
.atpStyle5 {
font-family: Helvetica,Arial,sans-serif;
color: #00000;
font-size: 24;
font-weight: bold;
}
</style>
</head>
<body>
No 2/201224
Practice
<?php $players = java_values($t->getPlayers()); ?>
<table align="center">
<tr>
<td colspan="3" align="center" class='atpStyle5'>THREE OF THE BEST EVER ATP PLAYERS</td>
</tr>
<tr>
<?php

foreach
($players as &$value) {

echo
("<td><img src='images/".$value."' alt=''></td>");
}
?>
</tr>
<tr>
<?php

foreach
($players as &$value) {

echo
("<form name='atpForm_".$value."' action='".$PHP_SELF."'
method='get'>");
echo("<input type='hidden' name='cp' value='".$value."'>");
echo("<td><input type='submit' value='Details'></td>");
echo("</form>");
}
?>
</tr>
<?php

if
(
strlen
(java_values($t->getCurrentplayer())) > 0){

echo
("<tr><td><hr></td><td><hr></td><td><hr></td></tr>");
echo("<tr><td><img src='images/".java_values($t->
getCurrentplayer())."' alt='' width='45'
height='60'></td><td class='atpStyle1'>Name: </td>
<td class='atpStyle3'>".java_values($t->getName())."</td></tr>");
echo("<tr><td></td><td class='atpStyle2'>Country: </td>
<td class='atpStyle4'>".java_values($t->getCountry())."</td></tr>");
echo("<tr><td></td><td class='atpStyle2'>Birth
Date
: </td>
<td class='atpStyle4'>".java_values($t->getBirthdate())."</td></tr>");
echo("<tr><td></td><td class='atpStyle2'>Birth Place: </td>
<td class='atpStyle4'>".java_values($t->getBirthplace())."</td></tr>");
echo("<tr><td></td><td class='atpStyle2'>Residence: </td>
<td class='atpStyle4'>".java_values($t->getResidence())."</td></tr>");
echo("<tr><td></td><td class='atpStyle2'>Weight: </td>
<td class='atpStyle4'>".java_values($t->getWeight())."</td></tr>");
echo("<tr><td></td><td class='atpStyle2'>Plays:
</td>
<td class='atpStyle4'>".java_values($t->getPlay())."</td></tr>");
echo("<tr><td></td><td class='atpStyle2'>Turned Pro: </td>
<td class='atpStyle4'>".java_values($t->getPro())."</td></tr>");
echo("<tr><td><hr></td><td><hr></td><td><hr></td></tr>");
}
?>
<tr>
<td colspan="3" align="center" class='atpStyle5'>THREE OF THE BEST EVER ATP PLAYERS</td>
</tr>
</table>
</body>
</html>
combining PHP with Java in the same application using PHP/Java bridge
www.phpsolmag.com
25
ing personal information. This will be accomplished by
the PHP script, which interact with the tenisBean.jar
through its set and get specific methods. The result is
listed in the end part of this article.
After you compile this Java source, place it in a .jar
archive named tenisBean.jar. Copy this file to the
atpPlayers/WEB-INF/lib
directory.
Now it's time to develop the PHP script that will call
the above Java class. Using the methods described in
the Using PHP/Java bridge section, you can write the
atpPlayers.php, listed below:
The output of mixing PHP with Java in the same ap-
plication, can be seen writing in browser the localhost:
8085/atpPlayers/atplist.php link.
Summary
This introduction to the PHP/Java Bridge hopefully
taught you the basic mechanisms that allow the inter-
action between these two powerful programming lan-
guages. Developing these kinds of applications may be
a delicate task, but if you think that you can get serious
advantages from this symbiosis, then don't hesitate to
use it!
Octavia andrea angHeL
Octavia Andrea Anghel is a senior PHP developer currently
working as a primary trainer for programming teams that par-
ticipate at national and international software-development
contests. She consults on developing educational projects at
national and international level. She was coordinating in Eu-
ropean Job and Career, International Comenius project along
with Spain, Italy, Turkey and Czech Republic countries. She is
a co-author of the book XML technologies—XML in Java and
at the moment is working at an online course to teach PHP for
Udemy website.
Figure 1. Three of the best
ever ATP players
No 2/201226
practice
Building an SeO-ready e-commerce site
Take a look at your traffic reports. Chances are, organic
search results (unpaid results) are one of your top 3 rev-
enue drivers. Among our e-commerce clients, organic
search drives 60%+ of all online revenue. That’s too big
a channel to ignore.
You need search engine optimization (SEO). Wait!
Keep reading! I can practically hear you rolling your eyes.
SEO gets a bad reputation among developers, I know.
But it’s a matter of approach. If you can follow a few best
practices when building your e-commerce site, you can
tap a huge revenue source for your company.
What SEO is for developers
There are a lot of different definitions for SEO. When
I say SEO, I’m not talking about secret formulas, link
buying or magic tricks. For our purposes in this article,
SEO means:
Building a web site that search engines can easily
crawl, categorize and classify, while offering the best
possible user experience.
As a developer, your work will impact SEO by:
1. Ensuring best crawl efficiency. Search bots have
a fixed number of URLs or an amount of time they
can spend crawling your site. You want them to
make best use of that time, so that you get more
pages crawled, indexed and ranked.
2. Influencing categorization, so that search engines can
easily determine where your content should appear.
3. Steering authority to the right place. Search bots
are as dumb as rocks. You need to make sure they
can easily attribute authority to different pages.
4. Impacting user behavior. We know that Google, at a
minimum, watches user behavior. A faster, easier-to-
use site will cause fewer people to bounce back to
Google search results. That will improve rankings.
No tricky stuff. Just clear, clean code and a few tools
your content team will need:
1: Use smart canonicalization
Every page of content on your site (every product, cat-
egory, blog post, etc.) must have a single, unique URL.
Not two URLs. Not 3. There should be only one address
for one page.
That’s canonicalization. Good canonicalization helps
with both crawl efficiency and authority.
If you want to get it right, do the following:
1. Link to your home page at
/
. Don’t link to
/index.
php
. You can link to the ‘www’ or non-www version
of your domain, but link consistently.
2. If you choose to link to your home page at the
‘www’ address, set up a 301 redirect from the non-
www address to the www version. If you choose to
link to the non-www address, do the reverse.
3. Always link to the first page of paginated content at
pagename.php. Do not link to pagename.php?p=1.
4. Make sure that a page not found returns a 404 re-
sponse code, not a 302. More about this in a mo-
ment.
5. Always use lower case for URLs. No capitals.
6. Strip out any query variables that you don’t need.
Never leave anything in the query string that you
don’t need.
7. When using query variables, always use them in
the same order.
8. Pay attention to query variables even if you’re us-
ing friendly URLs. The query strings are still there –
they’re just translated to a more readable format.
9. Don’t manage state using the URL. Use POST or
cookies.
10. Set up a
rel=canonical
tag for every page of your
site. Make sure the tag points to the shortest, sim-
plest version of your page URL.
I could write several articles just about this topic. I’ll
spare you. If you want to learn more about it, go to
Google and search for canonicalization SEO. The top
4-5 articles will tell you all you need to know.
Building an SeO-ready
e-commerce site
What you will learn...
• How site development impacts SEO.
• Canonicalization and its SEO impact.
• Which response codes to use on your site.
• How to build a fast site that crawlers love.
What you should know...
• Basic HTML and CSS.
• Basic PHP programming.
• Apache or IIS configuration, as relevant.
Building an SeO-ready e-commerce site
www.phpsolmag.com
27
If you want to test site performance, I highly recommend
Google Page Speed and http://tools.pingdom.com. Both
show page load times and provide suggestions for im-
prove performance.
4: populate tags and attributes
The meta tags aren’t as useful as they used to be. Only
the title tag directly impacts SEO. But it’s a very important
on-page ranking factor. Imag ALT attributes and smart
use of microformats can directly impact rankings, too.
Tagging and attribute use directly impact categorization:
1. Make the title tag of every page on the site, including
the home page, editable via your content manage-
ment system or store back end. The marketing team
should be able to edit title tags without your help. Un-
less, of course, you want them lined up outside your
office, asking for daily changes…? Didn’t think so.
2. Make the title tag of all product pages default to: pro-
ductname, category, brand. That provides the best-
case for crawlers that are trying to classify pages.
3. Auto-populate the description META tag of all pag-
es with the first 2 sentences of page content. While
the description tag doesn’t directly impact rankings,
it often appears as the search results ‘snippet’, and
a good one can get you more clicks.
4. Auto-populate product image ALT attributes with
product names.
5. Make the ALT attributes for product images editable
via the store back end or CMS.
6. For any images used in navigation, or any other
images with text in them, make the ALT attribute
match the image text.
7. Go read schema.org. Use the appropriate tagging
scheme for your products, store locations and reviews.
A lot of this seems like extra work when you’re doing
it, but it saves you a tremendous amount of busy work
later on. My rule: If any piece of text on any page could
show up in a search result, make sure there’s a good
default, and that non-developers can edit it. That’ll
save you a lot of time in the long run.
Just the beginning
There are endless little details and tweaks you can
make to improve a site’s SEO-readiness. But follow the
4 tips above and you’re off to a great start. Use them
and you’ll help your company sell more, reduce your
long-term workload and look like a hero.
2: return the right response codes
Search engine bots are very dependent on server re-
sponse codes. For example, if they visit your site and
attempt to load a page that doesn’t exist, they need to
see a 404 or 410 code. If they don’t, they’ll continue to
try to crawl that page, or at least try to follow a redirect.
Like canonicalization, response codes impact crawl
efficiency and authority. Be sure that your site:
• By default returns a 404 for missing pages. There are
times when a 301 redirect may make more sense,
but the default should be a 404 response. The eas-
iest way to do this is to configure 404 responses in
Apache. You can also use the
header()
function.
• Uses a 410 response code for missing pages you
want removed from search engines as permanently
as possible.
• Uses 301 redirects for any permanent redirection.
• Uses 302 temporary redirects only when the redi-
rected URL will eventually be back on the site.
About three quarters of the SEO issues I deal with start
with response codes. A little work on this, in advance,
goes a long way towards an efficient, well-indexed site.
3: Build for speed
A fast site means better user behavior and crawl effi-
ciency. Even if you hate the idea of SEO, it just makes
sense to make pages on your site load in as little time
as possible. You can speed up your site by working to
reduce bandwidth usage and latency:
1. Minify all javascript, css and HTML. You can do this
‘on the fly’ using Minify, which you can find at http://
code.google.com/p/minify.
2. Make javascript and CSS external. Put any javascript
longer than 5 lines in a .js file. Put CSS in .css files.
3. Minimize the number of HTTP requests per page
with CSS sprites, and by consolidating javascripts
that are always used together into a single .js file.
4. Serve static content, like images and javascript,
from a cookieless domain. The easiest way to do
this is to use a content delivery network, or CDN.
5. Use GZIP compression. On Apache 2.x and 1.3,
you can use
mod _ deflate
,
mod _ gzip
, respectively.
6. Set far future expires headers for static content.
7. Write clean HTML code. Avoid redundant DIVs, ta-
bles for layout or empty lines. Any of these might
bloat your code and slow transfer.
8. Optimize images. You know this one.
9. Don’t scale images using HTML. Make sure your
designers scale images using their image editing
software, instead.
10. Cache content whenever possible.
11. Minimize database calls.
12. Minimize round trips. At a minimum, make sure you
only use https on pages that require it.
Ian Lurie is CEO at Portent, an internet marketing company he started in
1995. He’s also one of the world’s top experts on SEO, web technology and
digital marketing. He co-published the Web Marketing for Dummies All In
One Desk Reference. In it, he wrote the sections on SEO, blogging, social me-
dia and web analytics (400 pages - not that he was counting).
Ian rants and raves, with a little teaching mixed in, on his blog, Conversation
Marketing - www.conversationmarketing.com.
You may find him teaching his kids to play D&D on the weekends, or drag-
ging his tongue on the ground as he pedals his way up Seattle’s ridiculous-
ly steep hills.
No 2/201228
Practice
introduction
Since the early 1990s, my professional focus has been
software performance. By that I mean speed. I’m a de-
veloper, too. In my career, I’ve written a lot of C code.
These days, I write a lot of Perl. My aim in this paper is
to give you a better understanding about what goes on
inside the Oracle Database in response to the code you
write. I’m going to show you examples of code written in
Java, but what you’ll see applies equally regardless of
whether your code is PHP, C#, Ruby, Python, Perl, C,
or something else.
My goal is simple. If I can get you to understand a lit-
tle more clearly what’s going on inside the Oracle Da-
tabase kernel, you’ll write better, faster code. Although
developers are often taught that they shouldn’t concern
themselves with what goes on inside the database,
that’s really not true. Not if you want to build big applica-
tions that run fast, anyway.
So my job is to help you make friends with Oracle.
When we’re done, you’ll write faster code, and you’ll
probably write less code, too, that’ll be easier to main-
tain. I’ll show you an example. I think that once you get
to know your database a little better, you’ll be impressed
with some of the things it does for you.
Making friends with new software is not altogether dif-
ferent from making a new human friend. The best way
to get started is to learn your new friend’s language.
Now, you’re probably already at least a little bit famil-
iar with the SQL language, which enables you to read
and write data to the Oracle Database. Teaching you
SQL is not my aim here. My aim is to teach you how
to communicate about the performance of the applica-
tion code you write. There are lots of tools that let you
assess the performance of the client code you write.
In this paper, I’ll acquaint you with a tool that Oracle
Corporation provides, which will help you measure very
easily how efficiently the code you write uses the Oracle
Database (Exhibit 1).
Where Software Speed comes in
Let’s take a look at how an application that uses Oracle
might work. Exibit 1 shows a rough UML sequence dia-
Making Friends with
the Oracle Database
To many application developers, a database is just a data store with an API that they
call when they need to persist an object. It’s an abstraction that makes sense from
one perspective: in a world where you’re expected to write dozens of new features
every day in Java, PHP, or C#, who has the time or the inclination to dive into
what’s going on deep inside the Oracle Database? As a result of this abstraction,
though, developers sometimes inflict unintended performance horrors upon their
customers. The good news is that you can avoid most of these horrors simply by
better understanding a bit more about what’s going on inside the Oracle kernel.
The trick is knowing which details you need to study, and which you can safely
learn later. This presentation describes, from a developer’s perspective, some of the
most important code paths inside the Oracle kernel that can make the difference
between an application that breaks down under load and one that can scale to
thousands of users.
What you will learn...
• Much more informed path toward writing scalable, high-per-
formance Oracle-based applications.
What you should know...
• Knowledge about writing software and performance prob-
lem.
Making Friends with the Oracle Database
www.phpsolmag.com
29
When I say scale, I mean it in the mathematical sense
that means the rate at which a user’s response times
will change as some other factor in the system chang-
es. For example, for a system not to scale well to large
user counts means that it slows down more aggres-
sively than you want as more users log into the sys-
tem. If it doesn’t scale well to large data volumes, then
that means it will slow down more aggressively than
you want as people insert rows into your database.
The point of this paper is to give you some insight
into how you can measure how efficiently your applica-
tion is written, so that you can know whether your code
is as fast as it should be and whether it will scale. Why
guess? …when you can know.
Feedback
Probably the most important tool you need to make
learning happen more efficiently is feedback. A little
girl reaches for the hot stove. Mommy yells, No! That’s
feedback. The little boy standing close by wants to see
what all the fuss is about, so he decides to touch the
stove anyway. POW! More feedback. Feedback from
the stove is even more memorable than the feedback
from Mommy.
Feedback that is close in time to the behavior it meas-
ures is more valuable than feedback that happens much
later than the behavior it measures. Imagine what would
happen if, when the little boy touched the hot stove, it
gram that serves as a good conceptual model for how
a business task that fetches rows from an Oracle data-
base works.
It’s a pretty simple model. First, the application does
what it has to do to connect to Oracle and prepare a SQL
statement for fetching the data. Then it fetches a row at
a time, processing each row in turn. It does this until it
has processed all the data that it needs. Then it cleans
up after itself.
Mission accomplished, right? It’s certainly a thrill the
first time you make something like this happen: mak-
ing data go from this complex and expensive black box
into your application, then perhaps into an HTML page
where anyone on the Internet can see it.
But what about the performance of the application?
Maybe on your development system, the code you wrote
is lightning fast. There are two questions you need to be
able to answer:
1. Is your code as fast as it should be?
2. Will your code scale?
exhibit 1. Sequence diagram for a multi-row database query.
exhibit 2. The Oracle two-tier, client-server architecture; the Oracle
kernel process is the one that writes the trace data you’ll be reading
No 2/201230
Practice
didn’t hurt until three months later. Without immediate
feedback, the boy might leave his hand on the stove for
several seconds, and then what happens three months
later would be absolutely horrifying. Three months lat-
er, a lot of the really good choices wouldn’t be avail-
able anymore. Deferred feedback can be deadly. Imag-
ine if there were a ten-second delay on what you could
see through your car’s windshield. See the story of the
chemical element radium for an important historical ex-
ample (Wikipedia 2008).
So, how can you shorten the feedback loop about the
performance of the application you are writing today?
With a feature that comes standard with every release
and edition of the Oracle Database since version 7. The
feature is called extended SQL trace. I’ve written about
its history in (Nørgaard, et al. 2004, 155–182).
When you’re interested in generating your own trace
files, you can see how to do that in section 17 near the
end of this paper. Right now, let’s take a look at the
kinds of things you’ll find in a trace file that will help you
write better code.
interpreting Your trace File
Trace files can be intimidating, especially when they
contain hundreds of thousands of lines. But even the
most complicated trace files are rooted in a surprisingly
small number of fundamental principles, which I’ll cover
here.
Oracle trace files record only two basic categories of
useful information about where your time has gone:
Database calls
Each line that begins with the token PARSE, EXEC, or
FETCH represents a single, completed database call
executed by the Oracle kernel. A database call line
tells you, with its e value, how many microseconds of
elapsed time the call consumed. Its c value tells you ap-
proximately how many microseconds of CPU time the
call consumed. (The c statistic in Oracle trace data is
only as accurate as the getrusage function your OS pro-
vides. On systems that don’t use microstate account-
ing (e.g., Solaris does), a c statistic is accurate to on-
ly ±10,000 µs. For more details, see chapter 7 of Cary
Millsap and Jeff Holt, Optimizing Oracle Performance
(Sebastopol, CA: O'Reilly, 2003).)
Operating system calls (OS calls)
Each line that begins with the token WAIT represents
a single, completed operating system call (OS call) exe-
cuted by the Oracle kernel.An OS call line tells you, with
its ela value, how many microseconds of elapsed time
the call consumed. ( It’s a little more complicated than
that, because sometimes the Oracle kernel calls more
than one OS call in the context of a single WAIT line.
But it usually doesn't matter if you don’t know this.)
The trick to understanding Oracle trace data is to learn
what the various database calls and OS calls mean.
Once you know how to figure this out, you’ll be able to
read for yourself an irrefutable play-by-play account of
exactly what your code has spent its time doing, meas-
ured from the Oracle Database’s perspective.
trace File Guided tour
Now let’s take a look at the guts of an Oracle trace file.
When we discuss Oracle trace file contents, you need
to remember that it’s the Oracle kernel process that
writes to the trace file. The play-by-play you’ll see in the
Oracle trace data stream is the story told from the per-
spective of an Oracle server process like the one shown
in Exibit 2.
The following paragraphs contain a play-by-play anal-
ysis of an Oracle trace file produced by a Java program
that consumed about 23 seconds of response time
querying 142,517 rows out of an Oracle database. You
can see more details from that trace file in section Slow
Query, near the end of this paper. The action within the
trace file that we’re really interested in begins at line
24:
24. ====================
25. PARSING IN CURSOR #1 …
26. select * from sla_run
27. END OF STMT
These lines reveal what future references to #1 will
mean: they’ll be references to a cursor associated with
the SQL statement
“select * from sla _ run”
.
28. PARSE #1:c=0,e=251,…
Line 28 is where the action really begins. This line re-
veals that the Oracle kernel executed an Oracle Pro-
gram Interface (OPI) parse function upon the select
statement shown on line 26. What I’m calling an OPI
parse function is the server-side companion of the
OCIStmtPrepare call, which the JDBC executed with-
in our client process. To see details about what such a
function does, consult (Oracle Corporation 2008). This
parse function consumed 251 µs (that is, 251 micro-
seconds, or 0.000251 seconds) of response time.
29. BINDS #1:
There were no placeholders in the SQL statement to
which values were to be bound; otherwise, we would
have seen more information (more lines of trace da-
ta) here.
30. EXEC #1:c=0,e=84,…
WAIT #1: nam='SQL*Net message to client' ela= 2 …
Making Friends with the Oracle Database
www.phpsolmag.com
31
The Oracle kernel executed an OPI exec function
(companion of OCIStmtExecute) upon the cursor de-
scribed in lines 24 through 27. The function consumed
84 µs of response time. Then the Oracle kernel wrote
some information back to its caller (the Java program)
through its SQL*Net interface. The 2 µs duration re-
ported here is not the duration of an OS write call, al-
though you have a right to expect that it would be (Mill-
sap, SQL*Net 2005). It is actually only the duration of
an OS timer call executed before the write. It’s an Ora-
cle bug, but not a very important one.
31. WAIT #1: nam='SQL*Net message from client' ela= 25227 …
Then the Oracle kernel blocked upon an OS read call
(the same way a read of your keyboard will block un-
til you press the Enter key), passing approximately
25,227 µs of end-user response time. This is actually
the duration of the whole network round-trip from the
Oracle kernel process to the client and back. It also in-
cludes all the time spent executing code on the client.
32. WAIT #1: nam='SQL*Net message to client' ela= 24 …
33. FETCH #1:c=0,e=355,…,r=10,…
34. WAIT #1: nam='SQL*Net message from client' ela= 7851

The kernel executed an OPI fetch function (companion
of OCIStmtFetch) upon the cursor executed in line 30.
The fetch returned 10 rows in one network round-trip,
consuming 355 µs. You can regard the SQL*Net mes-
sage to client call as a tiny little 24 µs of code path exe-
cuted within the context of the fetch. After the fetch, the
kernel blocked upon an OS read call for another 7,851
µs awaiting the next call from the client (Listing 1, 2).
(42,746 lines here are not shown)
Here, the same three-line pattern repeats thousands
of times. The kernel returns ten rows per network round-
trip to the client until finally there are only seven rows
returned in the final attempted ten-row fetch, at which
point the client code path can detect that it has fetched
all the rows that the kernel had to offer, and the query
is finished.
Reading a trace file line-by-line is not tremendously
difficult; it gets tedious, but not difficult. Another layer of
difficulty folds in when you want to summarize the infor-
mation presented by a block of trace file lines. The job
is more complicated than you might have guessed by
now, because there are gaps and overlaps in how the
Oracle kernel emits its own timing data. You can learn
about the details in (Millsap and Holt 2003).
You can often get a good general sense of what’s go-
ing on in a trace file simply by aggregating c or e values
for database calls and ela values for OS calls. I summa-
rize trace data with a software tool that I helped design
and develop, called the Method R Profiler.(Visit http://
method-r.com/software/profiler-info for details).
Summarizing the elapsed time consumed by data-
base calls and OS calls yields the information shown
in Exibit 3.
exhibit 3. Response time profile of our 23-second query program
execution
Response time (seconds) Call
20.070 85.8% SQL*Net message
from client
1.652 7.1% CPU service, fetch
calls
1.683 7.2% all other
23.405 100.0% Total response
time
Listing 1.
35. WAIT #1: nam='SQL*Net message to client' ela= 2 …
36. FETCH #1:c=0,e=84,…,r=10,…
37. WAIT #1: nam='SQL*Net message from client' ela= 1306 …
38. WAIT #1: nam='SQL*Net message to client' ela= 2 …
39. FETCH #1:c=0,e=81,…,r=10,…
40. WAIT #1: nam='SQL*Net message from client' ela= 1282 …
Listing 2.
41. WAIT #1: nam='SQL*Net message to client' ela= 2 …
42. FETCH #1:c=0,e=86,…,r=7,…
43. WAIT #1: nam='SQL*Net message from client' ela= 2141 …
The code spends 85.8% of its user’s time mov-
ing rows across the network and only 7.1% fetching
rows out of the database. That’s a pattern I want
you to recognize when you see it, this issue of re-
sponse time being dominated by network I/O. It is
an important performance antipattern.(Ironically,
many Oracle authors teach that you should ignore
all SQL*Net message from client calls. I hope by
now that you can see how awful of an idea that is.)
Optimizing the Query Program
Whenever network I/O time dominates the re-
sponse time of a program you’ve written, it’s prob-
ably an easy opportunity for you to optimize your
code. It’s usually not hard to perform the optimiza-
tion. The step that most people miss is to look at
response time decomposed this way to begin with.
Once you see the problem, you can focus your en-
ergy on fixing it. So, here we go…
No 2/201232
Practice
Did you flinch at all when you saw this line of the trace
data that I showed you earlier?
44. FETCH #1:c=0,e=355,…,r=10,…
Notice that the fetch call returned not just one row to
the application, but ten. Whenever your Java code
asks the JDBC for rowSource.next, the JDBC clever-
ly grabs and buffers rows foryou in batches of ten. The
default array size is 10. Had the array size been set
to 1 instead, can you guess what our response time
would have looked like?
The answer is that the program’s response time would
have been about ten times worse. We tried it:
Array fetch size Response time (seconds)
1 240.297
10 23.405
It’s easy to understand why, once you’ve seen the
trace data. If our program had fetched one row at a time
instead of ten, it would have made ten times more net-
work round-trips to fetch all the data. Thus, instead of
the 20 seconds of response time you saw in Exhibit 3,
you should expect 200 seconds of network I/O time. …
Which is almost exactly what did happen.
At this point, I hope you’ve begun to wonder about the
following questions:
1. What if you were to make the array fetch bigger
than 10? Would it improve response time?
2. Is there a point of diminishing returns, where the ar-
ray fetch size can be too big?
3. How do you manipulate the array fetch size?
The answer to the first question is that yes, absolute-
ly, increasing the array fetch size improves response
time for our program. We tried several array fetch siz-
es, and you can see from the shape of the curve in Ex-
hibit 4 that manipulating the array fetch size had a pro-
found impact upon performance.
In our program testing, we tried lots of array fetch siz-
es, and we found that the sweet spot for us (given our
row sizes, our TCP/IP packet sizes, etc.) was rough-
ly 2,048. That’s where our response times settled in at
about 2.4 seconds. That array fetch size yields about
a 90% reduction in response time compared to the de-
fault setting of 10.
Array sizes larger than 2,048 didn’t produce a per-
formance benefit for us, in spite of consuming a lot
more memory. For array fetch sizes larger than 4,096,
response times actually degraded a bit.
When we tried an array fetch size setting of 16,384,
our Java program promptly died of an out-of-memory
error.(We used a maximum Java heap size of 64 MB
by specifying java –Xms 2m –Xmx 64m.) The bathtub
shape of the data in Exhibit4tells us that bigger is better
to a point, at which too big becomes bad.
So how can you manipulate your array fetch size?
Well, if you use the Sun JDBC, you can’t. But if you use
the Oracle JDBC, it’s easy. You execute the
setFetchSize

method upon the statement object, like this (Listing 3).
Exhibit 5 shows how the program that used the array
fetch size of 2,048 spent our 2.4 seconds.
Don’t feel too unsettled by the negative number shown
in the all other category of Exhibit 5. It is an artifact of the
±10,000-µs accuracy on the CPU statistic, which I men-
tioned earlier. Sometimes, the c statistic over-accounts
for elapsed duration. For example, a database call with
c=10000,e=8000 would indicate:
Listing 3.
statement.setFetchSize(size);
resultSet = statement.executeQuery(query);
while (resultSet.next()) {
// do your business upon resultSet
}
exhibit 4. Array fetch size has a profound impact upon our query’s
performance.
response time
Seconds
250
200
150
100
50
0
1
4
16
64
256
1024
4096
16384
exhibit 5. Response time profile of our improved 2.4-second query
program execution.
Response time (seconds) Call
0.890 37.1% SQL*Net message from client
0.823 34.3% SQL*Net more data to client
0.745 31.0% CPU service, fetch calls
–0.058 –2.4% all other
2.400 100.0% Total response time
Making Friends with the Oracle Database
www.phpsolmag.com
33
The point is that by manipulating our array fetch size,
we reduced the total time we spent waiting for network
I/O calls from 20.070 seconds (Exhibit 3) to a much more
bearable 0.890 + 0.823 = 1.713 seconds. We even re-
duced the amount of CPU time we spent in fetch calls,
presumably because we’re not making nearly as many
fetch calls with the larger array fetch size.
The overall performance improvement is spectacular
(23.405 seconds to 2.400 seconds) because we target-
ed the exact reason that the program spent so much
of our time. Such is the principal beauty of profiling: it
focuses our attention where it belongs, and it safely al-
lows us to ignore everything that doesn’t matter.
Section Improved Query shows some of the raw trace
data for the improved program. Here’s a brief walk-
through of how that program progressed. The interest-
ing action begins on line 24 (Listing 4).
This is the same pattern as you saw previously in the
slow code. However, what comes next is very different:
33. WAIT #1: nam='SQL*Net message to client' ela= 6 …
34. WAIT #1: nam='SQL*Net more data to client' ela= 149 …
35. WAIT #1: nam='SQL*Net more data to client' ela= 7 …
36. WAIT #1: nam='SQL*Net more data to client' ela= 9 …
(many more
SQL*Net more data to client
calls hap-
pen here)
68. WAIT #1: nam='SQL*Net more data to client' ela= 10 …
69. FETCH #1:c=11998,e=22206,…r=2048,…
Here, the Oracle kernel makes a SQL*Net message to
client call to ship results back to the client, but the re-
sult set is so big that it won’t fit into a single network
packet. So the kernel makes a number of SQL*Net
more data to client calls to fulfill the passage of data
that it owes to the client. Finally, the kernel completes
the fetch call, which you can see returned 2,048 rows
in 22,206 µs, after consuming approximately 11,998 µs
of CPU time.
The pattern repeats until the end of the trace file:
(2,859 lines here are not shown)
70. FETCH #1:c=6999,e=6837,…,r=1205,…
71. WAIT #1: nam='SQL*Net message from client' ela= 18557

…where the last 6,837-µs fetch returns the final 1,205
rows. (Earlier, I mentioned that this query returns
142,517 rows. Note that 142,517 % 2,048 = 1,205 (us-
ing ‘%’ as the modulus operator), so 1,205 is exactly
the number of rows you should expect to be left, after
several 2,048-row fetches, for the final fetch to grab.)
Optimizing Begins with Measuring
Here’s a quick quiz. What have you learned so far?
a) Your optimal Oracle array fetch size is 2,048.
b) You should always check to make sure that you’ve
optimized your array fetch size.
c) You should always check to see where your response
time is going before you optimize anything.
Choice (a) is a really poor one, because the optimal
Oracle array fetch size for the next program you write
is probably going to be different than the optimal array
fetch size for the program we just analyzed. I haven’t
studied it in detail, but I believe that your optimal array
fetch size is a function of several factors, including at
least these:
• How big are the rows you’re returning?
• How big are your network packets?
I can easily imagine, for example, that if our rows had
been 100 times larger than they were, then our opti-
mal array fetch size might have been 100 times small-
er. However, I’ve learned enough over the years to be-
lieve that any kind of a mathematical model is useful
only as a starting point, and that you should base your
optimizations upon real operational data.
Choice (b) is therefore superior to choice (a), but what if
network response time had accounted for only 8.58% of
response time? Then the impact of successfully optimiz-
ing your network I/O would have been so diminished that
it might not be worth your time invested into doing it.
Choice (c) is therefore superior to choice (b). Not eve-
ry program you write will have response time dominated
by network I/O. There are thousands of different ways
your program can spend your user’s time. Why use
Listing 4.
24. =======================
25. PARSING IN CURSOR #1 …
26. select * from sla_run
27. END OF STMT
28. PARSE #1:c=1000,e=121,…
29. BINDS #1:
30. EXEC #1:c=0,e=57,…
31. WAIT #1: nam='SQL*Net message to client' ela= 3 …
32. WAIT #1: nam='SQL*Net message from client' ela=
5960 …

Response time (microseconds) Call
10,000 125.0% CPU service, …
–2,000 –25.0% all other
8,000 100.0% Total response
time
No 2/201234
Practice
a checklist to attack everything that might possibly be
slowing your code down when you have a tool at your
disposal (the Oracle extended SQL trace data) to show
you exactly how your code is spending its time? Why
guess? …when you can know.
Oracle’s trace files give you a language for under-
standing the performance of the code you write.
On Specification Legitimacy
I’ve just described a process of analyzing and improv-
ing performance for a query that returns 142,517 rows.
I didn’t tell you anything about how the Java program
fetching those rows was going to use those rows. I left
that to your imagination. I merely hoped you would tac-
itly assume that someone needed all those rows. Devel-
opers can tend to do that.
I, of course, chose such an example because it’s
easier to showcase the problem of too much network
I/O when you illustrate with a query that returns a lot of
rows. However, in real life, it’s fair to ask the question,
Why do you need 142,517 rows? Or even, Do you really
want all those rows?
Dan Tow (You can find Dan at http://www.singingsql.
com) and I once had a dinner conversation in which we
agreed on the following postulate:
No human ever wants to see more than 10 rows.
Our idea was that once you’re presented with more
than 10 rows to look at, all in one picture, you’d real-
ly rather see some kind of aggregation (count, sum,
mean, …whatever) of the data instead.(Our corollary, of
course, was, Auditors are not human).
So, the next time you’re asked to improve the per-
formance of a query that returns a jillion rows, at least
ask the question whether the user using your program
really, really wants to see a jillion rows in the first place.
It’s easier, cheaper, and more effective to give a user
less data (if that’s what he really wants) than to make
your program faster at returning stuff that people didn’t
really want to begin with.
trace File Guided tour #2
Let’s look at another example of some trace data that
reveals another performance antipattern you should
know about. Section Slow Insert shows an excerpt of
an Oracle trace file for a sequence of 10,000 inserts,
which consumed 32.706 seconds (about 33 seconds)
of end-user response time.
First, before we look at the trace data, let me ask you
this: Is 33 seconds a good response time for a program
that inserts 10,000 rows into a table? Or is it a bad re-
sponse time?
People’s first response when I ask a question like that
is usually either eyes-averted silence or What kind of
machine did it run on? The truth is, unless you’ve re-
cently encountered an application that did something
similar to what this program did, you probably don’t
have any idea. There’s nothing wrong with that. In a few
minutes, you’ll know the answer anyway.
So, let’s walk through the lines and see what’s going
on. The interesting action begins at line 24:
24. =====================
25. PARSING IN CURSOR #2 …
26. insert into JAVA_TEST_TABLE values ('0')
27. END OF STMT
Lines 24 through 27 indicate that cursor #2 refers to
the SQL statement that inserts a row into
java _ test _
table
.
24. PARSE #2:…,e=678,…
25. BINDS #2:
These lines indicate that an OPI parse call has con-
sumed 678 µs of response time, and that there were
no values bound to placeholders in the statement.
(That makes sense, because there aren’t any place-
holders in the statement.)
24. EXEC #2:…,e=1245,…,r=1,…
The Oracle kernel executed an OPI exec function,
which consumed 1,245 µs of response time to insert
one row.
25. XCTEND rlbk=0, rd_only=0
The kernel executed an OPI commit function (server-
side companion to OCITransCommit), which commit-
ted everything this session has done thus far to the da-
tabase.
24. WAIT #2: nam='SQL*Net message to client' ela= 2 …
25. WAIT #2: nam='SQL*Net message from client' ela= 1154 …
A network round-trip consumed 1,156 µs of response
time.
And then, in the following lines, you see the same pat-
tern over and over… (Listing 5).
(106,315 lines here are not shown)
However, you can see, on lines 42 and 53, a kind of
OS call that I haven’t shown you yet in this paper. The
Oracle name log file sync refers to a synchronization
event that takes place between the Oracle kernel proc-
ess inserting the rows and a background Oracle proc-
ess called the redo log writer. The two log file sync calls
shown here consumed 113 µs and then 107 µs of re-
sponse time.
Summarizing the elapsed time consumed by data-
base calls and OS calls (aggregating the e and ela val-
Making Friends with the Oracle Database
www.phpsolmag.com
35
ues for the individual call types) yields the information
shown in Exhibit 6.
Optimizing the insert Program
Now let’s revisit the question, Is 33 seconds a good re-
sponse time for this program? Exhibit 6. gives enough
data to prove that the answer is a resounding no. Here’s
why…
The whole point of our program is to insert 10,000
rows into a table. The Oracle function that does that
work is represented in the trace data by the
EXEC
lines.
This program spent only 2.9% of its time (less than
a second!) actually putting rows into the database; yet
I had to wait more than half a minute for the program to
complete. The remainder of the time was spent (wast-
ed?) doing other things.
Again, you can see in Exhibit 6., the dominant re-
sponse time contributor fits the excessive network I/O
antipattern. The trace file actually shows one network
round-trip for every single row that’s inserted into the
database. The next step should be obvious to you by
now: we need to find a way to insert more than one row
per network round-trip.
Here’s how the original Java code was written (List-
ing 6).
This code performs the extraordinarily nasty act of
creating 10,000 (the size of ilist) distinct SQL state-
ments within the Oracle Database. Yes, your database
administrator might tell you not to worry, that he has
a database parameter called
cursor_sharing
that will
take care of the problem.
But it won’t.
…Because your problem isn’t so much the 4.901 sec-
onds of parse work that’s being done on the Oracle Da-
tabase server as it is the 14.637 seconds of work mov-
ing data across the network from your client code to
your server code. Manipulating your Oracle Database’s
cursor_sharing
parameter may make your database ad-
ministrators happy (some of the red lights on his dash-
board will turn green), but it’s not going to make your
users happy, because their response times will still be
intolerable (The Oracle
cursor_sharing
parameter lets
your database administrator instruct the Oracle kernel
to translate your SQL statements into what those state-
ments might have looked like if you had used placehold-
ers instead of literal values. It’s a clever plan, but there
are two big problems with it. First, it puts even more
workload on your database server. More importantly, it
does nothing to reduce the quantity of network I/O that
is our example’s dominant problem.).
People can argue all they want about whether to
make adjustments to the network or the database, but
a good application developer can stop all the discus-
sion by writing the code a different way. I’ll show you
how we re-wrote it shortly. Before I get into the code
Listing 5.
24. =====================
25. PARSING IN CURSOR #2 …
26. insert into JAVA_TEST_TABLE values ('1')
27. END OF STMT
28. PARSE #2:…,e=302,…
29. BINDS #2:
30. EXEC #2:…,e=202,…,r=1,…
31. XCTEND rlbk=0, rd_only=0
32. WAIT #2: nam='log file sync' ela= 113 …
33. WAIT #2: nam='SQL*Net message to client' ela= 2 …
34. WAIT #2: nam='SQL*Net message from client' ela=
1918 …
35. =====================
36. PARSING IN CURSOR #2 …
37. insert into JAVA_TEST_TABLE values ('2')
38. END OF STMT
39. PARSE #2:…,e=232,…
40. BINDS #2:
41. EXEC #2:…,e=146,…,r=1,…
42. XCTEND rlbk=0, rd_only=0
43. WAIT #2: nam='log file sync' ela= 107 …
44. WAIT #2: nam='SQL*Net message to client' ela= 2 …
45. WAIT #2: nam='SQL*Net message from client' ela=
2050 …
exhibit 6. Response time profile of our 33-second insert program
execution
Response time (seconds) Call
14.637 44.8% SQL*Net message from client
6.389 19.5% log file sync
4.901 15.0% CPU service, parse calls
0.943 2.9% CPU service, execute calls
5.836 17.8% all other
32.706 100.0% Total response time
Listing 6.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
for (Integer i : ilist) {
String sql = String.format("insert into t values
('%d')", i);
Statement st = conn.createStatement();
st.executeUpdate(sql);
}
No 2/201236
Practice
changes, let me show you the performance improve-
ment we were able to achieve, which you can see in
Exhibit 7. That should serve as some motivation for you
to move forward.
That’s a 99.5% performance improvement, from
32.706 seconds to 0.151 seconds. More importantly,
that’s an improvement from get up from your desk slow
to click-flash fast. And it frees up over 32 seconds’ worth
of capacity on your system that allows other work to
take place without having to compete against your pro-
gram for resources.
The changes we made to the code are all in response
to what the profile in Exhibit 6. showed us. A simple
top-down walk of that profile led us to ask the following
questions:
• Do we really need to spend 14.637 seconds doing
network communications?
• Do we really need to spend 6.389 seconds execut-
ing log file sync calls?
• Do we really need to spend 4.901 seconds prepar-
ing SQL?
Now let’s answer those questions.
We’ve already seen the excessive network I/O antipat-
tern in the query example I worked through for you ear-
lier. It is related to the excessive amount of time we’ve
spent preparing SQL (those PARSE calls that showed
up in our raw trace data).
The answer here is, once again, to figure out how to
process more than one row per database call. The gen-
eral strategy we want to employ is to prepare our SQL
statement only once, bind values into that statement to
represent all our rows to be inserted, and then make
sure we’re bundling bunches of row insertions into each
database call our code motivates.
You can do that with the Java Database Connectiv-
ity API from Sun (the Sun JDBC). There are good re-
sources on the Internet to show you how (Crawford,
Farley and Flanagan 2005). We’ve found the Oracle
JDBC to be a little bit easier to use, and our expe-
rience leads us to believe it’s a little bit faster, too.
Here’s the code we used to produce our 0.151-sec-
ond example (Listing 7).
First, we imported the Oracle JDBC that’s re-
quired to use the setDefaultExecuteBatch value to
1,000. Skipping over the setAutoCommit call for
a moment, you can see that now instead of prepar-
ing thousands of distinct SQL statements inside the
loop, we’ve prepared a single SQL statement out-
side the loop, and this statement uses the
‘?’
char-
acter as a placeholder for the values to be inserted.
Using (and reusing) a PreparedStatement will save
9,999 of the OPI parse calls that plagued the original
32-second program.
Inside the loop, we now have a setInt call that binds
the value of i to the first (and only)
‘?’
placeholder in
the SQL statement, and then we have an executeUp-
date call to do the insertion. Since the default execute
batch size is now set to 1,000, this code will only make
a real Oracle database call (and the associated network
round-trip) once for every 1,000 calls.
So, with this code, we’ve saved thousands of network I/
Os and reduced the amount of time the program spends
parsing inside the database to practically zero. Magic.
The log file sync time represents another important
performance improvement opportunity (In case you’re
wondering how you’ll figure out what these event names
mean when you’re done reading this paper, I cover that
in section 18.
The repeated appearance of either log file sync calls or
XCTEND lines in a trace file (in our case, we had both) is
an indication of repeated database commit processing.
Excessive commit processing can create a performance
problem that accelerates as the amount of concurrent
workload increases (so it’s a scalability problem too), and
it even creates a functional problem as well.
Let’s talk about the functional problem first. Imagine
that you’re responsible for running this 10,000-row in-
sert once a day. Imagine that it clicks along just fine for
weeks on end, but then one day, part-way into the pro-
gram’s execution, your system housing the Oracle da-
tabase crashes. It happens.
So when someone gets the Oracle database restart-
ed, one of the things you’re going to have to do is fig-
ure out how many of the rows made it into the database
before it crashed. In fact, you’re going to have to figure
out exactly which rows made it into the database before
it crashed, because you’re going to need to make sure
that either you delete those rows so you can run your
Listing 7.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.OracleConnection;
((OracleConnection)conn).setDefaultExecuteBatch(1000);
conn.setAutoCommit(
false
);
String sql = String.format("insert into t values (?)");
PreparedStatement pst = conn.prepareStatement(sql);
for
(Integer i : ilist) {
pst.setInt(1, i);
pst.executeUpdate();
}
conn.commit();
Making Friends with the Oracle Database
www.phpsolmag.com
37
program again, or you’re going to need to figure out ex-
actly which rows you need to insert so that all 10,000
will be in the database when you’re done. None can be
missing, and none can be duplicated.
If you’re like most application developers, you’re only
going to want to do this job once. It’s not going to be a lot
of fun figuring out which rows made it and which rows
didn’t. If you’re lucky, there’ll be some kind of batch id
or date field that will allow you to pinpoint exactly which
rows made it in. Maybe you’ll be tempted to write some
kind of clean-up functions to go with your application so
that this kind of scenario will be easier for you (or your
customers) to deal with if it ever happens again.
If you use Oracle differently, however, you won’t have
to do that.
If, instead of committing every single row to the data-
base as your application inserts it, you waited until the
end of all 10,000 insertions and then committed the whole
transaction, your life would be much simpler. Then, if the
Oracle system ever failed in the midst of your job, you’d
know: either there are 10,000 new rows in the database
(if your commit call succeeded) or there are 0 new rows
in the database (if your commit call failed).
Plus, you’ll save a lot of unnecessary work that con-
sumes a lot of unnecessary time. Implementing the
single-commit idea reduced our time spent waiting for
log file sync events from 6.389 seconds (Exhibit 6.) to
0.040 seconds (Exhibit 7.). That’s huge. And we elimi-
nated over six seconds of labor that other processes on
the system had to wait behind, which makes the whole
system faster for everyone.
The decision of how often to commit is an important
one. I’ve shown here why committing after every row
can be bad. However, committing only once at the end
of a multi-million-row insert may be bad, too, because
of the stress upon the Oracle Database undo manage-
ment subsystem, and because maybe you don’t want to
have to restart a whole multi-million-row transaction af-
ter an instance failure. Maybe it would be better to com-
mit in batches of 10,000 rows, or maybe even 10 rows.
Part of your application design responsibility is to man-
age this tradeoff, which should include the task of using
measurements instead of guesses about the costs of
commit processing.
Section Improved Insert shows some of the trace data
for an execution of the new program. Notice that each
EXEC line now processes 1,000 rows (r=1000) instead
of just one. And notice that there is only one log file sync
calls now, right at the end of the trace file.
again, Measuring is Vital
So, what have you learned from this paper? It’s easy to
start thinking in absolutes about best practices. I’m sus-
picious about most so-called best practices because of
Clarke’s Fourth Law: For every expert, there is an equal
and opposite expert (Wikipedia 2008).
There is one best practice though, that I believe in
deeply: You should always measure your application’s
performance and target your optimization efforts at plac-
es where your code will benefit from it most.
As you now understand, the way I implement this
practice in the Oracle world today is with the extended
SQL trace feature. There are other ways to peek into
what the Oracle kernel is doing (Oracle’s Active Session
History (or ASH) is one name you might recognize.) but
no other way gives you such a simple sequential list-
ing of exactly where all of the time has gone for just the
code path that you’re interested in. If you learn how to
use the extended SQL trace feature, you’ll learn a lot
more about Oracle, and you’ll learn exactly what you
need to know, right when you need to learn it.
I think one of the nicest things about Oracle tracing
(and profiling in general) is that it focuses your atten-
tion where it needs to be right now. It doesn’t leave you
feeling like you have to know everything about Oracle
all at once. It’s also a feature that’s available in develop-
ment, testing, and production environments, so it’s truly
a single tool that you can use throughout your entire
software lifecycle.
As a developer, you can make it really easy to trace
your code. My team have tried to make it easy for de-
velopers to instrument code the right way by publishing
a free, open source instrumentation library for Oracle
called ILO (Method R Corporation 2008). ILO consists
of a couple of Oracle PL/SQL packages with functions
you can call from Java, PHP, or whatever language
you’re writing in. If you’re curious about how good in-
strumentation works, studying the PL/SQL within ILO is
a good place to start.
Summary
Speed is a vital feature of good software. Speed doesn’t
just happen by accident. It is a feature that you have to
design into good programs. Designing good perform-
ance into your code is extremely difficult to do without
good feedback about the speed of your code as you’re
writing it.
exhibit 7. Response time profile of our optimized 0.151-second
insert program execution.
Response time (seconds) Call
0.083 55.0% SQL*Net message
from client
0.040 26.4% log file sync
0.029 19.2% CPU service,
execute calls
–0.001 –0.5% all other
0.151 100.0% Total response
time
No 2/201238
Practice
Many Oracle application developers work under the
assumption that the Oracle Database kernel is a black
box that they shouldn’t bother to understand. But writing
code within the abstraction model that the Oracle Data-
base is merely a persistent data store almost assures
that you will write code that is slower and that wastes far
more precious computing resources than it should.
The Oracle extended SQL trace mechanism provides
the feedback that you need to write fast, efficient code
for Oracle-based systems. Extended SQL tracing re-
veals where your time is being spent inside the Oracle
Database kernel, all the way down to the individual da-
tabase or operating system subroutine call.
With this paper, I hope I have illuminated the following
key points for you:
• Oracle extended SQL trace data shows you exactly
where your code spends your user’s time.
• With trace data, you can determine whether your
code is efficient.
• If your code can run faster, tracing shows you why
and by how much. It allows you to predict the per-
formance impact of changing your code, without so
much trial and error.
• Tracing prevents you from wasting time “tuning” as-
pects of your program that won’t result in apprecia-
ble response time benefits.
• Tracing prevents the feeling that you have to know
everything about Oracle before you can write fast,
provably efficient code.
• You can use performance feedback in your pro-
grams to create self-adjusting applications that adapt
to your software’s true operational constraints.
• Not every perceived requirement is a legitimate
requirement. Tracing allows you to measure ob-
jectively the performance cost of a feature, which
in turn helps the business make better decisions
about which software features it truly requires.
• Knowing how to measure performance, and know-
ing how to determine whether the performance you
have observed is optimal are more important than
your being able to remember lists of software per-
formance best practices.
• You can make your application easier to tune and
debug by incorporating extended SQL tracing into
your code. Ideally, you should regard the ability to
measure performance at run-time as an important
functional specification.
• I hope this paper encourages you to instrument
your code, gain access to your Oracle trace data,
and study it for some of your own applications that
mean something to you and that need to be fast.
I haven’t covered everything you need to know here, of
course. For example, I haven’t discussed how connec-
tion management code paths prevent your application
from scaling. I haven’t told you any details about why ex-
cessive parse calls and excessive visits to the database
buffer cache cause an application not to scale. But I have
shown you enough to get you started upon a much more
informed path toward writing scalable, high-performance
Oracle-based applications. I’ve also included, in section
Appendix: Creating and Finding Trace Files near the end
of this paper, a list of call names that you’ll encounter fre-
quently as you look through trace files.
With this paper, you’ll be able to come a long way in
understanding what your trace files are trying to tell you.
If you’re interested in studying Oracle trace files in more
detail, then I hope you will have a look at (Millsap and
Holt, Optimizing Oracle Performance 2003), where Jeff
Holt and I have written about the subject in depth.
acknowledgments
Thank you to Harold Palacio for writing and testing the Java
code used in my examples, and to Karen Morton, Laura
Nogaj, Mark Sweeney, Ron Crisco, and Jeff Holt for review-
ing my work. I am indebted to Ken Ferlita and everyone else
at Method R for their ideas, research, encouragement, and
manifest contributions that make it possible for me to write
in the first place.
To draw the UML sequence diagram shown in Exhibit 1,
I used the nifty tool at http://www.websequencediagrams.com/.
Bibliography
• Crawford, William, Jim Farley, and David Flanagan. An Introduction to JDBC, Part 3. 2005. http://www.onjava.com/pub/a/on-
java/excerpt/javaentnut_2/index3.html (accessed 12 24, 2008).
• Method R Corporation. Instrumentation Library for Oracle (ILO). 2008. http://sourceforge.net/projects/hotsos-ilo/.
• Millsap, Cary. 'SQL*Net message to client' isn't what you might think it is. Nov 28, 2005. https://portal.hotsos.com/newsletters/
volume-i-issue-2/tips/sql-net-message-to-client-isn2019t-what-you-might-think-it-is/.
• Millsap, Cary, and Jeff Holt. Optimizing Oracle Performance. Sebastopol, CA: O'Reilly, 2003.
• Nørgaard, Mogens, et al. Oracle Insights: Tales of the Oak Table. Berkeley, CA: Apress, 2004.
• Oracle Corporation. My Oracle Support. https://metalink.oracle.com/CSP/ui/index.html.
• Oracle Call Interface Programmer's Guide." Oracle Corporation. 2008. http://download.oracle.com/docs/cd/B28359_01/app-
dev.111/b28395/toc.htm.
• Wikipedia. Clarke's three laws. Dec 17, 2008. http://en.wikipedia.org/wiki/Clarke%27s_three_laws.
• Radium. Dec 18, 2008. http://en.wikipedia.org/wiki/Radium.
Making Friends with the Oracle Database
www.phpsolmag.com
39
appendix: creating and Finding trace Files
This section describes how to create and find your own
Oracle extended SQL trace files.
turning the trace On and Off
You can turn tracing on or off by executing an Oracle
function call from your application. For Oracle Database
10g and beyond, you can turn tracing on and off with
calls to functions in the standard Oracle package called
dbms_monitor
:
dbms_monitor.session_trace_enable(null,null,true,true)
dbms_monitor.session_trace_disable(null,null)
There are other ways to do it, too, but this one is sim-
ple and secure. It will require the person acting as your
database administrator to grant you permission to exe-
cute the
dbms _ monitor
package.
If you’re using an Oracle Database version prior to
10g, then ask your database administrator to install the
Oracle package called dbms_support. There are in-
structions about how to do it at the My Oracle Support
web site (Oracle Corporation n.d.).
Finding Your trace File
When you’ve traced your code, you’ll need to find your
trace file. The process running your Oracle kernel code
writes your trace files to the operating system directory
named by an Oracle instance parameter. In Oracle ver-
sion 11, the Oracle kernel will write your trace files into
the Diag Trace directory, which you can identify by using
the following SQL statement:
select * from v$diag_info where name='Diag Trace'
In older versions of Oracle, you can identify your trace
file directory by using this SQL statement:
select * from v$parameter where name in ('user_dump_
dest','background_dump_dest')
The
user _ dump _ dest
directory is where most of your
trace files are probably going to show up. If you use Or-
acle parallel execution features, then you’ll find some of
your trace data in the
background _ dump _ dest directory
.
Different ports of Oracle use different naming conven-
tions for trace files. Your trace file names will probably
look something like one of the following:
xe_ora_10840.trc
prod7_23389_ora.trc
ora_1492_delta1.trc
ORA01215.trc
fin1_ora_11297_POSTING.trc
MERKUR_S7_FG_ORACLE_013.trc
Trace files may look different on different platforms
and different versions of Oracle, but you can count on
your trace file names containing some or all of the fol-
lowing elements:
• The string
«ora»
;
• Your Oracle instance name;
• Your Oracle kernel process id (on Microsoft Win-
dows, it will be your process’s thread id);
• If you set a
tracefile _ identifier
in your Oracle ses-
sion, then the string value of that parameter; and
• The suffix
«.trc
».
If you’re writing code that will connect to an Oracle in-
stance that someone else manages, you’ll need to co-
ordinate with that person to get permissions to read
your trace files (Method R Corporation sells an ex-
tension for Oracle SQL Developer that automates the
process of acquiring Oracle trace files for application
developers.). Without access to your trace files, opti-
mizing the code you write is going to be a lot more ex-
pensive for your company.
appendix: the important code Paths
You can learn a lot in a ten-minute session looking at
Oracle trace data, but what happens when you see
a call name that you don’t understand? This section
contains some very brief starter advice that will guide
you through the most common code paths that you’ll
see described in your trace files.
For calls not listed here, the best online sources are
probably Google and oracle.com (in that order). How-
ever, you’ll learn more reliably by learning to use a tool
like Dtrace or strace (or truss, sctrace, tusc, …whatever
OS call tracing tool your platform gives you) to see what
OS call each Oracle call name maps to. Once you’re to
that point, it’s easy: there is lots of reliable documenta-
tion for OS calls available all over the place, all the way
down to the source code level for many operating sys-
tems.
Here are some Oracle kernel code paths you’re going
to be seeing over and over again:
EXEC
If your execution has a query component, see the
FETCH

entry. If you are executing a PL/SQL package with no
real time consuming SQL statements within it, then use
the Oracle dbms_profiler package to identify which lines
of PL/SQL are costing you the most time.
FETCH
Don’t visit the buffer cache (the value cr + cu from your
Oracle extended SQL trace data) more than 10 times
per row returned per data source. For example, a 4-ta-
ble join that returns 3 rows should have cr + cu ≤ 120.
No 2/201240
Practice
PARSE
A good application never parses (that is, prepares) a given
SQL statement more than once per session. A great appli-
cation never parses a given SQL statement more than once
per Oracle instance startup. Parsing too often prevents an
application from scaling to large user counts, no matter
how many CPUs your system might happen to have. It’s
because parsing is a software-serialized operation.
buffer busy waits
Happens to your program when it tries to change an in-
memory block in the database buffer cache, but some
other process is in the midst of modifying that buffer. Fix
the problem by working with your database administra-
tor to make the most competed-for database blocks less
interesting to so many concurrent Oracle sessions.
db file scattered read
Indicates a read of two or more Oracle blocks in a single
readv OS call. Conventional advice instructs your data-
base administrator to make the database buffer cache
bigger when he sees lots of this kind of OS call. Better
advice is to write more efficient queries that follow the
rule-of-ten advice explained in the FETCH section.
db file sequential read
Normally indicates the read of a single Oracle block with
a pread OS call. If you can eliminate unnecessary buff-
er cache visits as directed in the FETCH section, you’ll
naturally eliminate db file sequential read calls as well.
enqueue
Indicates that your code is trying to change a row that
another Oracle session has locked. If you wrote the pro-
gram that’s holding the lock, then rewrite it to hold the
lock for a smaller total duration. For example, don’t al-
low any end-user data entry opportunities to occur be-
tween an insert, update, delete, or merge SQL state-
ment and its subsequent commit. If you didn’t write the
code that’s holding the lock, then find out who did.
latch* (call names with the word latch in them)
You’ll see shared pool or library cache latch waits (may-
be both of them) when you write code that makes too
many parse calls. You’ll see cache buffers chains or
cache buffers lru chain latch waits when you write in-
efficient SQL that visits the database buffer cache too
many times. Don’t do those things.
Note that in Oracle version 10 and beyond, the names
of many latch-related OS calls each contains the name
of the latch, as in latch: cache buffers chains. Prior to
version 10, all latch-related OS calls were recorded un-
der the call name latch free, and the type of the latch
was listed as the value of the p2 field in the WAIT line.
log file sync
Indicates commit call processing, which you can eas-
ily abuse when, for example, you write applications that
use auto-commit.
SQL*Net message from client
Indicates a network round-trip. Don’t make unnecessary
network round-trips. See the PARSE section for how to
eliminate unnecessary parse calls and the associated
round-trips. It’s almost never a good idea to write appli-
cations that process only one database row at a time.
If you’re sloppy in how you collect your trace data for
interactive applications, then some of the time included
in your SQL*Net message from client durations will be
time that the end user spends regarding the data just
presented to him. This is a false negative performance
indicator that you should fix by being more careful about
how you collect your trace data. Scope your trace data
collecting to include only the time that your end user is
waiting on the application to perform some operation.
SQL*Net more data to client
This is what you’ll see when you crank up your array
fetch size for programs that select a lot of rows. It’s like
a
SQL*Net message to client
, except it’s in the context
of a single data transfer that’s already in progress.
unaccounted-for
If you use a commercial profiling tool like the Method
R Profiler (also sold as the Hotsos Profiler), the pres-
ence of unaccounted-for time almost always indicates
time that your process has spent preempted by the op-
erating system. Fix it by making your program (and the
programs it competes against for CPU time) use as lit-
tle CPU as possible. Eliminate unnecessary parse calls,
and ensure that your SQL visits the database buffer
cache as little as possible.
appendix: trace Files
This section contains excerpts from the raw Oracle ex-
tended SQL trace data referenced in this paper.
Slow Query
The following trace file excerpt shows trace data for the
23.405-second query program (Listing 8).
Listing 8.
1. /usr/lib/oracle/xe/app/oracle/admin/XE/udump/xe_ora_9024_METHODR_TESTING_.trc
2. Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Making Friends with the Oracle Database
www.phpsolmag.com
41
3. ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server
4. System name: Linux
5. Node name: oracle01.dev.method-r.com
6. Release: 2.6.25.6-27.fc8
7. Version: #1 SMP Fri Jun 13 16:38:52 EDT 2008
8. Machine: i686
9. Instance name: XE
10. Redo thread mounted by this instance: 1
11. Oracle process number: 18
12. Unix process pid: 9024, image: oracleXE@oracle01.dev.method-r.com
13.
14. *** SERVICE NAME:(SYS$USERS) 2008-11-25 11:12:29.682
15. *** SESSION ID:(26.22686) 2008-11-25 11:12:29.682
16. =====================
17. PARSING IN CURSOR #2 len=69 dep=0 uid=55 oct=42 lid=55 tim=1198860497736569 hv=3164292706 ad='30e088a4'
18. alter session set events '10046 trace name context forever, level 12'
19. END OF STMT
20. EXEC #2:c=1000,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1198860497736561
21. XCTEND rlbk=0, rd_only=1
22. WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198860497737042
23. WAIT #2: nam='SQL*Net message from client' ela= 985 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198860497738097
24. =====================
25. PARSING IN CURSOR #1 len=21 dep=0 uid=55 oct=3 lid=55 tim=1198860497738418 hv=3808180571 ad='2d45e9d0'
26. select * from sla_run
27. END OF STMT
28. PARSE #1:c=0,e=251,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1198860497738414
29. BINDS #1:
30. EXEC #1:c=0,e=84,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1198860497738645
31. WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198860497738727
32. WAIT #1: nam='SQL*Net message from client' ela= 25227 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198860497764024
33. WAIT #1: nam='SQL*Net message to client' ela= 24 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198860497764438
34. FETCH #1:c=0,e=355,p=0,cr=4,cu=0,mis=0,r=10,dep=0,og=1,tim=1198860497764514
35. WAIT #1: nam='SQL*Net message from client' ela= 7851 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198860497772417
36. WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198860497772500
37. FETCH #1:c=0,e=84,p=0,cr=1,cu=0,mis=0,r=10,dep=0,og=1,tim=1198860497772568
38. WAIT #1: nam='SQL*Net message from client' ela= 1306 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198860497773924
39. WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198860497773985
40. FETCH #1:c=0,e=81,p=0,cr=1,cu=0,mis=0,r=10,dep=0,og=1,tim=1198860497774051
41. WAIT #1: nam='SQL*Net message from client' ela= 1282 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198860497775378
(42,746 lines here are not shown)
42. WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198860521138223
43. FETCH #1:c=0,e=86,p=0,cr=1,cu=0,mis=0,r=7,dep=0,og=1,tim=1198860521138298
44. WAIT #1: nam='SQL*Net message from client' ela= 2141 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198860521140722
No 2/201242
Practice
improved Query
The following trace file excerpt shows trace data for the improved 2.400-second insert program.
Listing 9.
1. /usr/lib/oracle/xe/app/oracle/admin/XE/udump/xe_ora_28638_QUERY_2048.trc
2. Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
3. ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server
4. System name: Linux
5. Node name: oracle01.dev.method-r.com
6. Release: 2.6.25.6-27.fc8
7. Version: #1 SMP Fri Jun 13 16:38:52 EDT 2008
8. Machine: i686
9. Instance name: XE
10. Redo thread mounted by this instance: 1
11. Oracle process number: 18
12. Unix process pid: 28638, image: oracleXE@oracle01.dev.method-r.com
13.
14. *** SERVICE NAME:(SYS$USERS) 2008-12-10 09:51:02.061
15. *** SESSION ID:(26.27749) 2008-12-10 09:51:02.061
16. =====================
17. PARSING IN CURSOR #2 len=69 dep=0 uid=55 oct=42 lid=55 tim=1200121349669845 hv=3164292706 ad='30d22bc0'
18. alter session set events '10046 trace name context forever, level 12'
19. END OF STMT
20. EXEC #2:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1200121349669837
21. XCTEND rlbk=0, rd_only=1
22. WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1200121349670096
23. WAIT #2: nam='SQL*Net message from client' ela= 869 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1200121349671014
24. =====================
25. PARSING IN CURSOR #1 len=21 dep=0 uid=55 oct=3 lid=55 tim=1200121349671182 hv=3808180571 ad='2d7dd08c'
26. select * from sla_run
27. END OF STMT
28. PARSE #1:c=1000,e=121,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1200121349671176
29. BINDS #1:
30. EXEC #1:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1200121349671305
31. WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1200121349671351
32. WAIT #1: nam='SQL*Net message from client' ela= 5960 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1200121349677368
33. WAIT #1: nam='SQL*Net message to client' ela= 6 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1200121349677583
34. WAIT #1: nam='SQL*Net more data to client' ela= 149 driver id=1952673792 #bytes=2001 p3=0 obj#=-1
tim=1200121349677942
35. WAIT #1: nam='SQL*Net more data to client' ela= 7 driver id=1952673792 #bytes=2002 p3=0 obj#=-1
tim=1200121349678167
36. WAIT #1: nam='SQL*Net more data to client' ela= 9 driver id=1952673792 #bytes=2000 p3=0 obj#=-1
tim=1200121349678404
37. WAIT #1: nam='SQL*Net more data to client' ela= 7 driver id=1952673792 #bytes=2006 p3=0 obj#=-1
tim=1200121349678670
38. WAIT #1: nam='SQL*Net more data to client' ela= 8 driver id=1952673792 #bytes=1998 p3=0 obj#=-1
tim=1200121349678893
39. WAIT #1: nam='SQL*Net more data to client' ela= 10 driver id=1952673792 #bytes=2000 p3=0 obj#=-1
tim=1200121349679321
40. WAIT #1: nam='SQL*Net more data to client' ela= 7 driver id=1952673792 #bytes=2000 p3=0 obj#=-1
tim=1200121349679641
41. WAIT #1: nam='SQL*Net more data to client' ela= 3402 driver id=1952673792 #bytes=2002 p3=0 obj#=-1
tim=1200121349683312
Making Friends with the Oracle Database
www.phpsolmag.com
43
42. WAIT #1: nam='SQL*Net more data to client' ela= 132 driver id=1952673792 #bytes=2000 p3=0 obj#=-1
tim=1200121349683800
43. WAIT #1: nam='SQL*Net more data to client' ela= 121 driver id=1952673792 #bytes=2002 p3=0 obj#=-1
tim=1200121349684161
44. WAIT #1: nam='SQL*Net more data to client' ela= 8 driver id=1952673792 #bytes=2001 p3=0 obj#=-1
tim=1200121349684409
45. WAIT #1: nam='SQL*Net more data to client' ela= 10 driver id=1952673792 #bytes=2000 p3=0 obj#=-1
tim=1200121349684666
46. WAIT #1: nam='SQL*Net more data to client' ela= 6 driver id=1952673792 #bytes=2002 p3=0 obj#=-1
tim=1200121349684920
47. WAIT #1: nam='SQL*Net more data to client' ela= 828 driver id=1952673792 #bytes=2000 p3=0 obj#=-1
tim=1200121349685966
48. WAIT #1: nam='SQL*Net more data to client' ela= 123 driver id=1952673792 #bytes=2002 p3=0 obj#=-1
tim=1200121349686428
49. WAIT #1: nam='SQL*Net more data to client' ela= 68 driver id=1952673792 #bytes=2000 p3=0 obj#=-1
tim=1200121349686696
50. WAIT #1: nam='SQL*Net more data to client' ela= 7 driver id=1952673792 #bytes=2001 p3=0 obj#=-1
tim=1200121349686943
51. WAIT #1: nam='SQL*Net more data to client' ela= 8 driver id=1952673792 #bytes=2003 p3=0 obj#=-1
tim=1200121349687223
52. WAIT #1: nam='SQL*Net more data to client' ela= 6 driver id=1952673792 #bytes=1999 p3=0 obj#=-1
tim=1200121349687468
53. WAIT #1: nam='SQL*Net more data to client' ela= 9 driver id=1952673792 #bytes=2002 p3=0 obj#=-1
tim=1200121349687736
54. WAIT #1: nam='SQL*Net more data to client' ela= 6 driver id=1952673792 #bytes=2000 p3=0 obj#=-1
tim=1200121349688054
55. WAIT #1: nam='SQL*Net more data to client' ela= 2047 driver id=1952673792 #bytes=2002 p3=0 obj#=-1
tim=1200121349690352
56. WAIT #1: nam='SQL*Net more data to client' ela= 11 driver id=1952673792 #bytes=2000 p3=0 obj#=-1
tim=1200121349690646
57. WAIT #1: nam='SQL*Net more data to client' ela= 7 driver id=1952673792 #bytes=2001 p3=0 obj#=-1
tim=1200121349690905
58. WAIT #1: nam='SQL*Net more data to client' ela= 7 driver id=1952673792 #bytes=2001 p3=0 obj#=-1
tim=1200121349691160
59. WAIT #1: nam='SQL*Net more data to client' ela= 8 driver id=1952673792 #bytes=2006 p3=0 obj#=-1
tim=1200121349691597
60. WAIT #1: nam='SQL*Net more data to client' ela= 8 driver id=1952673792 #bytes=1997 p3=0 obj#=-1
tim=1200121349691851
61. WAIT #1: nam='SQL*Net more data to client' ela= 7 driver id=1952673792 #bytes=2001 p3=0 obj#=-1
tim=1200121349692248
62. WAIT #1: nam='SQL*Net more data to client' ela= 7 driver id=1952673792 #bytes=2002 p3=0 obj#=-1
tim=1200121349692507
63. WAIT #1: nam='SQL*Net more data to client' ela= 5125 driver id=1952673792 #bytes=2003 p3=0 obj#=-1
tim=1200121349697850
64. WAIT #1: nam='SQL*Net more data to client' ela= 11 driver id=1952673792 #bytes=1997 p3=0 obj#=-1
tim=1200121349698254
65. WAIT #1: nam='SQL*Net more data to client' ela= 9 driver id=1952673792 #bytes=2005 p3=0 obj#=-1
tim=1200121349698541
66. WAIT #1: nam='SQL*Net more data to client' ela= 7 driver id=1952673792 #bytes=2003 p3=0 obj#=-1
tim=1200121349698782
67. WAIT #1: nam='SQL*Net more data to client' ela= 10 driver id=1952673792 #bytes=1999 p3=0 obj#=-1
tim=1200121349699020
68. WAIT #1: nam='SQL*Net more data to client' ela= 10 driver id=1952673792 #bytes=1997 p3=0 obj#=-1
tim=1200121349699464
69. FETCH #1:c=11998,e=22206,p=0,cr=16,cu=0,mis=0,r=2048,dep=0,og=1,tim=1200121349699630
(2,859 lines here are not shown)
70. FETCH #1:c=6999,e=6837,p=0,cr=16,cu=0,mis=0,r=1205,dep=0,og=1,tim=1200121352051111
71. WAIT #1: nam='SQL*Net message from client' ela= 18557 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1200121352069816
No 2/201244
Practice
Slow insert
The following trace file excerpt shows trace data for the slow 32.706-second insert program.
Listing 10.
1. /usr/lib/oracle/xe/app/oracle/admin/XE/udump/xe_ora_9835_METHODR_TESTING_.trc
2. Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
3. ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server
4. System name: Linux
5. Node name: oracle01.dev.method-r.com
6. Release: 2.6.25.6-27.fc8
7. Version: #1 SMP Fri Jun 13 16:38:52 EDT 2008
8. Machine: i686
9. Instance name: XE
10. Redo thread mounted by this instance: 1
11. Oracle process number: 21
12. Unix process pid: 9835, image: oracleXE@oracle01.dev.method-r.com
13.
14. *** SERVICE NAME:(SYS$USERS) 2008-11-25 11:39:16.895
15. *** SESSION ID:(24.25822) 2008-11-25 11:39:16.895
16. =====================
17. PARSING IN CURSOR #3 len=69 dep=0 uid=55 oct=42 lid=55 tim=1198862067281227 hv=3164292706 ad='30e088a4'
18. alter session set events '10046 trace name context forever, level 12'
19. END OF STMT
20. EXEC #3:c=0,e=71,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1198862067281220
21. XCTEND rlbk=0, rd_only=1
22. WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198862067281755
23. WAIT #3: nam='SQL*Net message from client' ela= 3633 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198862067285447
24. =====================
25. PARSING IN CURSOR #2 len=40 dep=0 uid=55 oct=2 lid=55 tim=1198862067286178 hv=223277221 ad='30d87524'
26. insert into JAVA_TEST_TABLE values ('0')
27. END OF STMT
28. PARSE #2:c=0,e=678,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1198862067286173
29. BINDS #2:
30. EXEC #2:c=2000,e=1245,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=1,tim=1198862067287560
31. XCTEND rlbk=0, rd_only=0
32. WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198862067288169
33. WAIT #2: nam='SQL*Net message from client' ela= 1154 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198862067289370
34. =====================
35. PARSING IN CURSOR #2 len=40 dep=0 uid=55 oct=2 lid=55 tim=1198862067289787 hv=1228865179 ad='2d6c3ea4'
36. insert into JAVA_TEST_TABLE values ('1')
37. END OF STMT
38. PARSE #2:c=0,e=302,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1198862067289781
39. BINDS #2:
40. EXEC #2:c=0,e=202,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=1,tim=1198862067290111
41. XCTEND rlbk=0, rd_only=0
42. WAIT #2: nam='log file sync' ela= 113 buffer#=737 p2=0 p3=0 obj#=-1 tim=1198862067290479
43. WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198862067290546
Making Friends with the Oracle Database
www.phpsolmag.com
45
improved insert
The following trace file excerpt shows trace data for the improved 0.154-second insert program.
44. WAIT #2: nam='SQL*Net message from client' ela= 1918 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198862067292503
45. =====================
46. PARSING IN CURSOR #2 len=40 dep=0 uid=55 oct=2 lid=55 tim=1198862067292826 hv=2978669578 ad='2d75ac84'
47. insert into JAVA_TEST_TABLE values ('2')
48. END OF STMT
49. PARSE #2:c=0,e=232,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1198862067292821
50. BINDS #2:
51. EXEC #2:c=0,e=146,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=1,tim=1198862067293093
52. XCTEND rlbk=0, rd_only=0
53. WAIT #2: nam='log file sync' ela= 107 buffer#=739 p2=0 p3=0 obj#=-1 tim=1198862067293425
54. WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198862067293477
55. WAIT #2: nam='SQL*Net message from client' ela= 2050 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1198862067295563
(106,315 lines here are not shown)
Listing 11.
1. /usr/lib/oracle/xe/app/oracle/admin/XE/udump/xe_ora_3807_INSERT_TEST.trc
2. Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
3. ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server
4. System name: Linux
5. Node name: oracle01.dev.method-r.com
6. Release: 2.6.25.6-27.fc8
7. Version: #1 SMP Fri Jun 13 16:38:52 EDT 2008
8. Machine: i686
9. Instance name: XE
10. Redo thread mounted by this instance: 1
11. Oracle process number: 22
12. Unix process pid: 3807, image: oracleXE@oracle01.dev.method-r.com
13.
14. *** SERVICE NAME:(SYS$USERS) 2008-12-22 16:37:52.283
15. *** SESSION ID:(24.34335) 2008-12-22 16:37:52.283
16. =====================
17. PARSING IN CURSOR #2 len=69 dep=0 uid=55 oct=42 lid=55 tim=1201157687776660 hv=3164292706 ad='28e47760'
18. alter session set events '10046 trace name context forever, level 12'
19. END OF STMT
20. EXEC #2:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1201157687776652
21. XCTEND rlbk=0, rd_only=1
22. WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1201157687776907
23. WAIT #2: nam='SQL*Net message from client' ela= 60441 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1201157687837388
24. =====================
25. PARSING IN CURSOR #1 len=39 dep=0 uid=55 oct=2 lid=55 tim=1201157687837622 hv=2094431222 ad='28f82278'
26. Insert into JAVA_TEST_TABLE values (:1)
27. END OF STMT
28. PARSE #1:c=0,e=160,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1201157687837613
29. BINDS #1:
30. kkscoacd
31. Bind#0
No 2/201246
Practice
32. oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
33. oacflg=03 fl2=1000000 frm=01 csi=178 siz=24 off=0
34. kxsbbbfp=b7f27e2c bln=22 avl=01 flg=05
35. value=0
36. WAIT #1: nam='SQL*Net more data from client' ela= 27 driver id=1952673792 #bytes=3 p3=0 obj#=-1
tim=1201157687844067
37. WAIT #1: nam='SQL*Net more data from client' ela= 11 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1201157687844425
38. EXEC #1:c=7999,e=7941,p=0,cr=396,cu=499,mis=0,r=1000,dep=0,og=1,tim=1201157687845652
39. WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1201157687845745
40. WAIT #1: nam='SQL*Net message from client' ela= 4885 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1201157687850661
41. BINDS #1:
42. kkscoacd
43. Bind#0
44. oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
45. oacflg=03 fl2=1000000 frm=01 csi=178 siz=24 off=0
46. kxsbbbfp=b7f27e2c bln=22 avl=02 flg=05
47. value=1000
48. WAIT #1: nam='SQL*Net more data from client' ela= 17 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1201157687851385
49. WAIT #1: nam='SQL*Net more data from client' ela= 14 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1201157687852578
50. EXEC #1:c=1999,e=2057,p=0,cr=131,cu=169,mis=0,r=1000,dep=0,og=1,tim=1201157687852764
(83 lines here are not shown)
51. EXEC #1:c=2999,e=2397,p=0,cr=236,cu=299,mis=0,r=1000,dep=0,og=1,tim=1201157687882375
52. WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1201157687882418
53. WAIT #1: nam='SQL*Net message from client' ela= 1841 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1201157687884288
54. BINDS #1:
55. kkscoacd
56. Bind#0
57. oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
58. oacflg=03 fl2=1000000 frm=01 csi=178 siz=24 off=0
59. kxsbbbfp=b7f27e2c bln=22 avl=02 flg=05
60. value=9000
61. WAIT #1: nam='SQL*Net more data from client' ela= 17 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1201157687884646
62. WAIT #1: nam='SQL*Net more data from client' ela= 12 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1201157687885716
63. EXEC #1:c=1000,e=1539,p=0,cr=118,cu=153,mis=0,r=1000,dep=0,og=1,tim=1201157687885866
64. WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1201157687885907
65. WAIT #1: nam='SQL*Net message from client' ela= 398 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1201157687886334
66. XCTEND rlbk=0, rd_only=0
67. WAIT #0: nam='log file sync' ela= 39869 buffer#=376 p2=0 p3=0 obj#=-1 tim=1201157687927436
68. WAIT #0: nam='SQL*Net message to client' ela= 4 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1201157687927533
69. WAIT #0: nam='SQL*Net message from client' ela= 1743 driver id=1952673792 #bytes=1 p3=0 obj#=-1
tim=1201157687929301
70. XCTEND rlbk=0, rd_only=1
Cary Millsap
Cary Millsap is well-known in the global Oracle community as a speaker, educator, consultant, and writer. He is the founder and president of Method R Corporation
(http://method-r.com), a small company devoted to genuinely satisfying software performance. Method R offers consulting services, education courses, and soft-
ware tools—including the Method R Profiler—that help you optimize your software performance.
Cary is the author (with Jeff Holt) of Optimizing Oracle Performance (O’Reilly), for which he and Jeff were named Oracle Magazine’s 2004 Authors of the Year. He is
also a contributor to Oracle Insights: Tales of the Oak Table (Apress). He is the former Vice President of Oracle Corporation’s System Performance Group, and a co-
founder of Hotsos. Cary is also an Oracle ACE Director and a founding partner of the Oak Table Network, an informal association of “Oracle scientists” that are well
known throughout the Oracle community. Cary blogs at http://carymillsap.blogspot.com.