Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2013
    Posts
    5

    Lightbulb Unanswered: Procedure Execution Idea

    Hi people,
    I am currently developing a new Oracle database and wanted to bounce a few ideas on the forum.

    We currently have an application which will connect to our Oracle database. We will deal with approximately 65,000 INSERT statements a month (approx. 2166 per day,approx. 90 per hour).

    I would like to share three script execution methods.

    1) The script will first declare a REFCURSOR. The procedure will be executed which will execute a SELECT statement, UPDATE statement and then another SELECT statement to retrieve some results. The results will be inserted into a FUNCTION and a SELECT statement calling the FUNCTION with a RETURN value. The results from the FUNCTION will be declared in the procedure as variable OUT. From there on the results will be displayed with PRINT.

    2) The script will execute the procedure which will execute a SELECT statement, UPDATE statement and then another SELECT statement to retrieve some results then display the results using dbms_output.put_line. This is my least favorite as I want to try and return the results in a grid format.

    3) The script will execute the procedure which will execute a SELECT statement, UPDATE statement and then another SELECT statement to retrieve some results. The results will be inserted into a Global Temp Table (On Commit Delete Rows). A SELECT statement will be executed against the Global Temp Table to pull the results out and a COMMIT statement will be executed to clear the rows.

    Please could I have some feedback from these ideas. They all work but I am looking at an idea which will return the results in a grid format.
    Last edited by bravomedic; 01-04-13 at 09:09.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    which benchmark test results in best performance?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2013
    Posts
    5
    I haven't bench marked anything at the moment. I'm working with the the criteria that we will be be dealing with 65,000 transaction as the maximum average. This may grow to 85,000 transaction by the end of 2013.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Some general rules I tend to follow for PLSQL

    1) Do it in as few statement executions as possible.

    2) Where possible avoid cursor loops and do it with a single sql statment. For example rather than looping through a result set and then updating a single record at a time do it with one update statment which updates all of the records which need updating.

    3) Avoid function calls if they are going to be executed lots of times. Note though that Oracle built in functions have less overhead than user defined function calls.

    Didnt understand why you were inserting into a global temp table and then selecting from it or does the process of inserting into it involve a lot of complex processing.

    Alan

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >(approx. 2166 per day,approx. 90 per hour).
    or 1.5 INSERT per minute
    Oracle DB can sustain multiple INSERTs every second, so I would expect even worst performing implementation to be easily accommodated.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jan 2013
    Posts
    5
    Quote Originally Posted by AlanP View Post

    Didnt understand why you were inserting into a global temp table and then selecting from it or does the process of inserting into it involve a lot of complex processing.
    Hi Alan,
    Thank you for your tips. We use a third party software which has houses external data, extracts data from our datawarehouse and also our Ecommerce gateway. Our current database executes a script and returns the results in a grid table format. Our third party software uses an API interface to extract the table results from our datawarehouse.

    We need to migrate over to Oracle as our current database is an OLAP based database technology and can't handle the amount of transactions. We will move over to Oracle as it should be able to hand more transactions.

    I have developed three possible execution concepts. The third one is the procedure which will do various calculations and then execute an INSERT statement to insert values into the global temp table. Our third party software will then execute the SELECT statement to retrieve the data.
    Last edited by bravomedic; 01-04-13 at 15:53.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •