Interesting Graphs
A few weeks ago, one of the testers performed a load test on one of our pre-production environment, and emailed his results in an Excel spreadsheets.
Below are some of the graphs produced:
Figure 1
Figure 2
Foreign keys and locking
Sometimes things are learnt the hard way. A couple of weeks ago, I was involved in purging some historical data from a production database using a set of DELETE statements. I assumed these transactions will not impact the Application (performance aside) because of how Oracle maintains read consistency and concurrency. This assumption was formed on weak knowledge on my part …
In short, the DELETE statements were executed on a parent table. It has foreign key on delete cascade relationships with several child tables. There were no indexes defined on the foreign key columns of the child tables. Whilst the DELETE statements were executing, the child tables became locked against other DMLs until a COMMIT or ROLLBACK is issued. Other DML transactions on these unindexed child tables appeared to hang, i.e. enqueued.
The on delete cascade constraint was the key, if it was disabled, locking would not happen but instead the transaction will fail with ORA-02292: constraint violation – child records found. However, enabling the on delete cascade constraint wass the right thing to do. To prevent locking, we just had to define indexes on the foreign key columns.
This apparent database design knowledge showed how little I know about Oracle, and how inexperienced I am.
I conjured a more simplistic example, and also found some reference to this in Metalink.
(Sorry… I am just being very silly with the example…)
Create tables:
Suppose I create two parent tables, and a child table with foreign keys linked to them. The foreign keys do not have indexes.
create table dad_table
(
id number(10),
name varchar2(10),
constraint pk_dad_table_id primary key (id)
);
create table mum_table
(
id number(10),
name varchar2(10),
constraint pk_mum_table_id primary key (id)
);
create table child_table
(
dad number(10)
constraint fk_child_dad_table_id
references dad_table(id)
on delete cascade,
mum number(10)
constraint fk_child_mum_table_id
references mum_table(id)
on delete cascade,
name varchar2(10)
);
SQL> select index_name from user_indexes
2 where table_name='CHILD_TABLE';
no rows selected
Populate data:
insert into dad_table (id, name) values (1, 'john'); insert into dad_table (id, name) values (3, 'peter'); insert into dad_table (id, name) values (5, 'david'); insert into mum_table (id, name) values (2, 'mary'); insert into mum_table (id, name) values (4, 'jane'); insert into mum_table (id, name) values (6, 'ivy'); insert into child_table (dad, mum, name) values (1, 2, 'ashton'); insert into child_table (dad, mum, name) values (3, 4, 'james');
Testing:
I am going to start three Oracle sessions, two as the schema user, one as SYSDBA which I will use to monitor locks. I used the locks information script from Metalink Note 1020012.6. I also found a script in Tom Kyte’s book (Expert Oracle Database Architecture – Chapter 6 – Locking and Latching) useful in displaying locks blocker and blockee.
i. Without indexes on foreign keys:
Session #1 (SID=11): SQL> delete from dad_table where id=1; 1 row deleted. Session #2 (SID=10): SQL> delete from mum_table where id=4; (... hangs) Session SYSDBA: * Note 1020012.6 - SID SERIAL# USERNAME Resource Type TAB Lock Held Lock Req. ---- ------- ---------- -------------------- ---------- -------------- -------------- 10 5596 FREDT TM - DML Enqueue DAD_TABLE Row Share 10 5596 FREDT TM - DML Enqueue MUM_TABLE Row Exclusive 10 5596 FREDT TM - DML Enqueue CHILD_TABL Shr Row Excl 11 3311 FREDT TM - DML Enqueue DAD_TABLE Row Exclusive 11 3311 FREDT TM - DML Enqueue MUM_TABLE Row Share 11 3311 FREDT TM - DML Enqueue CHILD_TABL Row Exclusive * Tom's Blocker, Blockee and Object Name - BLOCKER SID BLOCKING BLOCKEE SID ON_OBJ OBJECT_NAME ---------- ------ --------------- ---------- ------ ------ -------------------- CIMS 11 is blocking CIMS 10 on CHILD_TABLE * V$Session_Wait - SQL> select sid, event from v$session_wait where event='enqueue'; SID EVENT ---- ---------------------------------------------------------------- 10 enqueue
Comments:
The two sessions were deleting different rows of data, but one was blocking the other. The lock information showed SID 10 was requesting a Share Row Exclusive lock (SSX) on the child_table, when a Row Exclusive (SX) lock on the same table was held by SID 11. This conflict is explained in 10g Concept Guide (the 9i Concept Guide html formatting looks a bit out of whack).
The puzzling thing is why request a SSX lock? Metalink Note 33453.1 explains this behaviour, but it still does not explain the *why*:
Without indexes… A share lock (LMODE=4) of the entire child table is required until the transaction containing the insert/delete/update statement for the parent table is committed, thus preventing any modifications to the child table. It even can be a SSX (LMODE=5) lock when deleting from the parent table with a delete cascade constraint. In 9.2.0, the downgraded ‘mode 3 Row-X (SX)’ locks are no longer required except when deleting from a parent table with a ‘delete cascade’ constraint.
Another interesting point – SID 11 also acquired a Row Share (SS) lock on mum_table. It isn’t immediately obvious, but a SS lock (only) prohibits other transactions from acquiring an Exclusive (X) lock on table, either explicitly (lock table … in exclusive mode) or implicitly (alter table). Note 223303.1 also made this statement:
Starting in version 9.2.0, a Row-SS lock is taken on the parent table for any DML issued against the child table. This will occur with or without an index on the foreign key column of the child table.
The table structure is somewhat similar to the application schema I was working on. Whilst I was deleting from the parent table, the Application was also deleting rows from another parent table, to which both parent tables are linked to a single child table. Given the above experiment, I now understand why the Application appeared to have “hang” and threads were reporting timeout errors.
ii. With indexes on foreign keys:
Let’s first create two indexes on the child table:
create index ix_dad_child on child_table(dad); create index ix_mum_child on child_table(mum); Session #1 (SID=11): SQL> delete from dad_table where id=1; 1 row deleted. Session #2 (SID=10): SQL> delete from mum_table where id=4; 1 row deleted. Session SYSDBA: * Note 1020012.6 - SID SERIAL# USERNAME Resource Type TAB Lock Held Lock Req. ---- ------- ---------- -------------------- ---------- -------------- -------------- 10 5596 FREDT TM - DML Enqueue DAD_TABLE Row Share 10 5596 FREDT TM - DML Enqueue MUM_TABLE Row Exclusive 10 5596 FREDT TM - DML Enqueue CHILD_TABL Row Exclusive 11 3311 FREDT TM - DML Enqueue DAD_TABLE Row Exclusive 11 3311 FREDT TM - DML Enqueue MUM_TABLE Row Share 11 3311 FREDT TM - DML Enqueue CHILD_TABL Row Exclusive * Tom's Blocker, Blockee and Object Name - no rows selected * V$Session_Wait - SQL> select sid, event from v$session_wait where event='enqueue'; no rows selected
Comments:
The two sessions are no longer in blocker-blockee state, and each can delete their different rows. The indexes defined on the foreign key columns have resolved the locking scenario introduced in this example.
Note 33453.1 explains this:
An Insert/Delete/Update on the parent table will only acquire a row level lock on the parent table if there is an index on the foreign key of the child table. The child table will have NO locks on it and so any type of modifications can be made to the child table…
If the child table specifies ON DELETE CASCADE, waiting and locking rules are the same as if you deleted from the child table after performing the delete from the parent. In 9.2.0 onwards, Oracle requires ‘mode 2 Row-S (SS)’ locks on the child table.
Metalink References:
Note:33453.1 – REFERENTIAL INTEGRITY AND LOCKING.
Note:16428.1 – Prevent Deadlock in Existing Parent- Child Tables By INDEXING PK and FK.
Note:223303.1 – Correction to 9.2.0 foreign key constraint locking behaviour, per documentation.
Note:29787.1 – VIEW: “V$LOCK” Reference Note.
Note:11828.1 – FOREIGN KEYS, INDEXES AND PARENT TABLE LOCKING.
Java, Oracle, Bind Variables and Batch Update – Part II
In my last post, I have setup in Java different insert methods to a database. In this post, I will post what I found in the Oracle trace files / tkprof output and the different response times:
- Multiple Inserts using java.sql.Statement
- Multiple Inserts using java.sql.PreparedStatement, using Bind Variables
- Multiple Inserts using java.sql.Statement, with addBatch() & executeBatch()
- Multiple Inserts using java.sql.PreparedStatement, using Bind Variables with addBatch() & executeBatch()
For the impatient readers, the response times when I execute my Java code with each insert method is as follows:
| #1 | #2 | #3 | #4 |
| 8.012s | 9.083s | 9.093s | 0.15s |
Please do not place too much judgments on these timings, they may be affected by the network latency, CPU spikes at the time of testing. However, this study is intended to show the relative amount of work and time spent for each INSERT method.
#1. Multiple Inserts using java.sql.Statement
Oracle trace file / tkprof output:
INSERT INTO insert_test (str) values (:"SYS_B_0") call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1000 0.09 0.08 0 0 0 0 Execute 1000 0.31 0.23 2 1006 3065 1000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2000 0.40 0.32 2 1006 3065 1000 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 23 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 2 0.00 0.00 SQL*Net message to client 1002 0.00 0.00 SQL*Net message from client 1002 0.04 8.17 ********************************************************************************
* The parse count is 1000, and execute count is also 1000 – a 1:1 ratio.
* Some CPU resources were used to parse the statement, and consumed 9ms of CPU time.
* Times waited is 1002 – (my guess) – 1000 for executeUpdate(); 1 for stmt.close(); 1 for commit?
* Oracle waited 8.17s for Java client to issue 1002 calls, one by one.
#2. Multiple Inserts using java.sql.PreparedStatement, using Bind Variables
Oracle trace file / tkprof output:
INSERT INTO insert_test (str) values (:1) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1000 0.16 0.18 3 7 1065 1000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1001 0.16 0.18 3 7 1065 1000 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 23 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 3 0.00 0.00 SQL*Net message to client 1002 0.00 0.00 SQL*Net message from client 1002 0.06 8.52 ********************************************************************************
* Parse to Execute is a 1:1000 ratio. Almost negligible CPU resource and time spent on parsing.
* Oracle waited 8.52s for Java client to issue 1002 calls, one by one.
#3. Multiple Inserts using java.sql.Statement, with addBatch() & executeBatch()
Oracle trace file / tkprof output:
INSERT INTO insert_test (str) values (:"SYS_B_0") call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1000 0.09 0.08 0 0 0 0 Execute 1000 0.31 0.23 2 1006 3065 1000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2000 0.40 0.32 2 1006 3065 1000 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 23 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 2 0.00 0.00 SQL*Net message to client 1002 0.00 0.00 SQL*Net message from client 1002 0.04 8.17
* The statistics are similar to Method #1: Multiple Inserts using java.sql.Statement.
* This is because Oracle9i does not support batch updating with java.sql.Statement:
The Oracle implementation of standard update batching does not implement true batching for generic statements and callable statements. Although Oracle JDBC supports the use of standard batching syntax for Statement and CallableStatement objects, you will see performance improvement for only PreparedStatement objects. [source]
#4. Multiple Inserts using java.sql.PreparedStatement, using Bind Variables with addBatch() & executeBatch()
Oracle trace file / tkprof output:
INSERT INTO insert_test (str) values (:1) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.02 0.04 0 27 89 1000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.02 0.04 0 27 89 1000 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 23 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net more data from client 12 0.01 0.03 SQL*Net message to client 3 0.00 0.00 SQL*Net message from client 3 0.00 0.02
* The response time is 0.16secs.
* A (soft) parse of the SQL statement is made, with 1 execute to insert 1000 rows.
* 1 execute saves more time than the 1000 executes in previous methods.
* Communication between the Java client and Oracle is reduced to a minimum, as shown by 3 SQL*Net message from client.
* The 1000 bind values are sent in bulk using SQL*Net more data from client.
Java, Oracle, Bind Variables and Batch Update – Part I
This week, I decided to do some simple exercises in Java to test the performance of different Oracle database table insert methods, and observe how much work the database do for each method. Part 1 of this post will demonstrate my Setup, and the Java code I used for each Insert method. Part 2 will show a detailed analysis of the tkprof output, the response times.
- Multiple Inserts using java.sql.Statement
- Multiple Inserts using java.sql.PreparedStatement, using Bind Variables
- Multiple Inserts using java.sql.Statement, with addBatch() & executeBatch()
- Multiple Inserts using java.sql.PreparedStatement, using Bind Variables with addBatch() & executeBatch()
Test Environment:
* WinXP laptop; JDK1.3; Oracle9i 9.2.0.8 database with (cursor_sharing=similar); Oracle JDBC Thin Driver.
Please note all the statistics and timing shown in this blog will vary with different environment (e.g. performance of my laptop, JDK version, network speed and database configuration… etc.), do not treat the figures produced here as benchmarks. The Java code provided here are my test codes, and may contain unidentified faults, please use at your own risks.
Test Method:
* Create a simple table in my database,
SQL> create table insert_test (str varchar2(100));
Table created.
* Use Java to instantiate a String array of 1000 random strings (not very pretty I know),
String[] randStrings = new String[1000];
for(i = 0; i < randStrings.length; i++) {
randStrings[i] = “token” + Math.random();
}
* Trigger a Oracle trace using Java, the trace file generated can be found in $ORACLE_BASE/admin/$ORACLE_SID/udump directory on the database server. Trace files can get hugh very quickly, do not keep a trace running for a long duration. It is best used under the supervision of a DBA.
Statement strace = conn.createStatement();
try {
strace.executeUpdate(“ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 12′”);
strace.executeUpdate(“ALTER SESSION SET STATISTICS_LEVEL=all”);
} catch (SQLException t1) {
t1.printStackTrace();
} finally {
strace.close();
}
* Use each insert method to insert these strings into the table,
* Measure response times using java.lang.System.currentTimeMillis();
* Analyze Oracle trace files using tkprof.
Multiple Inserts using java.sql.Statement
try {
long start_ts = System.currentTimeMillis();
Statement stmt = conn.createStatement();
for (int i = 0; i < randStrings.length; i++) {
stmt.executeUpdate(“INSERT INTO insert_test (str) values (‘”+randStrings[i]+”‘)”);
}
conn.commit();
long end_ts = System.currentTimeMillis();
long sw = end_ts – start_ts;
System.out.println((double)sw/1000 + “secs”);
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
stmt.close();
}
Multiple Inserts using java.sql.PreparedStatement, using Bind Variables
PreparedStatement stmt = conn.prepareStatement(“INSERT INTO insert_test (str) values (?)”);
try {
long start_ts = System.currentTimeMillis();
for (int i = 0; i < randStrings.length; i++) {
stmt.setString(1, randStrings[i]);
stmt.executeUpdate();
}
conn.commit();
long end_ts = System.currentTimeMillis();
long sw = end_ts – start_ts;
System.out.println((double)sw/1000 + “secs”);
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
stmt.close();
}
Multiple Inserts using java.sql.Statement, with addBatch() & executeBatch()
try {
long start_ts = System.currentTimeMillis();
Statement stmt = conn.createStatement();
for (int i = 0; i < randStrings.length; i++) {
stmt.addBatch(“INSERT INTO insert_test (str) values (‘”+randStrings[i]+”‘)”);
}
stmt.executeBatch();
conn.commit();
long end_ts = System.currentTimeMillis();
long sw = end_ts – start_ts;
System.out.println((double)sw/1000 + “secs”);
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
stmt.close();
}
Multiple Inserts using java.sql.PreparedStatement, using Bind Variables with addBatch() & executeBatch()
PreparedStatement stmt = conn.prepareStatement(“INSERT INTO insert_test (str) values (?)”);
try {
long start_ts = System.currentTimeMillis();
for (int i = 0; i < randStrings.length; i++) {
stmt.setString(1, randStrings[i]);
stmt.addBatch();
}
stmt.executeBatch();
conn.commit();
long end_ts = System.currentTimeMillis();
long sw = end_ts – start_ts;
System.out.println((double)sw/1000 + “secs”);
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
stmt.close();
}
Results will be documented in Part 2.
CTAS via database link with timestamp column changes precision to 0
Found an interesting Oracle9i bug today, and it is easily reproduced (I am using 9.2.0.8 below). The bug is fixed in 10g (> 10.1.0.2).
SQL> create table timestamp_test (ts timestamp(5)); Table created. SQL> desc timestamp_test; Name Null? Type ----------------------------------------- -------- ---------------------------- TS TIMESTAMP(5) SQL> drop database link dblink_to_self; Database link dropped. SQL> create database link dblink_to_self 2 connect to fredt identified by fredt 3 using 'FOOMDB'; Database link created. SQL> create table timestamp_test_ctas 2 as 3 select * from timestamp_test@dblink_to_self; Table created. SQL> desc timestamp_test_ctas; Name Null? Type ----------------------------------------- -------- ---------------------------- TS TIMESTAMP(5) SQL> select table_name, data_type from user_tab_cols 2 where table_name like 'TIMESTAMP%'; TABLE_NAME DATA_TYPE ------------------------------ --------------- TIMESTAMP_TEST TIMESTAMP(5) TIMESTAMP_TEST_CTAS TIMESTAMP(0)
See Metalink Bug 2417643 for further reference.
Java, Oracle and CLOB
Today, one of the developers encountered the following error when trying to insert more than 4000 bytes into a CLOB datatype column. The environment is Java 1.4 and Oracle9i (9.2.0.8) database:
ORA-01704: string literal too long
That can happen if the literal (the 4000+ characters String) is embedded into an INSERT statement like a normal String/VARCHAR2. Suppose I create a table like so:
SQL> create table clob_test (id number(10), str clob);
Table created.
This INSERT method won’t work, and will result in the Oracle error, this is because VARCHAR2 has a 4000 bytes limit:
// Create a String of 5000 characters
char[] charArray = new char[5000];
String clobstr = new String(charArray);
Statement stmt = conn.createStatement();
try {
String sql = “INSERT INTO clob_test (str) values (‘”+clobstr+”‘)”;
stmt.executeUpdate(sql);
} catch (SQLException t1) {
t1.printStackTrace();
} finally {
stmt.close();
conn.commit();
}
Thanks to Holly, she’s worked out a few methods to do this, but they require the use of oracle.sql.CLOB. I will show one of the suggested ways here:
try {
String sql = “INSERT INTO clob_test (id, str) values (1, empty_clob())”;
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
sql = “select str from clob_test where id=1 for update”;
ResultSet rss=stmt.executeQuery(sql);if(rss.next()){
oracle.sql.CLOB clob= (oracle.sql.CLOB)rss.getClob(“str”);
clob.putString(1,clobstr);
sql = “update clob_test set str=? where id=1″;
PreparedStatement pstmt=conn.prepareStatement(sql);
pstmt.setClob(1,clob);
pstmt.executeUpdate();
pstmt.close();
}
} catch (SQLException t2) {
t2.printStackTrace();
} finally {
stmt.close();
conn.commit();
}
The Official Oracle9i suggested to use OutputStream, which you can read about it here. It looks a bit more complicated, but a Java developer has to evaluate which of these methods is a better alternative.
There are some good news for Oracle10g databases, which has a much easier way of working with CLOBs. The full Java demo is available here.
prstat
I found a need to track the total CPU usage by the oracle user:
$ id uid=101(oracle) gid=101(dba) $ prstat -t -u 101 > prstat_out $ more prstat_out NPROC USERNAME SIZE RSS MEMORY TIME CPU 332 oracle 1737G 529G 100% 283:44:40 7.0% Total: 333 processes, 720 lwps, load averages: 1.74, 1.73, 1.98 NPROC USERNAME SIZE RSS MEMORY TIME CPU 332 oracle 1737G 529G 100% 283:44:53 10% Total: 333 processes, 720 lwps, load averages: 1.88, 1.75, 1.98 NPROC USERNAME SIZE RSS MEMORY TIME CPU 332 oracle 1737G 529G 100% 283:45:05 13% Total: 333 processes, 720 lwps, load averages: 1.97, 1.77, 1.99 NPROC USERNAME SIZE RSS MEMORY TIME CPU 332 oracle 1737G 529G 100% 283:45:17 14% Total: 333 processes, 720 lwps, load averages: 2.12, 1.81, 2.00 NPROC USERNAME SIZE RSS MEMORY TIME CPU 332 oracle 1737G 529G 100% 283:45:30 16% Total: 333 processes, 720 lwps, load averages: 2.25, 1.84, 2.01 NPROC USERNAME SIZE RSS MEMORY TIME CPU 332 oracle 1737G 529G 100% 283:45:41 16%
It is not so pretty… so perhaps I will use awk:
$ grep oracle prstat_out | awk {'print $7'}
7.0%
10%
13%
14%
16%
16%
Size of NUMBER
Sometimes curiosity gets me into researching something that may not be of use at times, or maybe I just don’t have the wisdom to realise its value.
The last few weeks, whilst I was researching into a problem at work, I found myself sidetracked into learning the size of NUMBERs stored in an Oracle database, and more specifically, how Oracle stores NUMBERs internally. The size of NUMBERs in Oracle database is not a fixed number like Java’s int – 4 bytes, it is variable depending on the value.
Before the story continues, this information is documented in the Concept Guide: Oracle10g and Oracle9i. Oracle may change its implementation with every release, therefore, it is best to check the documentation. On top of that, some Metalink Notes also goes into demonstrating how the internal representation works: 1007641.6 – Understanding Oracle NUMBER Datatype; 1031902.6 – How Does Oracle Store Internal Numeric Data. For more reading, Chapter 12 of Thomas Kyte’s Expert Oracle Database Architecture book provides more explanation.
A numeric column can be defined as NUMBER or NUMBER (p, s), where p = precision (total number of digits) and scale (number of digits to the right of the decimal point). “Each value is stored in scientific notation, with one byte used to store the exponent and up to 20 bytes to store the mantissa. Oracle does not store leading and trailing zeroes.” – Note1031902.6.
For example, if I declare a NUMBER(5,2) column, this means I can store values up to precision (p) = 5 and scale (s) =2. Since values are represented in scientific notation, the number 123.45 has p=5 and s=2. It is interesting to note the number 10000 will not fit in a NUMBER(5,2) column because 10000.00 has 7 digits in total when converted to scale of 2 – a similar example is explained in Tom’s book.
Interesting thing is, how the column is defined in the table does not have a direct relation with the size (bytes) of the NUMBER it stores. To demonstrate, I will create a table with a NUMBER(5,2) column and insert a few values, then use the dump() and vsize() to show the internal representation:
SQL> create table number_test (i number(5,2));
Table created.
SQL> insert into number_test (i) values (123.45);
1 row created.
SQL> insert into number_test (i) values (12.23);
1 row created.
SQL> insert into number_test (i) values (1);
1 row created.
SQL> insert into number_test (i) values (0.99);
1 row created.
SQL> insert into number_test (i) values (1234.12);
insert into number_test (i) values (1234.12)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
SQL> insert into number_test (i) values (100);
1 row created.
SQL> commit;
Commit complete.
SQL> col dump format a40
SQL> select i, dump(i) as dump, vsize(i) as vsize from number_test;
I DUMP VSIZE(I)
---------- ------------------------------ ----------
123.45 Typ=2 Len=4: 194,2,24,46 4
12.23 Typ=2 Len=3: 193,13,24 3
1 Typ=2 Len=2: 193,2 2
.99 Typ=2 Len=2: 192,100 2
100 Typ=2 Len=2: 194,2 2
Interesting results to observe from the above output, the dump() function showed the length of NUMBER values, as well as how the NUMBER is internally represented. For example, 123.45 is represented in 4 bytes and the value of each byte is 194, 2, 24, 46 respectively. How these four bytes represent the actual value is explained in Note 1007641.6.
Perhaps what us mere mortals are really after, is a simple formula that can calculate the size of the NUMBER values. This is given in the Oracle Concept Guide and Metalink Note in a slightly different form:
Oracle Concept Guide: ROUND((length(p)+s)/2))+1, where p = precision of the value, s = 0 if value is positive; s = 1 if value is negative.
Metalink Note 1031902.6: FLOOR[(p+1)/2] + 1 and add +1 byte (only for negative numbers where the number of significant digits is less than 38), where p = prevision of the value and scale has no effect.
The mind-boggling thing is these two formulas are actually the same, with a little interpretation required: length(p) actually means the number of significant digits without leading or trailing zeroes, and the p in the Metalink Note formula actually meant length(p). So following my examples above:
| Number | length(p) | ROUND((length(p)+s)/2)+1 | FLOOR((p+1)/2)+1 |
| 123.45 | 5 | ROUND(5/2)+1 = 4 | FLOOR((5+1)/2)+1 = 4 |
| 12.23 | 4 | ROUND(4/2)+1 = 3 | FLOOR((4+1)/2)+1 = 3 |
| 1 | 1 | ROUND(1/2)+1 = 2 | FLOOR((1+1)/2)+1 = 2 |
| 0.99 | 2 | ROUND(2/2)+1 = 2 | FLOOR((2+1)/2)+1 = 2 |
| 100 | 1 | ROUND(1/2)+1 = 2 | FLOOR((1+1)/2)+1 = 2 |
It is not hard to prove mathematically that ROUND(x/2)+1 = FLOOR((x+1)/2)+1, where x >= 0.
Failover for 9i RAC
– Edit: I have updated the title of this post to include 9i. New failover features are available in 10g, so this article is a little out-of-date.
Over the week, my colleagues and I discussed application failover on a Oracle9i RAC database. I don’t know much about how RAC works, so I decided to do some research and post some notes here. Hopefully, this information can add to someone else’s researching into understanding the technology.
Disclaimer: What I am posting here isn’t something new, the information is freely available in Oracle documentation. If you find the information I post here is wrong, please kindly let me know. By all means, test the technology for yourselves. If you would like to ask a question, please feel free and I will try research a bit more to give an educated answer when I have time.
To start this off, I would imagine I have a 2-node Oracle9i RAC database and the simplest client tnsnames entry would look like this:
BLOGRAC =
(DESCRIPTION =
(LOAD_BALANCE = ON)
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=blograc1)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=blograc2)(PORT=1521))
)
(CONNECT_DATA = (SERVICE_NAME=BLOGRAC))
)
The first failover feature is connect-time failover, where “a client connect request is forwarded to another listener if a listener is not responding”[source][source]. To enable this, the client tnsnames entry might look like this:
BLOGRAC =
(DESCRIPTION =
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=blograc1)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=blograc2)(PORT=1521))
)
(CONNECT_DATA = (SERVICE_NAME=BLOGRAC))
)
The next failover feature is Transparent Application Failover (TAF), where “the failover and reestablishment of application-to-service connections. It enables client applications to automatically reconnect to the database if the connection fails, and optionally resume a SELECT statement that was in progress. This reconnect happens automatically from within the Oracle Call Interface (OCI) library.” [source].
TAF has several restrictions [source]:
- All PL/SQL package states on the server are lost at failover
ALTERSESSIONstatements are lost- If failover occurs when a transaction is in progress, then each subsequent call causes an error message until the user issues an OCITransRollback call. Then Oracle issues an Oracle Call Interface (OCI) success message. Be sure to check this message to see if you must perform additional operations.
- Oracle fails over the database connection and if
TYPE=SELECTin theFAILOVER_MODEsection of the service name description, Oracle also attempts to fail over the query - Continuing work on failed-over cursors can result in an error message
To enable this feature, the client tnsnames entry might look like this:
BLOGRAC =
(DESCRIPTION =
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=blograc1)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=blograc2)(PORT=1521))
)
(CONNECT_DATA =
(SERVICE_NAME=BLOGRAC)
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic))
)
)
The TYPE are session| select | none, and METHOD are basic | preconnect [source].
As stated above, TAF is only supported on JDBC OCI drivers and not on JDBC Thin drivers. I found a couple of Oracle Metalink Notes that re-iterates this, although they are updated for Oracle10g, they still apply to Oracle9i. I also found a note which explains how to write Java code to enable OCI TAF callbacks:
Note:297490.1 Is TAF (Transparent Application Failover) Supported On The Latest JDBC Thin Driver (10g)?
Note:465423.1 Can the JDBC Thin Driver Do Failover by Specifying FAILOVER_MODE?
Note:276225.1 JDBC Sample on How to Implement OCI TAF callbacks with DML and PL/SQL
This is an aside… I wanted to know what error will occur to a Java application [source] when there’s instance shutdown or failure. I have written a very simple Java program to test this, and it will query results from a big table I created on a Oracle9i RAC database, using the Oracle9i JDBC Thin Driver:
1. Oracle instance shutdown
Vendor Error Code: 1089
java.sql.SQLException: ORA-01089: immediate shutdown in progress – no operations are permitted
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oclose.receive(Oclose.java:122)
at oracle.jdbc.ttc7.TTC7Protocol.close(TTC7Protocol.java:683)
at oracle.jdbc.driver.OracleStatement.close(OracleStatement.java:644)
at TestDBOracle.main(TestDBOracle.java:49)
2. Oracle instance failure (by killing the pmon and smon processes) – yes, it came out with funny characters…
Vendor Error Code: 17410
java.sql.SQLException: �S���q��yŪ����
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160)
at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:963)
at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893)
at oracle.jdbc.ttc7.Oclose.receive(Oclose.java:101)
at oracle.jdbc.ttc7.TTC7Protocol.close(TTC7Protocol.java:683)
at oracle.jdbc.driver.OracleStatement.close(OracleStatement.java:644)
at TestDBOracle.main(TestDBOracle.java:49)
[Please note: If your application is getting a 17410 error, it doesn't always mean the database / instance died.]
ARC0: Media Recovery Disabled
Just a quick one… when I was creating a new database today, I encountered this message in the alert log after the database was created and instance restarted. The message repeated itself continuously to the point I felt the need to find out what is going on.
Turns out the instance started the archiver with the database created in NOARCHIVELOG mode:
1. My CREATE DATABASE statement did not include the keyword ARCHIVELOG or NOARCHIVELOG. The default is NOARCHIVELOG.
2. My initialization parameter had log_archive_start=true.
Oracle Metalink Note: 131119.1 explains more about this:
To verify:
SQL> select log_mode from v$database; /* LOG_MODE is NOARCHIVELOG */
SQL> show parameter log_archive_start; /* VALUE is TRUE */
I am not going to copy the fix on the Note here. To fix my problem, I set log_archive_start=false in the initialization parameters and restarted the instance. I wanted my new database to be in NOARCHIVELOG mode initially.
The log_archive_start parameter is deprecated in Oracle10g (10.1).
