Submit Your Questions subscribe to our ezine
What's changed in Oracle 9i with respect to performance and what will these changes mean to you as a developer/dba and to the business you support.
Obviously, as Oracle is a high-end database, the performance enhancements have been focused on the needs of large enterprises, particularly in terms of e-business systems but applicable to all types of application that want better performance (is there any that doesn't ?). However, this is not a book, just an overview of what's changed so you can experiment and dig deeper for yourselves. ( see Oracle 9i New Features by Robert Freeman for more details, see resources section)
New subscribers can read the Oracle 9i New
Features Overview article and articles on general Oracle
performance tuning
Just to give a quick overview of what we'll be discussing - the new features of Oracle 9i in terms of performance enhancements include:
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 have Oracle done? Let's find out.
Up until now PL/SQL has been compiled into an intermediate code that has to be executed by the PLSQL engine in the 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 was portable - it 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.
This performance penalty can now 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 will only benefit CPU-intensive routines - it won't have much of an effect on procedures that perform a lot of SQL as the SQL statements are executed by the Oracle server process separately.
Waiting for I/O and for locks can be a big problem in a large multi-user systems. In Oracle 9i, latch contention (i.e. waiting to acquire an internal lock on parts of the SGA) has been reduced thereby giving a big performance boost to applications with high transaction throughput.
I/O improvements include index skip/scans which improve the performance of composite indexes where the the leading column of the index is not specified (i.e. skipped) 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 if we had an employee table containing the columns, dept_id, emp_id and emp_name with an index on (dept_id, emp_id) and furthermore we had only a few departments compared to the number of employees, then for a query as follows :
SELECT * FROM emp WHERE emp_id BETWEEN 1 AND 500;
the execution would not scan the dept_id column in the index, but the sub-indexes would be scanned for each of the distinct values in the leading index column (in this case dept_id).
Another big improvement has been made in the performance of clusters by using a new "thin" communication protocol called VI (Virtual Interface) which relies on dedicated, high-speed networking hardware, instead of the CPU, to perform most of the work. Oracle state that this can produce a 10% performance increase overall. Oracle have also improved client-server performance by optimizing the network interface, improving virtual circuit I/O and having a unified event/wait model.
Oracle 9i has improved the performance of server-side Java routines by improving the compiler to give better native compilation, enhancing garbage collection to reduce memory leaks, increasing the amount of object sharing and increasing session pinning. Both SQLJ and ODBCJ have also been improved to increase the performance of Java applications on the middle tier (the application server) and the client.
Improvements in this area include better inter-database communication and support for distributed query optimization in the optimism.
Oracle continue to work hard to improve database performance particularly for e-business applications. A large number of fundamental changes have been made to Oracle in the last few releases, so many that developers and DBAs and the companies they work have been struggling to keep pace with all the changes. Fortunately it looks as if the rate of change has slowed significantly due to the dotcom bust, so this will give time for everyone to consolidate and catch up.
---------------------------------------
Looking for more Oracle tips and tricks ?
Take a short cut now and subscribe to our ezine. Published monthly, it's jam-packed full of tips and tricks to help you make more of your Oracle systems and save you hours of blood, sweat and tears searching for information. Subscribe today and your first issue will soon be winging its way to your mailbox.
Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320
Contact Us
Click here to view our privacy policy .
This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright Smartsoft Computing Ltd 2001-2008. All rights reserved.