Re: INSERT ALL Statement times out in AN EJB enviornment

nibum2001_at_gmail.com wrote:
> Hi all,,
>
> am now facing a prblm with insert all statement in Oracle...it takes
> too much time greater than two minutes ,,,is there any way to optimize
> my query...
>
> the query given below...
>
> INSERT ALL
> INTO object_version_permission( object_version_guid, permission_guid )
>
> VALUES ( object_version, :permission_guid_ )
> INTO workflow_audit ( workflow_audit_guid, object_version_guid,
> new_ws_instance_guid, notes, old_ws_instance_guid, user_guid)
> VALUES ( SYS_GUID(), object_version, workstage_inst, :note_,
> workstage_inst, :user_guid_ )
> SELECT ovw.object_version_guid object_version, ovw.ws_instance_guid
> workstage_inst
> FROM object_version_permission op
> JOIN object_version_workstage ovw ON op.object_version_guid =
> ovw.object_version_guid
> AND ws_instance_guid != :ws_instance_guid_
> WHERE op.permission_guid IN ( per_guid_ ) ';
>
> per_guid_ variable contains around 100 guids....

I have no clue what your data distribution between the two entities looks like, but is permission_guid indexed? Tune the SELECT piece using a facility like EXPLAIN PLAN, as documented in http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#i19260.

If the INSERT piece itself is slow, you will have to trace further to see what part of it is the problem.

I assume you are on my version (10.2.0), as I have nothing else on which to go. Always try to post OS and database version, as the answer may or may not vary based on that info.

Post the output of plan analysis, and maybe someone can help.

Regards,

Steve Received on Mon Jun 26 2006 - 10:42:23 CDT