Long Running PL/SQL Dilemma
I have an issue that has been handed over to me by our developers for poor performing PL/SQL, because it's never the developer's code that's the problem -- it must be the database :P
We have a procedure that queries Advancement data with several columns and nested sub-queries to create a CSV file (by UTL_FILE) that gets uploaded to Hobson's for use in communication plans, etc. The query returns about 60,000 rows. Because of UTL_FILE limitations, the file is broken up into 14-16 different files to get uploaded (partially for Hobson's benefit as well). The first few files get created reasonable quickly (first 15 minutes), but over time, slow down to a crawl and get created once every 10 to 20 minutes, resulting in the job completing in about 12 hours (ARGG!!)
We have a procedure that queries Advancement data with several columns and nested sub-queries to create a CSV file (by UTL_FILE) that gets uploaded to Hobson's for use in communication plans, etc. The query returns about 60,000 rows. Because of UTL_FILE limitations, the file is broken up into 14-16 different files to get uploaded (partially for Hobson's benefit as well). The first few files get created reasonable quickly (first 15 minutes), but over time, slow down to a crawl and get created once every 10 to 20 minutes, resulting in the job completing in about 12 hours (ARGG!!)
0