Home » RDBMS Server » Performance Tuning » hard parse for SQL (merged)
hard parse for SQL (merged) [message #424505] Fri, 02 October 2009 15:07 Go to next message
casttree
Messages: 83
Registered: August 2008
Member
I tried the following SQL statement which insert the random data into test table. I can see a lot of hard parse from SQL. Since I use the variable in SQL, should it be use same execution plan? What is the main reason for hard parse in this case?

Thanks,



declarel_number number;
begin  for i in 1..1000000  
loop  
l_number := dbms_random.random; 
execute immediate 
'insert into test_table values ( ' || l_number || ',' ||  l_number || ',' || l_number || ',' || l_number || ')';    
end loop;  
commit;
end;

[Updated on: Sat, 03 October 2009 01:42] by Moderator

Report message to a moderator

Re: hard parse for SQL [message #424507 is a reply to message #424505] Fri, 02 October 2009 15:39 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
Oracle's internal behavior differs based upon OS name & version
and Oracle version (SELECT * from v$version)
which you decide to avoid posting & ignore Posting Guidelines

see below for details
http://www.orafaq.com/forum/m/424505/136107/#msg_424505
Re: hard parse for SQL [message #424509 is a reply to message #424507] Fri, 02 October 2009 15:42 Go to previous messageGo to next message
casttree
Messages: 83
Registered: August 2008
Member
I can see the hard parse from different DB versions. For example , we can see it from Linux i386 and Oracle 10.2.0.4.
Re: hard parse for SQL (merged) [message #424536 is a reply to message #424505] Sat, 03 October 2009 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is an example taken from T. Kyte answer in AskTom, read the whole thread and he explained very well the reason of hard parse.

Anyway, the reason is quite clear, the statement is different each time.

Regards
Michel
Re: hard parse for SQL (merged) [message #424709 is a reply to message #424536] Mon, 05 October 2009 07:51 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm curious as to why you'd repost part of an AskTom question here?

If you want to avoid hard parses, then do as Tom suggests, and use bind variables, rather than building a different statement each time.
Previous Topic: Too many columns in one index
Next Topic: Histogram Issue
Goto Forum:
  


Current Time: Sat Oct 01 04:09:36 CDT 2016

Total time taken to generate the page: 0.25567 seconds