We recently had an issue where a particular insert was generating a large amount of CPU time requirements. When we examined the PL\/SQL code, we found the developer had coded the following…<\/p>\n
\r\nbegin\r\n insert into table values(pk);\r\nexception\r\n when dup_val_on_index then\r\n null;\r\nend;\r\n\/\r\n<\/pre>\nWe found the insert was executed over 25 million times per half hour, with only 600 rows actually being inserted. In other words, 99.999% of the time, the exception handler was being fired and any changes generated (for undo, at a minimum) for the insert up to that point needed to be rolled back.<\/p>\n
The purpose of this post is to provide an argument for using a MERGE SQL statement for a single row insert to handle this type of situation. We start with the current code and insert the same primary key value (or attempt to) 100,000 times…<\/p>\n
\r\nSQL> create table t0301(c number primary key);\r\n\r\nTable created.\r\n\r\nSQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'redo size');\r\n\r\n VALUE\r\n----------\r\n 21688\r\n\r\nSQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'CPU used by this session');\r\n\r\n VALUE\r\n----------\r\n 3\r\n\r\nSQL> begin\r\n 2 for i in 1..100000 loop\r\n 3 begin\r\n 4 insert into t0301 values(1);\r\n 5 exception\r\n 6 when dup_val_on_index then\r\n 7 null;\r\n 8 end;\r\n 9 end loop;\r\n 10 end;\r\n 11 \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'CPU used by this session');\r\n\r\n VALUE\r\n----------\r\n 2916\r\n\r\nSQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'redo size');\r\n\r\n VALUE\r\n----------\r\n 45226476\r\n\r\nSQL> drop table t0301 purge;\r\n\r\nTable dropped.\r\n\r\nSQL> exit\r\n\r\n\r\n<\/pre>\nWe then ran SQL code that was similar functionally, but replaced the INSERT with a MERGE…<\/p>\n
\r\nSQL> create table t0301(c number primary key);\r\n\r\nTable created.\r\n\r\nSQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'redo size');\r\n\r\n VALUE\r\n----------\r\n 21748\r\n\r\nSQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'CPU used by this session');\r\n\r\n VALUE\r\n----------\r\n 2\r\n\r\nSQL> begin\r\n 2 for i in 1..100000 loop\r\n 3 begin\r\n 4 merge into t0301 a\r\n 5 using (select 1 c from dual) b\r\n 6 on (a.c = b.c)\r\n 7 when not matched then\r\n 8 insert values(1);\r\n 9 end;\r\n 10 end loop;\r\n 11 end;\r\n 12 \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'CPU used by this session');\r\n\r\n VALUE\r\n----------\r\n 350\r\n\r\nSQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'redo size');\r\n\r\n VALUE\r\n----------\r\n 23588\r\n\r\nSQL>\r\n\r\n<\/pre>\nAs you can see, the INSERT generated about 45MB of redo, whereas the MERGE generated almost none. Also, the INSERT generated over 29 seconds of CPU utilization, while the MERGE was about three seconds.<\/p>\n","protected":false},"excerpt":{"rendered":"
We recently had an issue where a particular insert was generating a large amount of CPU time requirements. When we examined the PL\/SQL code, we found the developer had coded the following… begin insert into table values(pk); exception when dup_val_on_index…<\/p>\n