Oracle Best Practices Part 4

In this series during Part 1 I mentioned below best practices;
1- Put your Database into ARCHIVELOG Mode
2- Comment on Tables and Columns similar to your PL/SQL or SQL Source Codes
3- Use DBMS_STATS for statistic collection
4- Using Recovery Manager(RMAN) for Backup and Recovery needs

following Part 2 I mentioned below best practices;
5- Resumable Statements and Space Allocation
6- Maintenance of Global Partitioned Indexes
7- Default Tablespaces
8- PL/SQL Bulk Operations
9- Locking Issues
10- Oracle System Event Triggers
11- Autonomous Transactions

and following Part 3 I mentioned below best practice;
12- Learn what is already provided with PL/SQL language

13- Defining Application Services for Oracle Database 10g

Services are logical abstractions for managing workloads in Oracle Database 10g, they represent groups of applications with common attributes, service level thresholds, and priorities. Application functions can be divided into workloads identified by services. For example, the CRM application can define a service for each responsibility. Or a HR application can be a specific service. A service can span one or more instances of an Oracle database or multiple databases in a global cluster, and a single instance can support multiple services.

Server side work, such as the Scheduler, parallel query, and Oracle Streams Advanced Queuing set the service name as part of the workload definition. For the Scheduler, jobs are assigned to job classes, and job classes run within services. For parallel query and parallel DML, the query coordinator connects to a service, and the parallel query slaves inherit the service for the duration of the query. For Oracle Streams Advanced Queuing, streams queues are accessed using services. Work running under a service inherits the thresholds and attributes for the service and is measured as part of the service.

The Database Resource Manager binds services to consumer groups and priorities. This lets services be managed in the database in the order of their importance. For example, you can define separate services for high priority online users and lower priority internal reporting applications. Likewise, you can define gold, silver, and bronze services to prioritize the order in which requests are serviced for the same application.

The Automatic Workload Repository lets you analyze the performance of workloads using the aggregation dimension for service. The Automatic Workload Repository automatically maintains response time and CPU consumption metrics, performance and resource statistics wait events, threshold-based alerts, and performance indexes for all services.

Service, module, and action tags identify operations within a service at the server. (MODULE and ACTION are set by the application) End to end monitoring enables aggregation and tracing at service, module, and action levels to identify high load operations. Oracle Enterprise Manager administers the service quality thresholds for response time and CPU consumption, monitors the top services, and provides drill down to the top modules and top actions for each service.

With the Automatic Workload Repository, performance management by the service aggregation makes sense when monitoring by sessions may not. For example, in systems using connection pools or transaction processing monitors, the sessions are shared, making accountability difficult.

With Real Application Clusters(RAC), services can be provisioned on different instances based on their current performance. Connect time routing and runtime routing algorithms balance the workload across the instances offering a service. The metrics for server-side connection load balancing are extended to include service performance. Connections are shared across instances according to the current service performance. Using service performance for load balancing accommodates nodes of different sizes and workloads with competing priorities. It also prevents sending work to nodes that are hung or failed.

The Automatic Workload Repository maintains metrics for service performance continuously. These metrics are available when routing runtime requests from mid-tier servers and TP monitors to RAC. For example, Oracle JDBC connection pools use the service data when routing the runtime requests to instances offering a service.
High Availability with Services

RAC use services to enable uninterrupted database operations. Services are tightly integrated with the Oracle Clusterware high availability framework that supports RAC. For planned outages, RAC provides interfaces to relocate, disable, and enable services. When the service at an instance stops, the event is used to interrupt applications using the service at that instance. Using the notification eliminates the client waiting on TCP timeouts. The events are integrated with Oracle JDBC connection pools and Transparent Application Failover (TAF).

With Oracle Data Guard, production services are offered at the production site. Other standby sites can offer reporting services when operating in read only mode.

This section describes Oracle Database 10g services and includes the following topics:
* Deploying Services
* Configuring Services
* Using Services

