Oracle Tips And Tricks Part #3

Tips 12: Rebuild indexes & gather statistics after table shrink.

When you finish a table segment shrink, it is a good practice rebuilding indexes of that table and also gather statistics of that table. These thinks help Oracle to create better execution plans for the SQL quarries related with the table.

Tips 11: Shrink table segment that experienced large delete

Segments that undergo significant data manipulation language (DML) activity, such as UPDATE and DELETE operations, can become sparsely populated, with chunks of free space within their data blocks. Besides simply wasting space, sparsely populated segments can also lead to poor performance, because operations such as a full table scan will need to scan more blocks than necessary to retrieve data.

Before Oracle Database 10g, you could reclaim the free segment space by dropping the table, re-creating it, and then reloading the data. You could also use the ALTER TABLE MOVE command to move the table to a different tablespace. Both of these processes, however, must occur with the table offline. Online table reorganization is another way to reclaim space, but it requires significant disk space. With Oracle Database 10g, you can now shrink segments directly, without taking the tablespaces or the objects offline. The process of shrinking a segment :

1. Enable row movement : ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
2. Recover space and amend the high water mark (HWM): ALTER TABLE scott.emp SHRINK SPACE; OR
   
3. Recover space, but don't amend the high water mark (HWM) : ALTER TABLE scott.emp SHRINK SPACE COMPACT;
4. Recover space for the object and all dependant objects : ALTER TABLE scott.emp SHRINK SPACE CASCADE;

You can use 2 or 3 but 3, both have some advantage/ disadvantage. Follow 3 for best performance.

To learn segment shrink please go through the following two articles :

1. http://shaharear.blogspot.com/2008/05/online-segment-shrink.html
2. http://shaharear.blogspot.com/2008/06/segments-shrink.html

Segments Shrink

Among Windows users, disk drive fragmentation is a well known terminology. Regular data write and delete operations causes disk drive fragmentation. We usually avoid this by performing disk de-fragmentation.

Now the question is “Does Oracle has same problem ?”
If yes then, what is its remade ?

Unfortunately (i would say naturally :-) ) Oracle also has same problem because a data segment may experienced heavy UPDATE and DELETE. When the segment is rewrite, there is a scope to leave some free space within data blocks, just like disk fragmentation. This free space cause space waist and poor performance because Oracle need to scan more data block to fetch data. Now you should ask “What is the remade?” and the answer is “Segment Shrink”.


Before Oracle 10g

In earlier versions of Oracle, you can reclaim the free segment space by performing the following ways:

1. Create a table same as old table, move data from old table to new table and finally drop the old table.
   
2. Or create the new table as CAST (create table test2 as select * from test). drop test table and rename table test2 to test.
3. You also can move the table to another datafile by using ALTER TABLE MOVE command.

Both of the three processes, cause the table unavailable while performing operations. Another alternative way is 'Online table reorganization'. The benefit is, this time the table is available while performing 'Online table reorganization' but it consume significant disk space.


In Oracle 10g

You can shrink oracle data segments online that means you need not taking tablespaces or objects off line while performing shrinking . Shrinking of a data segment has two phases:

1. Compacted Segment Data: compacting objects. If the object is a table You must enable row movement so that rowid can changed and disable all triggers on that table. It also readjust High-water mark (HWM) to an appropriate location.
   
2. And Shrink Space: Free up unnecessary space.

Lets go to do the job !

ALTER TABLE TEST SHRINK SPACE COMPACT
--This command only compact the table object.

ALTER TABLE TEST SHRINK SPACE
--This command free up unnecessary space from table object.

1. How do you identify which segments should undergo shrinking?
2. How do you make this check process to a regular part of your system maintenance?
   

With Oracle Database 10g, you can easily gather those answer from Oracle Automatic Workload Repository (AWR) infrastructure. You can also use DBMS_ADVISOR expectingly Segment Advisor. You can also use Oracle Enterprise manager to perform the shrinking stuff with some clicks.

So check the cool feature of oracle now !

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.