Skip to Main Content
  • Questions
  • Applying profiles to identical yet sadly unique SQLs

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tyler.

Asked: February 25, 2021 - 4:44 pm UTC

Last updated: March 02, 2021 - 2:01 am UTC

Version: 11.2.0.4

Viewed 100+ times

You Asked

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;


and we said...

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

Rating

  (1 rating)

Comments

Tis what I thought

Tyler Forsyth, March 01, 2021 - 7:15 pm UTC

Thanks Chris, appreciate the insight!

I don't think the session stats will be an option either since the application is utilizing the same SQLs for OLTP and batch processes (just a matter of volume in the GTTs). I was under the impression stats would need to be gathered with each load in order to accommodate such a situation; if I am wrong please let me know. I don't think we'd want to suffer the performance impact of gathering stats with the OLTP queries.

For now my best recommendation to the application team has been to try slot the cardinality values into buckets (1-5000, 5001-25000, 25001-100000 for example) and utilize that for the cardinality hint meaning we will have a low/med/high setting (so we have a max 3 variants of the SQL). That should cater to the varying volumes of data in the GTTs while minimizing the SQLs in the shared pool and allow for some semblance of plan management.

As always if you have better suggestions I'd love to hear them.

Thanks again!
Connor McDonald
March 02, 2021 - 2:01 am UTC

You could explore dynamically changing the SQL to perhaps remove the hints or add your hint.

Here's a video demo of that (which in this case was for changing date references) but the same principle applies for finding/changing SQL


More to Explore

Performance

Get all the information about database performance in the Database Performance guide.