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

Thread: very simple question.

  1. #1
    Join Date
    Jun 2001
    Posts
    193
    we are developing a web application.
    what we did is use jdbc like

    executeQuery("select ename from emp")

    or

    execute ("delete from EMP where EMPNO = 1500")

    or

    PreparedStatement pstmt =
    conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (?, ?)");
    // Add LESLIE as employee number 1500
    pstmt.setInt (1, 1500); // The first ? is for EMPNO
    pstmt.setString (2, "LESLIE"); // The second ? is for ENAME
    // Do the insertion
    pstmt.execute ();

    by doing this, we get almost same parse_calls and executions.
    i know that if i put it inside a store procedure, the parse will be less. but since it is very simple sql, as simple as select something or delete something or update something, do u think i should put it inside a store procedure.

    BTW, it is OLTP, and it will run lots of times.
    my point is:this query will execut exactly ONCE per program
    but the program will invoked lots of time by web program, does put the query inside the store procedure help performance by decrease soft parse?



    [Edited by beginner on 07-18-2001 at 02:52 PM]
    guru is on the way!!!!

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Did you think of using bind variable? Other wise you could pin it in the cache.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    use prepared statement so you can use bind variable but if you cannot Oracle 8i has a new parameter cursor_sharing this will force use of bind variable even the SQL are literal, set cursor_sharing to force if you dont plan to use prepared statement

  4. #4
    Join Date
    Jun 2001
    Posts
    193
    the thing is:
    it is not similar sql run several times in a program,

    it is one sql runing in one program. but this program will be
    called lots of time.

    pando& sambavan:
    use prepared statement and cursor_sharing, it can aviod hard parse, but i don't think it can decrease soft parse.
    using bind variable can not decrease soft parse either.

    what i want to know is how to decrease soft parse AS MUCH AS POSSIBLE
    guru is on the way!!!!

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    To decrease the soft parses pin the object in the cache. That would help you to an extent.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Jun 2001
    Posts
    193
    ping which object in the cache?

    table? sql? store procedure?
    guru is on the way!!!!

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hello

    You gotta to pin the stored Proceedure or the sql ....i think so
    as pinning the table wont help you to decrerase your soft parses.why dont you try both and see the performance results yourself.

    regards
    hrishy

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