Code listing 76 : Oracle Services Example

Continue reading with Part 5

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release

Refences Used :
Oracle® Database Administrator’s Guide 10g Release 2 (10.2) – Chapter 2 Creating an Oracle Database – Defining Application Services for Oracle Database 10g
Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)-Chapter 95 DBMS_SERVICE
Services for a DBA presentation by David Austin
DBMS_SERVICE and net service names by Mr.Lutz Hartmann

Take the risk and migrate to 10gR2

Take a five minutes break and check the output of “select * from v$version” on your critical databases;

9.2 Oracle 9i Release 2 – May 2002
10.1 Oracle 10g Release 1 – January 2004
10.2 Oracle 10g Release 2 – July 2005
Today – April 2007 (and 11g is coming!)


Especially if you are a developer or dba of an OLAP Oracle database, please check these Oracle whitepapers dated July 2005; (paper) (presentation) (paper just for sort)

The improvements in algoritms for both in-memory sort and hash based aggregation are special cases with 10gR2, any 10gR2 migration is of course something to be tested carefully but this time we have more promising arguments and motivation in doing so.

After all sorting and aggregation are what a data warehouse system lives for :)

Remember two more important motivations;

1- in any case if you need Oracle’s support they will somehow first request you to install the latest patch,
2- Also there are lots of great new features you may enjoy with Release 2 like “Flashback Database Restore Points” and “Error-Logging Clause”

Refences Used :

“What’s New in” series on

In one of the previous post, Project Lockdown by Arup Nanda, I mentioned Mr.Arup Nanda’s below two articles, all time most read articles on Oracle Technology Network published articles;

Oracle Database 10g: The Top 20 Features for DBAs
Oracle Database 10g: Top Features for DBAs Release 2 Features Addendum

They have both detailed information as a starting point to 10g. Also they provide good answers why you might want to migrate and how more efficiently you may use Oracle, the software you already paid a lot.

Also for another great summarised information you might visit Oracle’s documentation and search for “What’s New in”. It was love in first sight for me with these ones;

What’s New in Oracle Performance?
What’s New in PL/SQL?
What’s New in Application Development?
What’s New in Oracle Database Administrator’s Guide?
What’s New in Oracle Database Security?
What’s New in Oracle Streams AQ?
What’s New in Oracle Text?
What’s New in Enterprise Manager 10.2
What’s New in Oracle Call Interface?
What’s New in Oracle Spatial?

Becareful, availability must be your primary concern, always


Yesterday one of my best friends called me because of the above situation. Since he is a developer minded expert it was ok for me to help him, if he had a little dba profile I would never answer his calls :)

This one is of the worst situations you may experience, it is similar to making sex with someone you already know having hiv virus without protection, but with Oracle there is always hope! Below is the support message of Oracle, it is like a doctor bringing someone back to life after some minutes of death on the operation table;

Code listing 75 : “_ALLOW_RESETLOGS_CORRUPTION” and EVENTS 10015 example

So please forget tuning, development etc. and immediately check this post for the basic backup and recovery needs like ARCIVELOG MODE-MULTIPLEXING REDO and CONTROL FILES-RMAN BACKUP, even it may be your local XE test database!

There are more than one INDEX Type Part 3

In Part 1 after a brief introduction on default indexing feature B*Tree Indexes in Oracle I mentioned “1- Virtual / NoSegment Indexes”. And in Part 2 “2- Reverse Key Index and Index Range Scan Problem” and “3- How to disable an index” were the topics.

4- Oracle Text option

For a brief history, interMedia after Oracle 8i is a feature for managing rich data such as documents, video, audio, and images in Oracle. And starting with Oracle 9i, the text searching components were named as Oracle Text and are not a part of interMedia services. Oracle Text is not separately licensed product but rather a core feature of the database, and can be used with all Oracle Database editions, including the free Express Edition(XE).

