Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2010
    Posts
    24

    Smile Unanswered: Reg performance tuning of the application

    Dear all,
    Recently I have been assigned to tune the performance of an existing application.
    The procedures return the cursor data to the front end, where we display the data to the users.
    As the data is split into many pages, the count of the rows is also passed into the front end. (For Ex: to display 1-10 of 120).
    Thus currently the application is using 2 procedures for every data sent.
    1. For sending data.
    2. For sending count.

    EX:
    Code:
    Procedure 1 (param1 varchar2 ,..... , param_out  OUT SYS_REFCURSOR);
    Procedure 2 (param_count out number);
    The query used in both procedures is same. But in count procedure, select count(*) is used to get the count.



    Now, I think that using 2 different procedures for a same action is not a good practice.
    Can anyone suggest me with alternatives for such case ?
    Thanks in advance.

  2. #2
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    I can't see your point.

    So, you have 2 procedures:

    One with a varchar2 in parameter and a REF CURSOR as out parameter
    and
    one with a number as out parameter and no in parameter at all.

    How can they do the same thing ??
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  3. #3
    Join Date
    Jul 2010
    Posts
    24
    Both have same number of parameters, except the ref cursor has been replaced with a count in count_procedure.
    The query which returns data in both procedures is same, except that in count_procedure, it is taken as
    Code:
    select count(*) from (select main statement)

  4. #4
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    This is an interesting question, and quite debatable.

    If you asked Tom, he would say that doing the COUNT(*) is futile since the count at the time that you run the COUNT(*) may be different from when you actually read the data. At best, your COUNT(*) is an estimate of the number of rows. And an estimate is what you should always be reporting it as. See Tom Kytes response to a user asking the same question. He also provides a better method of estimating the number of rows without actually accessing the data.

    Having two procedures, I don't see too much problem with, but that depends on what the internals of the procedures looks like. In particular, I would be wondering how the COUNT procedure is obtaining the count, and secondly, I would be wondering if the data procedure is obtaining the "data window" in an optimal way.

    PS. Perhaps post procedure code? Don't forget to use the CODE tags to maintain formatting.

  5. #5
    Join Date
    Jul 2010
    Posts
    24
    Yeah, I agree that count(*) will not take much time as compared to the normal query.
    But, here in this case, the query in which we are taking count does join on large tables, hence time will be consumed in executing this procedure.
    So, need your suggestions in this regard.

    Thanks in advance.

  6. #6
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    You need to read the Tom Kyte article in my previous response. He covers this in depth.

    When returning a window of data, you only need an estimate of the total number of rows. Consider what a Google search returns. If you search for a phrase, Google will tell you "About x results (y seconds)". Imagine how slow it might be if Google had to try and do an accurate count of results for you phrase.

    So, you should learn from Google and simply return an estimated row count. To accomplish that, you will use the method described by Tom Kyte in answer to the same question. Ask Tom "Rows Estimate without executing the quer..."

  7. #7
    Join Date
    Jul 2010
    Posts
    24
    Thanks for the reply!
    The application requires exact count of rows to be passed to front end.
    Because, if it returns assume, 78 rows, user has a provision to select one of the option:
    1. Select 1-50 and then 50-78.
    2. Select 1-78.
    Thus, to give an option to user, whether to choose the first option or second, we need to have an exact count!

    The material you had mentioned, shows explain plan examples. But when I checked explain plans for both my count and data procedures, there was no much difference in cost.

    Please let me know if there is any way out in such a scenario!

  8. #8
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    But when I checked explain plans for both my count and data procedures
    I can see from this statement you are misinterpreting the solution. But anyway;
    user has a provision to select one of the option
    makes me think you are stuck with the current implementation anyway.

    The fact is:
    1) The font end has already been established and there will be no change in it's operation
    2) You need to optimise the performance of a) the row estimation; b) fetching a window of data.
    3) Based on the front-end requirements, the row estimation has to be accurate

    So, to get the count of rows, your query has to read every row in the result set (slooooow). And you can't do anything about that.

    When you read the actual data though, you should make sure you are using stop keys. Your query should look something like:
    Code:
    select col1, col2, ... coln from 
    (select col1, col2, ... coln, original_rownum
       from (select col1, col2, ... coln, rownum original_rownum
               from TBL1, TBL2, .. TBLN
              where [JOINS]
             order by [COLUMN LIST])
      where original_rownum <= p_torow
    )
    where original_rownum >= p_fromrow
    This will ensure that Oracle will only read the data row range you need to return.

Tags for this Thread

Posting Permissions

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