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
--
_ | Peter J. Holzer | Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: "Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern."
__/ | http://www.hjp.at/ | -- Wolfram Heinrich in desd
signature.asc
Description: Digital signature
