Sunday 25 September 2011

Oracle SQL "like clause" uses full table scan

It is normal behaviour within Oracle to do full table scan if 'like' clause is used to perform searches in where clause.
A sql statement as below will still perform FTS even if the column col2 is indexed.
select * from tab1 where col2 like 'Test%';

In order to let Oracle use index when indexed column is used in 'like' clause, you should implement Oracle Text feature and use 'contains' clause instead of like 'clause'.

Another way to do this without Oracle text is to use function based index which I will show below.

Below is my 1st test case with normal index:
1. Create dummy table and populate it with some test data.
SQL> create table tab1 (col1 number,col2 varchar2(200));
Table created.

SQL> begin
  2  for i in 1..1000 loop
  3  insert into tab1 values (i,'Test full scan');
  4  end loop;

  5  insert into tab1 values (1001,'Test1 full scan');
  6  end;
  7  /

PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

2. Create index
create index idx1 on tab1(col2);

3. Generate execution plan of sql to see whether index gets used or not:
set autotrace traceonly explain
select * from tab1 where upper(col2) like 'TEST1%';
Execution Plan
----------------------------------------------------------
Plan hash value: 2211052296

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


As you can see, the Full Table Scan is done.

Now, lets try to create a function based index and see how that works.
1. Drop the old index
drop index idx1;

2. Create function based index
create index idx1 on tab1 (upper(col2));

3. Generate the execution plan of the same SQL:
set autotrace traceonly explain
select * from tab1 where upper(col2) like 'TEST%';
------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |   115 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1 |     1 |   115 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX1 |     9 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------


And walla!!! The index gets used.

Please note that the function based index will only get used if the 'like' clause will have a wildcard '%' character at the end of the string. It will still do full table scan if the wildcard character is placed in the begining of the string. If that is the case, then I storngly suggest that you implement and use Oracle Text feature.

Wednesday 14 September 2011

SYSAUX Tablespace Space Management

To see what is occupying most of the space in SYSAUX tablespace, you can run the following on the database:
@?/rdbms/admin/awrinfo.sql

The above will prompt for an output file. Once it finishes to run, review the file to see which component occupies the most space. To find out which component occupies the most space, scroll to section:

(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| SM/OPTSTAT           SYS                       18,911.9 MB
| SM/AWR               SYS                          302.8 MB
| AO                   SYS                           56.8 MB
| XSOQHIST             SYS                           56.8 MB
| XDB                  XDB                           54.9 MB

...

If the SM/AWR is occuping the most space, then try to reduce the AWR retention.
We had a situation where SM/OPSTAT was taking up most of the space. The reason for this is that from 10g onwards, the database tries to retain backup of the object stats in the SYSAUX tablespace, the retention of this can be found by running following:

select dbms_stats.get_stats_history_retention from dual;

By default, the retention is for 31 days. To reduce this to 10days, you can run the following:

exec dbms_stats.alter_stats_history_retention(10);

After the above is run, you can let the default database stats job purge the object stats that are older than 31 days.

You may also find that after the purging is done, some of the objects still occupy a lot of space. You will need to shrink it.
Going through the report, we found the following objects were occupying the highest space:

(4) Space usage by non-AWR components (> 500K)
**********************************

COMPONENT        MB SEGMENT_NAME                                                          SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR     7,171.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY                                     TABLE
NON_AWR     6,133.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST                                     INDEX
NON_AWR     3,817.0 SYS.I_WRI$_OPTSTAT_HH_ST                                              INDEX
NON_AWR       391.0 SYS.WRI$_OPTSTAT_IND_HISTORY                                          TABLE
NON_AWR       339.0 SYS.I_WRI$_OPTSTAT_IND_ST                                             INDEX
NON_AWR       298.0 SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST                                        INDEX
NON_AWR       204.0 SYS.I_WRI$_OPTSTAT_TAB_ST                                             INDEX
...

 In order to srhink the above, can we moved the SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY table and rebuilt SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST, SYS.I_WRI$_OPTSTAT_HH_ST indexes:

alter table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY move;
alter index SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild;
alter index SYS.I_WRI$_OPTSTAT_HH_ST rebuild;

After doing the above, we were able to reclaim around 7GB of space.