Home » RDBMS Server » Performance Tuning » BULK COLLECT issue (10.2.0.1)
BULK COLLECT issue [message #555276] Tue, 22 May 2012 21:47 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Dear all,
The SQL1 and SQL2 are do the same thing that get data into temp table(nologging mode),the SQL2 is slower than SQL1,why?

SQL1:
insert /*+append */into mail_batchsendnotify_tmp
   (
     mobilenum,
     ispsid,
     readcount,
     seqno
    )
   select /*+PARALLEL(c,2) */
     mobilenum,
     c.ispsid,
     c.ReadCount,
     seqno
     from odsview.vw_mail_batch_notify c
    where c.modifytime >= trunc(Sysdate)
      and c.modifytime < trunc(Sysdate) + 1


SQL2:
Declare

  Cursor cur_batch_notify_data
  Is
  select /*+PARALLEL(c,2)*/
     c.mobilenum As usernumber,
     c.ispsid,
     c.seqno,
     c.ReadCount
     from odsview.vw_mail_batch_notify c
    where c.modifytime >= trunc(Sysdate)
      and c.modifytime < trunc(Sysdate) + 1
     ;

  TYPE type_table_SEQNO1 IS TABLE OF mail_batchsendnotify_tmp.seqno%type INDEX BY BINARY_INTEGER; --
  table_SEQNO1 type_table_SEQNO1;


  TYPE type_table_usernumber1 IS TABLE OF mail_batchsendnotify_tmp.MOBILENUM%type INDEX BY BINARY_INTEGER; --
  table_usernumber type_table_usernumber1;

  TYPE type_table_ispsid IS TABLE OF oss03.oss_mail_deliver_effective_tp.ispsid%type INDEX BY BINARY_INTEGER; --
  table_ispsid type_table_ispsid;


  TYPE type_table_readcount IS TABLE OF Number INDEX BY BINARY_INTEGER;
  table_readcount type_table_readcount;

  Type type_table_provcode Is Table Of Number INDEX BY BINARY_INTEGER;
  l_type_table_provcode type_table_provcode;

Begin
  OPEN cur_batch_notify_data;
  LOOP
    FETCH cur_batch_notify_data BULK COLLECT
      INTO table_usernumber,
           table_ispsid,
           table_seqno1,
           table_readcount LIMIT 3000;
    FORALL i IN 1 .. table_usernumber.count
      insert /*+append*/into mail_batchsendnotify_tmp
      (
       mobilenum,
       ispsid,
       seqno,
       readcount
       )
       Values
       (
        table_usernumber(i),
        table_ispsid(i),
        table_seqno1(i),
        table_readcount(i)
       );
       commit;
    EXIT WHEN cur_batch_notify_data%NOTFOUND OR cur_batch_notify_data%NOTFOUND IS NULL;
  END LOOP;
  CLOSE cur_batch_notify_data;
  End;
Re: BULK COLLECT issue [message #555278 is a reply to message #555276] Tue, 22 May 2012 22:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post EXPLAIN PLAN for both SQL

PL/SQL will NEVER be faster than plain SQL.
avoid PL/SQL when plain SQL can provide correct result set!
Re: BULK COLLECT issue [message #555280 is a reply to message #555278] Tue, 22 May 2012 22:24 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks,
BlackSwan, the SQL1 and the SQL2 use the same sql just as the flowing,the difference between them is SQL1 just on a insert sql,and SQL2 use the bulk collect. can you tell me the detail about SQL1 is faster than SQL2?

The same SQL:
  select /*+PARALLEL(c,2)*/
     c.mobilenum As usernumber,
     c.ispsid,
     c.seqno,
     c.ReadCount
     from odsview.vw_mail_batch_notify c
    where c.modifytime >= trunc(Sysdate)
      and c.modifytime < trunc(Sysdate) + 1
     ;
Re: BULK COLLECT issue [message #555281 is a reply to message #555280] Tue, 22 May 2012 22:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post EXPLAIN PLAN for both SQL

>The same SQL:
same as what exactly?

PL/SQL is different from SQL.
PL/SQL has its own "engine"
SQL has its own "engine" which is different from the PL/SQL engine.
only the SQL engine can actually read or write data that resides within the DB.
a Context Switch occurs when control is passed from the SQL engine to the PL/SQL engine
a Context Switch occurs when control is passed from the PL/SQL engine to the SQL engine
a Context Switch is a very resource intensive operation (slow & costly)
Re: BULK COLLECT issue [message #555282 is a reply to message #555281] Tue, 22 May 2012 22:34 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks,
how to put the explan for SQL2,because it is a PL/SQL bolck.
Re: BULK COLLECT issue [message #555283 is a reply to message #555282] Tue, 22 May 2012 22:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
enable SQL_TRACE then process trace file using tkprof
Re: BULK COLLECT issue [message #555287 is a reply to message #555283] Wed, 23 May 2012 00:32 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks!
Re: BULK COLLECT issue [message #555289 is a reply to message #555287] Wed, 23 May 2012 00:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
andy huang wrote on Tue, 22 May 2012 22:32
Thanks!

NO! post results here!
Re: BULK COLLECT issue [message #555295 is a reply to message #555280] Wed, 23 May 2012 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 67950
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition:
- APPEND hint is ignored (in your version) on INSERT VALUES
- PARALLEL does not exist in your PL/SQL version
- PL/SQL loops with context switches between PL/SQL and SQL engines as BlackSwan said.

Regards
Michel
Re: BULK COLLECT issue [message #555313 is a reply to message #555295] Wed, 23 May 2012 03:13 Go to previous messageGo to next message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member
:PARALLEL does not exist in your PL/SQL version

If we use statement inside the pl/sql block then also parrale will not be used?

execute immediate ('alter session enable parallel dml');


Please suggest....
Re: BULK COLLECT issue [message #555316 is a reply to message #555313] Wed, 23 May 2012 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 67950
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Suggest what?
The answer was given, do NOT use PL/SQL.

Regards
Michel
Re: BULK COLLECT issue [message #555319 is a reply to message #555316] Wed, 23 May 2012 03:37 Go to previous messageGo to next message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member
ok....

but suppose i have a big pl/sql block and lots of select query is there for different buisness requirement and we want to use parallel hint, so how we can use, any method to use it and what about the stored proc, in side the whether parallel can use it or not?
Re: BULK COLLECT issue [message #555322 is a reply to message #555319] Wed, 23 May 2012 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 67950
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
we want to use parallel hint


Why? Because you like it? Smile
You can use it. Will you take profit of it is another question.

Regards
Michel
Re: BULK COLLECT issue [message #555331 is a reply to message #555322] Wed, 23 May 2012 04:25 Go to previous messageGo to next message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member
Thanks Michel...

You can use it. --- means use it in both the place(pl/sql and stored proc) or only in stored proc....
Re: BULK COLLECT issue [message #555334 is a reply to message #555331] Wed, 23 May 2012 04:42 Go to previous message
Michel Cadot
Messages: 67950
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use the hint PARALLEL, what I said is that PL/SQL does not run in parallel.

Regards
Michel
Previous Topic: Index skip scan
Next Topic: Privilege to run explain plan
Goto Forum:
  


Current Time: Sun Sep 26 01:08:49 CDT 2021