Author Bill Ennis

Purpose

The purpose of this whitepaper is to discuss information gathered at a recent Oracle event held in Chicago. It is assumed that the reader has familiarity with Oracle and/or relational database technology. IDevelop is a world tour event sponsored by Oracle, Sun, HP, and Intel. The event had several tracks covering the Oracle database, database management tools, application server, and application development tools.

Abstract

During the conference I sat in on sessions with the following topics:

• Oracle XML application development tools/libraries
• Oracle internet file system
• Oracle Enterprise Manager
• Developing Multi Lingual applications with Oracle
• Oracle Jdeveloper integrated development environment
• A peek at Oracle 9i
• Resource Consumer Groups
• Locally Managed Tablespaces
• Rebuilding indexes online
• Support for drop column on tables
• Log Miner

Oracle XML application development libraries

I attended a "hands-on" session for using XML with Oracle. To be quite honest the session was poor at best. One thing I was able to gather was that the XML SQL utility could prove to be quite useful. This tool/API allows use to generate XML in coordination with an SQL select statement.

Oracle Internet File System

The Oracle Internet File System (iFs) comes with Oracle 8i. IFs provides an alternative to the Windows and Unix file systems. It also offers integration between application data files and enterprise data. Application files produced from Word, Excel, etc. can be indexed and stored in an Oracle database along side data that you would normally store in a database. The advantage here is one stop shopping for your data, in addition to enhanced performance for finding data stored in files not traditionally stored in a relational database. Binary files produced from applications such as Word are indexed with the use of Oracles’ interMedia text cartridge. These files can be read with specialized plug-ins dependent on their file type.

The presentation included a demo for a file search using Windows find vs. the same search using iFs. The performance using iFs was impressive. IFs also supports content management (i.e. document check in/out). It also supports a web interface to the file system.

One concern with using iFs is that it does not yet support NFS (it should be in the next version). Also, extensive thought should be given to the security policies that are assigned for access to documentation that was previously governed by the Windows or Unix file systems.

IFs can be extended and customized. It is implemented in java and a developer’s kit is available at the Oracle Technology Network (OTN, otn.oracle.com).

Enterprise Manager (version 2.1)

Enterprise Manager is a management tool normally used by a DBA. It provides tools to assist in the management of an Oracle server or group of servers. In Enterprise Manager you can perform tasks to manage users and security, data storage, database objects (tables, stored procedures, etc), as well as perform monitoring of your database.

An optional piece of Enterprise Manager is the use of a management server. Basically, a management server is a background process that acts an interface between the enterprise manager GUI and an Oracle database server. Some features in the Enterprise Manager GUI are not available without a management server; such as import/export, performance analysis, and monitoring.

The most essential interface of Enterprise Manager is DBA Studio. DBA Studio can be invoked with or without a management server. Within DBA studio you can create database users and roles, create an add database storage, monitor and manage user sessions, or even startup/shutdown the database. A new feature in DBA Studio 2.1 is a table data editor where you can edit data in tables with a spreadsheet like interface. SQL Server enterprise manager has this and it is a welcome addition. Stored Procedure development can also be done within Enterprise Manager.

Enterprise Manager also offers add on packages that assist in performance tuning, capacity planning, and database change management. These packages can be purchased on top of the default functionality that comes with the Enterprise Manager base product.

One nuance I noticed with version 2.1 was the disappearance of the right click menu popup. I use this extensively for any SQL that the tool generates so that I can copy into a script file. However, copy and paste is still available by highlighting text and using ctrl-c/ctrl-v.

Creating Multi Lingual Applications with Oracle 8I

Oracle 8I supports UTF-8 character encoding. If you wish to use this encoding you must create your database with a special configuration option that supports the UTF-8 character set. Issues that must be dealt with in multi-lingual applications include sorting, indexing, monetary and date formats, and calendars. Oracle does offer linguistic indexes to account for sorting differences; however these introduce potential performance problems in OLTP applications as well as space utilization. This is because if you use linguistic indexes you most likely need to carry indexes for each language that you intent on supporting. It is important to note that Oracle is only offering a piece of the total overall solution. There are many other issues to deal with when developing multi-lingual applications such as colors, cultural considerations, etc.

