For Better, Faster, Smarter,
Oracle 9i Database Performance Enhancements
This article discusses what's changed in Oracle 9i with respect to performance and what these changes mean to you as
a developer/dba and to the business you support.
Performance is nearly always a big issue with any application using a database of any significant size - how do you
get the information in quickly? How do you get the information out quickly? And as the size of databases increases
and the number of users and types of usage increase, this issue is not going to go away, so what enhancements have
been made for Oracle 9i? Let’s take a look.
Improvements in this area include:-
Real Application Clusters (previously called Parallel Server by
Oracle) which enable applications to run on multiple (grouped)
servers without modification.
native compilation of PL/SQL
reduction in latch contention (SGA locks) and I/O improvements
better Java performance (better compilation, improved garbage
and distributed database performance enhancements
Up until Oracle 9i, PL/SQL was always compiled into an intermediate
code that has to be executed by the PLSQL engine in the database
server or in the Forms/Reports PL/SQL engine (in much the same way
that Java programs are executed by the Java Virtual Machine). This
meant that the code could be easily transferred from the development
environment to the production environment without any change and even from the server to the client - but this
portability came at the price of a certain loss of speed.
From Oracle 9i onwards this performance penalty can be overcome by specifying that PLSQL procedures be
compiled into native C code, so that they can be compiled by the C compiler and stored in shared libraries which
automatically links them into the Oracle process.
This can be done both for Oracle-supplied packages and user-written packages. Another point worth mentioning is
that there are no restrictions on calling compiled packages from interpreted packages or vice versa - you can mix and
match to please yourself.
Native compilation however only benefits CPU-intensive routines - it doesn't have much of an effect on procedures
that perform a lot of SQL as the SQL statements are executed separately by the Oracle server process (in the SQL
General I/O and Latch Contention Enhancements
Waiting for I/O and for locks can be a big problem in a large multi-user systems. In Oracle 9i, latch contention
(waiting to acquire an internal lock on parts of the SGA) has been reduced providing a big performance boost to
applications with high transaction throughput.
General I/O Enhancements - Index Skip Scan
General I/O improvements include index skip scans which improve the performance of composite indexes where the
leading column of the index is not specified in a query and where the leading column has few distinct values. In this
case the optimiser logically divides the index into sub-indexes - with one sub-index for each distinct value in the
leading column in the index.
For example. let’s suppose our database tracks projects and allows an employee to be assigned to more than1 project
at a time and a project to have more than one employee assigned to it at a time. This creates a many-to-many
relationship between the employee and project entities which is resolved by creating the following table :
emp_proj (proj_id number, emp_id number)
With an index ep_pk on (proj_id, emp_id) and as we have just a few large projects compared to the number
of employees, then for this query:
SELECT * FROM emp_proj loyee WHERE emp_id BETWEEN 1 AND 500;
when it is executed, the proj_id column in the index won’t be scanned, but, for each of the distinct values in the
leading index column (proj_id), the sub-indexes will be scanned.
Enhanced Java Performance
Oracle 9i has enhanced server-side Java performance by improving native compilation, reducing memory leaks by
enhancing garbage collection and increasing object sharing and session pinning. The performance of Java applications
on the middle tier and the client is lso enhanced due to changes to .SQLJ and ODBCJ.
Distributed Database Enhancements
Improvements in this area include better inter-database communication and support for distributed query optimization
in the optimism.
For help with tuning your database see our series of articles on Oracle performance tuning. See the Oracle9i Database
Performance Tuning Guide and Reference for more details on these features or see our overview of the new features.
Looking to sky-rocket productivity, slash costs and accelerate innovation?
Training is a highly cost-effective, proven method of boosting productivity leaving time, money and staff available for
more innovation. Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities
across the UK as well as self-study online training. See our training course schedule, or let us know your
Oracle tips and tricks
Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and
make your systems better, faster and smarter.
Smartsoft Computing Ltd, Bristol, England
Tel: 0845 003 1320
This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such
as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
© Copyright Smartsoft Computing Ltd 2014. All rights reserved.