Hard Parse or Soft Parse ?

10-11  Source: Network gathering  Views:0 

Advertisement
Hi Experts,
If we execute a SELECT statement in SQL*PLUS by passing the value using ampersand operator, whether hard parsing will occur or soft parsing ?
SQL> select last_name from employees where department_id = &dept_no;
Enter value for dept_no: 100
old   1: select last_name from employees where department_id = &dept_no
new   1: select last_name from employees where department_id = 100
SQL> select last_name from employees where department_id = &dept_no;
Enter value for dept_no: 110
old   1: select last_name from employees where department_id = &dept_no
new   1: select last_name from employees where department_id = 110
When I am executing V$SQL, for each different value I am seeing different SQL_ID. Please explain on this.
Many thaks in advance for your help.
Cheers,
Suri
hi ,
the best way to check for for hard parse and soft parse is by querying the data dictionar view v$sqlarea . There is a column called parse_counts(not sure of the name). the moment you execute a query , a row is reflected in this view.
for eg , when you query
select * from emp where empno=101,
select * from emp where empno=102,
select * from emp where empno=103,
query the v$sqlarea  with a filter predicate on the sql_text . i.e  select * from v$sqlarea where sql_text like  '%select * from emp %'.
And then try querying the emp table using bind variable,
select * from emp where empno=:x; -- pass 101 ,102,103
No new rows are formed in the v$sqlarea,rather the parsed_count keeps on increasing , contrary to the above queries(without using bind variables in which each query is new to the parser).
@Gurus : in 11g , i  have observed that all queries are converted to make you of bind variables .I knew that this was the implicit behaviour of plsql ,but now have they enhanced the sql engine too ?
Thanks
Rahul
Related articles
  • Hard Parse or Soft Parse ? 10-11

    Hi Experts, If we execute a SELECT statement in SQL*PLUS by passing the value using ampersand operator, whether hard parsing will occur or soft parsing ? SQL> select last_name from employees where department_id = &dept_no; Enter value for dept_no:

  • Low Execute to Parse % and high soft parse % 11-30

    Hello Folks I am working on oracle 10g release2 on HP-UX After going through awr reports observed it have low Execute to Parse % but high soft parse % (Instance Efficiency Percentages) so cannot say issue with less use of bind variables,then what is

  • Can I change from hard cover to soft cover after the book is designed? 11-30

    Can I change from hard cover to soft cover after the iphoto book is desgned?Yes,  Just go to the View all pages window and click on the Change Theme button.  There you will be able to switch size and type of cover.  HOWEVER, just to be on the safe si

  • Hard Cover To Soft Cover Order ? 11-30

    Hi Is there a simple way to change my order (before I send it off) from a hard cover choice to a soft cover option ? I thought I had selected soft cover in the first place but I'm only getting a hard cover option when I click to buy book. Thanks for

  • How to parse using DOM parser: 11-30

    <employee>      <name>phaneendra</name>      <age>21</age>      <company>ABC</company>      <Authorization>           <ID>21<ID>           <Role>Normal</Role>      </Authorization&

  • Can't use DTD without a "file:" while parsing with SAX2 parser 10-11

    Hello, I'm using JDK 1.4 with JAXP parser. I'm trying to parse an xml document that use a dtd. The DTD is in same dir as the xml file. If i use a "file:./" before mydtd.dtd like this : <!DOCTYPE doc SYSTEM "file:./mydtd.dtd"> It

  • Syntax Error with JSON.Parse method to parse SharePoint List Items 10-11

    Hi All, I want to get SharePoint List data and bind that retrived data to the JQuery Grid Control. For this I used SPServices to get the SharePoint List data in SOAP Envelope. Now I need to parse the soap envelope and store the retrieved items in arr

  • C parser can't parser UTF-8 ?? 10-11

    Hello I'm working on Sun solaris with the version 2 C parser (2/21/00) I'm initailizing my parser with encoding set to (oratext*) 0 and calling "xmlparse" with encoding set to (oratext*) 0. So now I should be able to parse a message containing s

  • Parsing Error while Parsing XML file 10-11

    Hi I am trying to parse an xml file with use of an stylehseet as shown below but its giving an error message as follows [Fatal Error] myfile2.xml:1:8: The processing instruction target matching "[xX][mM][lL]" is not allowed. org.xml.sax.SAXParse

  • Xmlparser.parse fails when parsing xml with dtd 11-30

    Has anybody used successfully called xmlparser.parse in PL/SQL to parse an xml file with a dtd? When I try I get the following error: ERROR at line 1: ORA-20100: Error occurred while parsing: Invalid argument ORA-06512: at "SYS.XMLPARSER", line

  • Use of XML parser to read/parse xml stored in DB as CLOB 11-30

    Hello, I am new to XML technology. I have xml data stored in DB as CLOB. I need to parse this data to retrieve VALUE fro certain tags. I would greatly appreciate any help. Any examples that manipulate xml data stored in DB can be of extreme help. Des

  • JDOM parser fails to parse Huge Size Xml Files ??? Unable to Parse ??? 11-30

    Hi All, When i transformed or parsed many XML Files of huge size...I am getting java.lang.OutOfMemory error for all the huge xml files. It is working fine for files which is of small size. I've 2GB ram in my system. I have also set heapsize for the J

  • JRE1.5 swing.html parser fails to parse data between script tags 11-30

    Hi all... I've written a class that extends the java-provided default HTML parser to parse for text inside a table. In JRE1.4.* the parser works fine and extracts data between <script> tags as text. However now that I've upgraded to 1.5, the data be

  • SAX  ..crimson parser vs xerces parser..does changing parsers help 11-30

    Hello ...I relatively new to XML technology.My question is does changing parsers from say crimson to xerces(or such...) improove the performance of an application.Our application is faced with a performance problem and I was wondering if changing par

  • Proving prepared statements are being reused 10-11

    How can I prove that a prepared statement from a JDBC driver is being reused / pooled ? What essentially I want to do is to have prepared statements reused to reduce hard parsing ... but I also need to prove that the statements are being reused. If I

  • Help needed 10-11

    Help needed 1. When we use USING clause in execute immediate does it hard parse ? 2. Is it better to use values stored in SYS_CONTEXT or to use USING clause in Execute Immediate ? 3. In Dynamic SQL, if i use SYS_CONTEXT does it Hard Parse or Soft Par

  • Ref Cursor over Implicit and explicit cursors 10-11

    Hi, In my company when writing PL/SQL procedure, everyone uses "Ref Cursor", But the article below, says Implicit is best , then Explicit and finally Ref Cursor.. [http://www.oracle-base.com/forums/viewtopic.php?f=2&t=10720] I am bit confuse

  • High library cache load lock waits in AWR 10-11

    Hi All, Today i faced a significant performance problem related to shared pool. I made some observations, thought it would be a nice idea to share them with Oracle experts. Please feel free to add your observations/recommendations and correct me wher

  • How to pass a list as bind variable? 10-11

    How can I pass a list as bind variable in Oracle? The following query work well in SQL Developer if I set ":prmRegionID=2". SELECT COUNTRY_ID, COUNTRY_NAME FROM HR.COUNTRIES WHERE REGION_ID IN (:prmRegionID); The problem is that I can't find how

  • Bind variable and parse_call 10-11

    if i use a bind variable in sql will it reduce the values of column parse_calls in v$sqlarea? i am using bind variable in the following statement declare y number; begin :x:=101; select salary into y from hr.employees where employee_id=:x; end; but s