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>
Saturday, February 28, 2015
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.
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>
Subscribe to:
Posts (Atom)