Note: I installed MySQL on my labtop (a Windows XP machine) which is
located at the same site as the Oracle server, just to compare the insert
times with the remote MySQL database. Inserts which were taking over 4
minutes were now completing in 90 seconds or less. Approximately 1 second
per insert is not great, but it is a lot better than what we were seeing
before. So, we are planning on moving our MySQL database server to the
same site as the Oracle database.
Any other performance tuning suggestions would be be appreciated.
Thanks!
Bob
[EMAIL PROTECTED]
19-Jul-2004 11:35 EST
To: [EMAIL PROTECTED]
cc:
Subject: Slow Insert into MyISAM table from Oracle Stored
Procedure
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.
**********************************************************************
