DBI Users,
I'm having trouble with DBD::Oracle and very simple insert
statements into tables with less than 200 records only 2 FKs and no
indexes taking over 4 seconds. Inserts to other tables seem unaffected. I
can run the exact same statement from SQLPlus or SQL Developer with no
Confirmed. I double checked and they're both there, normal, and valid.
Even so MJMC.ABI_FL_SYS_FMT has about a dozen rows, MJMC.NDE_DATA_HIST has
about 200, and other tools can insert and commit lightning quick. So
unfortunately I'm not sure how the indexes might be relevant here. They
John,
Thanks. Please let me know if you need anyting else. I've attached
two gzipped files - a level 15 trace and my affected perl code module. The
trace should show the prepare then a single execute before die() ing. The
progam is a daemon and will run forever unless I die(). The sub
Martin,
do() takes 4 seconds as well. Sorry about the SQL_INTEGER junk. I
was testing things. It takes the same amount of time if I leave off the
types. I remember seeing the trace show it bind as VARCHAR or something
when I leave off the type and it was still slow.
Here is
List,
I'm not sure if this is relevant but I recently loaded 2 million
rows into a parent table NDE_DATA_HIST then issued a DELETE FROM to delete
all of the rows. I dont have access to TRUNCATE(). The table now has only
about 200 rows. However, the 2 other tools are still fast, its
Recently as in several days ago and the delete finished in about 5
minutes.
I'm not sure how that would affect DBD::Oracle and not my 2 other tools
though.
Let me double check with my DBA about that table.
John Scoles sco...@pythian.com
12/09/2010 11:13 AM
To
ericbamba...@discover.com
cc
Martin,
Most peices of the information you request are in my original
email and in the trace file in my original e-mail. Perhaps it was too
information dense.
-Oracle database version
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning,
John,
Its just the execute that takes a long time. The commit is instant
in DBD::Oracle.
Let me check with the DBA about those indexes. It might take a day
or two.
John Scoles sco...@pythian.com
12/09/2010 11:52 AM
To
dbi-users@perl.org
cc
Subject
Re: DBD::Oracle
Martin, John, list
My DBA ran an analyze table, looked at the indexes and server
load, and confirmed its not an 'Index Organized' table.
Yes, its just the execute() that takes 4 seconds not the commit
which makes the problem even more bizzarre. I would expect the opposite.
No, but another great suggestion! Let me see if that turns up anything
Rudy Lippan rlip...@remotelinux.com
12/13/2010 01:04 PM
To
ericbamba...@discover.com
cc
John Scoles sco...@pythian.com, Martin J. Evans
martin.ev...@easysoft.com, dbi-users@perl.org
Subject
Re: DBD::Oracle
List,
Truncating the table solved the problem. Since I inserted 2
million rows into a parent table then deleted all of them the Oracle high
watermark was very high.
This still doesn't explain the difference between DBD::Oracle and
sqlplus. They must have had different
Maybe an obvious quesiton, but can you catch SQLITE_BUSY and just loop and
retry in your application code? Is there any reason not to retry after a
second or so? Such application retry logic is pretty standard for
transaction deadlock issues.
David E.Wheeler da...@kineticode.com
I don't believe a lockfile would be neccesary. The database serves as your
lockfile. You simply re-start the transaction or re-issue the last SQL
statement until it goes through or you feel like giving up.
However, most RDBMS's will detect unresolvable deadlocks and abort a
transaction
I will be out of the office starting 12/24/2010 and will not return until
01/03/2011.
I will respond to your message when I return.
Please consider the environment before printing this email.
Are you using a nonstandard LIBPATH? Are you mixing/matching 32/64 bit
oracle libraries with a 32/64 bit perl?
We're had to stick to 32 bit oracle libs on our 32 bit AIX for similiar
reasons.
Eric Bambach | Discover
Assoc. Programmer, Warehouse Infrastructure and Tools
2500 Lake Cook Road,
Brandon,
This is probably more of a DB issue than DBI. I would google for
general DB tuning strategies for mysql and high insert scenarios. I bet
your unit of work is too small and you DB can't keep up with the inserts.
Can you switch to using transactions an only commit every N lines
Eirik Toft grep_...@yahoo.com wrote on 10/06/2011 05:04:10 PM:
Eirik Toft grep_...@yahoo.com
10/07/2011 03:51 AM
To
dbi-users@perl.org
cc
Subject
UPDATE statements causing me grief!
Greetings, been a DBI user for years now working with stuff from
unixODBC, Oracle, MySQL,
Paul DuBois p...@snake.net wrote on 10/14/2011 10:40:21 PM:
Paul DuBois p...@snake.net
10/14/2011 10:41 PM
To
Bill Ward b...@wards.net
cc
ZhangJun gb2...@gmail.com, dbi-users@perl.org
Subject
Re: why DBD::mysql need database name for connecttion ?
On Oct 14, 2011, at
18 matches
Mail list logo
