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

Thread: SQL Hard Parsing : How to Analyze

  1. #1
    Join Date
    Feb 2001
    If a new SQL statement is issued which does not exist in the shared pool then this has to be parsed fully. Eg: Oracle has to allocate memory for the statement from the shared pool, check the statement syntactically and semantically etc... This is referred to as a hard parse and is very expensive in both terms of CPU used and in the number of latch gets performed.

    Hard parsing happens when the oracle server parses a query and cannot find an exact match for the query in the library cache. This occurs due to inefficient sharing of SQL Statements and can be improved by using bind variables instead of literals in queries. Some times Hard parsing causes excessive CPU usage

    To start analyzing your database first understand how your SGA is laid out. You can get the values from server manager by using the \"show sga\" command or by running a query \"select * from v$sgastat;\"

    A simple sql statement can be used to find the statements that are similar but are being parsed due to the use of Literals in the statement.

    \"select substr(sql_text,1,50), count(*) from v$sql
    group by substr(sql_text,1,50) having count(*) > 25\"

    The above query would return the statements that have the same first 50 characters and have more than 25 occurences in the shared pool. Adjust the lenght of the statement based on your application.

    You can now go back to your application team and if its a java application, encourage them to use prepared statements so that your Shared Pool is not always full. They may also consider using bind variables rather than explicitly specified constants in your statements whenever possible.

    Hope that helps you find problems that you never knew existed.

    Added 04/11/01
    Found this interesting feature of 8.1.6 on metalink. Enjoy


    [Edited by uday on 04-11-2001 at 12:06 PM]

  2. #2
    Join Date
    Jun 2001
    I don't really get it.

    I tried the sql statement on a database where I had a high parse to execute ratio and got a statement count up to 500.

    What is wrong with that statement that has a high parse count? Is it the use of literals or is it just that the statement is not entered the exact same way in different parts of the code?


  3. #3
    Join Date
    Mar 2002
    I agree with "mbe". I want to know how literal strings have poorer performance as compared to bind variabled. Uday, please explain us the intricasy.

  4. #4
    Join Date
    Mar 2002
    Originally posted by dbafreak
    I agree with "mbe". I want to know how literal strings have poorer performance as compared to bind variabled. Uday, please explain us the intricasy.

    Ideally you would want the oracle to use prepared plans for similar queries so that we can save on the parse time which involves precious resources ( shared pool and latches ) ..

    But when we use string literals in a query .. each such query is assumed to be a different query and for each query parsing is done (costly) and then execution .

    So you should always try to use prepared statements whenever string literals are involved that way a same plan is used .

    Now there are some cases when you can not have prepared statement for example when the parameter is not just one but could be many .. ie someif in ( 'xyz','abc',...) so on ..

    When its the above case there are several options available in oracle 8.1.6 onwards where dynamic binding is possible .. (cursor_sharing option) in other words .. oracle binds the string literals for you ... now these options will have to be carefully used as each has its set of adv and disadv ..

    Hope it helps,

  5. #5
    Join Date
    Dec 2002

    Binding Variables and Stored Procedures using ADO

    I have been very careful to use a strong typing methodology for my cursors and procedures, however I am still getting stored procedure calls come up in the test:

    select substr(sql_text,1,20), count(*) from v$sql
    group by substr(sql_text,1,20) having count(*) > 100

    What I get looks like this:


    The RefCursor is always the same address, however in "PACCOUNTNBR=>'9791411'" the number changes.

    Any ideas?


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