OPTIMIZER_FEATURES_ENABLE: Downgrade Oracle optimizer functionality

After database up gradation or patched, you may find some under performing SQL statements. Due to one or more bug fixes Oracle optimizer my generate different execution plans for those SQL statements,  hence performance degraded . You need reasonable amount of time to tune them but unable to afford it in production environment! You have to do something quick ..........

In such situation, you can downgrade the optimizer functionality to a previous version or patch set; so that  optimizer just act like it's previous behavior. When you finish SQL optimization/tuning, you can switch to the upgraded or patched  optimizer version.

OPTIMIZER_FEATURES_ENABLE - An Initialization Parameter which can be altered at the system or session level to facilitate down gradation of oracle optimizer.

Current version (after up gradation or patched): 11.2.0.3

SQL> ALTER SYSTEM SET optimizer_features_enable='11.2.0.2' scope=both;
or
SQL> ALTER SESSION SET optimizer_features_enable='11.2.0.2';

Switch to current version
SQL> ALTER SYSTEM SET optimizer_features_enable='11.2.0.3' scope=both;

Finding Duplicate SQL

The presence of duplicate SQL indicate that there are some SQL statements which doesn't have Bind variables. These duplicate SQL can raise performance issue because they will increase the number of hard parse in database.

ORACLE 10g introduced two new columns in v$sql view, which can help to identifing duplicate SQL more accurately. Those two new columns are:

1. force_matching_signature 
2. exact_matching_signature

exact_matching_signature - If two or more SQL has same value in this column, ORACLE assumes they are same after making some cosmetic adjustments (removing white space, uppercasing all keywords etc) to them. The is simmiler, when parameter cursor_sharing is set to EXACT.

force_matching_signature - the same value in this column (excluding 0) marks SQLs that ORACLE will consider they are same when it replaces all literals with binds (that is, if cursor_sharing=FORCE).



SELECT sql_text , count(1)
FROM v$sql
WHERE force_matching_signature > 0
  AND force_matching_signature <> exact_matching_signature
GROUP BY sql_text
HAVING count(1) > 10
ORDER BY 2;

Transportable Tablespace (TTS)

'Transportable Tablespace' (TTS) first introduce in Oracle 8i and it become matured as time goes by.  You can use this  feature to copy a set of tablespaces from one Oracle Database to another.


For a DBA, transport data from one database to another is a very common task and you can do it in different ways

Method           Hardness     Best Suitable For    Availability

Database Link        Easy           Less than 50 GB           High
Data Pump           Moderate     Over 50 GB                 High
TTS                 Moderate     Terabytes of Data        Low
RMAN Duplicate    Hard           Full Database              High




Requirements:

• A tablespace must be totally self contained to be transportable.
• The source and target database must use the same character set and national character set.
• You cannot transport a tablespace to a target database in which a tablespace with the same name already exists.
• Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.
• There are some limitations in encryption tables.

Lets see how it works.................
 
IN SOURCE DATABASE


1. Create two tablespaces and one user

sqlplus / as sysdba;

CREATE TABLESPACE TT01 DATAFILE '/oradata/tt_01.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED BLOCKSIZE 8K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO ONLINE ;

CREATE TABLESPACE TT02 DATAFILE '/oradata/tt_02.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED BLOCKSIZE 16K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO ONLINE ;


CREATE USER test identified by test default tablespace tt01;
GRANT connect,resource to test;

2. Create a table with some data

conn test/test;

CREATE TABLE test_data(
id number (10),
alphabate varchar2 (10)
) TABLESPACE tt01;

ALTER TABLE test_data ADD CONSTRAINT PK_TEST_DATA_ID PRIMARY KEY (ID) using index tablespace tt02;

INSERT INTO test_data VALUES (1,'A');
INSERT INTO test_data VALUES (2,'B');
INSERT INTO test_data VALUES (3,'C');
INSERT INTO test_data VALUES (4,'D');
INSERT INTO test_data VALUES (5,'E');

commit;

SELECT segment_name, tablespace_name from user_segments;

SEGMENT_NAME       TABLESPACE_NAME
------------------ ------------------
TEST_DATA          TT01
PK_TEST_DATA_ID    TT02

So we have a table in one tablespace and an unique index (creat for primary key) in another tablespace.



3. tablespace's 'self contained' Test

conn / as sysdba;

EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TT01', incl_constraints => TRUE);

SELECT * FROM sys.transport_set_violations;

VIOLATIONS
------------------------------------------------------------------------------------------------------------------------------------
ORA-39908: Index TEST.PK_TEST_DATA_ID in tablespace TT02 enforces primary constraints  of table TEST.TEST_DATA in tablespace TT01.

we got this error because TT01 tablespace contain a table which has an index stored in another tablespace.

EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TT01,TT02', incl_constraints => TRUE);

SQL> SELECT * FROM sys.transport_set_violations;

no rows selected

This time no Error because all two tablespaces are included. Now make them 'Read Only'.


ALTER TABLESPACE tt01 READ ONLY;
ALTER TABLESPACE tt02 READ ONLY;


4. Take metadata of those 2 tablespaces by using datapump

expdp system directory=dump_dir transport_tablespaces=tt01,tt02 dumpfile=trans_tablespaces.dmp logfile=trans_tablespaces.log

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/ ******** directory=dump_dir transport_tablespaces=tt01,tt02 dumpfile=trans_tablespaces.dmp logfile=trans_tablespaces.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /d01/dump_dir/trans_tablespaces.dmp
******************************************************************************
Datafiles required for transportable tablespace TT01:
  /oradata/tt_01.dbf
Datafiles required for transportable tablespace TT02:
  /oradata/tt_02.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 02:27:17


5. Copy the datafiles to destination database

scp tt_01.dbf oracle@db2:/oradata/
scp tt_02.dbf oracle@db2:/oradata/

6. Make the read_only tablespaces to read write mode

sqlplus / as sysdba

ALTER TABLESPACE tt01 READ WRITE;
ALTER TABLESPACE tt02 READ WRITE;


IN DESTINATION DB

1. Create a user same as source DB

sqlplus / as sysdba;

CREATE USER test identified by test ;
GRANT connect,resource to test;


2. Import the metadata

impdp system directory=dump_dir dumpfile=trans_tablespaces.dmp transport_datafiles='/oradata/tt_01.dbf','/oradata/tt_02.dbf' logfile=imp_trans_tablespaces.log


3. Verify Tablespace and Object Movement

conn test/test

SELECT segment_name, tablespace_name from user_segments;

SEGMENT_NAME         TABLESPACE_NAME
-------------------- --------------------
TEST_DATA            TT01
PK_TEST_DATA_ID      TT02

SELECT * FROM TEST_DATA;

        ID ALPHABATE
---------- ----------
         1 A
         2 B
         3 C
         4 D
         5 E

Well we are done! To know more about TTS please go through the Oracle Doc http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces013.htm

ORA-14519 Conflicting tablespace blocksizes while importing partition tables

ORA-14519: Conflicting tablespace blocksizes for table : Tablespace T2 block size 16384 [partition specification] conflicts with previously specified/implied tablespace T1 block size 8192 [object-level default]

Well :) the error massage clearly indicate that I am trying to create a partition table in more than one tablespaces where the blocksize of the tablespaces are different. We all know that table partitions can not be placed in different blocksize tablespaces. Though I know this and not did this mistake before, I get this error when I am trying to make a replica schema in local test database.

I took dump by using EXPDP without any error and get the above error when try to import it by using REMAP_SCHEMA option of IMPDP. It is really annoying to get such error that can not be explainable. All of my table partitions are placed in a single tablespace but the error message says that I am trying to place table partitions in two tablespaces where their blocksize is different ! It sounds simply horrible.

But at the end of a long research :) I have found the mistake.The user's default tablespace is T1 [8k blocksize] and I create the table in tablespace T2 [16K blocksize] just like this -

CREATE TABLE TEST (ID NUMBER (10), TEXT VARCHAR2(50)

) PARTITION BY RANGE (ID) (

PARTITION TEST_PART_1 VALUES LESS THAN (100001) TABLESPACE T2 ,

PARTITION "TEST_PART_2" VALUES LESS THAN (MAXVALUE) TABLESPACE T2

);

