1 2 3 4 Previous Next 49 Replies Latest reply: Jul 9, 2009 2:13 PM by Hoek Go to original post RSS
      • 45. Re: Please help me to tune this PL/SQL...
        William Robertson
        There are diminishing returns as you increase the LIMIT value. You're loading up more at a time into memory, which reduces context switching but needs more memory resources. Also part of the benefit of reading a chunk, writing a chunk, reading another chunk etc is that you're spreading the workload around, streaming it like YouTube rather than doing all your reading and then all your writing. Usually the sweet spot will be somewhere in the hundreds of rows but it will depend on the query, how big the rows are, how much memory the system has, how fast it can read and write etc, so it may be worth testing with different values.
        • 46. Re: Please help me to tune this PL/SQL...
          Hoek
          Hi Phani,

          Just my $0.02:

          I have a feeling this thread is taking some ad-hoc directions.
          Like I said 24h ago (you could have done by far what I asked you):
          Or have a DBA trace/tkprof a session that runs that code to identify what's going on.
          Can't you just do that?

          And work on removing the inner loop...
          This BULK processing only raises new questions, and the PROFILING too...
          Now we're looking at an entirely different situation, in regard to the original question.

          Just run the procedure and trace (with wait events) and tkprof it, either you or your DBA.
          You are familiar with explain plans already.
          If it's still unclear how to interprete the tkprof result, then post it on this thread.
          • 47. Re: Please help me to tune this PL/SQL...
            phani marella
            Hi hoek,

            I didnt try only this option....(sory for that)
            Or have a DBA trace/tkprof a session that runs that code to identify what's going on.
            I will get back to you with the trace file output...

            one quick question .."Do i have to Trace the proc (for total records) or can i limit the records (like 100 or 200)"?

            Does it make any differnce in the ouput trace file? Please let me know..

            Thanks
            • 48. Re: Please help me to tune this PL/SQL...
              Randolf Geist
              phani marella wrote:
              why procedure is running OK with smaller numer in "LIMIT" clause..
              All your findings so far seem to confirm my speculation: The more rows you attempt to actually fetch, the more work the scalar subqueries need to perform for each row processed.

              Why don't you start following up my previous note? Your issue lies in your query and in particular in your nested subqueries that are totally inefficient since they perform full table scans on a large table for each row fetched.

              As suggested by "hoek" you can try to get confirmation by tracing your query execution. The simpler way is to test the query with and without the scalar subqueries to see the difference.

              Regards,
              Randolf

              Oracle related stuff blog:
              http://oracle-randolf.blogspot.com/

              SQLTools++ for Oracle (Open source Oracle GUI for Windows):
              http://www.sqltools-plusplus.org:7676/
              http://sourceforge.net/projects/sqlt-pp/
              • 49. Re: Please help me to tune this PL/SQL...
                Hoek
                Hi Phani,

                can i limit the records (like 100 or 200)?
                Sorry, but all I can say: it depends.
                Hardware, configuration etc etc
                Does it make any differnce in the ouput trace file? Please let me know..
                Yes, it does.
                But again it depends...
                Since you've already spent more than 24 hours: just make it a 'real testcase'.
                A representative test, before it is implemented on production.
                In other words: stop looking for Silver Bullets and just grab the bull by the horns.
                Trace.
                And avoid loops in loops.

                Oh, and post your DB-version.
                1 2 3 4 Previous Next