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>

Monday, February 23, 2015

Default Size of a CHAR Column

If you do not specify a size for a CHAR column, the default is 1. You can see what I mean in the example below, which I tested on Oracle 11.2:

SQL> create table t1
  2  (c1 char,
  3   c2 char(1))
  4  /
 
Table created.
 
SQL> desc t1
Name                       Null?    Type
-------------------------- -------- ------------------
C1                                  CHAR(1)
C2                                  CHAR(1)
 
SQL> 

However, if you rely on defaults like this and the software supplier changes them, you could be left with an application which does not work. 

VARCHAR and VARCHAR2

If you create a table with a VARCHAR column in Oracle 11.2, Oracle sets it up as a VARCHAR2:

SQL> l
  1  create table t1
  2  (c1 varchar(1),
  3*  c2 varchar2(1))
SQL> /
 
Table created.
 
SQL> desc t1
Name                       Null?    Type
-------------------------- -------- ------------------
C1                                  VARCHAR2(1)
C2                                  VARCHAR2(1)
 
SQL>

According to a book I am working through, this has been the case since Oracle 8. However, Oracle say you should not rely on this as, in a future release, the specifications for VARCHAR and VARCHAR2 may diverge.

Thursday, February 19, 2015

Oracle "read by other session" Wait Event

When a session needs to read data from a block on disk into the Oracle buffer cache, it may have to wait for another session to finish doing the same thing. Time spent doing this is recorded as a read by other session event. I decided to reproduce this in an Oracle 12 database. First, in session 1, in red, I set up a user called Fred to create a table:

SQL> conn / as sysdba
Connected.
SQL> create user fred
  2  identified by bloggs
  3  default tablespace users
  4  quota unlimited on users
  5  /
 
User created.
 
SQL> grant create session, create table,
  2  select any dictionary, alter system to fred
  3  /
 
Grant succeeded.
 
SQL>

Still in session 1, Fred then created a table, added some data and checked that all the rows were in the same block:

SQL> conn fred/bloggs
Connected.
SQL> create table t1 (c1 number)
  2  /
 
Table created.
 
SQL> begin
  2  for i in 1..200 loop
  3  insert into t1 values(1);
  4  end loop;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
SQL> select dbms_rowid.rowid_block_number(rowid), count(*)
  2  from t1
  3  group by dbms_rowid.rowid_block_number(rowid)
  4  order by 1
  5  /
 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ----------
                                 223        200
 
SQL>
 
Then he started updating the data. After each update, he did a commit so that other sessions could see the changed data and a checkpoint to write the changes to disk:

SQL> begin
  2  while (1=1) loop
  3  update t1 set c1 = c1 + 1;
  4  commit;
  5  execute immediate 'alter system checkpoint';
  6  end loop;
  7  end;
  8  /
 
In 5 separate sessions in blue (sessions 2 through 6 inclusive), Fred repeatedly flushed the Oracle buffer cache and read the table into it: 

SQL> conn fred/bloggs
Connected.
SQL> declare
  2  grand_total number;
  3  begin
  4  while (1=1) loop
  5  execute immediate 'alter system flush buffer_cache';
  6  select sum(c1) into grand_total from t1;
  7  end loop;
  8  end;
  9  /

In a 7th session, in green, Fred read the table into the buffer cache 10000 times. While he was doing this he had to wait for sessions 2 through 6, which were doing the same thing:

SQL> conn fred/bloggs
Connected.
SQL> declare
  2  grand_total number;
  3  begin
  4  for i in 1..10000 loop
  5  execute immediate 'alter system flush buffer_cache';
  6  select sum(c1) into grand_total from t1;
  7  end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
SQL>

So, when he finished, the time spent waiting on the read by other session event could clearly be seen: 

SQL> select event, time_waited/100
  2  from v$session_event
  3  where sid = (select distinct sid from v$mystat)
  4  /
 
EVENT                               TIME_WAITED/100
----------------------------------- ---------------
Disk file operations I/O                        .03
latch: cache buffers chains                     .01
buffer busy waits                                 0
read by other session                         33.36
db file sequential read                        7.35
db file scattered read                        22.07
db file parallel read                            .2
latch: In memory undo latch                       0
latch: row cache objects                          0
library cache: mutex X                            0
SQL*Net message to client                         0
SQL*Net message from client                     .02
events in waitclass Other                    405.87
 
13 rows selected.
 
SQL>

How Many Values Can You Have in an IN List?

I have often wondered how many values you could have following an IN and I have just found out. I loaded some new data into a name and address table in an Oracle 11 database over the weekend. On Monday, a user sent me an Excel spreadsheet containing a list of almost 18000 meter point references to search for in the table. I exported them into a file, copied it to the server and used vi to add a comma at the end of each line. Then I added a query at the start of the data:

select * from m_number_detail
where mpo_reference in (
0002538404,
0002538505,
0001312900,
0001313408,
0006175302,
Etc
Etc

When I tried to run it, I saw the following message at the end of the SPOOL file:

Etc
Etc
17906  8815785700,
17907  8817723609,
17908  0072096508,
17909  0072096710,
17910  0925482304)
17911  /
0005567909,
*
ERROR at line 1003:
ORA-01795: maximum number of expressions in a list is 1000
 
SQL>