Home » SQL & PL/SQL » SQL & PL/SQL » AWR Report SQL with Bind Variables (Oracle Version 12.1)
AWR Report SQL with Bind Variables [message #678014] Thu, 31 October 2019 07:47 Go to next message
kohila
Messages: 2
Registered: October 2019
Junior Member
We have AWR Report have different sql id for the same SQL since it seems different in space near BIND VARIABLES.

We have checked in Application team on passing Bind variables, there is no difference. Why this happens. Can anyone please clarify

BEGIN RATING_PKG.GET_CAP_AGREEMENT_RATE(:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 ); END;

BEGIN RATING_PKG.GET_CAP_AGREEMENT_RATE(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31); END;

Similarly other queries also.

We have enabled the CURSOR SHARING to FORCE also.
Re: AWR Report SQL with Bind Variables [message #678015 is a reply to message #678014] Thu, 31 October 2019 07:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: AWR Report SQL with Bind Variables [message #678016 is a reply to message #678015] Thu, 31 October 2019 08:03 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've got whitespace differences
Re: AWR Report SQL with Bind Variables [message #678023 is a reply to message #678014] Thu, 31 October 2019 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
there is no difference

This is not right.

Quote:
We have enabled the CURSOR SHARING to FORCE also.

This has no impact on your issue.
And it is wrong to set this parameter to FORCE (unless you have bad code which you should fix instead of setting this parameter).

Re: AWR Report SQL with Bind Variables [message #678041 is a reply to message #678023] Fri, 01 November 2019 08:43 Go to previous messageGo to next message
kohila
Messages: 2
Registered: October 2019
Junior Member
Can we make ignore spaces and make sqlid same.
Re: AWR Report SQL with Bind Variables [message #678042 is a reply to message #678041] Fri, 01 November 2019 09:35 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I doubt it.
Why do you care?
Re: AWR Report SQL with Bind Variables [message #678043 is a reply to message #678041] Fri, 01 November 2019 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
kohila wrote on Fri, 01 November 2019 14:43
Can we make ignore spaces and make sqlid same.
No.

Re: AWR Report SQL with Bind Variables [message #678062 is a reply to message #678016] Tue, 05 November 2019 10:03 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
cookiemonster wrote on Thu, 31 October 2019 09:03
You've got whitespace differences
exactly,
oracle will check the hash of your statement. If it is the same it will use the same stored plan, if there are ANY differences it will generate another one. remove the spaces for both sql statements (except for after BEGIN) and they will share the same entry

[Updated on: Tue, 05 November 2019 10:03]

Report message to a moderator

Re: AWR Report SQL with Bind Variables [message #678069 is a reply to message #678062] Wed, 06 November 2019 02:49 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
But given this is a procedure call there is only one plan - call the procedure.
Which is why I asked why the OP cared.
Previous Topic: ALTER TABLE STATEMENT FOR CLUSTER
Next Topic: Ajuda com procedure Type Pipilined
Goto Forum:
  


Current Time: Thu Mar 28 11:43:33 CDT 2024