Oracle Tips And Tricks Part #2

Tips 10: Decrease overhead of Large INSERT/DELETE.

When you insert or delete large data, it create a big overhead on database. Yesterday i had deleted 4,99,15,964 record from a single table. To reduce the overhead I had done the followings:

1. Split the single delete into 5 delete statement. Each statement deleted around 1,00,000 records.
2. Place the 5 delete statement into a single sql script and delay 3 min between two delete execution. delete 1 commit EXEC DBMS_LOCK.SLEEP(180) delete2 commit ....;
3. Bring the database in noarchive log mode from archive log mode;
4. Increase size of UNDO tablespace;
5. set undo retention time = 60 sec.

** You may grant execute permission to the schema :
GRANT execute ON dbms_lock TO scott.

Tips 9 : switch to noarchive mode when execute data pump import

It is better operating a Oracle Database in archive mode. But it is resource sensitive because it archive redo log files. When you need big size data movement like you importing a 20GB dump, the archive process slow up the import job. So it is a good option to switch the DB to noarchive mode when you execute data pump impost. After finishing the import job, you can go back to archive mode.

Tips 8 : Create Unique index if possible.

Unique indexes always give better performance Then Non Unique indexes. So when you are going to build a new index, try to build an unique index first than go for non unique index. You can follow either of the followings:

1. Create the index: CREATE UNIQUE INDEX test_indx ON test(clo2,col3); if (col2,col3) pare is unique in test table then the index is created but if the pare is not unique, an error occur and the index is not build. This is not a good idea if your table is big (because building index on a big table is time consuming..).
2. First check the uniqueness than build the index. To check the uniqueness you can try the following sql quarry:
   

SELECT x.col2,x.col3, count(*) FROM test HAVING count(*)>1 GROUP BY x.col2,x.col3.

If this quarry results any row than you cannot build unique index.


Tips 7 : Gather statistics after building an index.

Suppose you create a new index 'index_1' on table 'test'. Now you try to examine how much improvement achieved (one or more sql quarry of test table) by creating the index. you may use Execution plan of those quarry to see the improvements.

But the problem is no statistical information of newly build index are available to Oracle optimizer. Thus you will not measure actual improvement because those quarry may use old execution plans. To find the actual benefit :

1. Analyze the index : ANALYZE INDEX index_1 COMPUTE STATISTIC. You may do it while build the index (CREATE INDEX index_1 ON test(col1) COMPUTE STATISTICS).
2. Analyze the Table : ANALYZE TABLE test COMPUTE STATISTIC;
3. flush shared_pool: ALTER SYSTEM flush shared_pool;
   

Now proceed to your performance test :)

Tips 6 :Limit the Number of Indexes for Each Table

A table can have any number of indexes. However, the more indexes there are, the more overhead is incurred as the table is modified. Specifically, when rows are inserted or deleted, all indexes on the table must be updated as well. Also, when a column is updated, all indexes that contain the column must be updated.

Thus, there is a trade-off between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable.

Tips 5 : Order Index Columns for Performance

The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first. If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries that access just col1, or that access just col1 and col2, are also seeded up. But a query that accessed just col2, just col3, or just col2 and col3 does not use the index.