1 2 Previous Next 20 Replies Latest reply on Apr 30, 2013 4:11 AM by user007009 Go to original post
      • 15. Re: *Dynamic* Table Name in From Clause with input from cursor
        rp0428
        >
        I am not sure what I am doing wrong... can you please let me know your thoughts...
        >
        Sure - our thoughts are that what you are doing wrong is trying to implement the most complex solution possible for a problem that doesn't even exist.

        You haven't provided ANY justfication for needing to do this in parallel or for using any solution except the straightforward solution you already have (which you have shown is better than the complicated solution.
        • 16. Re: *Dynamic* Table Name in From Clause with input from cursor
          user007009
          >
          the parallel process didn't throwed any errors and it didn't insert any data either.
          >
          The reason for not inserting the data is, the below line
          >
          select table_name into table_name from group_table where group_key=vstart_id;
          >
          should be changed to
          >
          select table_name into table_name from table_group where group_key=vstart_id;
          >
          really weird that it didn't throwed any error neither during compilation nor after executing it.. But the performance is still the same 3.112 seconds...
          >
          You haven't provided ANY justfication for needing to do this in parallel
          >
          for suppose each query took 10 minutes and we have 5 tables, then it will take 50 minutes if executed serially and 5 minutes if all of them at a time right.. I am trying to save that time...one of the table (variable_table which is table_asercare,table_medcare, table_xycare) used in the query is kind of big and growing... 2 gb in size each one....
          No indexes on the tableX.. will have 10k or so records.

          Edited by: user007009 on Apr 28, 2013 2:48 PM

          Edited by: user007009 on Apr 28, 2013 2:49 PM
          • 17. Re: *Dynamic* Table Name in From Clause with input from cursor
            rp0428
            >
            for suppose each query took 10 minutes and we have 5 tables, then it will take 50 minutes if executed serially and 5 minutes if all of them at a time right.. I am trying to save that time
            >
            And THAT is what I am talking about when I said this
            >
            what you are doing wrong is trying to implement the most complex solution possible for a problem that doesn't even exist.
            >
            You don't HAVE a problem. You are just 'supposing'; 'suppose each query took 10 minutes . . .'.

            Well suppose that the time it takes to 'chunk' the query and execute it in paralle is longer than the time it takes to just execute the query. Oh, wait, this IS what you discovered.

            Well suppose each query took 2 seconds. Suppose the whole thing only takes a half second. Suppose you waited until you actually have a problem.

            Solutions are for solving actual problems, not imaginary and non-existent problems.

            If all you do is 'suppose' you will never find a 'solution' because you could always 'suppose' another issue that keeps that 'solution' from working.
            • 18. Re: *Dynamic* Table Name in From Clause with input from cursor
              sb92075
              user007009 wrote:
              for suppose each query took 10 minutes and we have 5 tables, then it will take 50 minutes if executed serially and 5 minutes if all of them at a time right..
              Please explain how you claim run time will be only FIVE minutes; when any single SQL now take TEN minutes.
              • 19. Re: *Dynamic* Table Name in From Clause with input from cursor
                user007009
                >
                You don't HAVE a problem. You are just 'supposing'; 'suppose each query took 10 minutes . . .'.
                >
                Actually one of the table (variable_table which is table_asercare,table_medcare, table_xycare) used in the query is kind of big and growing... 2 gb in size each one....
                and i am sure that it will end at that point soon...I am trying to design so that it hold the future load as well...and avoid rewriting again.
                >
                well suppose that the time it takes toto 'chunk' the query and execute it in paralle is longer than the time it takes to just execute the query. Oh, wait, this IS what you discovered.
                >
                By changing
                >
                parallel_level => 3
                >
                to
                parallel_level => 1
                the run time became 0.028 seconds.. what you said seems to be the root cause for the performance..
                By changing parallel_level => 1 to parallel_level => 3 the run time became 6.21 seconds.. actually the performance should increase as per the documentation... really not sure...

                Edited by: user007009 on Apr 28, 2013 7:45 PM
                • 20. Re: *Dynamic* Table Name in From Clause with input from cursor
                  user007009
                  >
                  Please explain how you claim run time will be only FIVE minutes; when any single SQL now take TEN minutes.
                  >
                  my bad, I mean to say 10 minutes..

                  Might be helpful to others...
                  As for the performance, we need to try on a realistic workload and compare plans to see the suitable implementation configuration to use. source---> https://blogs.oracle.com/warehousebuilder/entry/parallel_direct_path_inserts_into_partitioned_tables#comments
                  will keep posted as soon as i have the results....

                  Edited by: user007009 on Apr 29, 2013 9:09 PM
                  1 2 Previous Next