Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, subrata.

Asked: May 28, 2023 - 4:29 am UTC

Last updated: June 02, 2023 - 3:13 am UTC

Version: 19c

Viewed 1000+ times

You Asked

SQL-Profile fundamentally are additional optimizer statistics at the SQL statement level generated by SQL-Tuning-Advisor to fill the gaps of Oracle Optimizer.
My question is, can these additional finer optimizer-statistics within profiles, be shared/used by other similar SQL statements. Or is it that only that specific SQL for which the sql-profile was generated benefits?

and Connor said...

Yes - these hints are supported even outside a SQL profile, eg

SQL> set autotrace traceonly explain
SQL> select *
  2  from t
  3  where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2290 |   315K|   474   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  2290 |   315K|   474   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='SYS')

SQL>
SQL> select /*+ opt_estimate(@"SEL$1", TABLE, "T"@"SEL$1", SCALE_ROWS=2) */ *
  2  from t
  3  where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4579 |   630K|   474   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  4579 |   630K|   474   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='SYS')



MOS note 1955195.1 has a nice little script to assist here.

But I stress - the optimal number of hints in your code base is...zero. Look at things like optimizer stats, histograms, extended states, sql plan management etc before resorting to hints



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

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