Home » SQL & PL/SQL » SQL & PL/SQL » hard parsing (10g)
hard parsing [message #438539] Sun, 10 January 2010 13:54 Go to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
hi all,

just want to clear one thing.

if we use a execute immediate 'insert into table as select * from table where name = 'abc' '

note that we are not using any bind variables...
will oracle do the hard parsing for this statment. even we are using it in execute immediate?



secondly want to know how to avoid the hard parsing for dynamic sql statment. actualy i have one sql statment which is using many varialbes and i m returing a ref cursor. how to avoid the hard parsing. i can not bind the varialbes as i dont know how many and which are coming on run time.

thanks
Re: hard parsing [message #438540 is a reply to message #438539] Sun, 10 January 2010 14:11 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>secondly want to know how to avoid the hard parsing for dynamic sql statment.
In order for SQL to be executed, it needs to be hard parsed the first time.
This can not be avoided.
Re: hard parsing [message #438541 is a reply to message #438539] Sun, 10 January 2010 14:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ With dynamic SQL you ALWAYS parse but not always HARD parse, read Parsing

2/
Quote:
i can not bind the varialbes as i dont know how many and which are coming on run time.

Yes, you can with DBMS_SQL, read:
Database Application Developer's Guide - Fundamentals
Chapter 8 Coding Dynamic SQL
Section Choosing Between Native Dynamic SQL and the DBMS_SQL Package

Regards
Michel

[Updated on: Sun, 10 January 2010 14:41]

Report message to a moderator

Re: hard parsing [message #438559 is a reply to message #438541] Sun, 10 January 2010 22:22 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
Micheal,

I know about that binding but my problem is i have to return the results in ref cursor but dbms_sql returns the handler of the cursor.


execute immediate 'insert into table as select * from table where name = 'abc' '


will the above query will be hard parsed? if not then i m lookin to do the insertion in temp table first and then do the

"open ref cursor for select * from temp table". 


i guess that would avoid the hard parse?
Re: hard parsing [message #438561 is a reply to message #438539] Sun, 10 January 2010 23:01 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
secondly want to know how to avoid the hard parsing for dynamic sql statment. actualy i have one sql statment which is using many varialbes and i m returing a ref cursor. how to avoid the hard parsing. i can not bind the varialbes as i dont know how many and which are coming on run time.


you can actually do that

http://www.oracle.com/technology/oramag/oracle/09-jul/o49asktom.html

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=2369029908548088969


Quote:
In order for SQL to be executed, it needs to be hard parsed the first time.


AND If you use bind variables the query will be soft parsed all other times except the first time.

[Updated on: Sun, 10 January 2010 23:14]

Report message to a moderator

Re: hard parsing [message #438573 is a reply to message #438561] Mon, 11 January 2010 00:52 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
thanks ayush_anand .. these links realy help.
Re: hard parsing [message #438576 is a reply to message #438559] Mon, 11 January 2010 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I know about that binding

It seems not as you say if you don't know the number of variables you can't use dynamic SQL and this is wrong. Read the link I provided.

Quote:
will the above query will be hard parsed?

Did you:
1/ read my first sentence?
2/ read the first link I provided?

Quote:
is i have to return the results in ref cursor but dbms_sql returns the handler of the cursor.

You can't do it with "execute immediate".
If this is the issue you try to solve why don't you post this as question?
Explain in details what is your problem and what you try to achieve.

Regards
Michel

[Updated on: Mon, 11 January 2010 01:16]

Report message to a moderator

Re: hard parsing [message #438587 is a reply to message #438576] Mon, 11 January 2010 02:03 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
Thanks for the reply Micheal. and sorry for the confusion.

basicaly my problem is to avoid the hard parsing.

I have a dynamic sql statment which i m opening with ref cursor and return the results.

this sql statment includes around 10 parameter. which may come as blank also.

Now as this query runs every time with difernt literals it do the hard parse every time and database crash down when the load increase.

What i need is to some how avoid the hard parsing. binding variable and dbms_sql is one technique but it doesnt seems to be feasible as i have to return the results in ref_cursor.(11g provide API for dms_sql cursor to ref cursor conversion but we are having 10g)

AYUSH ANANAD has given some suggestion and links i m trying to implement those.

if you have some suggestion regarding this also please tell me.

hope this would clear the requirements now.

Re: hard parsing [message #438590 is a reply to message #438587] Mon, 11 January 2010 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tom Kyte's Oracle Magazine article gives a wrap-up of the ways you can do it. There is nothing I can add to this.

Regards
Michel
Re: hard parsing [message #438617 is a reply to message #438587] Mon, 11 January 2010 03:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The details of the best solution depend a lot on the details of your problem.

I have had similar problems to this, where users can fill in a variable number of fields on a Search page, and need to have a ref-cursor containing the matching records returned.
What I found out was that there were a small number of combinations of search fields that accounted for over 95% of the searches - this meant that by explicitly hard coding these queries I was able to reduce the amount of dynamic SQL used, and increase the performance.

As for the remaining 5% of cases, you can build up a query that always contains 10 bind variables so that you can execute it from a single point - something like this:
v_sql := 'SELECT *
FROM  table
WHERE 1=1';
IF bind_value_1 is not null then
  v_sql := v_sql||'AND table.column1 = :b1';
else
  v_sql := v_sql||'AND nvl(:b1,'X') = 'X';
end if;

IF bind_value_2 is not null then
  v_sql := v_sql||'AND table.column2 = :b2';
else
  v_sql := v_sql||'AND nvl(:b2,'X') = 'X';
end if;
....

execute immediate v_sql using bind_Value_1,bind_value_2,...


The IF-ELSE-END IF clauses guarantee that you'll always have the correct number of bind variables in the query is always the same.
Re: hard parsing [message #438625 is a reply to message #438617] Mon, 11 January 2010 04:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I agree on the 95% solution (if-then-else with predefined static SQL), but for the alternative, I tend to use sys_context. Very flexible and (in my view) very readable and maintainable.
Re: hard parsing [message #438632 is a reply to message #438625] Mon, 11 January 2010 05:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How would you use sys_context for this?
Re: hard parsing [message #438644 is a reply to message #438632] Mon, 11 January 2010 06:44 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read T. Kyte's article (first link in ayush_anand's post). Smile

Regards
Michel

[Updated on: Mon, 11 January 2010 06:45]

Report message to a moderator

Previous Topic: how to find column name having particular value
Next Topic: Constraint and Trigger
Goto Forum:
  


Current Time: Fri Dec 02 16:33:11 CST 2016

Total time taken to generate the page: 0.39701 seconds