Thanks in advance for all the help the team provides, much appreciated!
I have a situation where the application I am working with utilizes GTTs to load data in to and filter SQLs. The problem is that in so doing the application is also injecting a cardinality hint into the SQL for the number of rows in the GTT. For example:
select *
from table1
where column1 in (select /*+ cardinality ( 150 ) */ gtt_column from gtt);
So applying a sql profile to that will work just fine but it won't catch the next incarnation of the SQL which could be
select *
from table1
where column1 in (select /*+ cardinality ( 200 ) */ gtt_column from gtt);
Are there any options to account for this aside from application code changes?
Thanks !
Simple test case showing unique SQL IDs
create global temporary table gtt_test (column1 number) on commit delete rows;
create table perm_test as
select level as column1 from dual connect by level <= 1000;
insert into gtt_test select level from dual connect by level <= 10;
select * from perm_test where column1 in (select /*+ cardinality (1) */ * from gtt_test);
select * from perm_test where column1 in (select /*+ cardinality (2) */ * from gtt_test);
select sql_text, sql_id, executions from v$sql where parsing_schema_name = user and sql_text like '%cardinality%'
order by first_load_time desc;
Not that I'm aware of. The force matching property of SQL profiles only works for literals, not hints.
Note that, in general, you'll want queries with different cardinality hint values to use different plans. And thus different profiles. A query with cardinality ( 1 ) is much more likely to benefit from an index than cardinality ( 99999 ).
When you upgrade to a recent release it's worth investigating session stats for GTTs. These may allow you to ditch the hints and profiles altogether.
https://oracle-base.com/articles/12c/session-private-statistics-for-global-temporary-tables-12cr1