How to speed up Index creation in Oracle database

2 12 2009

Imagine a situation where you have to create an index on a table having 1 billion records. It might take approximately 4-5 hours to create the index. But there are ways to fasten this process. Following are some of the tips that will help you to create indexes faster than the normal time. 

While creating index oracle must first collect the symbolic key/ROWID pairs with a full-table scan. When you specify Parallel option to the create index script oracle will perform a full table scan in parallel threads based on the number of CPUs, table partitioning and disk configuration and hence will reduce index creation time.

Following will work fine on a server that has 4 CPUs:

create index indx_t_doc on t_document(doc_id,doc_type) parallel 3;

The NOLOGGING keyword in create index command will restrict the database from generating large redo logs and will create minimal redo log. This will improve the performance of Create index command.

create index indx_t_doc on t_document(doc_id,doc_type) PARALLEL 3 NOLOGGING;

The COMPRESS option will enable key compression. It eliminates repeated occurrence of key column values and may substantially reduce storage. Use integer to specify the prefix length (number of prefix columns to compress).
For unique indexes, the valid range of prefix length values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1.

For nonunique indexes, the valid range of prefix length values is from 1 to the number of key columns. The default prefix length is the number of key columns.

create index indx_t_doc on t_document(doc_id,doc_type) PARALLEL 3 NOLOGGING COMPRESS;

Follow these simple tips and save your time.

Happy Indexing🙂

Query to find Top 5 wait events in Database

30 10 2009

Performance optimization of databases is one of the routine tasks of DBAs. This task becomes very easy when you have Oracle Enterprise Manager tool installed. You can generate various reports from AWR and analyze the health of the database and then tune it accordingly. This is normal in every DBA’s life.

But what if you don’t have the OEM installed? This is very much possible in a test environment. In this case, you need to generate the reports manually. You need to query the database to extract the data requried for analysis.

For such requirements, we have the below query to find out “Top 5 wait events in the database”. The query gives Date, Event_Name and Total_Waits.

Query to find Top 5 wait events in database:

select Day, Event_name, Total_wait from (
select day, event_name, sum(event_time_waited) total_wait,
row_number() over (partition by day order by sum(event_time_waited) desc) rn from (
SELECT   to_date(to_char(begin_interval_time,’dd/mm/yyyy’),’dd/mm/yyyy’) day,s.begin_interval_time, m.*
    FROM (SELECT ee.instance_number, ee.snap_id, ee.event_name,
                 ROUND (ee.event_time_waited / 1000000) event_time_waited,
                 ROUND ((ee.event_time_waited * 100) / et.total_time_waited,
                       ) pct,
                 ROUND ((ee.event_time_waited / ee.total_waits) / 1000
                       ) avg_wait
            FROM (SELECT ee1.instance_number, ee1.snap_id, ee1.event_name,
                         – ee2.time_waited_micro event_time_waited,
                         ee1.total_waits – ee2.total_waits total_waits
                    FROM dba_hist_system_event ee1 JOIN dba_hist_system_event ee2
                         ON ee1.snap_id = ee2.snap_id + 1
                       AND ee1.instance_number = ee2.instance_number
                       AND ee1.event_id = ee2.event_id
                       AND ee1.wait_class_id <> 2723168908
                       AND ee1.time_waited_micro – ee2.time_waited_micro > 0
                  SELECT st1.instance_number, st1.snap_id,
                         st1.stat_name event_name,
                         st1.VALUE – st2.VALUE event_time_waited,
                         1 total_waits
                    FROM dba_hist_sys_time_model st1 JOIN dba_hist_sys_time_model st2
                         ON st1.instance_number = st2.instance_number
                       AND st1.snap_id = st2.snap_id + 1
                       AND st1.stat_id = st2.stat_id
                       AND st1.stat_name = ‘DB CPU’
                       AND st1.VALUE – st2.VALUE > 0
                         ) ee
                 (SELECT et1.instance_number, et1.snap_id,
                         et1.VALUE – et2.VALUE total_time_waited
                    FROM dba_hist_sys_time_model et1 JOIN dba_hist_sys_time_model et2
                         ON et1.snap_id = et2.snap_id + 1
                       AND et1.instance_number = et2.instance_number
                       AND et1.stat_id = et2.stat_id
                       AND et1.stat_name = ‘DB time’
                       AND et1.VALUE – et2.VALUE > 0
                         ) et
                 ON ee.instance_number = et.instance_number
               AND ee.snap_id = et.snap_id
                 ) m
         dba_hist_snapshot s ON m.snap_id = s.snap_id
) group by day ,event_name
order by day desc, total_wait desc
)where rn < 6

The query is very useful for the DBAs because it can give you loads of information. You can modify the query to fetch much more information and use it the way you want.

Best Luck🙂

How to create AWR report manually

29 10 2009

Oracle database 10g

If you do not have Oracle Enterprise Manager tool installed then you can create the AWR reports manually using the following commands:

1. Creating Snapshot

