OraFAQ Forum: Performance Tuning » sql_id of a sql not the same in different databases

Home » RDBMS Server » Performance Tuning » sql_id of a sql not the same in different databases (Oracle 11.2.0.4 on Linux. )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql_id of a sql not the same in different databases [message #624022]

Wed, 17 September 2014 05:14

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

Report message to a moderator

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

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What is this exact sql?

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Answer my question if you want I answer yours.

Report message to a moderator

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

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.

Report message to a moderator

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

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?

Report message to a moderator