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 |
|
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 #678062 is a reply to message #678016] |
Tue, 05 November 2019 10:03 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
cookiemonster wrote on Thu, 31 October 2019 09:03You'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
|
|
|
|
Goto Forum:
Current Time: Sat Jun 15 17:32:57 CDT 2024
|