Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Query insertion of 27 million rows, Query insertion of 27 million rows. The select has a function call
binti59
post Mar 30 2017, 04:02 PM
Post #1


Newbie
*

Group: Members
Posts: 5
Joined: 13-January 17
Member No.: 51,740



Hi ,

I am facing a severe performance problem. I have a query which calls a function and select data from a primary table which has 30 million rows. The function uses the data in the same table for each row and returns a values corresponding to the inputs of the column of each row. If I select the query I can get all the result set in 30 secs. However, when I insert the data it causes a severe performance issue. Basically, the function call happens for each row and 200-300 rows are processed per second. I tried and store the output of the function return in memory so that I can bulk collect and insert those values, but it seems when i fetch the records the function call happen again.

Is there some way by which the select statements result of the function call be stored in memory and dump directly into the table.(ie 30 million) return values. I have attached the function and the bulk insert. I have tried using result cache but it does not seem to be working.

Thanks a lot for you help in advance
Bikram


Attached File(s)
Attached File  function.txt ( 7.72K ) Number of downloads: 6
Attached File  Insert.txt ( 2.49K ) Number of downloads: 3
 
Go to the top of the page
 
+Quote Post
burleson
post Mar 31 2017, 09:54 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,437
Joined: 26-January 04
Member No.: 13



Hi Bikram,

>> I am facing a severe performance problem.

Only your SQL execution plan will reveal the cause of the performance issue.

http://www.dba-oracle.com/t_sql_execution_plan_tips.htm

Also, run an AWR/STATSPACK report during the insert.

**************************************************

>> However, when I insert the data it causes a severe performance issue.

That's a great clue!

See here, tips for improving insert performance:

http://www.dba-oracle.com/t_insert_tuning.htm

Lastly, look at using a global temporary table:

http://www.dba-oracle.com/t_global_temporary_tables.htm




--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
binti59
post Apr 3 2017, 06:09 AM
Post #3


Newbie
*

Group: Members
Posts: 5
Joined: 13-January 17
Member No.: 51,740



Hi ,

The problem seems to be- the function value is being returned sequentially for each row instead of parallely while writing the data/inserting. Is there a way the function calls be parallelized while writing the data.

Thanks
Bikram
Go to the top of the page
 
+Quote Post
burleson
post Apr 3 2017, 11:22 AM
Post #4


Advanced Member
***

Group: Members
Posts: 13,437
Joined: 26-January 04
Member No.: 13



Hi Bikram,

>> the function value is being returned sequentially for each row instead of parallely while writing the data/inserting.

But doesn't the function need to be called for each row inserted?

If so, se here on parallelizing PL/SQL functions:

http://www.dba-oracle.com/r_execute_pl_sql_in_parallel.htm

If not, just store the function as a virtual database column:

http://www.dba-oracle.com/oracle11g/oracle...sed_columns.htm




--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
binti59
post Apr 5 2017, 07:33 PM
Post #5


Newbie
*

Group: Members
Posts: 5
Joined: 13-January 17
Member No.: 51,740



QUOTE (burleson @ Apr 3 2017, 11:22 AM) *
Hi Bikram,

>> the function value is being returned sequentially for each row instead of parallely while writing the data/inserting.

But doesn't the function need to be called for each row inserted?

If so, se here on parallelizing PL/SQL functions:

http://www.dba-oracle.com/r_execute_pl_sql_in_parallel.htm

If not, just store the function as a virtual database column:

http://www.dba-oracle.com/oracle11g/oracle...sed_columns.htm


Hi Donald,

Yes, the function needs to be called for each row to be inserted. Can I define a virtual column with the function I attached. Do I need to set it as deterministic. If you check the function, when I insert rows into the table having a virtual column defined as the function, will that speed things up during insert. Will not the computation still happen for each row during the inserts.
Go to the top of the page
 
+Quote Post
burleson
post Apr 6 2017, 12:30 PM
Post #6


Advanced Member
***

Group: Members
Posts: 13,437
Joined: 26-January 04
Member No.: 13



Hi,

>> Yes, the function needs to be called for each row to be inserted.

Oracle allows for parallel inserts, what is your CPU count?

See here:

http://www.dba-oracle.com/t_parallel_dml_updates_inserts.htm




--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd June 2017 - 03:20 PM