Re: DBD::Oracle dbd_st_execute slow speed

  On 16/12/2010 10:08 AM, [email protected] 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"<[email protected]>
> 12/16/2010 07:29 AM
>
> To
> <[email protected]>
> 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:
>
> system@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:
>
> system@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
>