Oracle Text is a powerful search technology that enables you to build text query applications and document classification applications. Oracle Text provides indexing, word and theme searching, and viewing capabilities for text and with the development APIs allow software developers to easily implement full-featured content search applications.

Here are some highlights why you may want to use Text option;
– Oracle text uses standart SQL to index, search, and analyze text and documents stored in the Oracle database, in files and on the Web.
– Oracle text can perform linguistic analysis on documents.
– Oracle text can search text using a variety of strategies including keyword searching, contextual queries, Boolean operations, pattern matching, HTML/XML section searching etc.
– Oracle Text integrates with and benefits from features like; Partitioning, RAC, Query optimization.

Text searching examples without Oracle Text option;

select 	id
from 	mytext
where 	instr( thetext, 'Oracle') > 0;

select 	id
from 	mytext
where 	thetext like '%Oracle%';

Text searching example with Oracle Text option;

-- In order to use Oracle Text PL/SQL Packages, the user must be first granted the role CTXAPP.
create index mytext_idx
on mytext( thetext )
indextype is CTXSYS.CONTEXT

select 	id
from 	mytext
where 	contains(thetext, 'Oracle') > 0;

select 	score(1), id
from 	mytext
where 	contains ( thetext, 'oracle or california', 1 ) > 0
order by score(1) desc;

I will demonstrate the context type index, for other types of Oracle Text indexes visit this link please.

In below example we want to find the rows containing ‘FORALL’ word in the cust_street_address column. First way to accomplish this task is doing a full table scan access, second way to do is creating a domain index with Oracle Text and using the contains operator. We will also create another function based index to show that this index can not help for a ‘%FORALL%’ kind of search, so what performance difference does it make;

Code listing 74 : Oracle Text (Domain Index) Performance Comparison Example

There are a total of four tables that are automatically created whenever you create an Oracle Text index. These table names will always have a prefix of DR$, then the index name, and then a suffix of either $I, $K, $N, or $R. These tables will always be created in the same Oracle schema that owns the Oracle Text index. The $I table consists of all the tokens that have been indexed, together with a binary representation of the documents they occur in, and their positions within those documents. The $K table is an index organized table which maps internal DOCID values to external ROWID values. The $R table is designed for opposite lookup for $K table_ fetching a ROWID when you know DOCID value. The $N table contains a list of deleted DOCID values, which is used (and cleaned up) by index optimization process.

After 10g for CONTEXT indexes after 10g with CTX_DDL.SYNC_INDEX support we can bundle the index maintainance all up in a single DDL;

create index TEXT_IDX on T( TEXT_COL ) indextype is ctxsys.context parameters ('sync (every 'SYSDATE+10/1440')');

which creates a DBMS_SCHEDULER job for you, for above example running every 10 minutes. Another alternative is the “sync-on-commit” which ensures your text indexes stay right up to date(at the cost of index fragmentation and of course, commit performance).

create index TEXT_IDX on T( TEXT_COL ) indextype is ctxsys.context parameters ('sync (on commit)');

Also after 10g Oracle provides another complete solution for enterprise search, Ultra Search.

Intermedia and Location-based(Spatial, Locator) features of Oracle are very interesting to study, since you already paid a lot for your database software in order not to re-invent a dumper wheel check these features. Only by knowing the feature is available within the database(you do not need to be an expert on every each of them) you will gain competitive advantage both for long term needs of your customers and being time to market.

In Part 4 topic will be my favorite index type Bitmap Indexes, we will try to understand where to use and more important where NOT to use them with examples. Until than this article will be a good introduction.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release

Refences Used :
OTN article Building Full-Text Search Applications with Oracle Text by Marko Asplund
“Unscharfe Suche” in Datenbeständen
Connor McDonald,
Expert one–on-one Oracle. Chapter 17-interMedia. Thomas Kyte
interMedia Text & Oracle Text presentation by Gözde Ayrancı