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 collection) • and distributed database performance enhancements

PL/SQL 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 engine).

General I/O and Latch Contention Enhancements

Latch Contention

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 requirements. 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 Contact Us View our privacy policy 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 woopra privacy policy.  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.