Home » RDBMS Server » Performance Tuning » Its confusing me lot.... (11g, AIX)
icon3.gif  Its confusing me lot.... [message #612061] Fri, 11 April 2014 10:31 Go to next message
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
Confused

[Updated on: Fri, 11 April 2014 10:32]

Report message to a moderator

Re: Its confusing me lot.... [message #612064 is a reply to message #612061] Fri, 11 April 2014 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59785
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL_ID is based on the text including spaces and new lines and is case sensitive.

Re: Its confusing me lot.... [message #612068 is a reply to message #612064] Fri, 11 April 2014 11:03 Go to previous messageGo to next message
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 #612073 is a reply to message #612068] Fri, 11 April 2014 11:46 Go to previous messageGo to next message
BlackSwan
Messages: 23054
Registered: January 2009
Senior Member
>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?
Re: Its confusing me lot.... [message #612077 is a reply to message #612068] Fri, 11 April 2014 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59785
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
Roachcoach
Messages: 1224
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;
over the top of a regular OLTP load will very likely do it. Though sometimes I've seen internal recursive stuff force it too. It's very annoying to be honest.

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).
Previous Topic: enq: TM - contention
Next Topic: Can I instruct Oracle 11g to deliberately delay query completion?
Goto Forum:
  


Current Time: Wed Nov 26 04:22:22 CST 2014

Total time taken to generate the page: 0.07346 seconds