This script create 2 table partitions of TEST table in T2 tablespace but unfortunately the default tablespace attribute of TEST table set to T1 (As I did not mansion default table space of table TEST when I create it. So ORACLE set this attribute same as user's default tablespace). See! Sometime ORACLE does something smartly that can cause stupid scenario. IF you want to see this unbelievable thing, just generate the table's SQL. You will see this

CREATE TABLE TEST (ID NUMBER (10),TEXT VARCHAR2(50)

) TABLESPACE T1

PARTITION BY RANGE (ID) (

PARTITION TEST_PART_1 VALUES LESS THAN (100001) TABLESPACE T2 ,

PARTITION "TEST_PART_2" VALUES LESS THAN (MAXVALUE) TABLESPACE T2

);

This sturdiness of oracle cause ORA-14519 when I tryed to import TEST table because oracle can't create a partition table in different blocksize tablespaces.

So The solution is simple; mention default tablespace when you create a partition table. Just like this

CREATE TABLE TEST (

ID NUMBER (10),

TEXT VARCHAR2(50)

) TABLESPACE T2

PARTITION BY RANGE (ID) (

PARTITION TEST_PART_1 VALUES LESS THAN (100001) TABLESPACE T2 ,

PARTITION "TEST_PART_2" VALUES LESS THAN (MAXVALUE) TABLESPACE T2

);

[ It is not mandatory that all tablespace should be same (t2 in this case). You can put 3 different tablespace but their blocksize should be same.]

But, what is the solution for already created partition tables ? Well, the solution is very simple, just change the default tablespace attribute of your partition tables that are already created. Here is a script to identify the problematic tables.

SELECT * FROM (

SELECT x.table_name, x.def_tablespace_name

, (SELECT block_size from user_tablespaces where tablespace_name=x.def_tablespace_name) df

, x.tablespace_name

, (SELECT block_size from user_tablespaces where tablespace_name = x.tablespace_name) tb

FROM (

SELECT distinct t.table_name, t.def_tablespace_name, p.tablespace_name

FROM user_part_tables t, user_tab_partitions p

WHERE t.table_name = p.table_name

) x ) k WHERE k.tb <> k.df;

Then reset the default tablespace attribute

ALTER TABLE TEST MODIFY default attributes TABLESPACE T2;

Now take dump and apply it without facing ORA-14519 !

PL/SQL - Operator Precedence

Hi all, It has been almost an year since I posted a topic in this blog. After a 1 year study break, I back to my usual job and hopfully I am become regular in this blog again.


In SQL or PL/SQL we use several operators. Some are mathematical, logical and comparison operatiors. Oracle follow a order of precedence when execute an expression that contains more than one operators. If operatiors with same precidence are occured then it does not follow any order. Otherwise Oracle maintain the following order of precedence of operators.


Order |------------| Operator |------------| Operation
1 |------------| ** |------------| exponentiation
2 |------------| +, |------------| identity, negation
3 |------------| *, / |------------| multiplication, division
4 |------------| +, -, || |------------| addition, subtraction, concatenation
5 |------------| =, <, >, <=, >=, |------------| comparison
<>, !=, ~=, ^=, IS NULL, LIKE,
BETWEEN, IN
6 |------------| NOT |------------| logical negation
7 |------------| AND |------------| conjunction
8 |------------| OR |------------| inclusion


For example, when NOT, AND and OR operators are used in the same statement NOT is evaluated first, then AND and finally OR.

Estimate Tablespace growth

Some time it is very helpful to plan disk space/ tablespace management if you estimate the growth of your tablespace. Here is a select query which can be helpful :

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;


Another One: This query gives average increase per day 

SELECT b.tsname tablespace_name
, MAX(b.used_size_mb) cur_used_size_mb
, round(AVG(inc_used_size_mb),2)avg_increas_mb
FROM (
  SELECT a.days, a.tsname, used_size_mb
  , used_size_mb - LAG (used_size_mb,1)  OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
  FROM (
      SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days
       ,ts.tsname
       ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
      FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts
       ,DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt
      WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
       AND ts.tsname = dt.tablespace_name  AND sp.begin_interval_time > sysdate-7
      GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
      ORDER BY ts.tsname, days
  ) A
) b GROUP BY b.tsname ORDER BY b.tsname;

Add Unique key in a table that contains duplicate row

Requirement : A table contains some duplicate data. Now we want to add a unique constraint that skip existing duplicate values but check newly inserted duplicate values.

SQL> create table t2 (id number(10), t varchar2(20));

Table created.


SQL> insert into t2 values (1,'A');

1 row created.

SQL> insert into t2 values (1,'A');

1 row created.

SQL> insert into t2 values (1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE;
alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE
*
ERROR at line 1:
ORA-02299: cannot validate (HASAN.UK_T2) - duplicate keys found


SQL> select * from t2;

ID T
---------- ------------------------------------------------------------
1 A
1 A
1 A

So normal method does not work !



Case 1: New Table That means initially the table does not have any data

SQL> create table t3 (id number(10), t varchar2(20));

Table created.

SQL> alter table t3 add constraint gpu unique (id) deferrable initially deferred;

Table altered.

SQL> alter table t3 disable constraint gpu;

Table altered.


SQL> insert into t3 values(1,'A');

1 row created.

SQL> insert into t3 values(1,'A');

1 row created.

SQL> insert into t3 values(1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL>
SQL> select * from t3;

ID T
---------- ------------------------------------------------------------
1 A
1 A
1 A



SQL> alter table t3 enable novalidate constraint gpu;

Table altered.

SQL> insert into t3 values(2,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t3 values(2,'A');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HASAN.GPU) violated

SQL> alter table t3 modify constraint gpu INITIALLY IMMEDIATE;

Table altered.

SQL> insert into t3 values(1,'A');
insert into t3 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.GPU) violated



Case 2: Existing Table that contains duplicate data


SQL> create table t2 (id number(1),a varchar2(10));

Table created.


SQL> insert into t2 values(1,'A');

1 row created.

SQL> insert into t2 values(1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t2 add constraint uk_t2 unique(id) DEFERRABLE INITIALLY DEFERRED disable;

Table altered.

SQL> alter table t2 enable novalidate constraint uk_t2;

Table altered.

SQL> insert into t2 values(1,'A');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HASAN.UK_T2) violated


SQL> alter table t2 modify constraint uk_t2 INITIALLY IMMEDIATE;

Table altered.

SQL> insert into t2 values(1,'A');
insert into t2 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.UK_T2) violated


OR


SQL> alter table t2 add constraint uk_t2 unique(id) disable;

Table altered.


SQL> alter table t2 enable novalidate constraint uk_t2;

Table altered.

SQL> insert into t2 values(1,'A');
insert into t2 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.UK_T2) violated

