I?ve developed an Oracle PL/SQL stored procedure that takes information
from an Oracle 9.2.0.5 database and inserts this information into a MySQL
4.0.17 MyISAM table. The insert takes over 3 minutes to insert
approximately 90 records based on an Oracle SQL Trace. A few things that
may be factors in the slow performance
I use MySQL ODBC 3.5.1 to connect Oracle to MySQL. Are there any
parameters MySQL ODBC parameters that can be tuned to improve performance?
I?ve tried to turn on tracing, but don?t know if I am doing it correctly
because I am not getting any .trc files. Are .trc files only generated on
errors?
The table in question has 98 columns with 3 text fields. It appears the
insert statements actually inserts all non-?TEXT? fields first and then
updates the record with the ?TEXT? field data. I believe this is the
expected behavior, but it is slowing things down a bit. It would be nice
if I could ?trick? the MySQL database into thinking it?s inserting into a
VARCHAR or CHAR field.
The MySQL server resides a couple of hundred miles away from the Oracle
server so Network latency is a factor. However, we do have a 786KB/s line
with 70 ms latency which isn?t bad.
The only parameter/variable I?ve changed from the default on the MySQL
server is ?ascii. There are probably some memory variables that could be
tuned, but I?m not looking at high volumes yet, so I don?t think that this
would be the bottleneck.
Any suggestions/recommendations would be much appreciated.
Thanks,
Bob Runion
------------------------------------------------------
Here?s the Oracle SQL Trace of the INSERT statement?
INSERT INTO [EMAIL PROTECTED] VALUES (:1, :2, :3, :4, :5, :6, :7,
:8, :9,
:10, :11, :12, :13, :14, :15, :16, :17, :18, :19,
:20, :21, :22, :23, :24, :25, :26, :27, :28, :29,
:30, :31, :32, :33, :34, :35, :36, :37, :38, :39,
:40, :41, :42, :43, :44, :45, :46, :47, :48, :49,
:50, :51, :52, :53, :54, :55, :56, :57, :58, :59,
:60, :61, :62, :63, :64, :65, :66, :67, :68, :69,
:70, :71, :72, :73, :74, :75, :76, :77, :78, :79,
:80, :81, :82, :83, :84, :85, :86, :87, :88, :89,
:90, :91, :92, :93, :94, :95, :96, :97, :98)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 97 1.12 23.37 0 0 0 0
Execute 97 0.23 157.27 0 0 0 97
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 194 1.35 180.64 0 0 0 97
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18 (recursive depth: 1)
**********************************************************************
This e-mail message is intended only for the personal use of the
recipient(s) named above. This message is confidential. If you are not an
intended recipient, you may not review, copy or distribute this message.
If you have received this communication in error, please notify the sender
immediately by e-mail and delete the original message.
**********************************************************************
