 Its confusing me lot.... [message #612061] |
Fri, 11 April 2014 10:31  |
harpreetsinghkup
Messages: 52
Registered: May 2006
Location: Mumbai
|
Member |
|
|
Hello Experts,
I was doing small exercise to understand, when oracle creating new sql_id for the query, which is having same result set. Could you please help me to understand the concept here?
I am using my standard query "SELECT * FROM DUAL" in diffrent cases and getting new/existing sql_ids. Thus your guidance will help me to understand the magic here.
sql_id can be diffrent when you will run these query at your end :D:D:D
Test case:
select * from dual
SQL_ID :- a5ks9fhw2v9s1
SELECT * FROM DUAL
SQL_ID :- 9g6pyx7qz035v
SELECT * FROM dual
SQL_ID :- 3vjxpmhhzngu4
select * from DUAL
SQL_ID :- 1tqvcggcs4f1x
select *
from dual
SQL_ID :- 0g6vrn56vh7qu
select * from
dual
SQL_ID :- 704y29dd90gbn
select
* from dual
SQL_ID :- 65yd2m752a5wj
select
*
from
dual
SQL_ID :- bqwn109bbhamu
select
*
from dual
SQL_ID :- fx0h82wcsbacg
select
*
from DUAL;
SQL_ID :- c8zvuv0ba983d
[Updated on: Fri, 11 April 2014 10:32] Report message to a moderator |
|
|
|
|
|
| Re: Its confusing me lot.... [message #612068 is a reply to message #612064] |
Fri, 11 April 2014 11:03  |
harpreetsinghkup
Messages: 52
Registered: May 2006
Location: Mumbai
|
Member |
|
|
Thanks Michel,
Is this meaning, oracle does not using any intelligence to club these type of statements and create a single sql_id, so that hard parsing can be avoided.
I was just thinking, Please correct, if i am wrong.
Sometime oracle programmers are not giving much attention to, write queries, even for smaller one. which causes hard parsing itself and impacting performance of application.
During development of software, if we can consider this, can we avoid hard parsing?
Waiting for your valuable comments here..
|
|
|
|
|
|
| Re: Its confusing me lot.... [message #612077 is a reply to message #612068] |
Fri, 11 April 2014 12:17 |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:oracle does not using any intelligence to club these type of statements and create a single sql_id
No, it is just a hash value, it comes far before any syntax and semantic analysis. Oracle must know if the statement has already been analysed before it analyses it otherwise it is meaningless. The first purpose of sql_id is to not parse again.
And for bad programmers, if they do not follow the programming standard fire them. If there is no programming standard, fire the project leader.
[Updated on: Mon, 14 April 2014 02:30] Report message to a moderator |
|
|
|
| Re: Its confusing me lot.... [message #612177 is a reply to message #612073] |
Mon, 14 April 2014 02:29 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
BlackSwan wrote on Fri, 11 April 2014 17:46>which causes hard parsing itself and impacting performance of application.
post reproducible test case which shows the performance impact.
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Yes, hard parse consume resources; but is it large enough that you can actually measure it?
If you eliminated every hard parse, how much faster would the application actually be?
It's not so much the application, but the database. I've seen DBs with manual memory management ignore that and resize the pools when there is sufficient pressure on the shared pool (and it's already BIG).
Something like an automated dump of select * from accounts where account = &number;
I agree the difference to the app is liable to be negligible, but it creates headaches for the DBAs (usually such resizes cant be fixed without a bounce).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
