On 16/12/2010 10:08 AM, ericbamba...@discover.com wrote:
something for 1.29 I guess I am fully booked for 1.28:(
BTW Peat are you able to give the release 1.27 candidate a quick spin??
http://www.pythian.com/news/wp-content/uploads/DBD-Oracle-1.27-RC1.zip
cheers
John
I won't be getting a 10046 trace as it took me several days just to
truncate the table. Yes, in dev. Yay for the paperwork of large
corporations. However, if you're curious, you might be able to recreate
this by having 2 tables related by a FK, insert a few million junk rows
into one then use DELETE FROM $TABLE. Then try to insert in the other.
Multiple versions of perl using multiple versions of DBI and DBD::Oracle
exhibited the problem while sqlplus and sql developer did not so it might
be easy to hit this issue.
"Peter J. Holzer"<h...@wsr.ac.at>
12/16/2010 07:29 AM
To
<dbi-users@perl.org>
cc
Subject
Re: DBD::Oracle dbd_st_execute slow speed
On 2010-12-16 07:15:02 -0500, John Scoles wrote:
On 16/12/2010 7:06 AM, Ludwig, Michael wrote:
-----Original Message-----
From: John Scoles
More likely SQLplus is spawning a thread while DBD::Oracle does not.
You mean performing the actual work in the background while making
the prompt available for the user to enter the next command?
yep It might I could ask an oracle buddy of mine who works on it if
you want?
Its been a while since the last time I tried to get OCI treads to
work but in the case of an update statement it would make perfect
sense to use them for that as there is no 'return' from the DB like
'select' statement.
Sqlplus does display the result of the insert (either "1 row created."
or a suitable error message (like "ORA-00001: unique constraint
(FIWPROD.SYS_C0028271) violated") before the next prompt, so I doubt
very much that it does anything in the background.
SQLplus might also be using the array interface under the hood for
all inserts which could be faster.
It might, but for a single row that shouldn't make much difference.
The 10046 trace will be interesting ...
My guess is that oracle uses an index when the query comes from sqlplus,
but doesn't when the query comes from perl. It is sometimes hard to
determine why Oracle chooses a specific plan.
Oh, and I think it hasn't been mentioned that you can display plans for
queries which have already been executed.
First find the query:
sys...@dbi:Oracle:fiw> select sql_id, child_number from v$sql
where sql_text= 'select * from setcoords sc where sc.base_set=:p1';
+-------------+------------+
|SQL_ID |CHILD_NUMBER|
+-------------+------------+
|9bvzsg998zgy5|0 |
|9bvzsg998zgy5|1 |
|9bvzsg998zgy5|2 |
+-------------+------------+
[3 rows of 2 fields returned]
then get the plan for the query:
sys...@dbi:Oracle:fiw> SELECT * FROM
table(DBMS_XPLAN.DISPLAY_CURSOR('9bvzsg998zgy5', 2));
+-------------------------------------------------------------------------------+
|PLAN_TABLE_OUTPUT |
+-------------------------------------------------------------------------------+
|SQL_ID 9bvzsg998zgy5, child number 2 |
|------------------------------------- |
|select * from setcoords sc where sc.base_set=:p1 |
| |
|Plan hash value: 1863347061 |
| |
|-------------------------------------------------------------------------------|
|| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
||
|-------------------------------------------------------------------------------|
|| 0 | SELECT STATEMENT | | | | 7529 (100)| ||
||* 1 | TABLE ACCESS FULL| SETCOORDS | 87312 | 1961K| 7529 (2)|
00:01:31 ||
|-------------------------------------------------------------------------------|
| |
|Predicate Information (identified by operation id): |
|--------------------------------------------------- |
| |
| 1 - filter("SC"."BASE_SET"=TO_NUMBER(:P1)) |
| |
+-------------------------------------------------------------------------------+
[18 rows of 1 fields returned]
You need special privileges for that, though. I don't think a normal
user can do it even for their own queries.
hp
