Saturday, February 28, 2015

ORA-04000

I created a table in an Oracle 11.2 database. I did not specify pctfree or pctused so it was given the defaults of 10% and 40% respectively:

SQL> conn system/manager
Connected.
SQL> create table t1 (c1 number)
  2  /

Table created.

SQL> select pct_free, pct_used
  2  from user_tables
  3  where table_name = 'T1'
  4  /

  PCT_FREE   PCT_USED
---------- ----------
        10         40


SQL>

When Oracle inserts rows into a table, the pctfree specifies the percentage of space to leave free in each block for subsequent updates to the rows. This free space is used later if an extra column is added to the table or if a varchar2 column is updated to store a longer value than before. Once the pctfree in a given block falls below the specified value, no new rows can be inserted in that block so it is removed from the free list. It is then not allowed to accept new rows until the percentage of space used in the block falls below the pctused figure. When this happens, the block goes back onto the free list again. You can alter the pctfree and/or pctused settings like this:

SQL> alter table t1 pctfree 20
  2  /

Table altered.


SQL>

Going through some old notes from an Oracle 9 performance tuning course, I read that the sum of pctfree and pctused cannot be more than 100. This seemed reasonable. If you had a pctused of 40%, a pctfree of 70% and a block which was 35% full, Oracle would not know what to do with it. The pctused figure of 40% would tell Oracle to leave the block on the free list wheras the pctfree figure of 70% would tell Oracle to remove it (from the free list). In situations like this, Oracle usually has a special error message to display. In this case, it is ORA-04000, as you can see below:

SQL> alter table t1 pctfree 70
  2  /
alter table t1 pctfree 70
*
ERROR at line 1:
ORA-04000: the sum of PCTUSED and PCTFREE cannot
exceed 100

SQL>

Thursday, February 26, 2015

FULL and NO_INDEX Hints

I was reading about hints and decided to try out a couple on an Oracle 11.2 database. First I created a table, added some data and created an index:

SQL> create table t1 (c1 varchar2(30))
  2  /
 
Table created.
 
SQL> insert into t1 select table_name from dba_tables
  2  /
 
3159 rows created.
 
SQL> create index i1 on t1(c1)
  2  /
 
Index created.
 
SQL>

I ran a SELECT statement on the table. I thought it would use the index and it did:

SQL> set autotrace on explain
SQL> select count(*)
  2  from t1
  3  where c1 = 'T1'
  4  /
 
  COUNT(*)
----------
         1
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2349582935
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    17 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |    17 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |     1 |    17 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("C1"='T1')
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
SQL>

I then added a full hint to force the optimizer to choose a full table scan and this worked too:
 
SQL> select /*+ full(t1) */ count(*)
  2  from t1
  3  where c1 = 'T1'
  4  /
 
  COUNT(*)
----------
         1
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3693069535
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    17 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("C1"='T1')
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
SQL>

I added an alias after the table name. The hint stopped working and the query used the index again:

SQL> select /*+ full(t1) */ count(*)
  2  from t1 my_alias
  3  where c1 = 'T1'
  4  /
 
  COUNT(*)
----------
         1
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2349582935
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    17 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |    17 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |     1 |    17 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("C1"='T1')
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
SQL>

That was because, if you use an alias, the hint must specify the alias, not the table name. I changed the hint to do this and the optimizer chose a full table scan again:

SQL> select /*+ full(my_alias) */ count(*)
  2  from t1 my_alias
  3  where c1 = 'T1'
  4  /
 
  COUNT(*)
----------
         1
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3693069535
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    17 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("C1"='T1')
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
SQL>

It occurred to me that I might also be able to force a full table scan by telling Oracle not to use a particular index. I did this with a no_index hint and it worked as expected:

SQL> select /*+ no_index(t1 i1) */ count(*)
  2  from t1
  3  where c1 = 'T1'
  4  /
 
  COUNT(*)
----------
         1
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3693069535
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    17 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("C1"='T1')
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
SQL>