How to speed up Index creation in Oracle database

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. 

Tip#1 – PARALLEL:
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;
 

Tip#2 – NOLOGGING:
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;
 

Tip#3 – COMPRESS:
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

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,
                 ee.total_waits,
                 ROUND ((ee.event_time_waited * 100) / et.total_time_waited,
                        1
                       ) pct,
                 ROUND ((ee.event_time_waited / ee.total_waits) / 1000
                       ) avg_wait
            FROM (SELECT ee1.instance_number, ee1.snap_id, ee1.event_name,
                           ee1.time_waited_micro
                         – 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
                  UNION
                  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
                 JOIN
                 (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
         JOIN
         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 🙂