The list of the snapshot Ids along with database Ids is availabe in the view DBA_HIST_SNAPSHOT.

2. Dropping a Range of Snapshots.
Refer to the DBA_HIST_SNAPSHOT view column SNAP_ID to view available snapshots. To delete contain SNAP_ID from from 102 to 122,

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 102, high_snap_id => 122, dbid => 8187786345);

3. Modifying Snapshot Settings
If you want to modify the retention period as 43200 minutes (30 days), the interval between each snapshot is specified as 30 minutes, and the number of Top SQL to flush for each SQL criteria as 100 then use following:

interval => 30, topnsql => 100, dbid => 7123356265);
The dbid is optional.

4. Extract the AWR Data
The awrextr.sql script extracts the AWR data for a range of snapshots from the database into a Data Pump export file. Once created, this dump file can be transported to another system where the extracted data can be loaded. To run the awrextr.sql script, you need to be connected to the database as the SYS user.

To extract AWR data at the SQL prompt, enter:

SQL> @$ORACLE_HOME/rdbms/admin/awrextr.sql

5. Load the AWR Data
Once the export dump file is transported to the target system, you can load the extracted AWR data using the awrload.sql script. The awrload.sql script will first create a staging schema where the snapshot data is transferred from the Data Pump file into the database. The data is then transferred from the staging schema into the appropriate AWR tables. To run the awrload.sql script, you need to be connected to the database as the SYS user.

To load AWR data at the SQL prompt, enter:

SQL> @$ORACLE_HOME/rdbms/admin/awrload.sql

6. Generate AWR Reports
The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

First, you need to specify whether you want an HTML or a text report.

Enter value for report_type: text

Specify the number of days for which you want to list snapshot Ids.

Enter value for num_days: 2

After the list displays, you are prompted for the beginning and ending snapshot Id for the workload repository report.

Enter value for begin_snap: 95
Enter value for end_snap: 97

Next, accept the default report name or enter a report name. The default name is accepted in the following example:

Enter value for report_name:
Using the report name awrrpt_1_95_97

The workload repository report is generated.

The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.

The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.

The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.

The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.

The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.

The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.

Best Luck🙂

ORA-01503, ORA-01161, ORA-01110

15 02 2009

Problem Scenario:

You are trying to (clone) copy one database to another and you receive the following errors during create controlfile step:

ORA-01503: create controlfile

ORA-01161: database name in file header does not match given name of

 ORA-01110: ………………………………………..


For CREATE CONTROLFILE commands you have to ensure that all the datafiles are from the same database. So in this case the ‘given name’ in the error message is the DB NAME from the first file header. Verify that all the dbf files were copied to the correct directories. Also double check to see if all of them are from the same database.

If everything seems to be perfect but you are still receiving ora-1161 then you might have attempted to create controlfile before copying all the files and then alter/added that file.  During Create Controlfile command oracle allocates a db_id and after this if we add any new files then oracle considers them to be from a different database and gives the errors that you received. So you should copy all the files before creating the controlfile.

This error can also occur if you copy the datafiles as a Unix user other than Oracle. If the owner read/write permissions are not the same as Oracle user, then when the create controlfile command is executed, the datafile header will not be updated and the errors occur.


a. Copy all the datafiles again and re-execute the Create Controlfile command

b. Check read/write permissions and ownership of the copied datafiles

Quick Database Cloning

20 01 2009

One of the common tasks of the DBAs is to clone a database. Very often there is a need to create a test or dev database which is a replica of Production database. In such cases Database cloning can be of great help. This process is quick and has simple steps.
Database cloning procedure can be used to clone/copy a database from one UNIX server to another server. This is one of the fastest ways to copy a database to another server.
Before you clone the database it is important to know that this procedure is Operating System dependent. You cannot clone a database from HP-UX to Windows server or Red Hat Linux server. The simple reason being the difference in Endianness. So before starting the cloning process, please make sure that the target and source OS are same or atleast follow same Endianness.

1. Create the script from Source database

Connect to the source database using SQL*PLUS (as SYSDBA) and execute the following command:

SQL> alter database backup controlfile to trace;

This command will generate a script containing a create database syntax in the trace file directory identified by USER_DUMP_DEST parameter in init.ora file.
The file looks like this:

GROUP 1 (‘/app1/oradata/src_db/log1a.dbf’,
‘/app1/oradata/src_db/log1b.dbf’) SIZE 50M,
GROUP 2 (‘/app2/oradata/src_db/log2a.dbf’,
‘/app2/oradata/src_db/log2b.dbf’) SIZE 50M
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
# Database can now be opened normally.

2. Shutdown the source database

SQL> Shutdown immediate

3. Copy datafiles to destination server

Copy all the datafiles on the destination server. You may change the filenames if you wish, in this case you will also have to edit the controlfile to reflect the new datafile names of the destination server.

