Database Reference
In-Depth Information
â–
The
SCOTT
account or whatever account you use to test this with will need to have the
EXECUTE
privilege
granted on the
DBMS_MONITOR
package.
Note
Now, the method
doInserts()
is fairly straightforward. It starts by preparing (parsing) an
INSERT
statement so we
can repeatedly bind/execute it over and over:
static void doInserts(Connection con, int count, int commitCount )
throws Exception
{
PreparedStatement ps =
con.prepareStatement
("insert into test " +
"(id, code, descr, insert_user, insert_date)"
+ " values (?,?,?, user, sysdate)");
It then loops over the number of rows to insert, binding and executing the
INSERT
over and over. Additionally, it
checks a row counter to see if it needs to
COMMIT
or not inside the loop :
int rowcnt = 0;
int committed = 0;
for (int i = 0; i < count; i++ )
{
ps.setInt(1,i);
ps.setString(2,"PS - code" + i);
ps.setString(3,"PS - desc" + i);
ps.executeUpdate();
rowcnt++;
if ( rowcnt == commitCount )
{
con.commit();
rowcnt = 0;
committed++;
}
}
con.commit();
System.out.println
("pstatement rows/commitcnt = " + count + " / " + committed );
}
}
Now we'll run this code repeatedly with different inputs and review the resulting
TKPROF
file. We'll run with
100,000 row inserts—committing 1 row at a time, then 10, and so on. The resulting
TKPROF
files produced the
results in Table
6-1
.
Search WWH ::
Custom Search