14 Replies Latest reply on Dec 6, 2018 4:58 PM by rp0428

    How to parallelize 2 million customer pl/sql bill run??

    PLSQL_GUY

      Hello, I am interested in in how can we can parallelize pl/sql tasks.  We having a billing system where billing engine is in pl/sql. With new customer we have to processes bills for 2 million customers every bill run (which happens once for every month).

       

      Can we break the 2 million customers into 20 groups (using something like customer rowid or customer code) of 100,000 customers each and run 20 separate bill runs in parallel??

       

      This should theoretically finish in 1/20th of the time it takes to process 2 million customers.

       

      But these bill runs have to run in separate servers to get full advantage of paralleling, I think?

       

      Can we have Oracle RAC with 20 instances (from what I understand of RAC every RAC instance is a separate physical server) and then run the 20 bill run batches in parallel?

       

      However since all 20 RAC instances will be accessing the same one DB will that affect performance??

       

      Do we have to write separate PL/SQL program for this (i.e. to break up into batches and make them run on 20 separate RAC instances), or does Oracle RAC configuration SW configuration (which we have to do) can handle this?

       

      Any advice on this with your experience?

        • 1. Re: How to parallelize 2 million customer pl/sql bill run??
          Marwim

          The easiest way to execute tasks in parallel is DBMS_PARALLEL_EXECUTE

          You need to find a way to address the ranges, e.g. by customer ID. The rest is quite straightforward. While the examples in the docu show SQL only you can easily execute PL/SQL. You only need two parameters with :start_id and :end_id.

           

          Marcus

          • 2. Re: How to parallelize 2 million customer pl/sql bill run??
            AndrewSayer

            As mentioned you would use the built in dbms_parallel_execute for this kind of chunk-and-distribute task.

             

            You don’t need RAC, in fact RAC introduces a lot of serialisation points and is best used when each instance is doing very separate tasks that are all CPU bound. You just need the CPU on your server to have many job processes running at once - up to one CPU/core per parallel degree + enough to run everything else. Remember, if your process requires serialised access to a resource then even with a million parallel jobs, you’ll get the throughput of one.

             

            Parallelism is only one of your tools! Make sure you tune your code too, does it really need to be done One customer at a time using row-by-row (slow-by-slow loops)? Does it really need to full tablescan that medium sized table for each customer? etc. You might be pleasently surprised what you can achieve with one SQL statement and it’s likely that will be all you need (don’t forget about analytical functions which are able to do a lot of the work you’d be typically doing in these sorts of pl/sql processes - one SQL run once against all customers will be much quicker that running a pl/sql program (which likely contains lots of SQL all executed with different consistency) for each customer.

            1 person found this helpful
            • 3. Re: How to parallelize 2 million customer pl/sql bill run??
              GregV

              Hi,

               

              2 million data volume is not that big. What will be your Oracle version and edition?

              If you go for Entreprise Edition then you can use native parallelism to help. If you go for Standard Edition 2 (SE2) then you can use DBMS_PARALLEL_EXECUTE as suggested, or DBMS_SCHEDULER jobs.

              I personally use a combination of DBMS_SCHEDULER and DBMS_PIPE to do DIY parallelism, involving global temporary tables because their contents are session-specific so I find they fit quite well for parallel work.

              You don't need several servers, just one with several CPUs (SE2 will internally cap you application threads to 16).

              I don't know how much work you need to do on these 2 million customers but you may not even need parallelism if you code your logic efficiently and not slow by slow as it is often seen in pl/sql programs.

              • 4. Re: How to parallelize 2 million customer pl/sql bill run??
                Sven W.

                PLSQL_GUY wrote:

                 

                Hello, I am interested in in how can we can parallelize pl/sql tasks. We having a billing system where billing engine is in pl/sql. With new customer we have to processes bills for 2 million customers every bill run (which happens once for every month).

                 

                Can we break the 2 million customers into 20 groups (using something like customer rowid or customer code) of 100,000 customers each and run 20 separate bill runs in parallel??

                 

                This should theoretically finish in 1/20th of the time it takes to process 2 million customers.

                 

                But these bill runs have to run in separate servers to get full advantage of paralleling, I think?

                 

                Can we have Oracle RAC with 20 instances (from what I understand of RAC every RAC instance is a separate physical server) and then run the 20 bill run batches in parallel?

                 

                However since all 20 RAC instances will be accessing the same one DB will that affect performance??

                 

                Do we have to write separate PL/SQL program for this (i.e. to break up into batches and make them run on 20 separate RAC instances), or does Oracle RAC configuration SW configuration (which we have to do) can handle this?

                 

                Any advice on this with your experience?

                I did similar things before and it is certainly doable. However your question implies that you are not so familiar with Oracle. So it is certainly advisable to hire some external help with such a task.

                 

                1) Can we break the 2 million customers into 20 groups (using something like customer rowid or customer code) of 100,000 customers each and run 20

                separate bill runs in parallel??

                 

                Yes. You should break it down by customer ID or CODE (not rowid). Rowids are very stable, but they can change under specific circumstances. Don't mistake me. You can split it into groups by rowid. And while you do your work, ROWIDs will not change. But it is the physical address of the customer, it does not identify the customer. As such it will make your life much harder when you later want to find out what went wrong with a specific customer.

                 

                2) This should theoretically finish in 1/20th of the time it takes to process 2 million customers.

                 

                Theoretically yes. In reality you might encounter issues, where thoses different threads still compete for the same ressources. I had a very similar case, which ended up doing a run in parallel needed more time than doing it in a single thread. (Reason had been wait events on ITL).

                 

                3) But these bill runs have to run in separate servers to get full advantage of paralleling, I think?

                Can we have Oracle RAC with 20 instances (from what I understand of RAC every RAC instance is a separate physical server) and then run the 20
                bill run batches in parallel?

                 

                You can buy RAC: It is probably one of the most expensive options that you can do. And in the end it might not help that much.

                 

                All modern servers are have CPUs with multiple cores and are able to run several tasks in parallel. No need to offload that capability to different servers and then make sure all the offloaded work is stitched together correctly afterwards.

                 

                Other options I would consider first before I would consider RAC are:

                - partitioning

                - parallel option

                - DYO plsql parallel processing

                - optimize your current processes (SQL and plsql tuning)

                - buy better hardware (maybe cloud based)

                 

                4) Do we have to write separate PL/SQL program for this (i.e. to break up into batches and make them run on 20 separate RAC instances)

                 

                Yes.

                1 person found this helpful
                • 5. Re: How to parallelize 2 million customer pl/sql bill run??
                  Sergey Krasnoslobodtsev

                  Hi.

                  Without a detailed understanding it is not easy to offer the optimal solution for the problem.

                  2 million customers is not a lot.

                  important questions:

                  1) what processing in pl/sql do you plan ?

                  2) are your objects partitioned?

                   

                  For example, if updating attributes by client is one solution.

                  If you are going to generate documents, then this is another option.

                   

                  In General, the first candidate of solution is the dbms_parallel_execution package.

                  The second acceptable option is to use light jobs (dbms_scheduler).

                  There are other options, but you need to describe your task in more detail.

                  • 6. Re: How to parallelize 2 million customer pl/sql bill run??
                    Billy~Verreynne

                    You need a basic thread/parallel safe PL/SQL code unit, that:

                    - accepts a customer CDR/data entity (or whatever struct needs to be processed for billing)

                    - applies the "rating engine" in order to bill that data entity

                    - creates one or more debit and/or credit transactions for this billed data entity

                     

                    The thread safe requirements means that different CDRs can be processed, by this code, at the same time.

                     

                    There are 3 basic approaches to running this code in parallel.

                     

                    Instead of using PL/SQL to rate and create transactions, use SQL - and then run this in parallel using Oracle Parallel Query. This will likely be the fastest and most scalable approach.

                     

                    Write the PL/SQL code as a parallel enabled pipeline - where the data (CDRs) to process by this code, is selected and delivered to the pipeline, using Oracle Parallel Query.

                     

                    Use DBMS_PARALLEL_EXECUTE to chunk the CDRs, and then have it use the PL/SQL code to process a chunk, and have this done in parallel.

                     

                    Each approach has its own pro's and con's - making the most appropriate approach to use fully dependant on the business requirements for rating a data entity (like a CDR), and the requirements for creating the billing transactions for it.

                    1 person found this helpful
                    • 7. Re: How to parallelize 2 million customer pl/sql bill run??
                      rp0428

                      Hello, I am interested in in how can we can parallelize pl/sql tasks.  We having a billing system where billing engine is in pl/sql. With new customer we have to processes bills for 2 million customers every bill run (which happens once for every month).

                      Ok - but there is NOTHING in what you posted that indicates there is ANY problem doing the job with ONE task - NOTHING.

                       

                      I suggest you follow the traditional troubleshooting steps:

                       

                      1. identify a problem/issue - you haven't posted one yet

                      2. confirm the the problem/issue REALLY exists

                      3. determine the cause of the problem/issue

                      4. identify potential solutions that will eliminate/mitigate the problem/issue

                      5. select a small number (2 or 3) of those solutions for prototyping and testing

                      6. select the 'best' solution from those tested

                       

                      REQUIREMENTS FIRST - solutions later.

                       

                      The most common mistake I see in orgs is failure to FULLY DOCUMENT the requirements.

                       

                      Does your org actually have WRITTEN requirements for your monthly billing process?

                       

                      1. what are the performance/execution requirements? 2 days? one hour? Ten minutes?

                      2. what is the current execution time of your monthly process?

                      3. how often is the performance requirement in #1 being missed?

                      4. why is the requirement being missed? Have you actually determined the reason(s) when the process exceeds the goal?

                      5. is your billing process properly instrumented? If so you should have DETAILED logs for EVERY step of the process that can provide clues as to which step(s) are problematic and how often they are a problem.

                       

                      At a minimum each step should log: start time, end time and number of rows affected.

                       

                      You need to start with the basics. There are several SIMPLE ways to improve performance or for performance to degrade periodically:

                       

                      1. missing, or improper, statistics on one or more tables or indexes

                      2. missing or invalid indexes

                      3. change of execution plans due to changes in data volumes, statistics or index status

                       

                      Does your org use a version control system?

                       

                      Do they have execution plans for the key queries in that system?

                       

                      When performance degrades did they compare the current execution plan to the 'good' plan from version control?

                       

                      1. app is developed and tested

                      2. execution plans are created after testing for ALL key queries. These plans show what Oracle did when performance was good/acceptable

                      3. every step of every batch process is instrumented to collect performance metrics into a custom log table

                       

                      When things go wrong

                       

                      1. metric/log table is examined to determine which step(s), if any, has degraded in performance.

                      2. execution plans are created for the problematic queries in those steps

                      3. the current 'bad' plan is compared to the 'good' plan in version control to try to determine what changed, if anything

                       

                      Most often I find that orgs haven't done ANY of the above: no version control, no instrumentation logging, no execution plans.

                       

                      Performance is all about COMPARISON. You need to compare 'NOW' with 'BEFORE'.

                       

                      You can't do that if you don't have the 'BEFORE' info.

                       

                      I suggest rather than concern yourself with a 'solution' for you non-existent problem you follow the above guidelines and do the necessary planning and preparation needed to solve problems when you actually have one.

                       

                      Planning and preparation will provide a MUCH BETTER return on investment for the time and resources you have available.

                      • 8. Re: How to parallelize 2 million customer pl/sql bill run??
                        Mark D Powell

                        PLSQL_GUY, I am not sure any of the responses you have been provided will work since you have not provided very much detail on how the current code works and how the application is organized: single package, fifty different stored procedures, etc...  The key is the how the process is driven.  If you can chunk the driver which I would think is a select of the customer code then you should be able to parallelize the processing.

                        - -

                        Be aware that if somewhere in this processing there are DML statements, especially inserts into a report or bill holding table that as you increase the degree of parallelism you will get waits due to contention: block contention, HWM contention, and index contention.  The point being there is a point where increasing parallelism will actually increase the run time compared to the previous lower degree of parallelism.

                        - -

                        HTH -- Mark D Powell --

                        • 9. Re: How to parallelize 2 million customer pl/sql bill run??
                          Mike Kutz

                          PLSQL_GUY wrote:

                           

                          Can we break the 2 million customers into 20 groups (using something like customer rowid or customer code) of 100,000 customers each and run 20 separate bill runs in parallel??

                           

                          Do you have enough hamsters to do that?

                           

                          Let's say running 1 thread takes up 100% of your disk I/O.

                          When you run 20 jobs, you will need 2000% of your disk I/O.

                           

                          Replace Disk I/O with CPU Clock cycles, network I/O, memory consumption, or any other hardware resource.

                           

                          "run in parallel"  <> "fast = true"

                           

                          My $0.02

                           

                          MK

                          • 10. Re: How to parallelize 2 million customer pl/sql bill run??
                            Mark D Powell

                            Mike, I do not follow your logic since if you break a job performing N number of I/O into 20 job tasks each task should perform roughly 0.05 * N worth of I/O.  There will be some overhead to the driving process but you do not increase your I/O load by a factor of 20X.

                            - -

                            HTH -- Mark D Powell --

                            • 11. Re: How to parallelize 2 million customer pl/sql bill run??
                              Mike Kutz

                              Mark D Powell wrote:

                               

                              Mike, I do not follow your logic since if you break a job performing N number of I/O into 20 job tasks each task should perform roughly 0.05 * N worth of I/O. There will be some overhead to the driving process but you do not increase your I/O load by a factor of 20X.

                              - -

                              HTH -- Mark D Powell --

                               

                              I/O takes time.  There is a limit to how much I/O you can use within a given time period.  If you are hitting the maximum amount of "I/O per time period" ("100% of your disk I/O"), you can't decrease the amount of time to process your data by splitting the job into chunks.

                               

                              Let's say the overall process requires 20000 IO Operations.  Your disk sub-system can only handle 1000 I/O Operations per second (IOPS).

                              Without parallelization, the job will run for 20 seconds.

                              With parallelization, the job will run for ...  20 seconds.

                               

                              If you want "fast = true" (ie complete the 20 jobs in 1 second) then you will need to "buy more hamsters" so that you can use "20000% of your [original systems] disk I/O capabilities".  end if;

                               

                              I guess, I'm just trying to ensure that the OP understands why DPE is not a "fast=true" feature.

                               

                              Another point I would like the OP to know:

                              One needs to run benchmarks to find out "how much more work" the system can handle for that particular task.

                               

                               

                               

                              MK

                              • 12. Re: How to parallelize 2 million customer pl/sql bill run??
                                John Thorton

                                Mike Kutz wrote:

                                 

                                Mark D Powell wrote:

                                 

                                Mike, I do not follow your logic since if you break a job performing N number of I/O into 20 job tasks each task should perform roughly 0.05 * N worth of I/O. There will be some overhead to the driving process but you do not increase your I/O load by a factor of 20X.

                                - -

                                HTH -- Mark D Powell --

                                 

                                I/O takes time. There is a limit to how much I/O you can use over time. If you are hitting the maximum amount of "I/O over time" ("100% of your disk I/O"), you can't decrease the amount of time to process your data by splitting the job into chunks.

                                 

                                Let's say the overall process requires 20000 IO Operations. Your disk sub-system can only handle 1000 I/O Operations per second (IOPS).

                                Without parallelization, the job will run for 20 seconds.

                                With parallelization, the job will run for ... 20 seconds.

                                 

                                If you want "fast = true" (ie complete the 20 jobs in 1 second) then you will need to "buy more hamsters" so that you can use "20000% of your [original systems] disk I/O capabilities". end if;

                                 

                                I guess, I'm just trying to ensure that the OP understands why DPE is not a "fast=true" feature.

                                 

                                Another point I would like the OP to know:

                                One needs to run benchmarks to find out "how much more work" the system can handle for that particular task.

                                 

                                 

                                 

                                MK

                                Everyone knows that it takes 9 month for lady to make 1 baby.

                                But you can't parallelize the task so that 9 ladies make 1 baby in 1 month.

                                • 13. Re: How to parallelize 2 million customer pl/sql bill run??
                                  Mark D Powell

                                  Mike, better explanation and you did say if one thread takes 100% of your I/O which I failed to consider; however, having actually encountered the situation where my SQL Server databases were issuing so many I/O requests that the disk could not keep up,I think just stating the requirement to check the existing resource load for CPU and I/O against capacity before applying parallelism would have been a lot clearer.

                                  - -

                                  IMHO -- Mark D Powell --

                                  • 14. Re: How to parallelize 2 million customer pl/sql bill run??
                                    rp0428

                                    John Thorton wrote:

                                     

                                    Mike Kutz wrote:

                                     

                                    Mark D Powell wrote:

                                     

                                    Mike, I do not follow your logic since if you break a job performing N number of I/O into 20 job tasks each task should perform roughly 0.05 * N worth of I/O. There will be some overhead to the driving process but you do not increase your I/O load by a factor of 20X.

                                    - -

                                    HTH -- Mark D Powell --

                                     

                                    I/O takes time. There is a limit to how much I/O you can use over time. If you are hitting the maximum amount of "I/O over time" ("100% of your disk I/O"), you can't decrease the amount of time to process your data by splitting the job into chunks.

                                     

                                    Let's say the overall process requires 20000 IO Operations. Your disk sub-system can only handle 1000 I/O Operations per second (IOPS).

                                    Without parallelization, the job will run for 20 seconds.

                                    With parallelization, the job will run for ... 20 seconds.

                                     

                                    If you want "fast = true" (ie complete the 20 jobs in 1 second) then you will need to "buy more hamsters" so that you can use "20000% of your [original systems] disk I/O capabilities". end if;

                                     

                                    I guess, I'm just trying to ensure that the OP understands why DPE is not a "fast=true" feature.

                                     

                                    Another point I would like the OP to know:

                                    One needs to run benchmarks to find out "how much more work" the system can handle for that particular task.

                                     

                                     

                                     

                                    MK

                                    Everyone knows that it takes 9 month for lady to make 1 baby.

                                    But you can't parallelize the task so that 9 ladies make 1 baby in 1 month.

                                    But 9 ladies can make 9 babies in 9 months.

                                     

                                    If you take the average it comes out to 1 baby per month.

                                     

                                    Just a demo of how statistics can be abused.