This category contains 6 posts

Tuning Query without Changing Code by Using SQL Plan Baseline

As a DBA most of us have had such experiences, when tuning a SQL statement we found the only way to improve the performance of the SQL is to change the code. The change can be adding/removing hints, changing the order of joins, removing unnecessary function calls, etc. However in reality we all know the … Continue reading

Object Privileges for Function Based Indexes

This is in database. A function based index stores values of a function or expression applied to one or more columns of a table. When you create a  function based index using a user-defined function that is owned by another schema, besides meeting all prerequisites of creating conventional indexes you will also need to … Continue reading

Space Usage of LOBs, General Behavior and in OTM Database

What is LOB LOB (large object) is a group of Oracle database build-in data types. Using LOB data types in Oracle database allows user to store large blocks of unstructured data, such as text, graphic images, video clips and sound waveforms, in character or binary format. Since they were introduced in Oracle 8, LOB data … Continue reading

Automatic Workload Repository Snapshots

Oracle Automatic Workload Repository (AWR) collects, processes and maintains performance statistics for problem detection and self-tuning purposes. The gathered data can be viewed in both reports and views. AWR collects cumulative and differential statistics data into AWR snapshots. The differential values captured by the snapshot represent the changes for each statistic over the time period … Continue reading

On Database Deadlock, Foreign Key, and Indexing

This is in 11g database. A database deadlock is a situation in which two user sessions are waiting for data locked by each other. Oracle database automatically detects deadlocks and resolves them by rolling back one statement involved in the deadlock, release one set of the conflicting row locks. The statement rolled back belongs to … Continue reading

Database I/O Calibration

There are several new features in 11g Oracle Resource Manager, I/O calibration is one of them. This feature enables user to assess the performance of the storage subsystem, and determine whether I/O performance problems are caused by the database or the storage subsystem. Unlike other external I/O calibration tools that issue I/Os sequentially, the I/O calibration feature … Continue reading