Virtual Indexes

For tuning SQL statements often requires the testing of alternate indexing strategies to see the affect on execution plans. Adding extra indexes to large tables can take a considerable amount of time and disk space. The additional indexes are available for use by other sessions, which may affect the performance of other parts of your application that you are not currently testing. This can be problematic when you are trying to identify problems on a production system.

In contrast to conventional indexes, a virtual index has no associated segment, so the creation time and associated disk space are irrelevant. In addition, it is not seen by other sessions, so it doesn't affect the normal running of your system. This article presents a simple example of how virtual indexes are used.


First, we create and populate a table

CREATE TABLE objects_tab AS SELECT * FROM user_objects;

Now we create an primary key on that table. So delete some rows where object_id is null.

DELETE from objects_tab where object_id is null;
COMMIT;

ALTER TABLE objects_tab ADD CONSTRAINT objects_tab_pk PRIMARY KEY (object_id);
EXEC DBMS_STATS.gather_table_stats('EMP', 'objects_tab', cascade=>TRUE);

If we query the table using the primary key, we can see this reflected in the execution plan.

SQL> SET AUTOTRACE TRACEONLY ;
SQL> SELECT * FROM objects_tab WHERE object_id = 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2097082964

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | OBJECTS_TAB_PK | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


If we query the table using a non-indexed column, we see a full table scan

SQL> SELECT * FROM objects_tab WHERE object_name = 'LOGIN';


Execution Plan
----------------------------------------------------------
Plan hash value: 821620785

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 1 | 87 | 11 (0)| 00:00:01 |
----------------------------------------------------------------------------

To create the virtual index on this column, simply add the NOSEGMENT clause to the CREATE INDEX statement.

SQL> CREATE INDEX objects_tab_object_name_vi ON objects_tab(object_name) NOSEGMENT;

Index Created

If we repeat the previous query we can see the virtual index is not visible to the optimizer.

SQL> SELECT * FROM objects_tab WHERE object_name = 'LOGIN';


Execution Plan
----------------------------------------------------------
Plan hash value: 821620785

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 1 | 87 | 11 (0)| 00:00:01 |
----------------------------------------------------------------------------

To make the virtual index available we must set the _use_nosegment_indexes parameter.

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session Altered