rcp /app1/oradata/src_db/*    newhost:/app1/oradata/dest_db
rcp /app2/oradata/src_db/*    newhost:/app2/oradata/dest_db
rcp /appdata/oradata/src_db/* newhost:/appdata/oradata/dest_db

4. Edit the Create database command

Change the Create controlfile clause in the Create database command generated in STEP1:





5. Remove extra clauses

Remove the “recover database” and “alter database open” clause from the script generated in STEP1:

# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
# Database can now be opened normally.

6. Change the path of datafiles

Change the path of the datafiles in the script generate in STPE1 to reflect the path on the destination server





7. Create the dump directories

Create the dump directories: bdump, cdump, udump

>cd /app/admin
>mkdir bdump cdump udump pfile

8. Copy the Initialization parameter file

Copy the old init.ora file to the destination server

rcp /app/admin/src_db/pfile/*.ora newhost:/app/oracle/admin/dest_db/pfile

9. Create the new database

SQL> @db_create_controlfile.sql


Finally, your cloned database is ready to use.

Expensive Merge Join Cartesian

20 01 2009

Merge Join Cartesian is very expensive to Oracle. It is never useful except for the case when two tables have many to many join relation and they have very few rows. In general, Merge Join Cartesian tells us that there is something wrong with the query. Below given are some tips to troubleshoot and remove the deadly Merge Join Cartesian:

1. Check Join Conditions

Start with the table joins. Check if there are any join conditions missing between the given tables in the query. As I said, Cartesian product is only possible when two tables have Many to Many join relation. In normal scenarios, the joins should be of the type: (a.primary_key = b.foreign_key)
In most of the cases, adding proper join conditions will remove the Merge Join Cartesian from the plan.

2. Stale Statistics

The optimizer selects the best execution plan on the basis of the object statistics. If the statistics are stale, the plan might not be the optimal plan. Gather the statistics using DBMS_STATS package.

3. Optimizer Bug (_optimizer_transitivity_retain)

The issue with Optimizer regarding (_optimizer_transitivity_retain) is explained best by Oracle ACE Syed Jaffer: 


If the database design is correct and if proper join conditions are used, then there are very little chances that the Optimizer will use Merge Join Cartesian in the for the execution.

PL/SQL enhancements in Oracle database 10g

18 06 2008

PL/SQL enhancements in Oracle database 10g

Oracle 10g Database has a lot new things for everybody… May it be Database Administrators or the Developers. A lot of people have been discussing the DBA features that Oracle 10g Database provides, but a very few have said anything about PL/SQL features in 10g.

So, what’s New in PL/SQL in Oracle Database 10g?

Well, a lot of things are new in PL/SQL in 10g db. In this post we will just list some of them:

A. Changes in 10g PL/SQL compiler

1. Freedom in order of evaluation of operands
eg: operators of equal precedence a+b+c can be evaluated in any order like: a+b+c or a+c+b or a+c+b or any other combination that the compiler feels correct

2. Freedom of not raising an exception
eg: if some operation can be performed in two ways, 1 of which may raise and exception, the compiler can choose not to select that option.

3. Freedom to raise the exception
eg: if an operation raises two exceptions, compiler has freedom to choose which exception it wants to raise

4. Freedom of computing correct result
eg: A*null = null; no matter A is of any datatype

.5 Takes care of the side effects
eg: RANDOM + 1/BSD
If RANDOM is a function call manipulating BSD, then sequence is taken care of

B. Freedom and Side Effects

Though Oracle 10g Database gives the compiler a lot of freedoms, the DB has to take care of the side effects arising out of such freedoms:

1. PL/SQL Compiler will not reorder, add or remove explicit procedure, function or method calls appearing in sepereate statements

2. PL/SQL Compiler has the freedom not to do an operation whose only effect is to raise and exception or to initialize a package

3. IF a PL/SQL subprogram requires initialization of the package, then PL/SQL is free to perform the initialization at any time between the beginning of the call to the subprogram which immediately encloses the package element reference and the moment when the package element is actually used

4. If the PL/SQL compiler can determine that a use of a package element does not require the initialization of the package, then PL/SQL is not required to initialize the package when the element is used

5. If an operation may raise an exception when executed, but there is an alternate possible method of execution which will not raise that exception, the PL/SQL compiler is free to choose the alternate method

6. The PL/SQL compiler may not introduce a computation as an alternate which may raise an exception which otherwise would not have been raised during the execution of a program

Example 1:

– A + B …

– A + B …

can be changed to

T := A + B;
… T …

… T …

Example 2:

for i in 1 .. 10 loop
A := B + C;

end loop;

can be changed to
A := B + C;
for i in 1 .. 10 loop

end loop;

Example 3:

if pkg.A = null then null; end if;

The entire if statement may be eliminated if A is not a procedure or method call because the PL/SQL compiler knows that the result of the comparison is always null.

These were some of the features/enhancements in PL/SQL compiler of Oracle 10g Database. So the next time you write a piece of code for Oracle database 10g, keep these things in mind and the database will give you the best output. In the subsequent post, we will try to debunk few performance myths in Oracle database 10g.


Get every new post delivered to your Inbox.