 Performance issue with RETURNING BULK COLLECT clause [message #352807] |
Thu, 09 October 2008 12:59  |
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 #354077 is a reply to message #353949] |
Thu, 16 October 2008 06:29  |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