If we repeat the query we can see that the virtual index is now used.

SELECT * FROM objects_tab WHERE object_name = 'LOGIN';


Execution Plan
----------------------------------------------------------
Plan hash value: 4006507992

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | OBJECTS_TAB_OBJECT_NAME_VI | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------


The virtual index does not appear in the USER_INDEXES view, but it present in the USER_OBJECTS view.

SQL> SET AUTOTRACE OFF;
SQL> SELECT index_name FROM user_indexes WHERE INDEX_NAME = 'OBJECTS_TAB_OBJECT_NAME_VI';

no rows selected

SQL> SELECT object_name FROM user_objects WHERE object_type = 'INDEX'
AND OBJECT_NAME='OBJECTS_TAB_OBJECT_NAME_VI';

OBJECT_NAME
----------------------------------------------------------------------------------------------------
OBJECTS_TAB_OBJECT_NAME_VI

1 rows selected.

Statistics can be gathered on virtual indexes in the same way as regular indexes, but as we have seen previously, there will be no record of this in the USER_INDEXES view.

SQL> EXEC DBMS_STATS.gather_index_stats('EMP', 'objects_tab_object_name_vi');

PL/SQL procedure successfully completed.

Oracle will prevent us from creating another virtual index with the same column list, but it will allow us to create a real index with the same column list.

SQL> CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT;

CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT
*
ERROR at line 1:
ORA-01408: such column list already indexed


SQL> CREATE INDEX objects_tab_object_name_i ON objects_tab(object_name);

Index created.


Related Links

1. Create INDEX
2. Execution Plan

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

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.

Oracle Tips And Tricks Part#1

Tips 4 : Join small table first.

Suppose we have three tables A,B,C which can be join each other by any combinations (i,e a join b join c, c join a join b, ... etc).

A has 10 columns and 100 rows, that means 10*100=1000 cells
B has 5 columns and 2000 rows, that means 5*2000=10000 cells
and C has 20 columns had 30 rows, that means 20*30=600 cells

Now When you apply "Left Join"/ "Right Join" on those tables, for achieving optimum performance please join small table fist (if possible for certain case. In our example a,b,c can be join by any combinations) that means

SELECT ..... FROM C Left Join A ON (...) Left Join B ON (...);

Tips 3: Do not use LONG datatype.

LONG datatype is used to store variable length character strings. Oracle has advised, since 8i, that the LONG datatype no longer be used, it has placed in dtatatype set only for backward compatibility. The limitation of LONG datatype are:

1. There is a limit of one column of type LONG per table.
2. Tables containing a LONG cannot be partitioned.
3. LONG datatypes cannot be used in subqueries.
4. Few functions will work with LONG data.

Instead of LONG please use CLOB. The CLOB datatype is the preferred datatype for character data larger than VARCHAR2.

Tips 2 : Place column/columns in last, if there is a possibility to contain null.

Suppose we have a table like this student (id,name not null,address1,address2,address3,age not null). Here you can see, there is a possibility to remain NULL in 3 column (address1,address2,address3). so for a row of this table that have null on those three columns, 3 byte space consume to store the NULL. But if you rearrange the columns like this student (id,name not null,,age not null,address1,address2,address3) then a row of this table that have null on those three columns, 1 byte space consume to store the NULL. Because it took 1 byte to store NULL on column 'address1' and no space needed for columns 'address2' and 'address2'. And again if you have data on 'address3' then again 2 byte space needed to store NULL info :( .

So you need to place the column such a way that all null columns stay in a row to get this space benefit . 1 byte space too small to consider but a large table with 30 columns (10 of then can be null) and have 4 lacks rows, this tricks can be hand full ! :)


Tips 1 : Create index on FOREIGN KEY columns

Suppose we have two Tables:
1. branch(id,name,Address,total_employee).
2. employee(id,name,branch_id,sal).

Here employee.branch_id is a foreign key (referenced by branch.id). Now suppose 50 branches has 20 employees and another 10 employee add in each branch (already inserted into employee table). so we need to update branch.total_employee =20 to 30. If we have no index on employee.branch_id column, update on branch will lock all 50*30=1500 rows of employee table and all DML operations on employee table wait until DML operation completed on branch table. But if we have an index on employee.branch_id column, update on branch will lock only 1*30=30 rows of employee table at a time.

For small database the wait may not bother you but in large database it will bother you significantly.