Completed OCE and OCA
I have taken a brief break from blogging for some personal reasons, and lately I have taken the time to pass and complete a couple of Oracle certifications: 1Z0-047 (Oracle Database SQL Expert Exam), and 1Z0-042 (Oracle Database 10g: Administration I Exam).
It’s certainly quite an experience. I must say on-the-job experience helped a lot, and reading up the Oracle documentation helped me to learn a lot of concepts that I obviously overlooked, such as restrictions and notes about a specific feature.
My April Fool’s Joke
I sent this around at work yesterday as an April Fool’s joke. Sadly, I don’t think anybody understood my humor. I am posting here for your viewing pleasure 
Hi all,
I am participating in Oracle12c Beta Testing, and I have installed it on MODDB. Please feel free to take a look! (My guess is ‘c’ stood for Cloud Computing, which brings it more inline with the current trend/hype… marketing perhaps?)
My Top 10 favourite features include:
(https://otn.oracle.com/db121/patchnotes?authid=SLOOFLIRPA)
· Apparently, it is now possible to view all Oracle hidden parameters using ‘show hidden parameter’.
· Parallel Queries can now be load balanced across RAC clusters.
· Rollback of DDLs is now possible using savepoints.
· Indexed Organised Tables was previously built as a B*Tree, it is now possible to build IOTs as a Bitmap.
· Listener no longer listens on 1521 by default due to security reasons. It now defaults to 2521.
· Fast Application Failover (FAF). In addition to the Fast Connection Failover introduced in 10g RAC, this feature now also allows in-flight transaction and query to transparently failover to surviving nodes in the cluster.
· Online Transparent Table Redefinition. Now it is possible to perform table redefinition without using DBMS_REDEFINITION package. DDLs now no longer put a table lock on the table.
· OCFS2 1.8 comes default with Oracle12c.
· LONG datatype was deprecated in favour of LOB datatype, but was kept for backward compatiability. It can now store variable-length character data containing up to 4 gigabytes of information (previously 2 gigabytes).
· SQL*Plus has a bash shell type feature to recall previous commands.
rgds,
fred
Client side connect time load balancing for RAC
Recently, I took an interest in studying how various load balancing features work in a 10g RAC. I thought Jim has written a very good article on this, in a language most people can understand. In my opinion, there are 3 types of load balancing on a 10g RAC. I say in my opinion because Run Time Load Balancing relies on Server Side Load Balancing configurations, but I have separated it out anyways.
- Client Side Load Balancing
- Server Side Load Balancing
- Run Time Load Balancing (using Implicit Connection Cache)
In this post, I wanted to test for evidence of Client Side Load Balancing, using Client Side Oracle Net Tracing.
Definition
Client Side Load Balancing is by definition “evenly spreads new connection requests across all listeners”, and “Oracle Database randomly selects an address in the address list and connects to that node’s listener. This provides a balancing of client connections across the available listeners in the cluster.” [source]. Another definition, “whereby if more than one listener services a single database, a client can randomly choose between the listeners for its connect requests. This randomization enables all listeners to share the burden of servicing incoming connect requests.” [source].
Client Side Load Balancing is configured by adding LOAD_BALANCE=ON in tnsnames.ora file. The Net Services Reference tells us how and were to embed this parameter – “embed this parameter under either the DESCRIPTION_LIST parameter, the DESCRIPTION parameter, or the ADDRESS_LIST parameter.” [source]. Note however, whether you place LOAD_BALANCE under ADDRESS_LIST or outside, will make a different in terms of load balancing ADDRESS_LISTs or load balancing ADDRESSes.
Configuration
I have a 2-node 10g RAC cluster, and for load balancing, I have the following entry in my tnsnames.ora file.
MODDB=
(description=
(load_balance=on)
(address=(protocol=tcp)(host=moddb1-vip)(port=2521))
(address=(protocol=tcp)(host=moddb2-vip)(port=2521))
(connect_data=
(service_name=MODDB)
)
)
I want to trace the Client Side Load Balancing behaviour from the client side, so I enable Oracle net tracing by adding the following lines in sqlnet.ora [source], and I should fine trace files for each physical connection made under $ORACLE_HOME/network/trace.
TRACE_LEVEL_CLIENT = USER TRACE_FILE_CLIENT = SQLTRC
Testing a connection
We can first test one connection…
$ sqlplus -s tester/tester@MODDB exit
Now take a look at the trace file produced, under $ORACLE_HOME/network/trace. An inspection of of the trace file shows two entries that are of interest (it is possible to observe server-side load balancing as well, but that’s a topic for another post):
niotns: Calling address: (description=(load_balance=on)(failover=on)(address=(protocol=tcp)(host=moddb1-vip)(port=2521))(address=(protocol=tcp)(host=moddb2-vip)(port=2521))(connect_data=(service_name=MODDB)(CID=(PROGRAM=e:\oracle\product\10.2.0\client_1\bin\sqlplus.exe)(HOST=LADDO)(USER=tester)))) nsc2addr: (DESCRIPTION=(load_balance=on)(failover=on)(address=(protocol=tcp)(host=moddb2-vip)(port=2521))(connect_data=(service_name=MODDB)(CID=(PROGRAM=e:\oracle\product\10.2.0\client_1\bin\sqlplus.exe)(HOST=LADDO)(USER=tester))))
Unfortunately, I couldn’t find much documentation on how to read a trace file, I can only guess…
* niotns entry shows a lookup of tns information based on the service name I supplied “MODDB”.
* nsc2addr entry shows Oracle Net connects to listener on moddb2-vip.
It’s difficult to observe load balancing with just one connection, so I wrote a script to run 1000 connections, using Bash Shell scripting:
#!/bin/bash
for a in {1..1000}
do
echo $a
sqlplus -s tester/tester@MODDB<<EOF
EOF
done
exit 0
Examining the traces
Extending the idea above, we can use a combination of grep, and wc to see the result of load balancing. For some reason, each connection produced 2 trace files, so we have 2000 trace files for 1000 connections.
$ ls -l *.trc |wc -l 2000 $ grep nsc2addr *.trc | grep load_balance |grep moddb1-vip |wc -l 498 $ grep nsc2addr *.trc | grep load_balance |grep moddb2-vip |wc -l 502
We can see over 1000 connections, 498 were made to moddb1-vip listener, whilst 502 were made to moddb2-vip listener. It is a fairly even distribution. But just how random is it? I have used the output of the trace files, and plot the first 100 connections on a scattered chart. The first note one can observe is that, these connections are not in a round-robin fashion. (1 = moddb1-vip; 2 = moddb2-vip)
Conclusion
As demonstrated above, it is quite simple to setup Oracle Net tracing on the client, to test and show whether Client Side Load Balancing is working properly. It is worth to note (again), that this type of load balancing has nothing to do with balancing server side load. It is to do with balancing load across the listeners.
Yonex Nanospeed 7000
Having recently broken my Yonex Nanospeed 6000 racquet, I purchased a Yonex Nanospeed 7000 racquet. I thought I would write a few random thoughts…
Disclaimer: I am certainly not a graded badminton player, and I play casually at my local club mainly doing doubles. The following thoughts are my own impressions, given my play style, experiences and skills (or lack thereof). Always consult experts when choosing your own racquet.
I have been reading badminton forums a lot about how to choose a racquet. I found that it was important to first understand my own play style – receive or control based, or smash based, and the speed of my racquet swing… etc. Secondly, it was really important to learn some of the terminology used to describe a badminton racquet like head light/heavy, extra-stiff/stiff/flex, grip size… etc, because it helps to establish some criteria to choose a racquet. If you are into the Yonex brand, make sure you take a look at their latest Yonex Racquet Chart (search google).
I read on some forum post that 95% of the game depends on techniques, and 5% on racquet. This is true because no matter how fancy is the racquet you are wielding, if you don’t have the skills to go with it, its power is not maximised. But I would add that choosing the wrong racquet that doesn’t suit your own style can actually worse your game.
NS6000:
- A head-light racquet, feels a bit funny at first but can get used to it at play time.
- I bought this racquet based on a local retail shop’s recommendation. I told them the majority of my game is doubles and received based.
- Sometimes I don’t feel the shuttle hitting the racquet, a bit too soft…
- Very easy to manoeuvre and defend smashes with… (but in the end, I thought to myself, something must be wrong with my game if I am always in a defensive position).
- Paints were chipping off quiet easily (or you may like to say I am very rough)…
- A flexible shaft racquet, plays well with whippy action. Clears are no problems if I *whip* it properly.
- A bit hard to smash with, really have to try hard.
- I can perform drops pretty well with this racquet (but not as good as the NS7000).
- My net game is quite poor, so I can’t comment on this.
NS7000:
- A head-light racquet, but I feel it is not as light as NS6000. It has a much more solid feel than NS6000.
- I bought this racquet based on what I read in the forums, tried a few swings in the shops. It was a decision between NS7000 and NS8000, but the NS8000 is classed as extra-stiff, which I was a bit weary of… I wanted to defend well, but be a bit more attacking when opportunities arise. I smash quite a bit…
- It has a stiff shaft, as opposed to a flexible shaft on a NS6000. Which I think a faster swing is required to generate the power. Not very good for *whippy* style… It took some getting used before I can hit the shuttle properly.
- Techniques and especially footwork has to be spot on with this racquet. Hitting without the right technique will likely result in lollypopping into opponent’s mid-court for a smash.
- Drops are much more accurate, and controlled.
- Smashes are more powerful, a little faster than what I can do with NS6000.
Head-heavy racquets are definitely not for me, I tried hitting with Arcsaber 10, it’s a bit too heavy for me to handle, maybe because of my weak wrist… In summary, I am quite happy with my new purchase…. now if only I can improve on my techniques and stamina on the court
Installing Oracle10g JAccelerator (NCOMP) on Solaris 10 (SPARC)
Let’s suppose you find the need to natively compile some Java code on an Oracle10g database, then you would need to have JAccelerator (NCOMP) installed. I have prepared a few installations steps to follow that worked for me. It took me a few days to work this out…
Disclaimer: These steps were tested on Solaris 10 SPARC platform, with a Oracle10g 10.2.0.4 standalone database. Best efforts were made to ensure the steps are correct, but please take extra care when executing them on your platform. Do not run this on your Production environment if you are unsure what it will do.
Let’s start from the beginning…
1. To find out whether you have NCOMP installed, there are two things you can do:
select dbms_java.full_ncomp_enabled from dual * ERROR at line 1: ORA-29558: JAccelerator (NCOMP) not installed. Refer to Install Guide for instructions. ORA-06512: at "SYS.DBMS_JAVA", line 236
or try executing this on command line, which should show two lines if NCOMP is installed.
$ORACLE_HOME/OPatch/opatch lsinventory -detail |grep JAccelerator JAccelerator (COMPANION) 10.2.0.1.0 JAccelerator (COMPANION) Patch 10.2.0.4.0
2. Suppose NCOMP is not installed, then you would need to get the Oracle10g Companion CD (downloadable here). Run the installer, and select the “Oracle Database 10g Products” option which will install into your existing Oracle home. Full installation guide is available here. This will install JAccelerator 10.2.0.1.0 along with other 10g Companion products. If you run the opatch command again, you should now see JAccelerator listed.
3. Next, we need to patch JAccelerator to the latest patchset – 10.2.0.4. Download the 10.2.0.4 patchset from Metalink and start the installer as usual. Metalink Note 293658.1 explains that the OUI will see the new components installed and only install the 10.2 patches associated to the new products. Re-run catupgrd.sql and utlrp.sql .
4. After completing the patchset installation, re-running the first two checks should show you some good news.
SQL> select dbms_java.full_ncomp_enabled from dual; FULL_NCOMP_ENABLED ---------------------------------------------------- OK
$ORACLE_HOME/OPatch/opatch lsinventory -detail |grep JAccelerator JAccelerator (COMPANION) 10.2.0.1.0 JAccelerator (COMPANION) Patch 10.2.0.4.0
5. Before one can natively compile anything, we need to follow a few more steps as documented here. Verify a C compiler and linker is installed, and the ncomp properties file knows their full path. Suppose your platform is like mine, then it means you probably need a proper C compiler:
$ which cc /usr/ucb/cc $ cc /usr/ucb/cc: language optional software package not installed
6. Metalink Note: 43208.1 documents a list of Certified Compilers. For Solaris SPARC:
* 10.1.0 Sun ONE Studio 8, C/C++ 5.5 * 10.2.0 Sun ONE Studio 8, C/C++ 5.5 * 11.1.0 Sun ONE Studio 11, C/C++ 5.8 Note: Sun ONE Studio 8 or higher is supported with 9.2, 10.1, 10.2
In any case, I downloaded Sun Studio 11 software here. This software installation requires root privilege and installs the C compiler into /opt/SUNWspro by default. Check all system requirements before installation. As an aside, I also tried gcc 3.4.6 but NCOMP didn’t like it very much…
7. After the Sun Studio software is installed, update the PATH and LD_LIBRARY_PATH environment variables, e.g.
export PATH=/opt/SUNWspro/bin:$PATH export LD_LIBRARY_PATH=/opt/SUNWspro/lib:$LD_LIBRARY_PATH
8. Inside the $ORACLE_HOME/javavm/jahome directory, you will possibly find two properties files.
$ grep 'CC =' Settings*.properties Settings.properties:CC = cc Settings_os.properties:CC = /opt/SunProd/SUNWspro8/bin/cc
Update these two lines to the C compiler location, such that if I do another grep:
Settings.properties:CC = /opt/SUNWspro/bin/cc Settings_os.properties:CC = /opt/SUNWspro/bin/cc
9. We are just about done… to enable the database user to perform native compilation, the user needs to be granted certain privileges as documented here, where /app/oracle/10/db is my $ORACLE_HOME:
grant java_deploy to fredt;
grant javasyspriv to fredt;
call dbms_java.grant_permission('FREDT', 'java.io.FilePermission', '/app/oracle/10/db/-', 'read,write');
10. We can now ncomp away!
ncomp -u fredt/fredt@MODDB <myJar.jar>
PL/SQL Instrumentation using ILO
I worked on a project to develop a domain index using the Oracle Data Cartridge Interface last year, When I was testing my code, I needed to know what the progress and state of the PL/SQL execution, and found dbms_output.put_line() inadequate as it only outputs when the call is returned to SQL*Plus, i.e. the output is not displayed during run-time. This is better explained by Robert Vollman. That was until Douglas showed me how to use DBMS_APPLICATION_INFO, MODULE, ACTION and CLIENT_INFO.
Whilst reading blogs of my favourite Oracle bloggers, I found Instrumentation Library for Oracle (ILO) developed by Method-R, presented here by Karen Morton. Instrumentation is described by Tom Kyte in this blog post. The idea behind ILO is quite interesting, a comment by Cary Millsap reveals the motivation behind this tool. I downloaded ILO 2.2, installed it on my 10g test database and test ran a very simple test case. I will write a few words about what I found.
I start off with a very simple test code, I granted myself privilege to execute dbms_lock:
DECLARE
BEGIN
-- Start Task 1
ILO_TASK.BEGIN_TASK(module=>'ILO Tester', action=>'Task 1', comment=>'Time 0s', begin_time=>sysdate);
dbms_lock.sleep(10);
-- Start Task 2
ILO_TASK.BEGIN_TASK(module=>'ILO Tester', action=>'Task 2', comment=>'Time 10s',begin_time=>sysdate);
dbms_lock.sleep(10);
-- End Task 1
ILO_TASK.END_TASK(end_time=>sysdate);
-- End Task 2
ILO_TASK.END_TASK(end_time=>sysdate);
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('Exception thrown');
ILO_TASK.END_ALL_TASKS(p_error_num=>SQLCODE, p_end_time=>sysdate);
END;
I am logged on the database using SQL*Plus, before I execute the above PL/SQL block, my session information looks like this:
SQL> select module, action, client_info from v$session where username='FREDT'; MODULE ACTION CLIENT_INFO --------------- --------------- --------------- SQL*Plus
When the PL/SQL block is executed:
MODULE ACTION CLIENT_INFO --------------- --------------- --------------- ILO Tester Task 1
10 seconds later inside the PL/SQL block:
MODULE ACTION CLIENT_INFO --------------- --------------- --------------- ILO Tester Task 2
10 seconds more, and PL/SQL block exits:
MODULE ACTION CLIENT_INFO --------------- --------------- --------------- SQL*Plus
ILO manages tasks in a stack like data structure, therefore, a developer can start multiple tasks using BEGIN_TASK and end the last (started) task using END_TASK, or end all tasks using END_ALL_TASKS. To view session information, one needs either DBA or SELECT ANY DICTIONARY privilege.
This most basic use of ILO requires continuous query on v$session to get anything meaningful. If one wants to take this further, ILO provides the framework to do so. For example, one can enable tracing by inserting this line of code at the beginning of the PL/SQL block (after the line BEGIN):
ILO_TIMER.set_mark_all_tasks_interesting(true, true);
A trace file will be produced in USER_DUMP_DEST, and one can find ILO information inside the trace file. It seems feasible to develop a script which can extract this information out of a trace file into something meaningful or simply more human-readable:
ILO_TASK.BEGIN_TASK[][ILO Tester][Task 1][oracle~pts/2~sqlplus@moddb1 (TNS V1-V3)~SYS$USERS][Time 0s] *** 2009-01-13 16:50:02.919 *** ACTION NAME: (Task 1) 2009-01-13 16:50:02.919 *** MODULE NAME: (ILO Tester) 2009-01-13 16:50:02.919 ILO_TASK.BEGIN_TASK[][ILO Tester][Task 2][oracle~pts/2~sqlplus@moddb1 (TNS V1-V3)~SYS$USERS][Time 10s] *** 2009-01-13 16:50:12.921 *** ACTION NAME: (Task 2) 2009-01-13 16:50:12.921 *** MODULE NAME: (ILO Tester) 2009-01-13 16:50:12.921 ILO_TASK.END_TASK[][ILO Tester][Task 2][oracle~pts/2~sqlplus@moddb1 (TNS V1-V3)~SYS$USERS][Time 10s][] *** ACTION NAME: (Task 1) 2009-01-13 16:50:22.931 *** MODULE NAME: (ILO Tester) 2009-01-13 16:50:22.931 ILO_TASK.END_TASK[][ILO Tester][Task 1][oracle~pts/2~sqlplus@moddb1 (TNS V1-V3)~SYS$USERS][Time 0s][] *** ACTION NAME: () 2009-01-13 16:50:22.931 *** MODULE NAME: (SQL*Plus) 2009-01-13 16:50:22.931
After reading the ILO source code, and playing around, I could think of a few more ways to take this idea further, but it will require additional development of the library. Most of the “hooks” are inside ILO_TIMER. Indeed, I believe that is why ILO is licensed as LGPL, allowing developers the flexibility to decide how they want to make use of the instrumentation.
Perhaps I will write about these ideas in another post. My parting thoughts were how to have Developers instrument their code the same way, if I am the DBA. Perhaps, if DBA=Developer then it would be a different story. I think about Apache Log4j and if the same adoption can be applied to a tool like ILO, that would be great.
HP-UX PA-RISC or Itanium
Don’t do what I did… I got this error when mistakenly installing Oracle 10gR2 (10.2.0.1) HP-UX PA-RISC distribution on a HP-UX Itanium platform:
ar: warning: the file <some_file> is from an incompatible architecture
Metalink Note: 603735.1 - HP-UX: “Incompatible Architecture” Error During The Relink Phase
… explains this.
uname -m
On HP-UX Itanium systems, the “uname -m” command returns “ia64″.
On HP-UX PA-RISC systems, the “uname -m” command returns something similar to “9000/800″.
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.
