PL/SQL vs. SQL*Plus remote query/insert problem

From: Vikas Agnihotri <vikas_at_mink.att.com>
Date: 1996/05/23
Message-ID: <31A48470.24A1_at_mink.att.com>#1/1

Here are the details:

In init.ora: OPTIMIZER_MODE=FIRST_ROWS

1. IN SQl*plus via a anonymous PL/SQL block BEGIN insert into closed_mr select * from closed_mr_at_bmpitrpt where filedate=19950120; insert into mr_activity select * from mr_activity_at_bmpitrpt where (mr_id,mr_revision) in (select mr_id,mr_revision from closed_mr_at_bmpitrpt where filedate=19950120); COMMIT; END;

This block takes a very very long time to execute. For all practical purposes, it hangs. Monitoring V$SHARED_SERVER, V$DISPATCHER, etc on server side shows that bytes/messages are being transfered all right. But the operation does not complete.

2. In SQL*Plus:

        The EXACT same insert statements MINUS the BEGIN and END. These come back in a flash....

Could someone please tell me what the difference between the above 2 methods is?

 Does enclosing the 2 statements in a anon. PL/SQL block change the execution plan or something?

HELP!!!! --Vikas Agnihotri

   vikas_at_mink.att.com Received on Thu May 23 1996 - 00:00:00 CEST