JDeveloper (version 3.1)

JDeveloper is an Oracle development environment for Java. It includes features to assist in development, deployment, and debugging. JDeveloper comes with an integrated web server and servlet engine. It also includes wizards for creating JSP's and Servlets. One key feature was its ability to compile JSP's within a JSP development environment. In past projects that I have been involved with JSP compilation errors were occurring after handoff from the JSP developer to the server side developer and proved time consuming. Jdeveloper is available for free.

Resource Consumer Groups

A feature new to Oracle 8I is resource consumer groups. These allow us to associate a percentage of a machines processing power to a group in an effort to best divvy up the available resources. As an example, let's say we create a resource group that performance is critical to. We may assign them 70% to the server processing power. Consequently, we have 30% to assign to any other groups we see needed. In addition, we can assign priority to users within a consumer group for an additional level of control.

Locally Managed Tablespaces

Before 8I all table segment extents were assigned by looking up information within the data dictionary. This could sometime cause contention in high volume systems since "hot-spots" were created. Locally Managed tablespaces simplify extent allocation at the expense of some flexibility of being able to assign exact extent sizes at the table level. Basically, they assign uniform extent sizes for all data contained within. There is some deviation from uniformity, however extent allocation is always done in a structured format in locally managed tablespaces. The tablespace will have a base unit of storage, for example 1 MB. All subsequent extents in this tablespace will either be 1 Mb or an even multiple of 1 MB. In addition, tables that grow in size greater than 1MB will automatically get assigned a greater extent size on their next allocation. TEMP tablespaces are excellent candidates for locally managed tablespaces.

Online Index Rebuilds

Oracle 8I supports the rebuilding of indexes with the database operational. This includes access to the table with the index.

Drop Column

Finally, we can drop columns on a table! When I was very new to Oracle I was asked if I could drop a column on a table. Coming from an Informix background I had done this hundreds of times. I was surprised then to learn it was not supported in Oracle. I then spent the next 3 hours creating an alternate table, moving the data over, dropping the constraints on the original table, and finally dropping the original table and renaming the new one.

Log Miner

Log Miner is a utility that allows browsing of the transaction logs. It also has some functionality for undoing transactions.

A Peek at Oracle 9I

Oracle 9I is currently going into Beta and will be available in the Spring of 2001.

In 9I rollback segments are being replaced by system undo segments. A key difference is that transactions will be allows to span multiple segments as opposed to being locked into one rollback segment. Rollback segments are used within Oracle to store the before image of data during a transaction. Most Oracle databases have less than 20 rollback segments that are shared among many users. During a transaction a user normally gets assigned to a rollback segment for the duration of that transaction. This has traditionally led to problems when you have a long running transaction since the space assigned to a rollback segment is limited.

In Oracle 9i, block level data recovery will be supported. Sorry, no more details available here.

Oracle 9I will also feature the ability to online transfer of a non-partitioned table into a partitioned table. Oracle has also done some worked on speeding up database recovery time in 9i. If you are using Oracle Standby you will be allowed 10 copies of the database (as opposed to 5 in 8I), and standby will also support subsets of the actual database. You can also use standby in a selective mode, effectively choosing the objects to be replicated.

9i has a flashback query feature to call up data from a past point in time. There must be a limit here to the amount of time you can go back, but interesting nonetheless.

And lastly, in 9I Parallel server will be replaced with what will be known as Real Application Clusters. There are many improvements here in terms of management and functionality. Fast failover can occur in less than a minute in most cases. Another feature called cache fusion allows instances to synchronize without writing to disk with fast memory interconnect. The Oracle listener is used to transfer the information across nodes. Transparent Application failover can be configured to pre-allocate connections for faster failover.