Home » RDBMS Server » Performance Tuning » sql_id of a sql not the same in different databases (Oracle 11.2.0.4 on Linux. )
sql_id of a sql not the same in different databases [message #624022] Wed, 17 September 2014 05:14 Go to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi,

I notice one strange thing: For the exact same sql, the sql_id is different in development versus that in Production. Is this expected? I thought that the sql_id would remain the same.


Thanks,
OrauserN
Re: sql_id of a sql not the same in different databases [message #624025 is a reply to message #624022] Wed, 17 September 2014 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is this exact sql?

Re: sql_id of a sql not the same in different databases [message #624027 is a reply to message #624025] Wed, 17 September 2014 05:27 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi Michel,

It is an application sql. This sql , which is exactly same in terms of its text has a different sql_id in 2 databases. Is that expected?

Thanks.
Re: sql_id of a sql not the same in different databases [message #624029 is a reply to message #624027] Wed, 17 September 2014 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Answer my question if you want I answer yours.

Re: sql_id of a sql not the same in different databases [message #624031 is a reply to message #624029] Wed, 17 September 2014 05:59 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi Mike,

I tried with another simple sql and see the same behavior...here it is:

variable v_user varchar2(16);
exec :v_user :='ABC';

select count(*) from all_users
where username=:v_user;


In dev. db it has this sql_id: 6sk5f7art67gc
In Prod. db is has this sql_id: 5m20vfx6j0f7p

Shouldn't it have remained the same? Note that the Oracle version is the same across dev. and prod.

Thanks.

Re: sql_id of a sql not the same in different databases [message #624032 is a reply to message #624031] Wed, 17 September 2014 06:06 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
I am sorry! I just realized that there was a one more blank line in sql I tested in prod. versus dev. I thought that doesn't matter but it seems that blank lines as well matter?

Re: sql_id of a sql not the same in different databases [message #624033 is a reply to message #624032] Wed, 17 September 2014 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

All characters matter. It is a hash value for the string, so any minor change (for us) is a complete different string for Oracle.

Re: sql_id of a sql not the same in different databases [message #624035 is a reply to message #624033] Wed, 17 September 2014 06:21 Go to previous message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thank you Mike!
Previous Topic: High insertion on the database | Tuning parameters
Next Topic: Query requires tuning
Goto Forum:
  


Current Time: Thu Mar 28 04:41:54 CDT 2024