Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Performance

  1. #1
    Join Date
    Jun 2003
    Posts
    294

    Lightbulb Unanswered: Performance

    Hello there, I have a doubt:

    I have a query like this:


    SELECT COL1, COL2, COL3
    FROM A,B
    WHERE A.ID=B.ID
    AND A.NAME='A';


    But Somebody told me that IT would be better if I use a variable like this, but I don't think so:

    value1 varchar(1);
    SELECT COL1, COL2, COL3
    FROM A,B
    WHERE A.ID=B.ID
    AND A.NAME=value1;


    He told me that Oracle parser, parses the query again, when It finds a costant value into the code, is It true ???

    Thank you so much !

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > is It true ???
    Run EXPLAIN PLANS on both and compare the two.
    Or ask "somebody" to prove their position is correct (in at least 1 case, if not all cases).
    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
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Your friend is correct; however, if you're using 9i or above there's a parameter that causes statements that differ only in literal values to share a cursor. So, in your case (assuming you have 9i or above):

    SHOW PARAMETER CURSOR;

    check that cursor_sharing is set to 'SIMILAR'; if not:

    ALTER SYSTEM SET CURSOR_SHARING = SIMILAR;
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  4. #4
    Join Date
    Oct 2004
    Posts
    145
    I believe "somebody" is referring to static/explict query versus bind variable query.

    If you use bind variables and sql statement has NOT been flushed from shared pool it will reuse it. Hence slightly faster. As to true performance gain, it would depend on your memory configuration and how many sql statements are being pused through the shared pool, etc.

  5. #5
    Join Date
    May 2003
    Location
    France
    Posts
    112
    effectively, the use of variables reduces parse executions for litteral values, but if you're on 9.2 the init parameter CURSOR_SHARING=SIMILAR can do this job .

    Rgds

  6. #6
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    It depends on your init.ora settings and whether you plan to always run the query using value A or different values. Think about the phases: parse, execute, and fetch.

    When you issue a SQL statement, Oracle generates a hash value based on you statement and then looks in the shared pool for an item with the same hash value. This includes the value you passed, in this case 'A'. If Oracle cannot find the same query with the same value 'A', then it's a cache miss and Oracle has to determine the execution plan.

    If you set your init.ora a certain way, you can instruct Oracle to use the bind variable name in place of an actual value when computing the hash. Then, all subsequent uses of the same query (with some restrictions) will cause a cache hit and Oracle can use the existing execution plan. That's a big time savings for OLTP applications, negligible for Warehouse applications - my query might run for 2 hours, so what do I care about saving 1 millisecond on the parse phase.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    don't rely on the CURSOR_SHARING parameter.
    that is not correcting the problem but only working around the problem.

    the main concern is to use bind variables so that the query stays in shared
    memory and does not get flushed out. This is proper coding procedure for
    obtaining and constructing a truely tuned database.

    ALSO, if you set CURSOR_SHARING=FORCE (this was added in 816 not 9.2)
    then you could run into problems. Especially for all queries in your application
    where you are using literals. CURSOR_SHARING turns everything into variables
    which could drastically affect your code.

    When using literals like " where 1 = 0 " you want the optimizer to
    know AHEAD of time that this statement = FALSE. However, when CURSOR_SHARING
    is set the statement changes to " where :"SYS_B_1" = :"SYS_B_2" "
    This is no longer a literal and now the optimizer has no idea that this part of
    the query is SUPPOSED to return zero rows. Instead, it must scan the whole
    table.

    Not to mention when you use literals which determine whether an index is
    used or not such as " select * from t where x < 50"
    In this situation the optimizer knows to use the index, but if cursor-sharing
    is being used then it is impossible for the optimizer will not have the
    literal values to consider when optimizing.

    CURSOR_SHARING=FORCE is not necessarily the answer and is more of a
    quick fix when a production DB needs help before all the code can be changed to use bind variables. You are asking for a great deal of other problems if you use this parameter.

    Also, through tests you will see bind variables run faster than setting cursor_charing=FORCE.

    Read "EXPERT One-on-One Oracle" for further information.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jun 2003
    Posts
    294
    It's none sense for me (Not logic), I mean, I don't see any technic - logic reason for this, Why #2 is better, if It has to replace the variable I created before, for the original value, Isn't It more complicated?

    Can you explain me why ?

  9. #9
    Join Date
    Jun 2003
    Posts
    294

    Lightbulb

    I don't know how to use this bind variables (even I don't know anything about It), Is it the same if I create a normal plsql variable, something like #2

  10. #10
    Join Date
    Apr 2004
    Posts
    246
    What you all seem to be forgetting is that the parse and plan building happen each and every time, no matter what. Cursor sharing has the benefit of reusing existing entries in the shared pool, therefore the sql gets aged out less often, but it still builds a plan, which may run differently than before (hence the CHILD_ADDRESS in V$SQL).

    If I run a query, then later you run the same query, that doesn't mean that you get the same access path. I may have added an index in between. Or maybe stats were recalculated.

    For every execution of a sql statement, oracle must parse the sql to determine that it is valid, check that all the objects exist, verify that I have access to them, and build a plan based on the current indexes and stats. Simple proof: write a stored procedure which executes a query inside a for loop (i.e., perform the exact same query 5 times), and has a wait statement after the query (dbms_lock.sleep), and use dbms_application_info.set_action to identify each execution of the loop. Now execute the procedure - after the 1st execution, drop an index used in the query; after the 2nd, add a new index (which you know it will use); after the 3rd, revoke a grant or drop a table. If you turned on trace, you can look at the dump file to prove that the same query, executed by the same transaction will have different plans. And, the final execution will result in an error (because the table was dropped). So, the same sql in the shared pool will NOT always use the same plan.

    And, "where 1=0" will still be executed before the full scans, even with cursor sharing. It may change the execution plan that gets built, but Oracle still knows that "where :"SYS_B_1" = :"SYS_B_2" " is comparing constants, and constants get evaluated first (of course, nothing is 100% with oracle, so complex sql, with subqueries, correlated suqueries and in-line views may still execute parts of the sql prior to evaluating the constants.

    In summary, cursor sharing save memory, not time (unless you consider the "1 millisecond" it takes to place the sql in the shared pool).
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  11. #11
    Join Date
    Oct 2004
    Posts
    145
    Only time I have seen this really become an issue is if your shared pool is sized incorrectly and your sql statement is large rather than the simple example given.

    System may thrash looking for that free space to place the SQL stement into the pool. I now this is not common but have seen it first hand with vendor that created application based on object oriented programming that produced massive (ugly) sql with absolutely no bind variable.

    Typically, as many have pointed out, time you save is in milliseconds. But as programmers perspective, if you use bind variable, would it not be a reusable code (sub routine concept) rather than repeating sql with hard coded values? ^.^

  12. #12
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    shoblock, I think your explaination isnt quite correct, using bind variables can make a big difference in performance and scalability (not just memory usage) in OLTP apps as Mark has already pointed out. Your example with the changed plan is due to the fact that Oracle will verify the objects referenced by your sql have not changed since the last time they were executed. If they have it does a hard parse. Also dont rely on cursor_sharing=similar/force as it doesnt always behave in the way you expect and can cause unexpected problems (atleast in 8i/9i).

    This URL goes into more detail
    http://asktom.oracle.com/pls/ask/f?p=4950:8:1320218815388063256::NO::F4950_P8_DISPL AYID,F4950_P8_CRITERIA:2588723819082,

    Alan
    Last edited by AlanP; 12-15-04 at 05:45.

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by jortiz
    SELECT COL1, COL2, COL3
    FROM A,B
    WHERE A.ID=B.ID
    AND A.NAME='A';

    But Somebody told me that IT would be better if I use a variable...
    It depends. Is this query hard-coded into the application so that the value is always 'A', or is it constructed dynamically with 'A' being just one of many values that get used.

    1) Value always 'A' => stick with 'A'

    2) Value changes => use a bind variable

    You say you don't know how to create a bind variable. What language are you writing this in?

  14. #14
    Join Date
    Apr 2004
    Posts
    246
    Yes, I agree that bind variables can make a difference with oracle maintaining memory, especially if the shared_pool is too small. But lets assume that you've allocated enough memory to the shared pool. The difference between using and not using bind variables is very small for a given statement. And people constantly overstate the benefits - suggesting that oracle doesn't parse the statement at all if it matches an existing statement. That is just wrong - hard or soft parse, it still gets parsed, validated, optimized and on and on. The timing difference is neglible, but the memory difference is the important one.

    HOWEVER, I always use bind variables. Not because it makes anything faster, but because it's the better way to code. It makes for reusable code - and let's face it, if you write the same statement twice, with one small difference (e.g., col = 1 vs col = 2), you run the risk of someone changing one statement and not the other. Also, for web apps, bind variables help to prevent sql intrusion. And, I put cursor_sharing=force in my db's, so when others forget to use bind variables, it helps to prevent memory problems in the shared pool.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  15. #15
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by shoblock
    That is just wrong - hard or soft parse, it still gets parsed, validated, optimized and on and on. The timing difference is neglible, but the memory difference is the important one.
    The timing difference is a REALISTIC and important benefit. You are neglecting the plain fact that all (99%?) applications will not be single-user apps. The applications will be handling hundreds to thousands of simultaneus users and queries. What was once only a 10th or 100th of a second is now MANY seconds of wait time which will probably be unacceptable to any end user.

    Testing a query is fine, but many development areas miss the important area of scalability and multi-user testing. The more users, the more data, the more transactions, the more queries, the more waits.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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