ba6.us - Dave's Database Related Stuff

The following SQL Profile adds a FIRST_ROWS every time it sees the query specified by SELECT...%. You will need to update the WHERE Clause of the SELECT below. Verify that it returns the SQL you are expecting and that the query only returns one row.

Obtain the SQL_ID of the SQL you would like to create a profile for:

It is easier to obtain the SQL_ID instead of trying to cut and paste the text of the whole statement. Often the SQL text is too cumbersome to cut and paste reliably, so why not grab it from the sqlarea?

  SELECT sql_id, 
         sql_text 
  FROM gv$sqlarea 
 WHERE sql_text LIKE 'SELECT...%';

Implement the profile:

DECLARE
  cl_sql_text CLOB;
BEGIN

  SELECT sql_text 
  INTO cl_sql_text 
  FROM gv$sqlarea where sql_id = '&myid.';

  DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => cl_sql_text, 
    profile => sqlprof_attr('FIRST_ROWS'), 
    name => 'FIRST_ROWS_TEST', 
    category => 'DEFAULT',
    force_match => TRUE);

end;
/

Notes

force_match : Ignores literals, allows SQL Profile to be applied to multiple versions of the same statement. This can be likened to the CURSOR_SHARING instance parameter which allows the optimizer to replace literals with bind variable placeholders. This allows the a single SQL Profile to be used for SQL statements with the same syntax but are submitted with literal values instead of Bind Variables. sqlprof_attr can take any hint as input. In this case I kept it simple but you can take the text from between the /*+ ... */ hint separators and paste it into the sqlprof_attr parameter.