Home » RDBMS Server » Performance Tuning » Performance issue with RETURNING BULK COLLECT clause (Oracle 10g on Linux)
icon5.gif  Performance issue with RETURNING BULK COLLECT clause [message #352807] Thu, 09 October 2008 12:59 Go to next message
snomula
Messages: 4
Registered: October 2008
Junior Member
Hello this is my firt time to your forum. Hope I find what I am looking for. I have been asking this question in every forum but couldn't find a right answer.

I have an update statement, which updates table based on some conditions from big tables in below format


UPDATE VOTING.RECONCILIATION REC SET REC.CURRENT_STATUS_ID=4,
  REC.CURRENT_STATUS_DATE=SYSDATE
WHERE
REC.CURRENT_STATUS_ID IN (2, 3, 4) 
AND REC.SHARES_HELD <> (SELECT
          SUM(B.SHARES_AVAILABLE_TO_VOTE) FROM VOTING.BALLOT  B 
          WHERE B.MEETING_ID=
          REC.MEETING_ID 
  AND B.CUSTODIAN_ACCOUNT_ID=REC.CUSTODIAN_ACCOUNT_ID )


The cost of the query was around 32000 which was not very bad. The query took about a second to complete for a small set of data in this table. My whole pl/sql procedure took about 5 seconds to complete which has 3 other similar statements.

I had to update the history table after each of these update statements. So I introduced RETURNING BULK COLLECT clause to capture the primary key of the table that I updated and use those values to update history table. So my query changed as below.

UPDATE VOTING.RECONCILIATION REC SET REC.CURRENT_STATUS_ID=4,
  REC.CURRENT_STATUS_DATE=SYSDATE
WHERE REC.CURRENT_STATUS_ID IN (2, 3, 4) 
AND REC.SHARES_HELD <> (SELECT SUM(B.SHARES_AVAILABLE_TO_VOTE)
                       FROM VOTING.BALLOT  B 
                       WHERE B.MEETING_ID=REC.MEETING_ID AND 
               B.CUSTODIAN_ACCOUNT_ID=REC.CUSTODIAN_ACCOUNT_ID )
RETURNING REC.RECONCILIATION_ID INTO :O0



Soon I introduced this RETURNING BULK COLLECT INTO cluase my plsql took for ever to complete when I did trace on the pl/sql using dbms_profile it showed that each sql took more than 100 seconds to complete. I never understood why RETURNING BULK COLLECT took such a long time. The returning result set is very small. So it couldn't be an issue of memory.

I expiremented my program by using trigger instead of insert statement from the application. That just took 6-7 seconds and didn't hinder the performance. But trigger is not my soultion as I dont have all the necessary data in the trigger to insert into history table.

So, I further expiremented by doing event trace at level 8 and below is what I found. I noticed that with returning clause the query plan has changed. It was doing FULL table scans on those big tables. Now I dont understand why it would change the plan just to return the result set to pl/sql.

Trace with out RETURNING BULK COLLECT clause

UPDATE VOTING.RECONCILIATION REC SET REC.CURRENT_STATUS_ID=4,
  REC.CURRENT_STATUS_DATE=SYSDATE
WHERE
REC.CURRENT_STATUS_ID IN (2, 3, 4) AND REC.SHARES_HELD <> (SELECT
  SUM(B.SHARES_AVAILABLE_TO_VOTE) FROM VOTING.BALLOT B WHERE B.MEETING_ID=
  REC.MEETING_ID AND B.CUSTODIAN_ACCOUNT_ID=REC.CUSTODIAN_ACCOUNT_ID )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      2.02       2.33         61      49963         34          30
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.04       2.34         61      49963         34          30

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 71     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  RECONCILIATION (cr=49963 pr=61 pw=0 time=2333157 us)
     30   FILTER  (cr=49963 pr=61 pw=0 time=1118286 us)
  10005    TABLE ACCESS FULL RECONCILIATION (cr=248 pr=0 pw=0 time=10085 us)
  10003    SORT AGGREGATE (cr=49715 pr=61 pw=0 time=2272740 us)
     32     TABLE ACCESS BY INDEX ROWID BALLOT (cr=49715 pr=61 pw=0 time=2238317 us)
     32      BITMAP CONVERSION TO ROWIDS (cr=49683 pr=40 pw=0 time=1927412 us)
     30       BITMAP AND  (cr=49683 pr=40 pw=0 time=1903078 us)
  10003        BITMAP CONVERSION FROM ROWIDS (cr=20008 pr=0 pw=0 time=247080 us)
370244         INDEX RANGE SCAN CUST_ACCT_ID (cr=20008 pr=0 pw=0 time=65908 us)(object id 81422)
   9639        BITMAP CONVERSION FROM ROWIDS (cr=29675 pr=40 pw=0 time=1592448 us)
4333763         INDEX RANGE SCAN B_MEETING_IDX (cr=29675 pr=40 pw=0 time=93329 us)(object id 86379)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                        61        0.02          0.33
********************************************************************************



Trace with RETURNING BULK COLLECT INTO

UPDATE VOTING.RECONCILIATION REC SET REC.CURRENT_STATUS_ID=4,
  REC.CURRENT_STATUS_DATE=SYSDATE
WHERE
REC.CURRENT_STATUS_ID IN (2, 3, 4) AND REC.SHARES_HELD <> (SELECT
  SUM(B.SHARES_AVAILABLE_TO_VOTE) FROM VOTING.BALLOT B WHERE B.MEETING_ID=
  REC.MEETING_ID AND B.CUSTODIAN_ACCOUNT_ID=REC.CUSTODIAN_ACCOUNT_ID )
  RETURNING REC.RECONCILIATION_ID INTO :O0


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2    111.10     412.43     847638     250209      12205          30
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    111.10     412.43     847638     250209      12205          30

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 71     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  RECONCILIATION (cr=250209 pr=847638 pw=600140 time=412437363 us)
     30   MERGE JOIN  (cr=250209 pr=847638 pw=600140 time=437028259 us)
8815129    SORT JOIN (cr=249961 pr=847508 pw=600010 time=382491306 us)
8815129     VIEW  VW_SQ_1 (cr=249961 pr=628799 pw=381301 time=267282365 us)
8815129      SORT GROUP BY (cr=249961 pr=628799 pw=381301 time=258467231 us)
9261767       TABLE ACCESS FULL BALLOT (cr=249961 pr=247498 pw=0 time=37055074 us)
     30    FILTER  (cr=248 pr=130 pw=130 time=23371787 us)
     30     SORT JOIN (cr=248 pr=130 pw=130 time=11112839 us)
  10005      TABLE ACCESS FULL RECONCILIATION (cr=248 pr=0 pw=0 time=65 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                      16551        0.13         27.89
  direct path write temp                     106159        0.00          0.04
  db file sequential read                       193        0.00          0.04
  direct path read temp                      563399        0.00          1.10
********************************************************************************



Can you please shed some light on why would optimizer change its plan when there is a returning bulk collect clause
Re: Performance issue with RETURNING BULK COLLECT clause [message #353849 is a reply to message #352807] Wed, 15 October 2008 05:55 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Now that you noticed the changed explain plan why don't you try to investigate on the optimizer choice?

Bye Alessandro

[Updated on: Wed, 15 October 2008 05:59]

Report message to a moderator

Re: Performance issue with RETURNING BULK COLLECT clause [message #353949 is a reply to message #353849] Wed, 15 October 2008 16:49 Go to previous messageGo to next message
snomula
Messages: 4
Registered: October 2008
Junior Member
Thanks for your response. I am glad that atlast someone responded to my post. I am new to 10053 event. I have to do some research to run this event. Thanks for the documentation link.

-snomula
Re: Performance issue with RETURNING BULK COLLECT clause [message #354077 is a reply to message #353949] Thu, 16 October 2008 06:29 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try this instead.
UPDATE VOTING.RECONCILIATION REC 
SET REC.CURRENT_STATUS_ID=4,
  REC.CURRENT_STATUS_DATE=SYSDATE
WHERE ROWID IN (
  SELECT r.ROWID
  FROM   VOTING.RECONCILIATION R
  LEFT JOIN (
    SELECT meeting_id, custodian_account_id
         , sum(shares_available_to_vote) AS shares_vote
    FROM   voting.ballot
    GROUP BY meeting_id, custodian_account_id
  ) b
  ON  REC.SHARES_HELD = b.shares_vote
  AND B.MEETING_ID = REC.MEETING_ID 
  AND B.CUSTODIAN_ACCOUNT_ID = REC.CUSTODIAN_ACCOUNT_ID
  WHERE R.CURRENT_STATUS_ID IN (2, 3, 4) 
  AND b.meeting_id IS NULL  -- left join failed
)


Ross Leishman
Previous Topic: OEM performance issue
Next Topic: Which is faster
Goto Forum:
  


Current Time: Fri Dec 09 21:11:27 CST 2016

Total time taken to generate the page: 0.11503 seconds