DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Tuning stored procedure

  1. #1
    Join Date
    Mar 2001
    Posts
    15

    Angry

    I am running a stored procedure, which is mainly querry
    intensive. I spent quite some times to tune the querries
    individually and after all of them were satisfactory in terms of
    performance I ran the procedure in our test environment
    and it ran well. But in the production environment,
    which is exactly same as test in terms of configuration( more powerful machine with more SGA) procedure is taking hell lot of time.

    The thing I noticed that when I ran it in production it is creating 2 sessions whereas it is not the case in test.

    Can anybody suggest me what are the areas I should be looking at?

    I am using oracle 8.1.5.0.0 on AIX 4.2 with EMC storage.

    Thanks in advance

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    could you paste your store proceedure here along with your query.probably we could help

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    I would suggest that you use the explain plan and tune the sql statements. Then make sure that if you are opening cursors, there are enough open cursor parameter setted up. Make sure the the table that you are using are all analyzed, such that they could be well used by the cost based optimizer. Also make sure that the indices are also analyzed. If your procedure does any updates try to use indices in the conditional clause. In the sql query and else where try to make use of the bind variables, This would save the library cache misses.

    If you have any more problems after using the above stated features, pl. let us know.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I must admit to being curious as to how the procedure is creating 2 sessions, and how you determined this.

    However, what I suspect is that your instances are not as similar as you think. There are *tons* of variables that can affect statement plan generation. You must verify that *all* of them are the same or equivalent (based on machine capabilities but using the same formula to get final value) on the two machines. These include:

    Init.ora params:

    Independant:
    · OPTIMIZER_FEATURES_ENABLE
    · OPTIMIZER_INDEX_CACHING
    · OPTIMIZER_INDEX_COST_ADJ
    · OPTIMIZER_MAX_PERMUTATIONS
    · OPTIMIZER_MODE
    · OPTIMIZER_PERCENT_PARALLEL
    · OPTIMIZER_SEARCH_LIMIT
    · HASH_JOIN_ENABLED
    · ALWAYS_ANTI_JOIN
    · PARTITION_VIEW_ENABLED

    Hardware-dependant:
    · HASH_AREA_SIZE
    · HASH_MULTIBLOCK_IO_COUNT
    · SORT_AREA_SIZE
    · SORT_WRITE_BUFFER_SIZE
    · DB_FILE_MULTIBLOCK_READ_COUNT
    · BITMAP_MERGE_AREA_SIZE


    Statistics.
    - Often, the development environ is smaller and has less data. If you are going to *tune* SQL in development, then it *must* have the statistics copied over from production. Look into DBMS_STATS package. Even better, of course, is to have the same data!
    - Same indexes. This means same definition, same size, same stats, same depth.
    - Same histograms

    There are probably more, but this is what I have handy.

    *All* of these variables affect the optimizer and contribute to what plan the optimizer will choose. If *any* of these are different, then your plans could be different in production vs development.

    Hope this helps,

    - Chris

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I agree 100% with Chris, could probably add some more init parameters which can affect performance significally.

    What immediatelly comes to my mind is parallel_execution related parameters. The reason for this is the fact that aghosha mentions "...noticed that when I ran it in production it is creating 2 sessions ...). A stored procedure surely can't initiate 2 simultaneous sessions, that's for sure. I think what aghosha actually noticed were two *processes*, not two *sessions". It might be that on production system optimizer has choosen to process some queries in parallel, which might not be the best decision under certain circumstances.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Mar 2001
    Posts
    15
    Thanks everybody. I will follow your suggestions and let you know the result.

    -- ABOUT 2 SESSIONS
    I created a new user who has execute permission on the procedure. During the course of the execution I ran the following querry

    SELECT osuser,username,status,terminal
    from v$session;

    I found 2 osuser of my name and one of them is active
    till the procedure finished and other one was getting active status periodically.
    I even noticed 2 sessions from SQL NAVIGATOR.

    Thanks

  7. #7
    Join Date
    Mar 2001
    Posts
    15
    Thanks again to everybody. The problem is solved.

    I checked all the initialization parameter and did not find any difference.
    Fo a small table which has 200 rows oracle optimizer was picking parallel
    execution plan. The table definition had DEGREE 2.

    But when I ran the querry individually execution plan was perfect.

    As soon as I changed the degrre to 1 the execution time came down from 4hrs to
    2min.

    I appreciate all of your help.

Posting Permissions

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


Click Here to Expand Forum to Full Width