OraFAQ Forum: Performance Tuning » SQL

Home » RDBMS Server » Performance Tuning » SQL_Text on SQL Profile (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0)

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

SQL_Text on SQL Profile [message #677869]

Thu, 17 October 2019 08:40

wtolentino
Messages: 422
Registered: March 2005

Senior Member

our app developer group has this query that they run that is the same as the query text on the dba_sql_profiles view. however, it appears to be that the SQL profile is not being used by the optimizer. when i looked further the query that they run is different from the query text on the dba_sql_profiles. the only difference is the hints "/*+ INDEX ... */". the query text on the dba_sql_profiles does not have hints while the query that they are running has a hints.

should the query be exactly the same as the query text on the dba_sql_profile for that to be recognize by the optimizer (text sensitivity such as upper and lower case, white space etc.)?

thanks,
warren

[Updated on: Thu, 17 October 2019 08:54]

Report message to a moderator

Re: SQL_Text on SQL Profile [message #677870 is a reply to message #677869]

Thu, 17 October 2019 09:01

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

Senior Member
Account Moderator

Yes.

Report message to a moderator

Re: SQL_Text on SQL Profile [message #682944 is a reply to message #677869]

Sun, 22 November 2020 12:56

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

No. The signature has to be the same. If you created your profile with force_match => true then the force matching signature has to be the same. If you created your profile with force_match => false (it is the default) then the exact matching signature has to be the same.

In your case the hint makes the signatures different.

Report message to a moderator

Previous Topic:	DBMS_SQLTUNE.REPORT_SQL_DETAIL: html report issue
Next Topic:	ORA-12705: Cannot access NLS data files or invalid environment specified with jmeter test

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 07:45:41 CDT 2025